Automating Dimensional Models with Metadata & dbt

Stop hand-coding stars. Let your data model drive SQL generation and keep your analytics layer agile.

Automating Dimensional Models with Metadata & dbt

Use metadata and dbt to automatically generate PIT-based snapshots, facts, and dimensions — reproducible, testable, and easy to evolve.

Series Overview

From Historized 3NF to Fast, Governed Analytics — A Metadata-Driven Journey (5 parts)

Why Start from a Business-Shaped, Historized 3NF
Generating Dimensional Models Automatically
Automating with Metadata & dbt ← you’re here
Solving Many-to-Many & Drill-Across with the Unified Star Schema
Putting It All Together

⚡ Recap from Part 2

We transformed a historized, SCD2 integration layer into snapshot views and star schemas :

PIT tables pre-resolved SCD2 timelines.
Snapshots simplified queries to snapshot_date + surrogate_key.
Facts and dimensions were built by joining those snapshots.

Now we’ll remove the manual SQL and let metadata + dbt macros do the heavy lifting.

🧩 Why Metadata-Driven Modeling

Your 3NF is already rich with entity types, attributes, relationships, and keys.

Export that metadata and you know:

Which entities are Core, Characteristic, Associative, Reference.
Which attributes belong to each.
Which relationships are M:1 (safe for dimension building).
Which facts require measures and surrogate key joins.

Instead of repeating this logic manually, we generate SQL.

🛠 Practical Automation Pattern

1. Export the Data Model Metadata

From your modeling tool or database: export tables, keys, relationships, attributes to YAML or JSON.
Include classification (Core, Characteristic, Associative, Reference).

entities:
customer:
type: core
key: customer_id
attributes: [name, segment, country]
claim_participation:
type: associative
key: claim_participation_id
measures: [claim_amount, reserve_amount]

2. Use dbt Macros to Generate Snapshot SQL

{% macro snapshot_view(entity) %}  
CREATE OR REPLACE VIEW snap.{{ entity.name }} AS  
SELECT  
    pit.snapshot_date,  
    t.{{ entity.key }}_sk,  
    {% for a in entity.attributes %}  
    t.{{ a }},  
    {% endfor %}  
    t.valid_from, t.valid_to, t.recorded_from, t.recorded_to  
FROM pit.{{ entity.name }}_pit pit  
JOIN integ.{{ entity.name }} t  
  ON pit.{{ entity.key }}_sk = t.{{ entity.key }}_sk  
{% endmacro %}

Loop through entities to create snap.* views.
No manual coding for each table.

3. Generate Fact Views

{% macro fact_view(fact) %}  
CREATE OR REPLACE VIEW mart.{{ fact.name }} AS  
SELECT  
    f.snapshot_date,  
    f.{{ fact.key }}_sk,  
    {% for m in fact.measures %}  
    f.{{ m }},  
    {% endfor %}  
    {% for d in fact.dimensions %}  
    d.{{ d }}_sk,  
    {% endfor %}  
FROM snap.{{ fact.name }} f  
{% for d in fact.dimensions %}  
LEFT JOIN snap.{{ d }} d  
  ON f.snapshot_date = d.snapshot_date  
 AND f.{{ d }}_sk   = d.{{ d }}_sk  
{% endfor %}  
{% endmacro %}

Define once: which dimensions link to each fact.
Regenerate all stars when the model changes.

4. Add dbt Tests

Key uniqueness: surrogate keys are unique per snapshot.
Referential integrity: fact keys must exist in dimensions.
No unexpected duplicates: snapshot + key combos must be unique.

tests:
– unique:
column_name: “customer_sk, snapshot_date”
– relationships:
column_name: “customer_sk”
to: ref(‘d_customer’)

🌟 Benefits

No repetitive SQL: model-driven code generation.
Reproducible: new entities or attributes auto-propagate.
Testable: dbt tests ensure data quality.
CI/CD friendly: PRs + automated runs catch issues early.

🔮 Next: Safe Drill-Across

Once your stars are automated, analysts still struggle with many-to-many joins and drill-across across facts.

In Part 4, Solving Many-to-Many & Drill-Across with the Unified Star Schema, we’ll add the Unified Star Schema (USS) — a single semantic bridge that keeps queries safe and performance predictable.

📜 Summary

Your data model already knows your facts, dims, keys, and relationships.
By exporting that metadata and using dbt macros , you can auto-generate snapshot and star views , enforce quality with tests, and evolve safely.

✅ Key Takeaways

Export your model (entities, relationships, types) to metadata.
Use dbt macros to generate snapshot & fact views dynamically.
Add tests for keys and referential integrity.
Embrace CI/CD to keep your data warehouse healthy and agile.