Model a star schema — facts, conformed dimensions, declared grain

At a glance

Learning objectives

• Declare a fact grain and surround it with conformed dimensions
• Layer staging, intermediate, and marts models below the star
• Scaffold the layered project with tests and CI from the template

What you need

• A dbt project (or the template below) with raw source tables landed
• One business process with a defensible grain, such as order lines
• Dimensions that recur across facts, such as customer, product, date

Steps

1. The pattern exists because analysts re-derive the same joins and disagree on metric definitions — the star schema is the fix. (from pattern-dimensional-model )
2. It fits a warehouse serving BI and analytics with recurring query patterns. (from pattern-dimensional-model )
3. Model facts (events/measures) surrounded by conformed dimensions (context): fct_orders at the grain of one row per order line, with dim_customer (SCD2 for history), dim_product, and dim_date. (from pattern-dimensional-model )
4. Declare the fact grain explicitly, conform dimensions across facts so they can be combined, and use SCD type 2 when an attribute's history must be preserved at the point of the fact. (from pattern-dimensional-model )
5. Layer the project below the star — staging renames and casts one row per source row, intermediate holds business logic with no direct consumer reads, and marts split fct vs dim with the grain declared in schema.yml. (from project-dbt-warehouse-tests )
6. Check the design against the pattern's forces — analysts need simple fast queries, definitions must be consistent, and history matters for some attributes. (from pattern-dimensional-model )
7. Scaffold rather than hand-roll — the template ships dbt_project.yml, models/staging, models/intermediate, models/marts, tests/, and a CI workflow. (from tpl-dbt-project )

Check yourself

• fct_orders declares its grain in schema.yml and every dim can be joined to every fact.
• Staging models are one row per source row, and no consumer reads intermediate directly.
• Any attribute whose history matters at the point of the fact is an SCD2 dimension column.

Worked example

Facts (events/measures) surrounded by conformed dimensions (context).
  fct_orders (grain: one row per order line)
    ├─ dim_customer (SCD2 for history)
    ├─ dim_product
    └─ dim_date

amp template new de-agent/dbt-project ./my-dbt

raw.orders ─▶ stg_orders ─▶ int_orders_enriched ─┐
raw.customers ─▶ stg_customers ──────────────────┼─▶ fct_orders ─▶ marts
seeds/ ─▶ dim_date ──────────────────────────────┘

Sourced from

• Dimensional Model (star schema)
• dbt → warehouse → tests → docs
• dbt Project Template

Security scan

Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.

Common gotchas

• Grain key not unique → a join fanned out upstream.
• con: less flexible for rare queries; ETL to maintain SCDs
• Intermediate holds business logic — no direct consumer reads.

Production considerations

• Declare the fact grain in schema.yml; marts split fct vs dim with that declaration.
• Conform dimensions across facts so they can be combined.

Diagram

dim_customer ─┐
dim_product  ─┼─▶ fct_orders ─▶ marts
dim_date     ─┘

staging ─▶ intermediate ─▶ fct / dim (grain declared in schema.yml)

Registry stats