Exercises: Data Warehouses

Half of these are cost arithmetic. That is deliberate — cost reasoning is the skill this chapter exists to build, and it is the one you cannot practise on DuckDB because DuckDB is free.

Difficulty: ⭐ warm-up · ⭐⭐ standard · ⭐⭐⭐ deeper. Solutions: daggered (†) and odd-numbered problems are in appendices/answers-to-selected.md. All costs use the frozen basis in Appendix J.

Part A — Warm-ups ⭐

8.1 † Name five ways a warehouse differs from an OLTP database. Which of the five surprises people most, and why does it matter for Chapter 23?

8.2 Define projection pushdown and predicate pushdown in one sentence each. State the condition that must hold for predicate pushdown to work at all.

8.3 † Name the four columnar encodings and give a Kestrel column that each one suits.

8.4 Why do columns compress better than rows? Answer in one sentence, then predict which of these compresses best and worst: channel, email, order_id, quantity.

8.5 † State the four things separated storage and compute enables. Then state three costs.

8.6 For each warehouse, name the characteristic mistake it invites: Snowflake, BigQuery, Redshift.

8.7 † What is the working file-size range for a bulk load, and what goes wrong at each end?

8.8 Name the three thresholds at which DuckDB stops being sufficient. Which two are not about data volume?

Part B — Standard ⭐⭐

8.9 Redo the §8.2 columnar arithmetic for a different query: SELECT channel, COUNT(*), SUM(net_revenue_cents) FROM order_items GROUP BY channel, needing three columns of the forty. (a) Bytes read by a row store, and by a column store uncompressed. (b) The channel column dictionary-encodes at about 24×; assume 3× for the other two. Compressed bytes? (c) Cost of each on BigQuery on-demand at $6.25/TiB. (d) The ratio. Compare it to §8.2's 100× and explain why it differs.

8.10 † Complete the 🧪 Try It from §8.3 against your Kestrel database. Report all three file sizes and the two queries' timings. Then answer: what did sorting alone buy you, and did the query-time ranking match the size ranking? If not, why not?

8.11 Build the Snowflake auto-suspend table from §8.5 for a Large warehouse (8 credits/hour) used by a scheduled pipeline that runs for 12 minutes at the top of every hour. Compute annual cost at auto-suspend values of never, 60 minutes, 5 minutes, and 60 seconds. Then state which you would choose and what the choice costs in cold-cache latency.

8.12 † A team proposes moving from BigQuery on-demand to capacity pricing. Their monthly scan volume is 340 TiB, spread very unevenly — 70% of it in a four-hour nightly window. Write the analysis you would do. You do not have current slot prices; say precisely what you would need to look up and what the break-even calculation looks like.

8.13 Take Kestrel's fct_order_item (6,480,000 rows/year). Choose a partitioning grain and a clustering key. Justify both against the actual query patterns: the 6am revenue dashboard (yesterday, by category and channel), the finance monthly reconciliation, and the data science team's twelve-month customer feature extract. State what your choice makes slower.

8.14 † §8.6's 🔁 callout notes that Snowflake's COPY INTO load metadata expires after 64 days. Write the incident that this causes: what someone does, what happens, when they notice, and how they diagnose it. Then design the control that prevents it.

8.15 Write the four day-one cost controls from §8.7 as concrete configuration for a warehouse you have access to (or, if none, for Snowflake and BigQuery from documentation). For each, state the value you would set and defend the number.

Part C — Deeper ⭐⭐⭐

8.16 The §8.7 ⚠️ callout describes a dashboard costing $9,823 a month. Design an audit that finds every instance of this pattern across an organization: dashboards refreshing more often than their underlying data updates. Specify what you query, what you join it against, and what threshold flags a finding. Then estimate the false-positive rate.

8.17 † §8.8 says spilling to remote storage is the strongest signal a query needs attention, and that it is frequently the fan trap appearing as a performance problem. Explain the mechanism — why does a grain-changing join cause spilling? Then write the two diagnostics you would run, in order, on seeing remote spill in a Snowflake query profile.

8.18 Construct the case for a coupled storage-and-compute architecture in 2026. Find the scenario where it genuinely wins, quantify the win, and state what the organization gives up. Then say what would make you change your mind.

8.19 † §8.9 lists what DuckDB cannot demonstrate, and calls the cost gap "a genuine pedagogical gap." Design an exercise that closes it: something a reader can do locally that builds the habit of estimating a query's cost before running it. It must produce a checkable number.

Part D — The Kestrel Platform ⭐⭐⭐

8.20 — Increment 8: the warehouse, and its cost model.

(a) Create platform/warehouse/kestrel.duckdb and load the gold-layer DDL from Chapter 6's code/kestrel_model.sql. Verify all sixteen objects exist.

(b) Write platform/warehouse/estimate_cost.py: given a table's row count, column count, average column width, and the columns a query names, it estimates bytes scanned and prints the cost under both models — BigQuery per-TiB and Snowflake per-warehouse-second, given a warehouse size and an estimated runtime.

It must print both, side by side, because the point of the exercise is that the same query has different costs under different meters and rewards different optimizations.

(c) Run it for the three Kestrel query patterns in 8.13 and record the results in platform/docs/cost-model.md. Chapter 33 extends this file into the platform's full cost model.

8.21 † Add platform/warehouse/load_gold.sql: an idempotent load of fct_order_item for a single date_key, using delete-insert inside a transaction. Then write the test that proves it is idempotent: run it twice, assert the row count and the revenue sum are identical after both runs.

That test is the point of the exercise. Chapter 1's duplicate-rows incident is what an untested load produces, and a test that runs the load twice is nine lines.

Reflection

A. §8.7 says people look at storage cost first and it is usually fourth. Why do you think that is? What other cost or risk in your experience gets attention out of proportion to its size because it is easy to understand?

B. The three warehouses each invite a different mistake. If you have used one, which mistake have you made? If you have not, which do you think you would be most prone to, and why?