Data Warehousing cookbook

12 recipes · 70 steps · about 66 minutes of reading

Design your first star schema
Nobody told me which columns go in the fact table and which belong in a dimension.
BeginnerRead 6 minDoing: about an hour6 steps
OLTP vs OLAP: decide when a report needs its own warehouse
Why can't I just query the app's database?
BeginnerRead 4 minDoing: half an hour to an hour, mostly comparing numbers you already have5 steps
Bronze, silver, and gold: lay out a new pipeline's layers
Every new source lands in one folder and nobody can tell what's safe to query yet.
Beginner to intermediateRead 4 minDoing: a couple of hours to lay out and check three tables for one new source6 steps
ETL or ELT: choose when to transform new data
The pipeline needs to run and I don't know if the cleanup happens before or after it lands in the warehouse.
Beginner to intermediateRead 4 minDoing: an hour or two, mostly reading real sample records and writing the note5 steps
Untangle a many-to-many relationship with a bridge table
One order can belong to three categories at once and my fact table keeps tripling the total.
IntermediateRead 7 minDoing: about an hour6 steps
Model a signup event with a factless fact table
There's no number to sum, just the fact that something happened, and I don't know how to model that.
IntermediateRead 7 minDoing: under an hour5 steps
Diagnose a slow warehouse dashboard and fix it with partitioning
The same query is instant in staging and takes five seconds in front of a real user.
IntermediateRead 4 minDoing: a couple of hours end to end, including watching a backfill finish6 steps
Fix a warehouse total that's doubled
A total that should match last week's report is running about double, and nobody knows why.
IntermediateRead 5 minDoing: an hour6 steps
Add a data quality check that explains a revenue swing
Finance says revenue is wrong. No idea why.
IntermediateRead 4 minDoing: a few hours6 steps
Star schema or one big table: which does your new dashboard need?
The BI tool keeps writing clumsy joins.
Intermediate to advancedRead 6 minDoing: an hour, mostly checking with whoever asked for the dashboard6 steps
Make two departments' revenue numbers agree with one conformed customer dimension
Sales says one customer count, support says another, and both pulled from the same warehouse.
Intermediate to advancedRead 6 minDoing: an afternoon to build, plus a conversation with the other department to agree on the anchor7 steps
Decide whether a new table needs an open table format like Iceberg
The vendor pitch says never worry about lock-in again, and I need to know if that's actually true for this table.
AdvancedRead 9 minDoing: half a day, spread across the conversations you need to have6 steps

Cookbook badge

Finish all 12 recipes to earn this.

In these roadmaps

Data Engineerstep 4 of 16

Data Warehousing cookbook

Design your first star schema

OLTP vs OLAP: decide when a report needs its own warehouse

Bronze, silver, and gold: lay out a new pipeline's layers

ETL or ELT: choose when to transform new data

Untangle a many-to-many relationship with a bridge table

Model a signup event with a factless fact table

Diagnose a slow warehouse dashboard and fix it with partitioning

Fix a warehouse total that's doubled

Add a data quality check that explains a revenue swing

Star schema or one big table: which does your new dashboard need?

Make two departments' revenue numbers agree with one conformed customer dimension

Decide whether a new table needs an open table format like Iceberg

In these roadmaps

Data Warehousing cookbook

Design your first star schema

OLTP vs OLAP: decide when a report needs its own warehouse

Bronze, silver, and gold: lay out a new pipeline's layers

ETL or ELT: choose when to transform new data

Untangle a many-to-many relationship with a bridge table

Model a signup event with a factless fact table

Diagnose a slow warehouse dashboard and fix it with partitioning

Fix a warehouse total that's doubled

Add a data quality check that explains a revenue swing

Star schema or one big table: which does your new dashboard need?

Make two departments' revenue numbers agree with one conformed customer dimension

Decide whether a new table needs an open table format like Iceberg

In these roadmaps