Data Engineering · Cluster
Data Modeling
Dimensional design and slowly changing dimensions.
- 01 Grain: What One Row of a Fact Table Means Why the grain declaration precedes every column choice, how it fixes which measures can be summed, and what a join at the wrong level does to a count.
- 02 Star Schemas and the Cost of Normalizing a Dimension What denormalizing a dimension into one wide table buys at query time, what it costs in update work, and why snowflaking usually loses on columnar storage.
- 03 Conformed Dimensions and the Bus Matrix Why two fact tables can only be compared when they share identical dimension keys and members, and how a bus matrix plans those dimensions before any table is built.
- 04 Type 2 Slowly Changing Dimensions: Versions, Validity and Lookups How a dimension row is versioned with surrogate keys and effective dates, why the load-time lookup is the hard part, and how facts join to the version that was current when they occurred.
- 05 Choosing Between SCD Types 1, 2, 3 and 6 The cost of overwriting an attribute, the cost of versioning it, and the cost of keeping a previous value in a column, set against the questions each type can and cannot answer.
- 06 Mini-Dimensions and Junk Dimensions: Narrowing a Wide Fact Table How to keep fast-changing attributes out of a versioned dimension, and how to collapse a handful of low-cardinality flags into one key instead of a dozen foreign keys.
- 07 Accumulating Snapshot Fact Tables: One Row Per Process, Updated in Place How a fact row can be rewritten as an order moves through picking, shipping and delivery, which measures stay additive, and why the row must be created before the process finishes.
- 08 Late-Arriving Dimensions and Inferred Members What happens when a fact references an entity the dimension has never seen, how inferred rows let the fact load proceed, and why the placeholder key is a debt with an interest rate.
- 09 Bitemporal Dimension History: Effective Time and Assertion Time Why a single validity interval cannot express both when an attribute was true and when the warehouse believed it, and what two intervals cost in keys, joins and corrections.
- 10 Evolving a Dimensional Model Without Rewriting Its History Which schema changes are additive, which silently change a measure's meaning, and how to refine a grain or retire a column while old and new consumers coexist.