Building a Star Schema with dbt
A star schema organizes data into a central fact table holding numeric, additive measurements — like order_total or session_duration — surrounded by dimension tables holding descriptive attributes like customer name, product category, or region, joined to the fact table through surrogate keys. dbt is well suited to building star schemas because its ref() function and DAG naturally express the dependency chain from raw sources through staging models into shared dimension builds and finally into the fact table, and its testing framework can directly enforce the referential integrity a star schema depends on. The goal is a mart layer where a BI tool like Looker or Tableau can drag a measure from the fact table and a slicer from a dimension table without writing a single join.
Cricket analogy: A scorecard's central 'runs' and 'wickets' numbers are meaningless without the surrounding context of which batter, which bowler, and which venue, mirroring how a fact table's measures are meaningless without the dimension tables that describe who, what, and where.
Building Dimension Tables with Surrogate Keys
Dimension models in dbt typically start from one or more staging models and are deduplicated down to one row per business entity — one row per customer, one row per product — using a surrogate key generated with dbt_utils.generate_surrogate_key() rather than relying on the source system's natural key, which can collide across sources or change format over time. This surrogate key, usually a hashed combination of the natural key and source name, becomes the join key the fact table references, and Slowly Changing Dimension (SCD) Type 2 tracking can be layered in using dbt's built-in snapshot feature when historical attribute changes — like a customer's address changing over time — need to be preserved rather than overwritten.
Cricket analogy: The ICC assigns each player a unique player ID that stays constant even if a player changes teams or the spelling of their name is recorded differently across scorecards, mirroring how a surrogate key stays stable even when a source system's natural key format changes.
Building the Fact Table and Grain
The single most important decision when building a fact table in dbt is declaring its grain explicitly — one row per order, one row per line item, one row per daily active session — because every measure and every join must be consistent with that grain or the fact table will silently double-count or under-count when aggregated. In practice, a fct_orders.sql model joins the relevant staging or intermediate models on their surrogate/natural keys, selects the foreign keys to each dimension alongside the additive measures, and should include a config(materialized='incremental') block once the table grows large enough that a full rebuild on every dbt run becomes too slow, using an is_incremental() macro to only process new or changed records.
Cricket analogy: A ball-by-ball commentary log has an explicit grain of one row per delivery, and mixing in over-summary rows would silently double-count runs, mirroring how a fact table's grain must be declared and never mixed with a coarser aggregation.
Testing Referential Integrity in a Star Schema
Because a star schema's entire value proposition is that BI tools can safely join fact to dimension without a data engineer double-checking the join, dbt's relationships test on every foreign key in the fact table is non-negotiable — it fails the build the moment an order references a customer_key that doesn't exist in dim_customers, catching a broken upstream feed before an executive dashboard shows a blank customer name. Combining this with a dbt_utils.unique_combination_of_columns test on the fact table's declared grain columns catches the double-counting problem directly, and running dbt test as a required CI check means a broken star schema never reaches the BI layer in the first place.
Cricket analogy: Third umpires cross-check a run-out against multiple camera angles before confirming it, and if any angle contradicts, the decision is overturned, similarly to how a relationships test cross-checks a foreign key against its dimension table and fails the build if there's no match.
-- models/marts/finance/dim_customers.sql
select
{{ dbt_utils.generate_surrogate_key(['customer_id', 'source_system']) }} as customer_key,
customer_id,
source_system,
full_name,
signup_date,
region
from {{ ref('stg_customers') }}
-- models/marts/finance/fct_orders.sql
{{ config(materialized='incremental', unique_key='order_id') }}
select
o.order_id,
dc.customer_key,
o.order_date,
o.order_total
from {{ ref('stg_orders') }} o
left join {{ ref('dim_customers') }} dc
on o.customer_id = dc.customer_id
{% if is_incremental() %}
where o.order_date > (select max(order_date) from {{ this }})
{% endif %}
A fact table without a relationships test on every foreign key is a star schema in name only. Without that test, a broken upstream feed can silently drop customer_key matches, and joins in the BI tool will return fewer rows than expected with no error anywhere in the chain — the report just looks quietly wrong.
- A star schema pairs a fact table of additive measures with dimension tables of descriptive attributes.
- Use dbt_utils.generate_surrogate_key() to build stable dimension keys instead of relying on inconsistent natural keys.
- Declare the fact table's grain explicitly and never mix two levels of aggregation in one model.
- Use materialized='incremental' with is_incremental() once a fact table is too large to rebuild fully each run.
- Every fact table foreign key needs a relationships test against its dimension table.
- dbt snapshots implement Slowly Changing Dimension Type 2 for tracking historical attribute changes.
- Grain-level uniqueness tests catch silent double-counting before it reaches a BI dashboard.
Practice what you learned
1. What does a fact table primarily contain?
2. Why use a surrogate key instead of a source system's natural key for a dimension?
3. What is the biggest risk of not declaring a fact table's grain explicitly?
4. Which dbt feature implements Slowly Changing Dimension Type 2 tracking?
5. What test is essential on every foreign key in a fact table to protect a star schema's integrity?
Was this page helpful?
You May Also Like
dbt Best Practices
The project structure, layering, testing, and deployment conventions that keep a dbt project maintainable as it scales past a handful of models.
dbt Quick Reference
A lookup sheet of the CLI commands, YAML config keys, and Jinja functions used every day in a dbt project.
dbt vs Traditional ETL
How dbt's ELT, SQL-in-Git approach differs from traditional GUI-based ETL tools like Informatica or SSIS in compute cost, collaboration, and testing.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
ProgrammingPowerShell Study Notes
Programming · 30 topics