Generating Dimensional Models Automatically from a Historized 3NF

Stop hand-coding stars. Turn your governed, bi-temporal 3NF into fast, user-friendly facts & dimensions automatically.

---

Generating Dimensional Models Automatically from a Historized 3NF

Stop hand-coding stars. Turn your governed, bi-temporal 3NF into fast, user-friendly facts & dimensions automatically.

---

Series Overview

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

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

---

⚡ Recap from Part 1

We built the case for a business-oriented, bi-temporal 3NF integration layer with:

• Core / Characteristic / Associative / Reference entities
• SCD2 historization (valid & recorded timelines)
• Surrogate keys
• Point-In-Time (PIT) tables for fast “as-of” lookups

Now we’ll flatten this governed model into dimensional stars that BI tools love.

---

🗂️ Step 1—Identify Facts

Facts are usually events or relationships (Associative entities) or sometimes Characteristic events.

Examples:
ClaimParticipation, Shipment, Sale, PolicyPremium

They have measures (amounts, counts) and foreign keys to other entities.

---

🧭 Step 2 — Identify Dimensions

Dimensions describe the fact: who, what, when, where, how.

• Typically Core or Reference entities (Customer, Product, Policy, Time).
• Trace M:1 paths upward from the fact.
• Avoid 1:M or M:M to prevent row explosions.

Because your integration layer already integrates subtypes into supertypes , you’ll usually have one clean Customer dimension instead of many near-duplicates.

---

🧩 Step 3 — Turn SCD2 History into Snapshots

Create a snapshot view for each historized table:

-- Customer snapshot  
CREATE OR REPLACE VIEW snap.d_customer AS  
SELECT  
    cp.snapshot_date,  
    c.customer_sk,  
    c.business_key,  
    c.name,  
    c.segment,  
    c.valid_from,  
    c.valid_to,  
    c.recorded_from,  
    c.recorded_to  
FROM pit.customer_pit cp  
JOIN integ.customer c  
  ON cp.customer_sk = c.customer_sk;


-- Claim participation snapshot  
CREATE OR REPLACE VIEW snap.f_claim_participation AS  
SELECT  
    cpp.snapshot_date,  
    f.claim_participation_sk,  
    f.claim_amount,  
    f.reserve_amount,  
    f.customer_sk,  
    f.policy_sk,  
    f.product_sk  
FROM pit.claim_participation_pit cpp  
JOIN integ.claim_participation f  
  ON cpp.claim_participation_sk = f.claim_participation_sk;

Each PIT maps snapshot_date → surrogate key ; snapshot views resolve SCD2 timelines so queries don’t.

---

🔗 Step 4 — Build Stars with Simple Joins

CREATE OR REPLACE VIEW mart.f_claim_participation AS  
SELECT  
    f.snapshot_date,  
    f.claim_participation_sk,  
    f.claim_amount,  
    f.reserve_amount,  
    c.customer_sk,  
    p.policy_sk,  
    pr.product_sk,  
    t.time_sk  
FROM snap.f_claim_participation f  
LEFT JOIN snap.d_customer  c  ON f.snapshot_date = c.snapshot_date AND f.customer_sk = c.customer_sk  
LEFT JOIN snap.d_policy    p  ON f.snapshot_date = p.snapshot_date AND f.policy_sk    = p.policy_sk  
LEFT JOIN snap.d_product   pr ON f.snapshot_date = pr.snapshot_date AND f.product_sk  = pr.product_sk  
LEFT JOIN d_time           t  ON f.snapshot_date = t.calendar_date;

• **BETWEEN valid_from AND valid_to** in queries.
• Joins are snapshot_date + surrogate key → fast and deterministic.
• Facts & dimensions remain fully SCD2 and bi-temporal aware.

---

🌟 Benefits

• Performance: snapshot joins are light and predictable.
• Simplicity: analysts avoid temporal logic.
• Governance: PIT + surrogate keys give one version of the truth.
• Reusability: snapshots power any star or semantic layer.

---

🔮 Next: Automate It

Your entity types and relationships are already metadata-driven. Next we’ll show how to export that metadata and generate the SQL automatically using dbt macros and CI/CD in Part 3:Automating with Metadata & dbt

---

📜 Summary

Flattening a historized 3NF into snapshot views and stars turns a complex, bi-temporal model into a fast, analyst-friendly schema.
It keeps governance intact and sets the stage for metadata-driven automation.

---

✅ Key Takeaways

• Use PIT tables to pre-resolve SCD2 timelines.
• Build snapshot views — one per entity/fact — keyed by snapshot_date.
• Generate stars with simple joins (snapshot_date + surrogate key).
• This pattern keeps history, performance, and governance while simplifying BI.

---

👉 Next in the Series

Part 3 — Automating Dimensional Models with Metadata & dbt