Semantic & Metrics Layer

Dimensional Modeling Fundamentals

Grain, facts, dimensions, measures, keys — the engine-agnostic vocabulary every metrics layer assumes. Build the words you'll use for the rest of Strata 7.

Slowly Changing Dimensions

When a customer changes their region, every historical fact silently lies — unless you've modeled the change. SCD types 1/2/3/6, effective-dating, bitemporal, and the production anti-patterns that bite teams in their first year.

Conformed Dimensions & the Bus Matrix

Kimball's organizational technology: how to keep 'customer' meaning one thing across marketing, finance, and product — and how the bus matrix turns conformance from a Slack-thread into a treaty.

Cumulative Table Design

The pattern behind dim_all_users: full-outer-join yesterday to today, coalesce, and carry all of history in one row. Complex types (struct, array, map), the compactness-vs-usability tradeoff, temporal cardinality explosions, why run-length encoding is the reason Parquet won, and how to collapse cumulative history into an SCD Type 2 with window functions and a hand-rolled incremental merge.

Fact Data Modeling

Facts are the biggest data you'll ever touch: immutable events at 10-100x the volume of your dimensions. What makes a fact atomic, why raw logs aren't fact data, when denormalization is the fix (not the bug), deduplication at trillion-row scale, and the blurry line where aggregated facts become dimensions.

Datelists and Reduced Facts

The compression patterns behind Facebook-scale activity analytics: cumulate user activity into date arrays, pack 30 days of history into one integer with bit math, and reduce daily fact volume 30x with value arrays, turning decade-long analyses from weeks of pipeline time into hours.

Data Vault 2.0

The audit-first, source-volatile-first alternative to Kimball. Hubs, links, satellites, hash keys, and PIT tables — and the honest discussion of when Vault is the right tool versus when it's cosplay.

One Big Table & Wide-Table Design

The columnar-engine-native modeling style. When 200-column denormalized tables go from heresy to the right answer — and where OBT's quiet costs (update amplification, fan-out, governance) live.

Choosing a Modeling Style

The decision framework that spans Kimball, Vault, and OBT. By the end, the choice is defensible — you can name the inputs, predict the costs, and explain why one approach wins for your specific team, sources, engine, and change rate.

Why a Metrics Layer Exists

Metric drift is an org failure with a technical fix. This course makes the case for a metrics layer: the three-definitions-of-MAU war story, the headless BI thesis, what a metrics layer is NOT, and why adoption fails at month 6 rather than at install.

Measures, Metrics, Entities, Dimensions

The four primitives every metrics store is built on, made precise. Why a measure is not a metric, how entities are the join graph you already have, the ratio-metric order-of-aggregation trap, cumulative metrics and the time spine, and the metric DAG you never drew.

Query Planning Over a Semantic Graph

How a metrics engine turns 'total_revenue by region' into a multi-join SQL plan. Join path resolution, the fan-out join trap, symmetric aggregates, aggregation pushdown, time grain resolution, slice-and-dice semantics, and why you cache a tuple, not a metric.

Applying Analytical Patterns

Nearly every analytics pipeline is one of three patterns: aggregation, cumulation, or window. Learn to see the pattern before writing the SQL, then apply the big four: growth accounting (new / retained / churned / resurrected), survivorship J-curves, self-join funnels, and GROUPING SETS pre-aggregation that makes dashboards joinless.