Dimensional star schema (Kimball) modeling with dbt

[ataft]

I created a dbt example project that highlights some of the features and benefits of multi-dimensional star schema (Kimball) modeling:

https://github.com/flexanalytics/dbt-business-intelligence

Some of the concepts:

• using dbt_utils.surrogate_key for surrogate keys - in addition to the benefits from Tristan's blog about The Most Underutilized Function in SQL, it also allows you to rebuild your dimensions without rebuilding fact tables.
• building a full-featured Date Dimension - the simple year, quarter, month, day grains might suffice for many, but as things scale, an exstensible and full-featured Date Dim is always recommended.
• joins - model relationships tests serve double-duy as both an integrity test, and a way for BI tools to know the relationships
• foundation - rather than argue the merits of star vs columnar, get the best of both worlds. Build the star schemas as a clean, reusable foundation, then use it for 1) direct analysis, or 2) build de-normalized models for analysis. Take a look at the models/metrics/product_monthly.sql model: it uses the metrics package and dimensions to build a custom de-normalized model.