Why Analytics Wants a Different Shape
Your app's database is beautifully normalized and exactly wrong for analytics — here's why the warehouse wants a different shape, and where we start building it.
Dimensional Modeling from the Ground Up
What to actually build in the warehouse — the star schema, fact and dimension tables, and slowly changing dimensions, each modeled in dbt on Snowflake.
-
-
The Star Schema
One fact table in the middle, dimension tables around the edge — the shape that turns a five-join question into a one-pattern answer.
-
Grain and the Four-Step Design
Kimball's four-step design process, and the one decision inside it that governs everything else: what a single fact row means.
-
Fact Tables: Measuring the Business
A fact table is where the numbers live — but not every number sums the way you think. Anatomy, the additivity taxonomy, and building fct_order_items in dbt on Snowflake.
-
Three Kinds of Fact Table (and the Factless One)
One process, more than one fact table. Transaction, periodic snapshot, and accumulating snapshot — plus the factless fact — all built for real in dbt on Snowflake, including the fct_orders table the rest of the series leans on.
-
Dimension Tables and Surrogate Keys
Dimensions are where the facts get their meaning — wide, denormalized, and keyed by a surrogate the warehouse mints itself. Anatomy, the hash-vs-sequence debate settled in SQL, and the unknown member every dimension owes its facts.
-
The Hardest-Working Table in the Warehouse
Why the date dimension is generated instead of sourced, what it must contain, and how it keeps fiscal calendars, business days, holidays, and unfinished milestones from leaking into every query.
-
Slowly Changing Dimensions
A customer moves from Boston to Denver, and suddenly last year's Boston orders report as Denver. SCDs are the discipline for deciding what a dimension does when the truth it describes changes underneath it.
-
Type 2 in Practice: dbt Snapshots
dbt snapshots hand you a Type 2 dimension for a YAML file's worth of effort — the two strategies, the meta-columns, a two-run walkthrough, the tests that actually protect the history, and the honest cases where you have to roll your own.
-
Late-Arriving Data: The Warehouse After Hours
How to handle facts that arrive before their dimensions, dimension changes that arrive after the events they describe, and the inferred-member and re-windowing patterns that keep history honest.
-
Star, Snowflake, and Galaxy
Three schema shapes, one decision — and the one that actually matters isn't the shape of a single star but whether your stars share the same dimensions.
-
Bridge Tables and Many-to-Many Dimensions
When a plain foreign key cannot represent the relationship — multi-author books, weighted allocations, time-variant bridges, and hierarchy bridges built without double-counting.
-
Junk, Mini, and Role-Playing Dimensions
The specialized dimension patterns for flags nobody owns, attributes that won't sit still, and one calendar wearing three hats — built end-to-end in dbt on Snowflake.
-
Tests That Keep a Star a Star
A star schema degrades silently — duplicated grain, orphaned keys, overlapping SCD windows — and the layered dbt test strategy that catches each failure before a dashboard does.
-
Aggregates, Rollups, and the Semantic Layer
How aggregate fact tables, shrunken dimensions, drill-across queries, and governed metrics make dimensional models fast and consistent without losing their grain.
-
Streaming Facts: When the Nightly Batch Stops Being Nightly
Micro-batching, hot/cold splits, Snowflake Streams, Tasks, and Dynamic Tables — how a star schema survives minute-level latency, exactly what breaks dimensionally when it does, and the honest test for whether you need any of it.
-
Beyond Kimball: Does Any of This Still Matter?
Inmon, Data Vault, One Big Table, activity schemas, and the lakehouse walk into a columnar warehouse — and the honest verdict on whether star schemas still earn their place.