Chapter 8: Data Warehouses

"The warehouse did not get faster. It stopped reading the columns you did not ask for."

Overview

Chapter 7 ended with a specific problem: reading one column of six million rows on a row store reads all forty columns of all six million rows, and no index fixes it.

This chapter is that problem's solution, and the solution turns out to be worth more than the performance improvement it delivers — because it changed the economics of the field.

Two ideas do all the work. Columnar storage stores each column contiguously, so a query reads only the columns it names. Separated storage and compute decouples what you keep from what you spend to query it, so keeping data becomes nearly free and processing becomes elastic. Chapter 3 §3.3 asserted that the second one is why ELT displaced ETL; this chapter shows the mechanism.

The chapter is also unusually cost-focused, and for a specific reason. Warehouses are the largest line item in most data platforms, their pricing models are genuinely different from one another, and each pricing model rewards a different architecture. A design that is cheap on BigQuery can be expensive on Snowflake and vice versa, and engineers who learn one model and carry its habits to the other are a well-documented source of surprise invoices.

There is an honesty problem to handle up front. This book teaches with DuckDB, which is an excellent columnar engine and is not a cloud warehouse — it does not separate storage from compute, it has one writer, and it has no cost meter. Those are exactly the three properties this chapter is about. So §8.9 is explicit about what you cannot learn locally and how to reason about it anyway, rather than pretending the substitution is clean.

In this chapter, you will learn to:

• Explain how columnar storage changes query cost, and quantify it for a specific query against Kestrel's data.
• Describe the four encodings that make columnar compression effective, and predict which applies to a given column.
• Explain storage/compute separation and what it changed economically.
• Compare Snowflake, BigQuery, and Redshift on architecture, cost model, and — the useful part — the specific mistake each one invites.
• Compute a query's cost under per-second compute and per-byte-scanned models.
• Choose between clustering, partitioning, and materialization.
• State what DuckDB does not show you, and reason about it anyway.

Who needs this chapter: everyone. §8.7 in particular is the chapter that saves you from your first surprising bill.

8.1 What Makes a Warehouse Different

A data warehouse is a database optimized for reading a lot of rows and few columns, rather than writing a few rows with many columns. Five differences follow from that inversion.

The row without an obvious analogue is the index row. Warehouses generally do not have B-tree indexes, and new arrivals find this alarming. They do not need them, because a scan of a columnar table is cheap enough that indexed lookup is rarely the win — and because zone maps (§8.8) provide most of the skipping benefit at a fraction of the maintenance cost.

What a warehouse is bad at is worth stating with equal clarity, because people try:

• Single-row lookups. A warehouse will answer WHERE order_id = 88214 and it will be slower and more expensive than PostgreSQL. Warehouses are not application databases.
• High-frequency small writes. Inserting one row at a time is pathological on almost every warehouse. Batch or micro-batch.
• Enforced constraints. Most warehouses accept PRIMARY KEY and FOREIGN KEY declarations and do not enforce them. They are hints to the optimizer and documentation for humans. This surprises people badly — Chapter 23's tests exist partly because the database will not do this for you.

8.2 Columnar Storage

The layout

   ROW-ORIENTED (PostgreSQL heap)
   ┌──────────────────────────────────────────────────────────┐
   │ 88214│8841│2025-11-28│2│4995│499│362│... 33 more columns │
   │ 88215│2210│2025-11-28│1│2995│  0│217│... 33 more columns │
   │ 88216│8841│2025-11-28│5│1295│324│470│... 33 more columns │
   └──────────────────────────────────────────────────────────┘
   SUM(unit_price_cents) reads every byte above.

   COLUMN-ORIENTED (Parquet, Snowflake, BigQuery)
   ┌─────────────┐ ┌──────────┐ ┌──────────────┐ ┌─────┐ ┌──────────┐
   │ order_id    │ │ cust_id  │ │ placed_at    │ │ qty │ │ price    │
   │ 88214       │ │ 8841     │ │ 2025-11-28   │ │ 2   │ │ 4995     │
   │ 88215       │ │ 2210     │ │ 2025-11-28   │ │ 1   │ │ 2995     │
   │ 88216       │ │ 8841     │ │ 2025-11-28   │ │ 5   │ │ 1295     │
   └─────────────┘ └──────────┘ └──────────────┘ └─────┘ └──────────┘
   SUM(unit_price_cents) reads ONLY the last block.

In words: a row store interleaves all columns of each row; a column store keeps each column contiguous, so a query reads only the columns it names. That is projection pushdown, and it is the first of the two savings.

What it buys, quantified

Kestrel's order_items: 6,480,000 rows a year, 40 columns after enrichment, averaging 8 bytes per column value.

$$\text{row size} \approx 40 \times 8 = 320 \text{ bytes} \qquad \text{table} \approx 6{,}480{,}000 \times 320 = 2.07 \text{ GB}$$

Now the query SELECT SUM(quantity * unit_price_cents) FROM order_items, which needs two columns.

Row store: reads the whole table. 2.07 GB.

Column store, uncompressed: reads two columns.

$$6{,}480{,}000 \times 2 \times 8 = 103.7 \text{ MB} \qquad \textbf{20× less}$$

Column store, compressed. Columnar data compresses far better than row data because a column holds values of one type with related magnitudes. At a conservative 5× for these two integer columns:

$$103.7 / 5 = 20.7 \text{ MB} \qquad \textbf{100× less than the row store}$$

Two orders of magnitude, from layout alone. No index, no faster hardware, no cleverness.

💸 Cost Check — The same query on BigQuery's per-byte model

BigQuery's on-demand pricing is per byte scanned, at the frozen rate of $6.25 per TiB. That pricing model makes projection pushdown directly visible on the invoice, which is why BigQuery users learn it faster than anyone.

Kestrel's order_items at 2.07 GB uncompressed, one year:

Query	Bytes scanned	Cost
SELECT SUM(quantity * unit_price_cents)	103.7 MB	$0.00059
SELECT * then aggregate in the client	2.07 GB	$0.01179

Twenty times the cost for the same answer. Individually trivial — six-hundredths of a cent against a little over a cent.

Now run it hourly for a year, across the twenty-two dashboards that each do something similar:

$$\$0.01179 \times 24 \times 365 \times 22 = \$2{,}272 \text{ per year}$$

against $114 for the disciplined version. $2,158 a year, from SELECT *.

This is the shape to internalize: individual query costs in a warehouse are small enough to feel unimportant, and they are multiplied by frequency and by consumer count, both of which grow. The cheapest optimization in this entire book is naming your columns.

Predicate pushdown and row groups

The second saving. Columnar formats store data in row groups (Parquet's term) or micro-partitions (Snowflake's), each holding some number of rows, and each carrying statistics — the minimum and maximum of every column in that group.

Before reading a group, the engine checks the statistics against the query's predicates. If a group's placed_at range is entirely outside WHERE placed_at >= '2025-11-01', the group is skipped without being read at all.

That skipping is predicate pushdown, and it is why Chapter 1's CAST incident was so expensive: a function on the column made the statistics unusable, so nothing could be skipped and the scan read 4.2 TB instead of 34 GB.

The critical dependency: pushdown only works if the data is physically ordered by the column you filter on. If placed_at values are scattered randomly across row groups, every group's min-max range spans the whole year and no group can ever be skipped. The statistics exist; they are useless.

That single sentence is most of §8.8, and it is why clustering is the main performance lever a warehouse gives you.

8.3 Compression, and Why Columns Compress So Well

A column holds values of one type, usually with related magnitudes and often with repetition. That is close to ideal for compression, and warehouses exploit it with four encodings before any general-purpose compressor runs.

Run-length encoding. A sorted or low-cardinality column becomes runs. 'paid','paid','paid','paid','shipped' → ('paid', 4), ('shipped', 1). Devastatingly effective on a sorted status column; useless on a random one.

Dictionary encoding. Replace repeated values with small integers into a dictionary. Kestrel's channel column has four distinct values across 6.48 million rows; as a dictionary it is 2 bits per row plus a four-entry dictionary, against roughly 6 bytes per row as text — about a 24× reduction on that column.

Delta encoding. Store differences instead of values. A monotonic order_id becomes a long run of 1s, which then run-length encodes to almost nothing. Timestamps in an append-only table compress the same way.

Bit packing. Use only the bits a value needs. quantity at Kestrel is 1–30, so 5 bits, not the 32 an INTEGER reserves.

Then a general-purpose codec — Snappy (fast), zstd (better ratio, this book's default), or gzip (older, slower) — on top.

Read that table for the pattern rather than the numbers. Low cardinality and sorted order both compress enormously; high-cardinality free text barely compresses at all. That has a direct design consequence: sorting a table by a low-cardinality column before writing it can shrink it several times over, which is why compaction jobs sort (Chapter 9 §9.6).

🧪 Try It — Measure compression yourself

This is one of the more satisfying ten minutes in the book, because the numbers are large.

```python import duckdb, pathlib con = duckdb.connect("platform/warehouse/kestrel.duckdb")

1. As CSV

con.execute("COPY order_items TO '_out/oi.csv' (FORMAT CSV, HEADER)")

2. As Parquet, unsorted, with zstd

con.execute("COPY order_items TO '_out/oi.parquet' " "(FORMAT PARQUET, COMPRESSION zstd)")

3. As Parquet, SORTED by a low-cardinality column first

con.execute("COPY (SELECT * FROM order_items ORDER BY product_id, order_id) " "TO '_out/oi_sorted.parquet' (FORMAT PARQUET, COMPRESSION zstd)")

for f in ["oi.csv", "oi.parquet", "oi_sorted.parquet"]: mb = pathlib.Path("_out", f).stat().st_size / 1e6 print(f"{f:22} {mb:8.1f} MB") ```

Then answer three questions in writing:

1. What is the CSV-to-Parquet ratio?
2. What did sorting alone buy you, with no other change?
3. Time SELECT SUM(quantity) FROM read_parquet(...) against each file. Does the ranking match the size ranking, and if not, why not?

Question 2 is the one people are surprised by. Sorting is free at write time and it is paid back on every read, forever.

8.4 The Separation of Storage and Compute

The architectural change that reorganized the field.

The old shape

A traditional warehouse — Teradata, early Redshift, an on-premises appliance — coupled storage and compute on the same nodes. Adding storage meant adding nodes, which added compute you might not need. Adding compute meant adding storage you might not need. And scaling either one meant redistributing data across nodes, which took hours and could not be done during business hours.

Two consequences shaped a generation of practice: you sized for peak and paid for it always (Chapter 3 §3.5's $63,072), and every gigabyte loaded consumed capacity you had bought, which made transforming before loading economically forced (Chapter 3 §3.3).

The new shape

                    ┌──────────────────────────────────────┐
                    │   STORAGE  (object storage)          │
                    │   one copy, cheap, effectively        │
                    │   unlimited, $0.023/GB-month          │
                    └──────────────────────────────────────┘
                        ▲            ▲            ▲
            ┌───────────┘            │            └───────────┐
   ┌────────────────┐      ┌────────────────┐      ┌────────────────┐
   │ COMPUTE  ETL   │      │ COMPUTE  BI    │      │ COMPUTE  DS    │
   │ XL, 02:00-04:00│      │ S, 08:00-20:00 │      │ M, on demand   │
   │ suspended else │      │ auto-suspend   │      │ suspended else │
   └────────────────┘      └────────────────┘      └────────────────┘

In words: one copy of the data in cheap object storage, with independently sized compute clusters attached to it — each scaled for its workload, each billed only while running, and none of them competing with the others for resources.

Four things this enables, and the fourth is the one people underuse:

Independent scaling. Store ten years and query it with one small cluster.

Workload isolation. The nightly transformation cannot slow the morning dashboards, because they run on different compute against the same data. This is the property that most directly improves life at Kestrel.

Elasticity. Chapter 3's $63,072 fixed-at-peak against $11,952 elastic. Compute exists only while it is working.

Zero-copy sharing and cloning. Because storage is separate and immutable, a "copy" of a table can be a metadata operation rather than a data copy. Cloning a 2 TB warehouse for a test takes seconds and costs nothing until you modify it — which is what makes the CI pattern in Chapter 27 §27.4 possible at all.

📐 Design Decision — What separated storage and compute costs

The architecture is not free and the costs are systematically underdiscussed.

Latency. Compute reads from object storage over a network. A local NVMe read is measured in tens of microseconds; an S3 GET is measured in tens of milliseconds. Warehouses hide this with aggressive caching, and the first query against cold data is noticeably slower. You will see this and wonder if something is broken.

Cold starts. A suspended cluster takes seconds to resume. Auto-suspend saves money and makes the first query of the morning slow, and users notice the tail, not the average.

Cost visibility inverts. With a fixed appliance you knew the bill in advance and did not know which query was expensive. With elastic compute you know exactly which query was expensive and you do not know next month's bill. Most teams find the second harder to manage, and it is why Chapter 33 exists.

What you give up by choosing coupled storage and compute instead: almost nothing, at small scale, and the ability to grow without a migration. A single machine with local NVMe genuinely outperforms a cloud warehouse below a few hundred gigabytes — this is DuckDB's whole argument, and it is a good one right up until you need a second concurrent writer.

8.5 Snowflake, BigQuery, Redshift

Three architectures, three cost models, and — the useful part — three characteristic mistakes.

Snowflake

Architecture. Data in cloud object storage as immutable micro-partitions (~16 MB compressed, 50–500 MB uncompressed). Compute is a virtual warehouse, sized XS through 6XL, each step doubling both capacity and cost. A metadata layer tracks micro-partition statistics.

Cost model. Credits per second of warehouse runtime, minimum 60 seconds per resume. At the frozen $2.00/credit: XS = 1 credit/hour, S = 2, M = 4, L = 8, XL = 16, each step doubling. Storage billed separately at roughly object-storage rates.

What it rewards: short, dense bursts of work on a suspended warehouse. Consolidating twelve small jobs into one is directly cheaper.

The mistake it invites: an idle warehouse left running. You are billed per second the warehouse is up, not per second it is working. A warehouse with auto-suspend disabled — or set to 60 minutes when queries arrive every 55 — bills 24 hours a day for perhaps two hours of work.

💸 Cost Check — Snowflake auto-suspend, and why the interval matters more than the size

A Medium warehouse (4 credits/hour, $8.00/hour at the frozen rate) used for BI, receiving queries sporadically between 08:00 and 20:00.

Auto-suspend	Effective hours billed/day	Cost/day	Cost/year
Never (left running)	24.0	$192.00	$70,080
60 minutes	~12.5	$100.00 | $36,500
5 minutes	~4.2	$33.60 | $12,264
60 seconds	~2.8	$22.40	$8,176

$70,080 against $8,176 — 8.6× — from one configuration setting.

The nuance that makes this a judgment call rather than a rule: a suspended warehouse loses its local cache, so the next query is slower. The 60-second setting saves $4,088 a year over the 5-minute setting and makes more queries hit cold cache.

Kestrel uses 5 minutes for BI (users are interactive and notice latency) and 60 seconds for scheduled jobs (nothing is waiting). Matching the setting to who is waiting is the whole decision.

BigQuery

Architecture. Fully serverless. Storage is Google's Colossus; compute is allocated in slots from a shared pool. There is no cluster to size or manage, which is genuinely different from the other two.

Cost model, two options:

• On-demand: per byte scanned, at the frozen $6.25/TiB, first 1 TiB/month free.
• Capacity (editions): buy slots by the hour or commit to them, with autoscaling.

What it rewards: narrow queries on well-partitioned, well-clustered tables. Projection pushdown appears directly on the invoice, so SELECT * is visibly expensive rather than invisibly expensive.

The mistake it invites: SELECT * on a wide table, repeatedly. On Snowflake this wastes compute time you were paying for anyway; on BigQuery on-demand it is a line item. The related trap: a query on an unpartitioned table scans the whole table every time, and BigQuery charges you for every byte, so an unpartitioned 4 TiB table costs $25.00 per query regardless of the WHERE clause.

Redshift

Architecture. The oldest of the three, and it has changed substantially. Classic Redshift is a cluster of nodes with local storage — coupled, in the §8.4 sense. RA3 node types separate compute from managed storage, and Redshift Serverless removes the cluster entirely. Which one you are on determines almost everything about how you should use it.

Cost model. Per node-hour for provisioned clusters; per RPU-hour for Serverless. Reserved instances offer substantial discounts for commitment.

What it rewards: steady, predictable workloads with good distribution and sort keys. If your load is constant, reserved provisioned Redshift is frequently the cheapest of the three.

The mistake it invites: a bad distribution key. Redshift asks you to choose how rows are distributed across nodes (DISTKEY) and how they are sorted within a node (SORTKEY). A poor DISTKEY causes data skew — Chapter 4 §4.2's problem, made explicit as a configuration choice — and a join on a non-distribution key redistributes data across the network on every query.

The comparison that matters

🎓 Interview Angle — "How would you reduce our Snowflake bill?"

Answer in order of payoff, and make clear you would measure before acting:

"First I'd look at warehouse utilization — credits billed against credits doing work. Idle warehouses with long auto-suspend are usually the single biggest and easiest win; going from a 60-minute to a 5-minute auto-suspend on a Medium is roughly a 3× reduction on that warehouse.

Second, query patterns: SELECT *, unclustered scans on large tables, and joins producing spills to remote storage. QUERY_HISTORY gives you bytes scanned and spillage per query, so this is measurable rather than a guess.

Third, right-sizing. A bigger warehouse costs proportionally more per hour and often finishes proportionally faster, so it can be cost-neutral and latency-positive — but only up to the point where the query stops parallelizing. That's an experiment per workload, not a rule.

Fourth, the boring ones: drop unused tables, set retention on time-travel, and check whether anything is being clustered that does not need it, because automatic clustering has an ongoing cost.

I'd start with utilization because it usually pays for the rest of the analysis."

Naming what you would measure at each step is what distinguishes this from a list of tips.

8.6 Loading Data

Three patterns, and the differences matter more than they look.

Bulk load from object storage. COPY INTO on Snowflake and Redshift, LOAD DATA or an external table on BigQuery. This is the way. Files in object storage, loaded in parallel, at high throughput.

Streaming insert. Row-at-a-time or small-batch APIs. Available on all three, priced differently, and appropriate only when latency genuinely requires it (Chapter 3 §3.2).

External tables / query in place. Point the warehouse at files without loading them. Excellent for occasional access to a lake; slower than loaded data and, on BigQuery, priced the same per byte.

File sizing for bulk loads

The single most common loading mistake is file size, and it is worth being specific:

Too small and per-file overhead dominates. Loading 100,000 files of 100 KB each is dramatically slower than 100 files of 100 MB, for the same bytes — this is the small-files problem from Chapter 2 §2.4 appearing in a new place.

Too large and you lose parallelism, because a single file is usually processed by a single thread.

The working range is 100–250 MB compressed, which is close enough to Chapter 4 §4.2's 256 MB Parquet target that you can use one number for both.

🔁 Idempotency Check — Making a warehouse load re-runnable

A load will run twice. The four strategies from Chapter 4 §4.5, in warehouse-specific form:

Delete-insert. Delete the target partition, insert it. Simple, correct, and it briefly leaves the partition empty — so wrap it in a transaction where the warehouse supports one.

sql BEGIN; DELETE FROM gold.fct_order_item WHERE date_key = 20251128; INSERT INTO gold.fct_order_item SELECT ... WHERE date_key = 20251128; COMMIT;

MERGE. Upsert on a key. The right answer for dimensions and late-arriving facts. Note the trap: MERGE with a source containing duplicate keys is an error on some engines and non-deterministic on others. Deduplicate the source first, always.

Partition replacement. Write to a new table, swap atomically. CREATE OR REPLACE TABLE on Snowflake; WRITE_TRUNCATE on a BigQuery partition. Clean and atomic, with the full cost of rewriting the partition.

Snowflake-specific: load metadata. COPY INTO remembers which files it has loaded for 64 days and skips them by default. Genuinely useful and a trap for exactly one reason — the window is 64 days, so a backfill older than that silently loads files again. FORCE = TRUE overrides it, which is the setting people reach for during an incident and then forget to remove.

8.7 Cost Models, and How Not to Be Surprised

Warehouses are usually the largest line item in a data platform, and the surprises are structural rather than accidental.

The two models, and what each rewards

Per-time-compute (Snowflake, Redshift). You pay for compute being up. Optimize by: running less often, finishing faster, and suspending aggressively. Consolidating twelve small hourly jobs into one hourly job is a direct saving. The trap: idle time. §8.5's $70,080.

Per-byte-scanned (BigQuery on-demand). You pay for data read. Optimize by: partitioning, clustering, and naming columns. Query frequency costs nothing if the queries are cheap. The trap: an unpartitioned large table, where every query scans everything.

The habit that transfers between them: do not scan what you do not need. On BigQuery it appears on the invoice; on Snowflake it appears as longer warehouse runtime. Different meter, same waste.

Where the money actually goes

From cost reviews of platforms this size, the ranking is consistent:

1. Compute for scheduled transformations — usually the largest single item.
2. Compute for BI queries, especially dashboards that auto-refresh.
3. Idle compute — pure waste, and frequently third.
4. Storage — usually much smaller than people expect, and where they look first.
5. Data transfer — invisible until it is not. Cross-region and cross-cloud egress.

People look at storage first because it is the easiest line to understand, and it is usually fourth. Chapter 33 §33.3 does the full attribution.

The four controls to set on day one

1. Resource monitors and budget alerts. Snowflake resource monitors can suspend a warehouse at a credit threshold; BigQuery has custom quotas per project and per user. Set them before you need them — the alternative is finding out at the end of the month.

2. A per-query byte limit. BigQuery's maximum_bytes_billed fails a query that would scan more than a set amount, rather than running it and charging you. This is the single best guard against an accidental full-table scan, and it is off by default.

3. Auto-suspend everywhere, tuned to who is waiting: short for scheduled jobs, longer for interactive.

4. Query tagging. Snowflake's QUERY_TAG, BigQuery's labels. Tag every query with the pipeline or dashboard that issued it. Without tags, cost attribution is guesswork, and you cannot tell a team their dashboard costs $400 a month if you cannot prove it.

⚠️ Failure Mode — The dashboard that refreshed every minute

A BI dashboard is configured with a one-minute auto-refresh. It runs a query scanning 40 GB.

$$\frac{40 \times 10^{9} \text{ bytes}}{2^{40}} \times \$6.25 = \$0.2274 \text{ per refresh}$$

$$\$0.2274 \times 60 \times 24 \times 30 = \$9{,}823 \text{ per month}$$

For a dashboard nobody watches overnight, showing data that updates once a day.

Three things made this possible and all three are common:

1. The default refresh interval in the BI tool was one minute, and nobody changed it.
2. The query was not tagged, so the cost appeared as an unattributed lump.
3. No maximum_bytes_billed limit, so nothing failed.

The fix took ten minutes; finding it took three weeks. The general control is #4 above: tag everything, so the monthly cost review can point at a dashboard by name instead of at a total. The related habit: audit BI refresh intervals against the underlying data's actual freshness. A dashboard refreshing more often than its source updates is pure waste, and it is extremely common.

8.8 Performance: Clustering, Partitioning, Materialization

Three levers, in the order you should reach for them.

Clustering and sort order — the main lever

§8.2 established that predicate pushdown only works when data is physically ordered by the column you filter on. Clustering is how you arrange that, and it is the highest-leverage performance control a warehouse offers.

• Snowflake: define a clustering key; automatic clustering maintains it in the background, at a cost. Snowflake also clusters naturally by insertion order, so loading in date order gives you date clustering for free — which is most of what a time-series table needs.
• BigQuery: PARTITION BY a date column and CLUSTER BY up to four more. Partitioning gives hard pruning; clustering gives block-level skipping within a partition.
• Redshift: SORTKEY, maintained by VACUUM.

Choose the clustering key by what you filter on, not by what you join on. The most common mistake is clustering by a join key that never appears in a WHERE clause.

Partitioning

Physically separating data by a column value, usually a date. Gives hard pruning — the engine can eliminate a partition without reading anything.

Partition on the column your queries filter on, at a grain that yields substantial partitions. Daily partitions on Kestrel's fct_order_item give 17,753 rows each — reasonable. Hourly partitions would give 740 rows each, which is the small-files problem with extra metadata.

Materialization

Precomputing a result. Reach for it last, after clustering and partitioning, for the reasons Chapter 2's Case Study 2 gives at length — forty-one tables and seven definitions of revenue.

🔎 Read the Plan — Warehouse plans, and the two numbers to find

The plan interfaces differ — Snowflake's Query Profile is a visual DAG, BigQuery's execution details is a stage table, Redshift uses EXPLAIN plus SVL_QUERY_REPORT — and two numbers matter in all of them.

1. Bytes scanned versus table size. If a query filtering one day of a two-year table scans most of the table, pruning is not happening. The cause is almost always one of Chapter 4 §4.2's three: a function on the filter column, a type mismatch, or a predicate the planner cannot push down.

2. Spilling. When an operation needs more memory than the compute has, it spills — to local disk (bad) or to remote storage (very bad). Snowflake's profile shows "Bytes spilled to local / remote storage"; BigQuery's shows it in stage details.

Spilling to remote storage is the single strongest signal that a query needs attention. It usually means a join is producing far more rows than expected, or a window function is partitioning over something with enormous groups. It is frequently a 10× slowdown, and it is frequently the fan trap from Chapter 6 §6.9 appearing as a performance problem rather than as a wrong number.

Look at those two before anything else, exactly as PartitionFilters comes first in Spark.

8.9 What DuckDB Does Not Show You

An honest accounting, because this book teaches with DuckDB and you will interview about warehouses.

What DuckDB demonstrates faithfully

Columnar storage and its consequences. Compression and encoding. Projection and predicate pushdown. Vectorized execution. SQL semantics — window functions, CTEs, recursion, and everything in Chapter 18 transfers exactly. Reading query plans. Dimensional modeling. dbt. Incremental strategies.

That is most of this book, and it transfers completely.

What it does not

Concurrency. DuckDB is single-process with one writer. You cannot experience twenty analysts querying simultaneously, queue behavior, or workload isolation. Interview implication: you should be able to describe virtual warehouses and slot allocation even though you have not felt them.

Storage/compute separation. DuckDB's storage and compute are the same process. The defining economic property of a cloud warehouse (§8.4) is simply absent, so elasticity, zero-copy cloning, and independent scaling cannot be demonstrated — only reasoned about.

Cost. DuckDB is free, so there is no meter and no feedback. This is why this book teaches cost through arithmetic on a frozen basis rather than by demonstration, and it is a genuine pedagogical gap. Estimating a query's cost before running it is a skill you will have to build on the job.

Scale beyond one machine. DuckDB handles tens to low hundreds of gigabytes well on a laptop. Multi-terabyte behavior — distributed shuffles, cross-node data movement — is Chapter 21's territory.

📏 Scale Note — Where DuckDB stops and a warehouse starts

Three thresholds, in the order you hit them:

Concurrency, at roughly 3–5 simultaneous users. This is usually the first wall and it has nothing to do with data size. A single-writer engine cannot serve a BI tool used by a team, whatever the volume.

Data size, somewhere between 100 GB and 1 TB, depending on machine memory and query shape. DuckDB spills to disk gracefully and gets slower.

Operational needs, at any size. Access control per role, audit logging, time travel, point-in-time recovery, and multi-region availability are warehouse features rather than engine features.

Notice that the first and third thresholds are not about data volume at all. The most common honest reason to adopt a warehouse is concurrency and governance, not scale — which is exactly what Chapter 5's Case Study 2 found: the warehouse was justified by thirty simultaneous BI users, and that was never the reason given for buying it.

Concurrency, queuing, and the number that is not throughput

A warehouse's advertised performance is about one query. Your experience of it is about forty, and the gap between the two is queuing.

Every warehouse serves a bounded number of queries at once — a cluster's slots, a warehouse's concurrency level, a reservation's capacity — and requests beyond that bound wait. The waiting is invisible in every measurement people take, because a query's execution time excludes its queue time and the dashboards report execution time.

09:00  a scheduled refresh of 22 dashboards fires simultaneously
       concurrency limit: 8
       queries 1-8   execute immediately          4 s each
       queries 9-16  wait 4 s, then execute       8 s wall clock
       queries 17-22 wait 8 s, then execute      12 s wall clock

the warehouse reports:   p50 4 s, p99 4 s   (all executions were 4 s)
the analyst experiences:            12 s

Both numbers are correct and only one of them is about the user.

Three consequences worth designing around:

Concurrency is a scheduling problem before it is a sizing problem. Twenty-two dashboards refreshing at 09:00 because that is when the schedule template defaulted is a self-inflicted spike; staggering them across ten minutes costs nothing and removes the queue entirely.

Adding compute may not help. On a per-second meter, doubling the warehouse halves the execution time and does nothing about a queue caused by concurrency limits — you have paid twice as much to wait the same amount. Multi-cluster scaling addresses the queue; a bigger warehouse addresses the query, and they are different problems.

And the two failure modes look identical from a dashboard. "It was slow at 09:00" is produced both by a badly written query and by twenty-one other people's perfectly good ones. The discriminator is queue time, and every warehouse exposes it somewhere — Snowflake's QUEUED_OVERLOAD_TIME, BigQuery's slot-wait metrics, Redshift's WLM queue tables.

What to measure, and where

-- Snowflake: is the problem the query, or the queue?
SELECT date_trunc('hour', start_time)                      AS hr,
       count(*)                                            AS queries,
       avg(execution_time)/1000                            AS avg_exec_s,
       avg(queued_overload_time)/1000                      AS avg_queue_s,
       max(queued_overload_time)/1000                      AS max_queue_s
  FROM snowflake.account_usage.query_history
 WHERE start_time > current_date - 7
 GROUP BY 1 ORDER BY 5 DESC;

If avg_queue_s approaches avg_exec_s, you have a concurrency problem and no amount of query tuning will fix it. That single comparison is the most useful thing in this section, and almost nobody runs it — because the natural instinct when a query is slow is to look at the query.

🧱 Kestrel Platform — the warehouse, sized and scheduled

text warehouse size runs credits/month ──────────────────────────────────────────────────────────────────── TRANSFORM_WH Medium 02:00-05:48 nightly build 456 REPORTING_WH Medium 08:00-20:00, auto-suspend 60s 2,880 ADHOC_WH Small on demand, auto-suspend 60s 480 ML_WH Large 03:30-06:12 feature build 648 ────── 4,464 $8,928

Four decisions in that table are worth naming, because each is a rule rather than a preference.

Separate warehouses per workload, not per team. The transform build must not queue behind a dashboard refresh, and a dashboard must not wait for the nightly build. Isolation is the reason compute and storage were separated (§8.4), and running one warehouse for everything gives the separation away.

REPORTING_WH is the largest line and it is not the largest workload. It is up twelve hours because people query during the day, and it is the line that becomes $5,760 a month if somebody disables auto-suspend (Chapter 33 §33.7). The setting, not the size, is what makes it expensive.

ML_WH is a Large and runs for under three hours. A bigger warehouse that finishes sooner costs roughly the same on a per-second meter and delivers the features before the 6am deadline — size for the deadline, not for the bill, when the two are close.

And ADHOC_WH is deliberately Small. An analyst's exploratory query on a Small warehouse is slow enough to notice and cheap enough not to matter, which is the right trade: the feedback that a query is expensive is more valuable than the four seconds it saves.

The scheduling detail that is not in the table: the twenty-two dashboards refresh on a stagger across ten minutes rather than on the hour, which removed a queue that had been misdiagnosed as a warehouse-size problem twice.

🔐 Privacy & Governance — the warehouse is where personal data becomes joinable

A source system holds personal data in a shape nobody can misuse easily. The warehouse holds it joined, and joinability is what turns a set of harmless tables into a profile.

text in kestrel_app in the warehouse ──────────────────────────────────────────────────────────────────────── orders.customer_id dim_customer, with name, email, address, and every version of them a clickstream event with an anonymous_id the same event, joined to a person via a session that later logged in a support ticket the ticket, the customer's lifetime value, and their return history

None of those joins is wrong. All three are why the warehouse exists. The point is that the warehouse's risk is not proportional to the rows it holds; it is proportional to what it makes possible, and that is a step change from the sources.

Three controls that belong here rather than upstream, because here is where they work:

Column-level access, not table-level. An analyst needs dim_customer and does not need email. Every warehouse in this chapter supports masking policies or column grants, and a table-level grant is the default because it is easier to write.

A separate schema for the columns that identify. gold.dim_customer without direct identifiers, restricted.customer_pii with them, joined on the surrogate key by the few queries that need it. The join is the audit point, and it can be logged.

And retention that differs by layer. Bronze retains raw for the reasons Chapter 34 gives; gold does not need three years of a person's address history unless somebody named the question that requires it (Exercise 20.24's rule, applied to privacy rather than to size).

The observation worth carrying to Chapter 31: every one of those controls is cheaper to apply when the table is created than when it is discovered in an audit — and the moment of creation is here, in Chapter 8, before anybody has thought about it.

🧭 Version Note — the warehouse landscape moved under the advice

Most warehouse advice you will find is about a shape that no longer dominates, and knowing which era a claim comes from tells you whether to trust it.

text claim true in now ───────────────────────────────────────────────────────────────────────── "distribution keys are the most important tuning decision" Redshift RA3 and serverless pre-2019 changed this materially "you must vacuum and analyze" same largely automatic "storage and compute are coupled" pre-2015 no "BigQuery has no indexes so you cannot do point lookups" pre-2020 clustering + search indexes exist "Snowflake has no partitioning" always TRUE -- micro-partitions are automatic, and clustering keys are the lever "the warehouse cannot read your lake" pre-2018 external tables and Iceberg support are standard

The last row is the one that matters most for this book's argument. Chapter 3's hybrid — a lake for bronze and silver, a warehouse for gold — is viable precisely because the boundary between them became porous. A decade ago it would have meant two copies of everything.

And the row that is still true is worth noticing too. Snowflake genuinely has no user-managed partitioning, which means every piece of partitioning advice written for another engine has to be translated into clustering keys, and the translation is not one-to-one — clustering is a background reorganisation with a cost, not a layout you control.

The reading rule for this chapter's subject: check whether the advice assumes coupled storage and compute. If it does, its conclusions about sizing, scheduling, and cost are from a different world, and its conclusions about query shape — scan less, join smaller, aggregate later — are still exactly right.

🏭 From the Pipeline — the warehouse bill that doubled because a dashboard was popular

A finance dashboard was rebuilt and became genuinely useful. Usage went from about forty views a week to four hundred. The warehouse bill went from $8,900 a month to $17,400, and the increase was attributed to "growth."

It was not growth. The dashboard's twelve tiles each ran a query against a view that scanned fct_order_line in full, because the view aggregated across all history and the tile filtered afterwards.

```text per view of the dashboard 12 tiles x a full scan of 6.5M rows ~4 s each on a Medium warehouse, 8 concurrent -> ~6 s wall clock ~0.013 credits per tile

40 views/week -> 6.2 credits/week $12.48 400 views/week -> 62.4 credits/week $124.80 ```

$124.80 a week is not $8,500 a month, and that gap is where the interesting part is: the dashboard's own queries were a small fraction of the increase. What actually happened is that the reporting warehouse stopped auto-suspending, because at four hundred views a week there was almost always a query in flight during business hours.

The warehouse went from about 3 hours a day of running time to about 12. The queries were cheap; the idle time between them was the bill.

Three lessons, in the order they were learned:

Attribution was impossible for six weeks because no query tag was set. Exercise 25.22's one line of profile configuration would have answered the question on day one.

The fix was not query tuning. A materialised daily aggregate reduced each tile from 4 s to 0.1 s, which was worth doing and saved $110 a week. Suspending the warehouse between bursts saved the other $8,300, and it was a scheduling change rather than a SQL change.

And "growth" was accepted as an explanation for six weeks because it was plausible and because nobody had a per-job cost figure to contradict it. A plausible explanation with no measurement behind it is the most expensive kind, which is Chapter 33's argument arriving early.

8.10 Summary

A warehouse inverts the OLTP optimization: many rows, few columns; scans rather than point lookups; no B-tree indexes; and — the thing that surprises people — most warehouses accept PRIMARY KEY and FOREIGN KEY declarations and do not enforce them. They are optimizer hints. Chapter 23's tests exist partly because the database will not do this for you.

Columnar storage buys two orders of magnitude from layout alone. Kestrel's order_items at 2.07 GB is read in full by a row store for a two-column aggregate; a column store reads 103.7 MB uncompressed and about 20.7 MB compressed. Projection pushdown reads only named columns; predicate pushdown skips row groups whose min-max statistics exclude them — and pushdown only works if the data is physically ordered by the column you filter on, which is why clustering is the main performance lever a warehouse gives you.

Columns compress extraordinarily well because a column holds one type with related magnitudes. Run-length encoding on sorted low-cardinality data, dictionary encoding on repeated values, delta encoding on monotonic sequences, bit packing on small ranges, then zstd on top. Sorting a table before writing it can shrink it several times over and is free at write time.

With one exception, measured in Chapter 11 §11.6: sorting helps when the data arrives in no useful order, and hurts when it destroys one it already had. Append-only event data arrives in timestamp order, which already delta-encodes almost perfectly — and sorting it by something else cost 3.4% on this book's own benchmark.

Separated storage and compute gives independent scaling, workload isolation, elasticity, and zero-copy cloning — the last being what makes CI against a warehouse clone possible. It costs network latency on cold reads, cold starts on suspended clusters, and an inversion of cost visibility: you learn which query was expensive and lose the ability to predict next month's bill.

Three warehouses, three characteristic mistakes. Snowflake bills per second the warehouse is up, so it invites an idle warehouse left running — $70,080 a year against $8,176, from one auto-suspend setting. BigQuery bills per byte scanned, so it invites SELECT * and unpartitioned tables — and an unpartitioned 4 TiB table costs $25.00 per query regardless of the WHERE clause. Redshift invites a bad distribution key, which is Chapter 4's skew problem made explicit as a configuration choice.

Load in bulk from object storage, in files of 100–250 MB compressed. Make every load idempotent by delete-insert, MERGE on a deduplicated source, or partition replacement — and know that Snowflake's COPY INTO load metadata expires after 64 days, so a backfill older than that silently reloads.

The cost ranking is consistent and counter-intuitive: scheduled transformation compute, then BI compute, then idle compute, then storage — which is where everyone looks first. Four controls on day one: resource monitors and budget alerts, a per-query byte limit (maximum_bytes_billed is off by default and is the best guard against an accidental full scan), auto-suspend tuned to who is waiting, and query tagging, without which cost attribution is guesswork. A one-minute dashboard refresh on a 40 GB query is $9,823 a month, and finding it took three weeks because nothing was tagged.

Reach for clustering first, partitioning second, materialization last. In any warehouse plan, find two numbers before anything else: bytes scanned against table size (pruning working or not), and spilling — especially to remote storage, which is the strongest signal a query needs attention and is frequently the fan trap appearing as a performance problem.

DuckDB teaches most of this faithfully — columnar behavior, compression, pushdown, SQL, plans, modeling, dbt — and cannot show you concurrency, storage/compute separation, or cost. The thresholds where a warehouse becomes necessary are concurrency at 3–5 simultaneous users, data size somewhere past a few hundred gigabytes, and operational needs at any size. Two of those three are not about volume at all.

What's next

Chapter 9 is the data lake: object storage, Parquet, and the organizational discipline that separates a lake from a swamp. Object storage is not a filesystem, and most data lake failures trace directly to treating it like one — including the small-files arithmetic this chapter has now referenced three times without doing properly.