Jaco van der Laan

Why Start from a Business-Shaped, Historized 3NF Before Building Stars

01/10/2025  ·  Data Architecture  ·  Data Engineering  ·  Data Modeling

‹ ›

A modern approach: design once in 3NF, capture history with SCD2 + PIT, and lay the groundwork for automated stars and future-proof analytics.

Summary

In my article “From Kimball to Metadata — How Dimensional Thinking Still Shapes Modern Data Architecture”, I explored how Kimball’s classic star-schema mindset still anchors modern analytics — but that metadata-driven design is taking center stage.

This new series takes that conversation a step further. We’ll go beyond dimensional thinking and show how to start with a governed, bi-temporal 3NF integration layer and automatically generate fast, user-friendly stars.
Along the way you’ll learn how historization, Point-in-Time (PIT) tables, metadata, and automation combine to create an agile yet trustworthy analytics foundation.

Series Overview

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

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

🚀 The Problem

  • Many teams jump straight to dimensional models because they’re easy to report on.
  • But as the business evolves — new products, channels, rules — these hand-built stars become brittle.
  • Surrogate keys drift, Slowly Changing Dimensions (SCD) become inconsistent, and every change means rewriting ETL.

🧩 A Smarter Foundation: The Business-Shaped 3NF Integration Layer

Instead of going directly to stars, build a business-oriented, normalized integration layer:

  • Historized: every record version is kept with SCD2 semantics.
  • Bi-temporal: tracks both validity and recording time.
  • Surrogate keys: every record version has one.
  • Subtype integration: collapse subtypes into supertypes.
  • PIT tables: enable lightning-fast “as-of” snapshots.

This gives you one governed source of truth that’s still flexible enough to feed downstream stars, marts, or semantic layers.

🏛️ Understanding the Entity Types

  • Core = nouns the business cares about.
  • Characteristic = descriptive details that can change.
  • Associative = events or relationships (often become facts).
  • Reference = static codes and lookups.

This classification drives metadata-driven automation later in the series.

Figure 1 — Business-Oriented Historized 3NF

⏳ Bi-Temporality & SCD2 — Capturing “What We Knew, When”

Two time axes matter:

  1. Validity time — when something was true in reality.
  2. Recording time — when we learned or ingested it.

Storing both (e.g., valid_from / valid_to and recorded_from / recorded_to) plus surrogate keys means you can answer:

  • “Who was the insured party as of 2023–12–31 based on what we knew then?”
  • “What did we believe the product price was when last year’s report ran?”

⚡ PIT Tables — The Performance Multiplier

Point-in-Time (PIT) tables map snapshot_date → surrogate key for each business key.

  • Without PIT: every query must scan the full SCD2 history and filter BETWEEN valid_from AND valid_to.
  • With PIT: analysts simply join on snapshot_date + surrogate_key — fast and deterministic.

Figure 2 — PIT Table Concept

🌟 Why This Matters Before You Build Stars

  • Consistency: one governed model drives everything.
  • History: complete temporal trace.
  • Agility: change the model and regenerate stars instead of re-ETLing.
  • Performance: PIT tables make snapshot joins cheap and reliable.

This foundation lets you generate dimensional models rather than hand-craft them.

Figure 3 — Metadata-Driven Automation Workflow
How metadata about entities, relationships, and SCD2 timelines flows into PIT tables, snapshot views, and ultimately automated star schemas.

📜 Summary

A well-designed, historized 3NF with bi-temporal SCD2, surrogate keys, and PIT tables is the launchpad for modern analytics.
It’s operationally clean enough to integrate, yet business-oriented enough to support metadata-driven dimensional models.

✅ Key Takeaways

  • Start with a business-shaped 3NF, not raw source tables.
  • Use SCD2 + bitemporality to capture both truth and recording time.
  • PIT tables make “as-of” resolution fast and reliable.
  • Classify entities (Core/Characteristic/Associative/Reference) — it unlocks automation.
  • A solid integration layer future-proofs your BI and AI stack.

👉 Next in the Series

Part 2 — Generating Dimensional Models Automatically from a Historized 3NF