Introduction
Moving onto Microsoft Fabric or Databricks makes storage cheap and compute elastic. That convenience often tempts teams to skip formal data modeling early on, right up until Power BI reports slow down or an upstream ERP update breaks the nightly pipelines. When teams step back to fix the foundation, the architecture debate usually centers on two approaches: Kimball Dimensional Modeling and Data Vault 2.0.
Both methodologies have been around for decades, and both see heavy use on modern cloud lakehouses. People often pit them against each other, yet they solve very different engineering problems. Looking at how each model behaves inside Fabric, Power BI, and Databricks makes it much clearer where each one belongs.
Kimball prioritizes fast analytical queries and straightforward BI reporting. Its star schemas work natively with Power BI Direct Lake and Databricks SQL. Data Vault 2.0 prioritizes parallel data loading, strict auditability, and resilience when source schemas change, relying on append-only writes that fit Delta Lake and OneLake storage. On larger platforms, teams frequently use Data Vault in the integration layer to feed Kimball models in the reporting layer.
Core Architecture
What Is Kimball Dimensional Modeling?
Introduced by Ralph Kimball, dimensional modeling takes a bottom-up, business-process-centric approach. Instead of mapping out the entire enterprise upfront, you model one business process at a time—such as retail sales, inventory movements, or customer support tickets—based on what the business needs to measure.
Core Architectural Components
- Fact Tables: Store numeric measurements (such as revenue, units sold, or call duration) at a single, well-defined grain, along with foreign keys linking to related dimensions.
- Dimension Tables: Hold the descriptive context around those measurements, including Customer, Product, Geography, and Date attributes used for filtering and grouping.
- Star Schema Topology: Place a central fact table directly adjacent to denormalized dimension tables. Keeping joins one hop away keeps query plans simple and fast.
- Conformed Dimensions & Bus Matrix: Standardize shared dimensions across multiple fact tables so that a "Customer" or "Product" means the exact same thing in Finance reports as it does in Sales.
In Microsoft Fabric and Power BI, Kimball star schemas fit the VertiPaq engine naturally. When using Direct Lake mode in Fabric, a clean star schema lets Power BI read Delta tables directly from OneLake without falling back to slower DirectQuery execution. In Databricks, denormalized dimensions similarly reduce data shuffling across worker nodes during Databricks SQL queries.
What Is Data Vault 2.0?
Created by Dan Linstedt, Data Vault 2.0 combines a data modeling pattern with an engineering methodology built for parallel loading and agile delivery. Rather than designing tables around specific report layouts, it organizes data around core business keys and separates structural relationships from descriptive attributes.
Core Architectural Components
- Hubs: Store a unique list of core business keys (such as a Customer ID or Product SKU) alongside a deterministic hash key, a load timestamp, and the source system name.
- Links: Record transactions and associations between two or more Hubs. Because Links capture many-to-many relationships by default, adding a new relationship later does not require altering existing tables.
- Satellites: Hold descriptive attributes and track every historical change attached to a Hub or a Link. Satellites are strictly insert-only; existing records are never updated or deleted.
- Business Vault & Information Marts: Sit downstream from the Raw Vault. Here, engineers apply standardized business rules and build Point-in-Time (PIT) or Bridge tables so reporting tools can query the data without complex multi-table joins.
Because Parquet files in Databricks Delta Lake and Fabric OneLake are immutable on disk, frequent row-level updates force the engine to rewrite files. Data Vault's insert-only Satellites avoid that write amplification, while deterministic hash keys allow Hubs, Links, and Satellites to load concurrently without waiting on foreign-key lookups.
Architectural Comparison
| Architectural Dimension | Kimball Dimensional Modeling | Data Vault 2.0 |
|---|---|---|
| Primary Design Objective | Fast analytical queries and intuitive self-service BI | Multi-source integration, auditability, and resilience to schema drift |
| Core Table Structures | Facts and Dimensions (Star or Snowflake schemas) | Hubs, Links, Satellites, plus PIT and Bridge helper tables |
| Lakehouse Storage Mechanics | Relies on merge and upsert operations on Delta tables to maintain SCD Type 1 and Type 2 dimensions | Uses append-only writes on Delta and Parquet files, avoiding heavy file rewrites during ingestion |
| Handling Upstream Schema Changes | Often requires altering dimension schemas and updating transformation logic | Non-destructive evolution; new fields or sources attach as separate Satellites or Links without touching existing tables |
| Historical Tracking | Configured per attribute via Slowly Changing Dimensions (SCD Type 1, 2, or 3) | Full, immutable history stored by default with load timestamps and source tags |
| Ingestion Parallelism | Sequential order required; dimensions must load before facts to resolve keys | High concurrency; hash keys remove foreign-key dependencies so all tables load at once |
| Power BI & Databricks SQL Fit | Works directly with Power BI Direct Lake, Import mode, and Databricks SQL | Poor fit for direct BI connections due to high table counts; needs a Kimball Information Mart layer |
Decision Framework
Key Benefits of Kimball Architecture
- Strong Power BI & Direct Lake Performance: Power BI's engine is built for star schemas. Pointing Direct Lake semantic models in Microsoft Fabric at clean Kimball tables keeps queries fast and avoids DirectQuery fallbacks.
- Fast Databricks SQL Execution: Denormalized dimensions cut down on join complexity and data shuffles in Databricks Photon-accelerated SQL warehouses.
- Easy for Business Users to Navigate: Analysts and report authors can understand a central Fact table surrounded by clear Dimensions without deep data engineering training.
- Faster Delivery on Defined Scopes: When source systems are stable and reporting requirements are clear, Kimball gets dashboards into production with fewer moving parts.
Key Benefits of Data Vault 2.0
- Append-Only Efficiency on Delta Lake & OneLake: Frequent row updates on Parquet-based storage cause file fragmentation and extra I/O. Data Vault's insert-only Satellites keep write throughput high in Databricks jobs and Fabric pipelines.
- Resilience During M&A and ERP Migrations: When onboarding a newly acquired company or integrating multiple regional ERPs, new data feeds attach to existing Hubs via new Satellites and Links without breaking existing pipelines.
- Built-In Auditability & Governance: Every row preserves its load timestamp and source system identifier, giving compliance teams full lineage that works well alongside Databricks Unity Catalog and Microsoft Purview.
- Metadata-Driven Automation: Because Hubs, Links, and Satellites follow strict structural patterns, teams can generate most of their ingestion pipelines automatically using metadata frameworks in Spark, dbt, or Fabric Data Factory.
Mapping Both to the Lakehouse Medallion Architecture
On Databricks and Microsoft Fabric, most enterprise data teams do not treat Kimball and Data Vault as an all-or-nothing choice. Instead, they place each methodology in a separate tier of the Medallion Architecture:
- Bronze Layer (Raw Ingestion): Lands raw source files, CDC streams, and API extracts into Delta tables in OneLake or Unity Catalog in their original structure.
- Silver Layer (Data Vault 2.0 — Raw & Business Vault): Consolidates core business keys into Hubs, maps relationships in Links, and records full history in append-only Satellites. Standardized business rules and PIT or Bridge tables sit in the Business Vault.
- Gold Layer (Kimball Information Marts): Projects the Business Vault into conformed Fact and Dimension star schemas. Power BI connects directly to this Gold layer via Direct Lake, while analysts and data scientists query clean metrics in Databricks SQL.
Which Architecture Fits Your Platform?
Build Kimball Directly in the Gold Layer When:
- You are consolidating a small number of stable operational systems (roughly one to five core sources).
- Your primary goal is delivering Power BI semantic models and Databricks SQL dashboards quickly.
- You have a lean data team that prefers straightforward ELT pipelines over multi-layer vault orchestration.
- Standard Slowly Changing Dimension (SCD Type 2) tracking meets your historical reporting and compliance needs.
Adopt Data Vault 2.0 Feeding Kimball Marts When:
- You integrate dozens of disparate source systems that share core business entities across departments or regions.
- Your organization deals with frequent mergers, ERP replacements, or unannounced upstream schema changes.
- Auditors or regulators require an unaltered, permanent record of every source change as it arrived.
- Your team uses metadata-driven automation to generate repetitive ingestion pipelines at scale.
Conclusion
Kimball Dimensional Modeling and Data Vault 2.0 solve different problems on a modern lakehouse. In Microsoft Fabric and Databricks, Kimball remains the most practical structure for the Gold presentation layer feeding Power BI and self-service analytics. When multi-source integration, high-concurrency ingestion, and strict audit lineage become your main bottlenecks, placing Data Vault 2.0 in the Silver layer gives your engineering team a durable foundation without slowing down downstream reports.
