Clean data is not yet business-ready data. Modeling organizes trusted data around decisions, metrics, and business questions. Three major methodologies have shaped the field, each with distinct trade-offs.
Inmon advocates building a centralized, normalized (3NF) enterprise data warehouse first, then deriving dimensional data marts for specific business units. The warehouse is the single source of truth, and all downstream models inherit its consistency.
When the organization needs a single, consistent, enterprise-wide view of data. 3NF minimizes redundancy and update anomalies. Ideal for heavily regulated industries (banking, healthcare) where a single governed truth is critical.
Kimball advocates building dimensional data marts (star schemas) organized by business process. Facts tables hold measures (revenue, quantity, clicks). Dimension tables hold descriptive context (customer, product, date). Conformed dimensions link marts together into an integrated warehouse.
Fastest path to BI value. Star schemas are intuitive, query-friendly, and optimized for the way analysts think. Build one mart at a time, deliver incremental value, and connect marts through shared dimensions.
Data Vault 2.0 (Dan Linstedt) is a modeling methodology designed for agile, scalable data integration from many sources. It separates data into three entity types: Hubs (business keys), Links (relationships between hubs), and Satellites (descriptive and historical context).
When you need to integrate data from many disparate sources, preserve full history, and support agile development. Data Vault is uniquely suited for environments where sources change frequently and full auditability is required.
| Dimension | Inmon (3NF) | Kimball (Star) | Data Vault 2.0 |
|---|---|---|---|
| Approach | Top-down | Bottom-up | Hub-link-satellite |
| Structure | Normalized (3NF) | Denormalized (star/snowflake) | Hubs + Links + Satellites |
| Primary goal | Single source of truth | BI-optimized fast delivery | Scalable, auditable integration |
| Time to first value | Long (months) | Short (weeks) | Medium |
| History tracking | Requires extra design | SCD types | Built-in (satellites) |
| Complexity | High (modeling) | Low to moderate | High (many tables) |
| Best for | Large regulated enterprises | BI-first analytics teams | Multi-source integration at scale |
In practice, many modern data teams combine approaches: Data Vault for the integration layer (Silver, where multiple sources merge with full history) and Kimball star schemas for the consumption layer (Gold, optimized for BI and analytics). This gives you the scalability and auditability of Data Vault where sources land, plus the query performance and analyst friendliness of star schemas where dashboards connect.
Select a scenario to see which approach fits.
How it works: Data is extracted from sources, transformed in a separate processing engine (Spark, Python, Informatica), then loaded into the target warehouse. Best for: Environments where the warehouse has limited compute, or when sensitive data must be masked/filtered before loading. Trade-off: Transformation logic lives outside the warehouse, making it harder to iterate and test with SQL.
| Type | Strategy | History? | Example |
|---|---|---|---|
| SCD Type 0 | Never change | N/A (immutable) | Date of birth, original signup date |
| SCD Type 1 | Overwrite | No (lost) | Fix a typo in a customer name |
| SCD Type 2 | Add new row with validity dates | Yes (full history) | Customer changes address: old row gets end_date, new row gets start_date |
| SCD Type 3 | Add column for previous value | Partial (one change) | current_city and previous_city columns |
SCD Type 2 is the most important for data engineers. It preserves full history by creating a new row for each change, with effective_from and effective_to dates plus a current_flag. This lets analysts answer "what was the customer's address when they placed this order?" rather than only "what is the customer's address now?"
1. Which modeling methodology builds dimensional star schemas organized by business process?
2. What are the three entity types in Data Vault 2.0?
3. What is a fact table in dimensional modeling?
4. Which SCD type preserves full history by adding new rows with validity dates?
5. What is the modern hybrid modeling approach many teams use?