Where the build stands
Seven sessions in, Daybreak has the full stack: typed staging tables (b2), a star schema with dim_date, dim_customer, dim_product and fct_order_line (b3-b4), history via SCD2 (b5), safe incremental loads with MERGE (b6), and team-facing marts (b7). Everything is correct. Tonight is about making it fast and cheap - because in a cloud warehouse those are the same property: bytes scanned. The millisecond counter under every playground on this page is your measuring stick.
Scan less, pay less: the whole art 6 min live
Every warehouse optimization you will ever meet is one idea wearing different hats: arrange the data so the engine can skip most of it. Three hats matter. Column pruning: ask for 2 columns and a columnar engine reads 2 - you learned this in b1. Partition pruning: filter on the date and the engine skips every foreign month entirely. Clustering: sort the data so rows that get filtered together sit together, letting whole blocks be skipped even mid-partition.
LiveThe three prunes, one at a time4 min▶
- Column pruning - free with columnar storage.
SELECT channel, SUM(quantity * unit_price)touches 3 columns; the other 4 stay on disk. You control it by never writingSELECT *. - Partition pruning - the table is physically split by a key, almost always the date.
WHERE order_datein March means January's files are never opened. You control it by always filtering on the partition column, and by choosing that column well when you create the table. - Clustering - within what survives, sorted data lets the engine skip blocks using min/max metadata. A table clustered by
channelanswers channel filters by opening only the matching stretch. You control it with a clustering key (cloud) or an ORDER BY at build time (DuckDB, Parquet).
Self-studyWhy warehouses mostly do NOT use indexes3 min read▶
Coming from OLTP, the reflex is "slow query? add an index". Warehouses mostly skip b-tree indexes, and it is worth saying why out loud:
- B-trees excel at finding a few rows. Analytics reads millions of rows and aggregates them - hopping through an index row by row is slower than a straight columnar scan.
- Zonemaps do the useful part for free. Min/max metadata per block (which DuckDB, Snowflake and Parquet all keep automatically) already lets filters skip blocks - that is index-like skipping without index maintenance.
- Indexes tax every load. Warehouses ingest in bulk; keeping b-trees fresh on billion-row tables costs more than it saves.
So the warehouse answer to "make it fast" is layout (partition, cluster, summarize), not indexes. If you ever meet a warehouse column named ..._idx, be suspicious - someone brought OLTP habits to an OLAP party.
How the cloud engines do it - and what they charge 7 min live
The pruning funnel is universal; each cloud engine just gives it a brand name. Snowflake slices every table into micropartitions (~16 MB compressed blocks) and stores min/max metadata for every column of every block - filters consult the metadata and skip whole blocks. BigQuery asks you to declare a partition column (usually date) plus up to four clustering keys. DuckDB, your in-browser engine, keeps zonemaps - the same min/max trick - per row group. Different names, one idea.
Honesty note: this part is verified at topic level against the LinkedIn Learning outline - re-verify block sizes and current behavior in the vendor docs before you tune a production system. These numbers move.
LiveThe cost model: storage is pennies, scans are dollars4 min▶
Cloud warehouse pricing splits into two very unequal halves. Storage is object-store cheap - roughly coffee money per terabyte per month. Compute is where the bill lives: BigQuery bills per byte scanned, Snowflake bills per second a virtual warehouse runs. Either way, the invoice is a function of how much data your queries touch.
- Consequence 1: copies are cheap, scans are not. Keeping a summary table costs almost nothing to store; recomputing the same aggregate 40 times a day costs real money.
- Consequence 2: materialized views are "compute once". A materialized view stores a query's result and serves it repeatedly - the warehouse refreshes it when sources change. Your
daily_summarytable in Demo 2 is the hand-rolled version of the same trade. - Consequence 3: the pruning funnel IS cost control. Every block skipped is a block not billed. Performance work and cost work are the same work.
The $10,000 SELECT *. A BI tool pointed at a wide raw table, refreshing hourly with SELECT *, is the classic cloud-bill horror story. The fix is never "negotiate the contract" - it is a summary table and named columns. One afternoon of b8-style work routinely cuts warehouse bills in half.
Reading a query plan 5 min live
You do not have to guess whether pruning happened - the engine will tell you. Prefix any query with EXPLAIN and it prints the physical plan instead of running it. You are looking for two things: the scan node showing which columns it projects, and the filter sitting inside or next to the scan (that is "filter pushdown" - the filter applied during the scan, not after it).
LiveX-ray a filtered aggregate5 min▶
Run it as written, then read bottom-up: the scan on big_orders lists only the columns the query needs, and the date filter is pushed down to the scan itself. Then delete the EXPLAIN keyword and run again to see the real result.
EXPLAIN
SELECT channel,
ROUND(SUM(quantity * unit_price), 0) AS march_revenue
FROM big_orders
WHERE order_date >= DATE '2026-03-01'
AND order_date < DATE '2026-04-01'
GROUP BY channel;
WHERE strftime(order_date, '%Y-%m') = '2026-03', which can defeat pushdown.
Feel the pruning: three timed variants ★ 10 min · everyone builds
Three queries, same 300,000-row table, wildly different work. Run each and read the "N ms in your browser" line under the result. One honest caveat: every Run also regenerates the 300k rows fresh, so compare the differences between variants, not absolute numbers - and run each twice, trusting the second reading.
Run variant 1: two output columns, March-only filter. Partition-prune plus column-prune - the funnel from Part 1, live.
Run variant 2: same aggregate, no date filter. Every row of all 8 months now gets scanned - watch the ms tick up.
Run variant 3: SELECT * with a sort. Every column of every row is touched - the layout's worst case, and the one BI tools commit by default.
Call the pattern: filter on the date, name your columns, never ship SELECT *. The ms counter is your cloud bill in miniature.
SELECT channel,
ROUND(SUM(quantity * unit_price), 0) AS march_revenue
FROM big_orders
WHERE order_date >= DATE '2026-03-01'
AND order_date < DATE '2026-04-01'
GROUP BY channel;
SELECT channel,
ROUND(SUM(quantity * unit_price), 0) AS all_time_revenue
FROM big_orders
GROUP BY channel;
SELECT * FROM big_orders ORDER BY order_date DESC, unit_price DESC LIMIT 10;
In the cloud this is money, not milliseconds. On BigQuery, variant 2 scans roughly 8x the bytes of variant 1 (8 months vs 1), and variant 3 scans every column on top. Same table, same business question available either way - one phrasing costs 8-20x the other, forever, on every refresh.
The pre-aggregation payoff ★ 12 min · everyone builds
The founder's revenue dashboard runs 40 times a day, and it never needs row-level detail - only daily totals. So compute the daily totals once, store them, and point the dashboard at the summary. This is the hand-rolled materialized view, and it is the single highest-ROI move in warehouse tuning.
Run the build below: a CTAS collapses 300,000 order rows into a few hundred daily-summary rows, then the dashboard query reads the summary. Note the total ms.
Now run the second box: the exact same dashboard question asked of the RAW table. Compare - and remember the raw version is what runs 40 times a day if nobody builds the summary.
Your turn: in the third box, decide which build wins for a 40x/day dashboard, then prove it - rewrite the query to hit daily_summary and re-run.
CREATE TABLE daily_summary AS
SELECT order_date, channel,
count(*) AS orders,
SUM(quantity * unit_price) AS revenue
FROM big_orders
WHERE status = 'completed'
GROUP BY order_date, channel;
SELECT strftime(order_date, '%Y-%m') AS month,
ROUND(SUM(revenue), 0) AS revenue
FROM daily_summary
GROUP BY month
ORDER BY month;
SELECT strftime(order_date, '%Y-%m') AS month,
ROUND(SUM(quantity * unit_price), 0) AS revenue
FROM big_orders
WHERE status = 'completed'
GROUP BY month
ORDER BY month;
LiveYour turn: which build wins at 40 runs a day?4 min▶
Below is the dashboard query against raw. Do the founder's math: 40 runs a day of THIS, versus one nightly summary build plus 40 cheap reads. Then rebuild daily_summary in this box (copy the CTAS from above - fresh database per box, remember) and point the query at it.
SELECT strftime(order_date, '%Y-%m') AS month,
ROUND(SUM(quantity * unit_price), 0) AS revenue
FROM big_orders
WHERE status = 'completed'
GROUP BY month
ORDER BY month;
Self-studyThe trade you just made (and when NOT to make it)3 min read▶
- You bought speed with staleness. The summary is only as fresh as its last rebuild. For a daily-revenue dashboard, nightly is plenty; for a fraud alert, it is not. Always name the freshness contract out loud.
- You bought speed with storage. Trivially cheap here - summaries are almost always tiny relative to their source.
- Skip it when the query is ad-hoc (runs once), needs row detail, or the base table is already small. Pre-aggregate the repeated, aggregate-shaped, big-table queries - which is exactly what dashboards are.
- Cloud version: Snowflake and BigQuery both offer materialized views that self-refresh, plus result caching for repeated identical queries. Same trade, managed for you, billed accordingly.
Try it yourself - this week ◐ 20-30 min total
- EXPLAIN both Demo 2 dashboard queries (summary vs raw) and compare the scans - the plan difference IS the bill difference.
- On
big_orders, build amonthly_channel_summary(month × channel × status grain) and answer "refund rate by channel by month" from it. - Break pushdown on purpose: filter March using
strftime(order_date, '%Y-%m') = '2026-03', EXPLAIN it, and compare against the>=/<range version. - Find the most-refreshed dashboard at work and answer three questions: what grain does it actually need, does a summary table exist, and who owns its freshness contract?
- Bring one slow or expensive query to b9 - the lakehouse session shows where files and formats fit into the same scan-less story.
Official sources covered
This page teaches the performance-and-cost core of the source curricula, timed live on a real columnar engine instead of shown on slides. This page covers:
Three questions before you go 🎯 ◐ 90 seconds
1 · A BigQuery table holds 3 years of orders, partitioned by date. Which change cuts the dashboard's scan bill the most?
Partition pruning skips whole months of data before a byte is read - the biggest lever. Warehouses do not use b-trees, and DISTINCT still scans everything.
2 · Why do columnar warehouses mostly skip b-tree indexes?
B-trees shine at finding a few rows. OLAP reads many rows and few columns, so min/max block metadata plus straight scans win, with zero maintenance cost at load time.
3 · The daily_summary table made the dashboard 40x cheaper. What did it cost you?
Compute-once trades staleness for speed. The numbers are exact as of the last refresh - the summary is only wrong if nobody owns the freshness contract.