A Type 1 dimension put 15% of a year's revenue on the wrong plan
Type 1 moved $152,227 of a year's revenue to the wrong plan. A Type 2 dimension from dbt snapshots got all 5,048 invoices right, and only a unit test saw the bug.
11 min read
Topic
Data modelling & dbt
Star schemas, slowly changing dimensions and dbt: as-of joins, snapshots, contracts and tests, measured against data whose truth is known.
A model is a set of promises about what a number means: which version of a customer a payment belongs to, what grain a fact table has, which joins can drop or double a row. Most of those promises fail without an error. The totals reconcile and the keys resolve while revenue sits on the wrong plan. These notes build the models in dbt on data whose ground truth is known, so each design choice can be scored, and the tests are broken on purpose to see which ones notice.