COPY from Stage, Never Row Inserts, and the Two Billing Models
Warehouses ingest by pointing COPY at a stage of files in object storage and loading them in parallel across slices. Then the money: per-TB-scanned and per-second-compute price the same workload very differently.
How data actually gets into a warehouse
You do not stream rows into a warehouse one at a time. You write files to object storage, point the warehouse at that prefix, and issue one bulk load command. Redshift calls it COPY, Snowflake calls it COPY INTO from a stage, and the shape is the same everywhere:
- Producer writes filescompressed Parquet or CSV into a stage prefix
- COPY commandone statement names the prefix, not the rows
- Slices read in parallelroughly one file per slice at a time
- Columnar blocks writtenone commit for the whole load
- Table is queryableloaded and sorted in place
The heuristic worth remembering: give the load at least as many files as the cluster has slices. A single enormous compressed file cannot be split across slices, so one 40 GB gzip or Parquet file loads on one slice while the rest idle. Uncompressed delimited files are the exception, because Redshift can split those across slices, and that exception is a good detail to have ready when an interviewer pushes on the rule. Many evenly sized files, ideally a multiple of the slice count, keep everyone working either way.
Why row-by-row INSERT is the classic anti-pattern
Ten thousand separate INSERT statements do not just cost ten thousand round trips. Each statement is its own transaction, so the warehouse pays a commit per row, and each commit has to write columnar blocks. Columnar storage is built to be written in large sorted batches, so a one-row write touches every column's block structure to add a single value. The result is a load that runs orders of magnitude slower than the same rows delivered as files, and leaves the table fragmented on top of it.
The exercises measure that gap directly from a load log.
The two billing models
Warehouse pricing splits into two families, and the same workload can be cheap on one and ruinous on the other.
| billing model | you pay for | cheap when | expensive when |
|---|---|---|---|
| per TB scanned | bytes the query reads | narrow, partitioned, pruned queries | a dashboard rescans a huge table hourly |
| per compute second | seconds a warehouse runs | short bursts on an idle-suspending cluster | a cluster left running while nobody queries |
Per TB scanned is Athena's model: $5.00 per terabyte read, with a 10 MB minimum charged per query. You pay for bytes touched, not rows returned, which is why converting to Parquet and partitioning is the single most repeated cost fix in this field. The 10 MB minimum also means thousands of tiny queries are not free, they are 10 MB each.
Per compute second is the Snowflake and Redshift Serverless model: you rent a sized cluster and pay for the time it is awake, regardless of how much or little it reads. Here the lever is not bytes, it is idleness and concurrency. An auto-suspend setting saves more money than any query rewrite. That family has its own floor to match the scan model's: a warehouse that resumes bills a 60-second minimum even if the query takes two seconds, which is why a trickle of tiny queries against an auto-suspending warehouse costs far more than the work implies.
The interview version of this is a two-part answer: name which model the engine uses, then name the lever that model responds to.
Common mistake: assuming a query that returns three rows is cheap. On a scan-priced engine SELECT COUNT(*) over an unpartitioned CSV table reads every byte in the table and bills for all of it. The result size has nothing to do with the bill.
Interview nuance: "why is inserting 10,000 rows one at a time slow in a warehouse" is a favorite trap, and the answer is commit-per-statement plus a columnar rewrite per commit, versus one parallel bulk load with a single commit. Follow it with "so I would stage the rows as files and COPY them" and you have given the fix as well as the diagnosis.
On a real platform this differs. The seeds here flatten two real logs: Redshift's
STL_LOAD_COMMITS(one row per file per slice, with the actual load timings) and Snowflake'sCOPY_HISTORYview. Billing comes from a cost-and-usage export rather than a tidy per-query table, and real per-TB pricing varies by region. The arithmetic you practice is the arithmetic those exports need.
CREATE TABLE load_history (
load_id INTEGER,
target_table TEXT,
load_method TEXT, -- copy_from_stage | single_row_insert
files_loaded INTEGER, -- files in the stage prefix, 0 when the load was row by row
rows_loaded INTEGER,
mb_loaded INTEGER,
duration_s REAL
);
INSERT INTO load_history (load_id, target_table, load_method, files_loaded, rows_loaded, mb_loaded, duration_s) VALUES
(1, 'fact_trips', 'copy_from_stage', 16, 5200000, 3840, 42.0),
(2, 'fact_payments', 'copy_from_stage', 16, 3100000, 1280, 27.5),
(3, 'fact_ratings', 'copy_from_stage', 8, 4800000, 1520, 39.0),
(4, 'dim_driver', 'copy_from_stage', 4, 42000, 12, 2.5),
(5, 'fact_sessions', 'copy_from_stage', 16, 2600000, 960, 21.0),
(6, 'fact_trips', 'single_row_insert', 0, 12000, 9, 10.5), -- 12,000 separate INSERTs
(7, 'fact_payments', 'single_row_insert', 0, 8000, 4, 6.5),
(8, 'dim_driver', 'single_row_insert', 0, 3000, 1, 2.5);-- Every load, fastest first. Notice which method never gets above a few thousand rows a second.
SELECT load_id, target_table, load_method, files_loaded, rows_loaded, duration_s,
ROUND(rows_loaded / duration_s, 0) AS rows_per_second
FROM load_history
ORDER BY rows_per_second DESC;Apply
Your turn
The task this lesson builds to.
Write a query that returns each load method's throughput as (load_method, rows_per_second), fastest first, over load_history(load_id, target_table, load_method, files_loaded, rows_loaded, mb_loaded, duration_s).
Combine all the loads of a method: total rows_loaded divided by total duration_s, rounded to 2 decimals, ordered descending.
3 hints and 1 automated check are waiting in the workspace.
Practice
Make it stick
A second problem on the same idea, plus 2 bonus drills.
Write a query that returns each query's cost under both billing models as (query_id, scan_cost_usd, compute_cost_usd, cheaper_model), in query order, over query_billing(query_id, tb_scanned, compute_seconds).
Price the scan model at $5.00 per TB scanned and the compute model at $0.0004 per compute second, both rounded to 4 decimals. cheaper_model is the string 'scan' when the scan cost is strictly lower, otherwise 'compute'. Ignore the 10 MB minimum here; the medium drill below is where you apply it.
2 hints and 1 automated check are waiting in the workspace.