Dimensional and Analytics Data Modeling
Parent: Data Analysis · Published reference · snapshot 2026-09-08 · skill da-data-engineering-platform/references/da-29-dimensional-data-modeling.md
↓ Facts as markdownall context files
The discipline of structuring data for analytics: how to shape facts, dimensions,
These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.
Dimensional & Analytics Data Modeling
- The discipline of structuring data for analytics: how to shape facts, dimensions, and layered models so a warehouse or lakehouse is queryable, consistent, and maintainable. This skill covers the modeling decisions, not the engines or pipelines that move the data. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#dimensional-analytics-data-modeling]
- Scope boundaries. Document/NoSQL schema design → mongodb-schema-design. Ingestion/orchestration/ETL → da-13-data-engineering-and-pipelines. Metric & headless-BI semantic layer → da-18-semantic-layer-headless-bi. OLAP engine internals → da-28-realtime-olap-databases. This skill owns the analytical modeling discipline that those skills sit on top of. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#dimensional-analytics-data-modeling]
1. Kimball dimensional modeling — the core
- Dimensional modeling organizes data into fact tables (numeric measurements of a business process) and dimension tables (the descriptive "who/what/where/ when/why/how" context, wide and denormalized). Instantiated in a relational DB this is a star schema: a central fact table joined to dimensions via primary/foreign keys. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#1-kimball-dimensional-modeling-the-core]
- Four-step design process (always in this order): [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#1-kimball-dimensional-modeling-the-core]
- Select the business process (e.g. orders, shipments, web sessions) - a process produces one or more fact tables, not a department or report. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#1-kimball-dimensional-modeling-the-core]
- Declare the grain - exactly what one fact row represents. This is the pivotal step; every candidate dimension and fact must be consistent with it. Prefer the lowest (atomic) grain - it is the most flexible and future-proof. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#1-kimball-dimensional-modeling-the-core]
- Identify the dimensions - descriptive context that applies at that grain. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#1-kimball-dimensional-modeling-the-core]
- Identify the facts - numeric measures valid at that grain. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#1-kimball-dimensional-modeling-the-core]
- Star vs snowflake. A star keeps each dimension as one flat denormalized table. A snowflake normalizes dimension hierarchies into sub-tables (e.g. product → brand → category). Kimball discourages snowflaking: it saves little storage, complicates queries, and hurts BI-tool usability. Normalize a dimension only for genuine outriggers or very large/volatile sub-hierarchies. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#1-kimball-dimensional-modeling-the-core]
- Fact additivity. Facts are additive (summable across all dimensions, e.g. sales amount), semi-additive (summable across some dims but not time, e.g. account balances, inventory levels - use periodic snapshots), or non-additive (ratios, percentages - store the numerator and denominator as additive facts and compute the ratio at query time, never store the ratio). [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#1-kimball-dimensional-modeling-the-core]
- Sources: Kimball Group, Four-Step Design Process; Grain; Star Schema / OLAP Cube; A Dimensional Modeling Manifesto, 1997. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#1-kimball-dimensional-modeling-the-core]
2. Conformed dimensions & the enterprise data warehouse bus matrix
- A conformed dimension is shared across multiple fact tables/business processes with identical keys, attribute names, and meanings (or one is a perfect subset of the other). Conformed dimensions are what let you drill across and integrate results from different processes (e.g. compare Sales and Returns by the same Customer and Date dimensions). [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#2-conformed-dimensions-the-enterprise-data-warehouse-bus-matrix]
- The bus matrix is the planning artifact: rows = business processes, columns = dimensions, shaded cells mark which dims a process uses. Scanning a column shows where a dimension must be conformed. The matrix is how a team plans incremental, integrated delivery (deliver one process/data mart at a time, but plan the shared dimension bus up front so marts integrate later). [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#2-conformed-dimensions-the-enterprise-data-warehouse-bus-matrix]
- Sources: Kimball Group, Enterprise DW Bus Matrix; Bus Architecture; The Matrix: Revisited, 2005. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#2-conformed-dimensions-the-enterprise-data-warehouse-bus-matrix]
3. Slowly changing dimensions (SCD)
- How to handle dimension attributes that change over time. The three basics are type 1, 2, 3; the rest are hybrids. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#3-slowly-changing-dimensions-scd]
- Type 2 is the workhorse - most "track history" requirements resolve to type 2. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#3-slowly-changing-dimensions-scd]
- Sources: Kimball Group, Design Tip #152: SCD Types 0,4,5,6,7, 2013; Type 0; Type 1; Type 7; SCD overview, 2008. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#3-slowly-changing-dimensions-scd]
4. Fact table types
- The accumulating snapshot is the only type whose rows are routinely updated. Factless tables answer "did it happen / what was eligible" questions. The three core types complement each other and often coexist for the same process. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#4-fact-table-types]
- Sources: Kimball Group, Periodic Snapshot; Accumulating Snapshot; Design Tip #133: Factless Fact Tables, 2011; Design Tip #167: Complementary Fact Table Types, 2014. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#4-fact-table-types]
5. Keys & specialized dimensions
- Surrogate keys - meaningless integer (or hash) primary keys on dimensions, generated by the warehouse. Required for SCD type 2 (each version gets its own key), insulates the warehouse from source-system key changes/reuse, and joins faster than wide natural keys. Facts carry surrogate FKs, not business keys. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#5-keys-specialized-dimensions]
- Natural / business keys - the source-system identifier (e.g. customer_id). Keep it as a durable attribute on the dimension; under type 2 it is not unique (it repeats across versions). The durable key joins all versions of an entity together. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#5-keys-specialized-dimensions]
- Degenerate dimension - a dimension key with no attributes of its own, stored directly on the fact (e.g. order number, invoice number, ticket ID). No separate table. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#5-keys-specialized-dimensions]
- Role-playing dimension - one physical dimension referenced multiple times in one fact under different roles (e.g. Date as order_date, ship_date, due_date). Expose via views/aliases with role-specific column names. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#5-keys-specialized-dimensions]
- Junk dimension - a single table collecting low-cardinality flags and indicators (yes/no, status codes) that would otherwise clutter the fact, holding the distinct combinations actually observed. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#5-keys-specialized-dimensions]
6. Methodology comparison — Inmon vs Kimball vs Data Vault 2.0
- Data Vault 2.0 building blocks (Dan Linstedt): Hubs store unique business keys; Links store the relationships (often M:N) between hubs; Satellites store descriptive attributes and their history, are append-only (every change is preserved, like type-2 history), and hang off hubs or links. DV2.0 adds hash keys, hash diffs, and load metadata for parallel, scalable, auditable loads. The raw vault is not a query layer - you build dimensional marts on top for users. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#6-methodology-comparison-inmon-vs-kimball-vs-data-vault-20]
- A common real-world pattern is hybrid: an Inmon/3NF or Data Vault integration core feeding Kimball star-schema marts for consumption. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#6-methodology-comparison-inmon-vs-kimball-vs-data-vault-20]
- Sources: Keboola - Kimball vs Inmon; Scalefree - Data Vault 2.0 Definition; Data Vault modeling - Wikipedia; WhereScape - What is Data Vault 2.0. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#6-methodology-comparison-inmon-vs-kimball-vs-data-vault-20]
7. One Big Table (OBT) vs star schema in columnar cloud warehouses
- Modern columnar MPP warehouses (BigQuery, Snowflake, Redshift, Databricks SQL) change the normalization math. Columnar storage with run-length/dictionary encoding makes repeated dimension values cheap, and join-heavy star queries incur cross-node shuffles. So a fully denormalized One Big Table (facts + all dimension attributes pre-joined into one wide table) is often faster than a star. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#7-one-big-table-obt-vs-star-schema-in-columnar-cloud-warehouses]
- Benchmarks: OBT ~10-45% faster than star across many queries; BigQuery ~49% average improvement (parallel engine, fewer shuffles). Snowflake is mixed - the star sometimes wins on simpler queries, so test on your engine. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#7-one-big-table-obt-vs-star-schema-in-columnar-cloud-warehouses]
- OBT cost: heavy redundancy and painful updates - renaming one product means rewriting millions of rows, vs a one-row dimension update in a star. SCD history is also awkward in an OBT. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#7-one-big-table-obt-vs-star-schema-in-columnar-cloud-warehouses]
- Recommended hybrid (2024-2026): keep a star schema in the silver layer for integrity, history (SCD), and exploration; materialize OBT/wide tables in the gold layer to power high-concurrency BI dashboards. dbt makes this a single extra model - build the OBT as a ref() over your dimensional marts. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#7-one-big-table-obt-vs-star-schema-in-columnar-cloud-warehouses]
- Don't reflexively denormalize: a star is still the better default when you need SCD history, frequent dimension updates, governed conformed dimensions, or ad-hoc exploration. Reach for OBT for read-heavy, high-concurrency dashboard serving. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#7-one-big-table-obt-vs-star-schema-in-columnar-cloud-warehouses]
- Sources: Fivetran - Star Schema vs OBT; MotherDuck - Star Schema Guide; CloudQuery - 3NF vs Star Schema. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#7-one-big-table-obt-vs-star-schema-in-columnar-cloud-warehouses]
8. Medallion architecture (bronze / silver / gold)
- A lakehouse layering pattern that progressively improves data quality: [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#8-medallion-architecture-bronze-silver-gold]
- Bronze - raw landing, source structure "as-is" plus ingest metadata (load timestamp, source file, process ID). Append/immutable; no business logic. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#8-medallion-architecture-bronze-silver-gold]
- Silver - cleansed, conformed, deduplicated, type-cast; matched and merged to an "enterprise view" of key business entities ("just-enough" cleaning). This is the natural home for normalized/3NF or dimensional integration models. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#8-medallion-architecture-bronze-silver-gold]
- Gold - business-level aggregates, dimensional marts, and OBT/wide tables ready for BI, reporting, and ML features. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#8-medallion-architecture-bronze-silver-gold]
- It is a reference architecture, not a mandate - add or remove layers to fit. Databricks guidance: don't write to silver directly from ingestion (schema drift / corrupt records); prefer streaming reads from bronze for append-only sources; use Unity Catalog with a separate catalog/schema per layer. Medallion is orthogonal to Kimball/Inmon/Data Vault - those describe how you model within a layer (typically silver and gold). [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#8-medallion-architecture-bronze-silver-gold]
- Sources: Databricks - What is Medallion Architecture; Databricks docs - Medallion lakehouse architecture; Databricks - Lakehouse Data Modeling: Myths, Truths, Best Practices. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#8-medallion-architecture-bronze-silver-gold]
9. dbt modeling layers & materializations
- dbt's recommended 3-layer structure maps cleanly onto medallion silver/gold: [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#9-dbt-modeling-layers-materializations]
- Staging (stg_<source>__<entity>) - one model per source table, 1:1 with the source. Rename/recast/clean only; no joins. Materialize as views. Organize subfolders by source system. Define raw inputs as sources in YAML and reference them with source(). [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#9-dbt-modeling-layers-materializations]
- Intermediate (int_<concept>) - purpose-built transformation steps that combine a handful (~4-6) of staging models. Shift to business-conformed subfolders by area of concern. Ideal as ephemeral (interpolated as CTEs). [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#9-dbt-modeling-layers-materializations]
- Marts (fct_<process>, dim_<entity>, or plain entity names) - the business-defined fact and dimension tables, each at its own grain. Materialize as tables (or incremental). If a mart pulls together more than ~4-5 concepts, factor out intermediate models. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#9-dbt-modeling-layers-materializations]
- ref() and source() build the DAG: models reference each other with ref('model_name') and raw tables with source('schema','table'), so dbt infers lineage and build order. Set defaults per folder in dbt_project.yml (e.g. staging→view, marts→table, separate schemas per layer). [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#9-dbt-modeling-layers-materializations]
- Five built-in materializations: view (default, logic only, instant/cheap), table (stores data), incremental (only transform new/changed rows), ephemeral (no DB object, inlined as a CTE), materialized_view (platform-managed refresh of incremental logic). Choose incremental for large append-mostly facts; views for staging; tables for marts users hit directly. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#9-dbt-modeling-layers-materializations]
- Sources: dbt Labs, How we structure our dbt projects (Staging, Intermediate, Marts); Materializations; Best practices for materializations. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#9-dbt-modeling-layers-materializations]
10. Semantic vs physical modeling
- Physical model - the actual tables/views in the warehouse: facts, dims, marts, OBTs, their grain, keys, types, and materializations. This is everything above. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#10-semantic-vs-physical-modeling]
- Semantic model - a metadata layer on top of the physical marts that defines metrics, dimensions, and join paths once, so every BI tool computes "revenue" or "active users" identically (avoids metric drift). In dbt this is the Semantic Layer / MetricFlow; semantic marts sit above physical marts. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#10-semantic-vs-physical-modeling]
Practical patterns
- Always declare the grain in one sentence before modeling anything. "One row per ___." If you can't, you don't understand the process yet. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#practical-patterns]
- Model the atomic grain first. Aggregates are derivable; you can't drill into detail you didn't keep. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#practical-patterns]
- Plan the dimension bus up front, deliver marts incrementally. Conform Date, Customer, Product early - retrofitting conformance is painful. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#practical-patterns]
- Default dimension change handling = type 2 with surrogate keys, unless the business explicitly only wants the current value (type 1) or "original" (type 0). [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#practical-patterns]
- Store additive components, not ratios. Compute percentages/averages at query time from additive numerator+denominator facts. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#practical-patterns]
- Hybrid layering: 3NF/Data-Vault or conformed star in silver for integrity + history; OBT/wide tables in gold for dashboard speed. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#practical-patterns]
- In dbt: staging=views 1:1 with sources, intermediate=ephemeral, marts=tables; one fact/dim concept per mart at its own grain. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#practical-patterns]
Anti-patterns
- Mixed grain in one fact table - the cardinal sin; forces double-counting. Split into separate fact tables. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#anti-patterns]
- Storing non-additive ratios as facts - they can't be summed correctly. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#anti-patterns]
- Snowflaking everything - normalizing dimensions for "cleanliness"; hurts usability and performance with negligible storage gain on columnar engines. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#anti-patterns]
- Smart/natural keys as fact FKs - couples the warehouse to source key changes and breaks SCD type 2. Use surrogate keys. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#anti-patterns]
- Reflexive OBT everywhere - denormalizing without considering update cost, SCD history, or that Snowflake sometimes favors the star. Benchmark your engine. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#anti-patterns]
- Writing to silver directly from ingestion - schema drift and bad records leak in; always land in bronze first. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#anti-patterns]
- Reports as the design driver - designing the fact table around a specific report instead of the physical measurement event; build at the atomic event grain and let reports aggregate. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#anti-patterns]
- Baking every metric into physical tables - causes metric drift; define reusable metrics in the semantic layer. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#anti-patterns]
Troubleshooting
- Numbers double-count when joining two facts → you joined fact-to-fact. Never do that; drill across via conformed dimensions and combine aggregated results. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#troubleshooting]
- Totals wrong after summing a snapshot over time → the fact is semi-additive (balances/levels); don't sum across the time dimension - take a point-in-time or average. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#troubleshooting]
- Dimension table row count exploding → a fast-changing attribute under type 2; split it into a type-4 mini-dimension. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#troubleshooting]
- Can't reproduce a historical report → attributes were handled as type 1 (overwritten). Convert the relevant attributes to type 2. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#troubleshooting]
- Star query slow / shuffling on columnar DW → consider a gold-layer OBT/wide table for that dashboard; keep the star for exploration. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#troubleshooting]
- Same metric differs across dashboards → metric defined per-report in physical tables; move it to the semantic layer. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#troubleshooting]
- dbt build slow / rebuilding huge facts every run → switch the large fact mart from table to incremental. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#troubleshooting]
References (selected, with years)
- Kimball Group - Four-Step Design Process; Grain; Bus Matrix; SCD Design Tip #152 (2013); Type 7; Periodic Snapshot; Accumulating Snapshot; Factless Fact Tables, Design Tip #133 (2011); 10 Essential Rules (2009). dbt Labs - How we structure our dbt projects; Staging; Intermediate; Marts; Materializations; Semantic-layer marts. Data Vault - Scalefree DV2.0 Definition; WhereScape - What is Data Vault 2.0; Data Vault modeling, Wikipedia. Inmon vs Kimball - Keboola (2024); Computer Weekly. Medallion - Databricks - What is Medallion Architecture; Databricks docs - Medallion; Databricks - Lakehouse Data Modeling (2024). OBT vs Star - Fivetran - Star Schema vs OBT; MotherDuck - Star Schema Guide; CloudQuery - 3NF vs Star Schema. [archived source: https://llms-explorer.com/sources/mdb-context-hub/da-29-dimensional-data-modeling/#references-selected-with-years]
Where this helps
- Designing a data warehouse schema for a BI tool where analysts need fast, intuitive slice-and-dice queries across a star schema rather than a normalized transactional schema. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
- Deciding how to handle a dimension attribute that changes over time, such as a customer's address or a product's category, using slowly changing dimension patterns. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
- Choosing between a star schema and a wide "one big table" approach when working in a columnar cloud warehouse where join cost is less of a concern than it is in row-store systems. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
- Structuring a medallion (bronze/silver/gold) architecture so raw ingested data, cleaned data, and business-ready aggregates each live in a clearly separated layer. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Project ideas
- Model a small transactional dataset, such as an ecommerce order system, as a Kimball star schema with a fact table and conformed dimensions, then write BI-style slice-and-dice queries against it. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
- Implement a Type 2 slowly changing dimension for an attribute that changes over time, tracking effective-date ranges so historical queries return the value that was true at that point. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
- Build a dbt project with staging, intermediate, and mart layers mirroring a medallion architecture, including tests for referential integrity between fact and dimension tables. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
- Compare query performance and modeling complexity between a normalized star schema and a denormalized one-big-table design for the same analytical query set in a columnar warehouse. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Common mistakes
- Over-normalizing a warehouse schema the way you would a transactional OLTP database, which defeats the purpose of dimensional modeling and makes BI queries slow and hard to write. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
- Building fact tables at the wrong grain, such as mixing order-level and line-item-level facts in one table, which silently produces double-counted or under-counted aggregates. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
- Treating semantic modeling (business definitions, metric logic) as identical to physical modeling (how tables are actually stored), which leads to the same metric being computed differently by different tools or teams. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
- Skipping SCD handling for a dimension that actually changes over time, so historical reports silently use today's dimension values instead of the values that were true at the time of the fact. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Known issues
- Slowly changing dimension logic, especially Type 2, adds real complexity to ETL/ELT pipelines and can silently produce wrong historical answers if effective-date boundaries aren't handled correctly at every write. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
- The Inmon vs. Kimball vs. Data Vault 2.0 debate isn't fully settled — each methodology has genuine tradeoffs in flexibility, initial modeling effort, and query simplicity, and the right choice depends heavily on team size and change velocity. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
- One Big Table designs improve query simplicity in columnar warehouses but can reintroduce the update-anomaly and storage-duplication problems that dimensional modeling was originally designed to avoid. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
- Conformed dimensions require organizational discipline across teams via a shared bus matrix; without it, different teams build inconsistent versions of "the same" dimension, such as two different customer dimensions, that don't reconcile. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Children
- Kimball Dimensional Modeling (frontier)
- Conformed Dimensions and Bus Matrix (frontier)
- Slowly Changing Dimensions (SCD 0-7) (frontier)
- Fact Table Types (frontier)
- Surrogate vs Natural Keys (frontier)
- Specialized Dimensions (degenerate/role-playing/junk) (frontier)
- Inmon vs Kimball vs Data Vault 2.0 (frontier)
- One Big Table vs Star Schema (frontier)
- Medallion Architecture (bronze/silver/gold) (frontier)
- dbt Modeling Layers and Materializations (frontier)
- Semantic vs Physical Modeling (frontier)
Frontier under this node: Conformed Dimensions and Bus Matrix, Fact Table Types, Inmon vs Kimball vs Data Vault 2.0, Kimball Dimensional Modeling, Medallion Architecture (bronze/silver/gold), One Big Table vs Star Schema, Semantic vs Physical Modeling, Slowly Changing Dimensions (SCD 0-7), Specialized Dimensions (degenerate/role-playing/junk), Surrogate vs Natural Keys, dbt Modeling Layers and Materializations