Chapter 8 — Key Takeaways (Data Warehouses)

The page for a cost review or a warehouse decision.

Warehouse vs. OLTP

Warehouses are bad at: single-row lookups · high-frequency small writes · ⚠️ enforcing constraints — most accept PRIMARY KEY / FOREIGN KEY and do not enforce them. They are optimizer hints. This is part of why Chapter 23's tests exist.

Columnar storage — two savings

Projection pushdown: read only the columns you name. Predicate pushdown: skip row groups whose min/max statistics exclude them.

⚠️ Pushdown only works if the data is physically ordered by the column you filter on. Scattered values → every row group's range spans everything → nothing skippable. That is why clustering is the main performance lever a warehouse gives you.

Kestrel order_items, 6.48M rows × 40 cols × 8 bytes:

Compression — four encodings, then zstd

Sorting a table by a low-cardinality column before writing can shrink it several times over, and is free at write time. It is why compaction jobs sort.

⚠️ Exception, measured in Ch. 11 §11.6: sorting hurts when it destroys a beneficial order the data already had — append-only events arrive in time order, which already delta-encodes.

Separated storage and compute

Buys: independent scaling · workload isolation (nightly jobs stop competing with dashboards) · elasticity · zero-copy cloning (which is what makes CI on a warehouse clone possible).

Costs: network latency on cold reads · cold starts on suspended clusters · cost visibility inverts — you learn which query was expensive and lose the ability to predict next month's bill.

The three warehouses — and the mistake each invites

Snowflake auto-suspend — one setting, 8.6×

Medium warehouse, $8.00/hour, sporadic BI queries 08:00–20:00:

Shorter = cheaper and colder cache. Match the setting to who is waiting: 5 min for interactive BI, 60 s for scheduled jobs.

BigQuery — the meter is visible

$$\text{cost} = \frac{\text{bytes scanned}}{2^{40}} \times \$6.25$$

SELECT * vs. naming two columns: $0.01179 vs. $0.00059 per query → $2,158/year across 22 hourly dashboards. An unpartitioned 4 TiB table costs $25.00 per query, filter or no filter.

Loading

Bulk from object storage, files of 100–250 MB compressed. Too small → per-file overhead; too large → lost parallelism.

Idempotency: delete-insert in a transaction · MERGE on a deduplicated source (duplicate source keys are an error or non-deterministic) · partition replacement.

⚠️ Snowflake COPY INTO load metadata expires after 64 days — an older backfill silently reloads.

Cost — where the money actually is

1. Scheduled transformation compute
2. BI compute
3. Idle compute
4. Storage ← where everyone looks first
5. Data transfer

Four controls on day one

1. Resource monitors / budget alerts — set before you need them
2. Per-query byte limit (maximum_bytes_billed) — off by default, best guard against an accidental full scan
3. Auto-suspend, tuned to who is waiting
4. Query tagging — without it, cost attribution granularity = service-account granularity

Sort cost reports by executions × cost, not by cost. A cheap query run 1,440 times beats an expensive one run twice. Execution counts that are multiples of 24 are schedules; 1,440/day is a minute-level cadence almost nothing needs.

Performance — in this order

1. Clustering / sort order ← the main lever. Choose by what you filter on, not what you join on.
2. Partitioning — hard pruning. Grain that yields substantial partitions (daily, not hourly).
3. Materialization — last. View · materialized view · dbt table · result cache (free, helps more than expected).

Two numbers to find in any warehouse plan

1. Bytes scanned vs. table size — pruning working, or one of Chapter 4's three pushdown killers
2. Spilling — local is bad, remote is the strongest signal a query needs attention, and is frequently the fan trap appearing as a performance problem

What DuckDB does NOT show you

✅ columnar behavior · compression · pushdown · vectorized execution · all SQL · plans · modeling · dbt

❌ concurrency (single writer) · storage/compute separation · cost (no meter) · multi-machine scale

Thresholds where a warehouse becomes necessary:

Two of the three are not about data volume. The most common honest reason to adopt a warehouse is concurrency and governance.