Micro Free Course — 100% Free Learning
All lessons in this module are free to learn. Sign in with Google to save your progress.
Modern Warehouse Engines & Performance
Columnar storage, BigQuery partitioning and clustering, One Big Table (OBT) refactoring, and surrogate key strategy.
Module Content
Columnar vs Row Storage — Why It Changes Everything
The single architecture difference that makes warehouses fast and OLTP databases slow at analytics. FIND_VIDEO: search 'columnar vs row storage analytics' — recommended channel: Snowflake / BigQuery / Crunchy Data. Aim for 10 min or under.
Quiz: Designing for Columnar Engines
How columnar storage changes what 'good' schema design looks like, and why wide tables are no longer a sin.
Partitioning & Clustering in Action — BigQuery Engine Deep-Dive
The two physical-layout choices every warehouse table has. Get them right and queries are 100× faster. FIND_VIDEO: search 'partitioning clustering Snowflake BigQuery' — recommended channel: Snowflake / Google Cloud / Crunchy Data. Aim for 11 min or under.
Quiz: Choosing Partition Keys
Partition by what you filter on. Cluster by what you join on. Per-engine quirks matter — here's how to think about them.
One Big Table (OBT) vs Star — The Modern Debate
The modern provocation: just flatten everything into one wide table. When it's brilliant, when it's wrong. FIND_VIDEO: search 'one big table OBT vs star schema modern data stack' — recommended channel: dbt Labs / Roche's Maxim. Aim for 11 min or under.
Quiz: When OBT Wins
The case for One Big Table, the case against it, and how to pick per use case rather than per philosophy.
SUBMISSION: Case 5 — Refactoring One Big Table (OBT) to Star
Refactor an inherited 200-column bloated One Big Table (OBT) into a clean, performant dimensional star schema with zero-downtime migration.
Surrogate Keys vs Natural Keys
The key-strategy decision every dimension and fact lives with. Get it wrong and SCDs are broken from day one. FIND_VIDEO: search 'surrogate key vs natural key warehouse' — recommended channel: Kahan Data Solutions. Aim for 10 min or under.
Quiz: Key Strategy in Practice
Surrogate everywhere, natural for traceability. How to actually generate keys in modern warehouses.
BigQuery Sandbox Setup — First Query & Bytes-Billed Report
Plan 100 W8 wh-02: BigQuery sandbox setup (video companion article). Check: LAB BQ-1 bytes report.
Snowflake Trial Setup — First Query & Credits Report
Plan 100 W8 wh-03: Snowflake trial setup (video companion article). Check: LAB SF-1 credits report.
LAB: Partitioning Pruning Proof + Clustering Scan Win
Plan 100 W8 wh-04/wh-05: prove partition pruning (LAB BQ-2) and measure the clustering scan win in BigQuery.
LAB: Cost Control + EXPLAIN — Cut a Query's Cost
Plan 100 W8 wh-06: read a plan and cut a query's billed cost.