Dimensional and Analytics Data Modeling

Parent: data analysis · researched 2026-05-31T01:19:36.567Z· 30 sources · 11 concepts · skill da-29-dimensional-data-modeling

The discipline of structuring data for analytics: how to shape facts, dimensions,

Dimensional & Analytics Data Modeling

The discipline of structuring data for analytics: how to shape facts, dimensions, [source]
and layered models so a warehouse or lakehouse is queryable, consistent, and [source]
maintainable. This skill covers the modeling decisions, not the engines or [source]
pipelines that move the data. [source]
Scope boundaries. Document/NoSQL schema design → mongodb-schema-design. [source]
Ingestion/orchestration/ETL → da-13-data-engineering-and-pipelines. Metric & [source]
headless-BI semantic layer → da-18-semantic-layer-headless-bi. OLAP engine [source]
internals → da-28-realtime-olap-databases. This skill owns the analytical [source]
modeling discipline that those skills sit on top of. [source]

1. Kimball dimensional modeling — the core

Dimensional modeling organizes data into fact tables (numeric measurements of [source]
a business process) and dimension tables (the descriptive "who/what/where/ [source]
when/why/how" context, wide and denormalized). Instantiated in a relational DB [source]
this is a star schema: a central fact table joined to dimensions via [source]
primary/foreign keys. [source]
Four-step design process (always in this order): [source]
Select the business process (e.g. orders, shipments, web sessions) - a [source]
process produces one or more fact tables, not a department or report. [source]
Declare the grain - exactly what one fact row represents. This is the [source]
pivotal step; every candidate dimension and fact must be consistent with it. [source]
Prefer the lowest (atomic) grain - it is the most flexible and future-proof. [source]
Identify the dimensions - descriptive context that applies at that grain. [source]
Identify the facts - numeric measures valid at that grain. [source]
Star vs snowflake. A star keeps each dimension as one flat denormalized table. [source]
A snowflake normalizes dimension hierarchies into sub-tables (e.g. product → [source]
brand → category). Kimball discourages snowflaking: it saves little storage, [source]
complicates queries, and hurts BI-tool usability. Normalize a dimension only for [source]
genuine outriggers or very large/volatile sub-hierarchies. [source]
Fact additivity. Facts are additive (summable across all dimensions, e.g. [source]
sales amount), semi-additive (summable across some dims but not time, e.g. [source]
account balances, inventory levels - use periodic snapshots), or non-additive [source]
(ratios, percentages - store the numerator and denominator as additive facts and [source]
compute the ratio at query time, never store the ratio). [source]
Sources: Kimball Group, Four-Step Design Process; [source]
Grain; [source]
Star Schema / OLAP Cube; [source]
A Dimensional Modeling Manifesto, 1997. [source]

2. Conformed dimensions & the enterprise data warehouse bus matrix

A conformed dimension is shared across multiple fact tables/business processes [source]
with identical keys, attribute names, and meanings (or one is a perfect subset of [source]
the other). Conformed dimensions are what let you drill across and integrate [source]
results from different processes (e.g. compare Sales and Returns by the same [source]
Customer and Date dimensions). [source]
The bus matrix is the planning artifact: rows = business processes, [source]
columns = dimensions, shaded cells mark which dims a process uses. Scanning a [source]
column shows where a dimension must be conformed. The matrix is how a team plans [source]
incremental, integrated delivery (deliver one process/data mart at a time, but [source]
plan the shared dimension bus up front so marts integrate later). [source]
Sources: Kimball Group, Enterprise DW Bus Matrix; [source]
Bus Architecture; [source]
The Matrix: Revisited, 2005. [source]

3. Slowly changing dimensions (SCD)

How to handle dimension attributes that change over time. The three basics are [source]
type 1, 2, 3; the rest are hybrids. [source]
Type 2 is the workhorse - most "track history" requirements resolve to type 2. [source]
Sources: Kimball Group, Design Tip #152: SCD Types 0,4,5,6,7, 2013; [source]
Type 0; [source]
Type 1; [source]
Type 7; [source]
SCD overview, 2008. [source]

4. Fact table types

The accumulating snapshot is the only type whose rows are routinely updated. [source]
Factless tables answer "did it happen / what was eligible" questions. The three [source]
core types complement each other and often coexist for the same process. [source]
Sources: Kimball Group, Periodic Snapshot; [source]
Accumulating Snapshot; [source]
Design Tip #133: Factless Fact Tables, 2011; [source]
Design Tip #167: Complementary Fact Table Types, 2014. [source]

5. Keys & specialized dimensions

Surrogate keys - meaningless integer (or hash) primary keys on dimensions, [source]
generated by the warehouse. Required for SCD type 2 (each version gets its own [source]
key), insulates the warehouse from source-system key changes/reuse, and joins [source]
faster than wide natural keys. Facts carry surrogate FKs, not business keys. [source]
Natural / business keys - the source-system identifier (e.g. customer_id). [source]
Keep it as a durable attribute on the dimension; under type 2 it is not [source]
unique (it repeats across versions). The durable key joins all versions of an [source]
Degenerate dimension - a dimension key with no attributes of its own, stored [source]
directly on the fact (e.g. order number, invoice number, ticket ID). No separate [source]
Role-playing dimension - one physical dimension referenced multiple times in [source]
one fact under different roles (e.g. Date as order_date, ship_date, due_date). [source]
Expose via views/aliases with role-specific column names. [source]
Junk dimension - a single table collecting low-cardinality flags and [source]
indicators (yes/no, status codes) that would otherwise clutter the fact, holding [source]
the distinct combinations actually observed. [source]
Sources: Kimball Group, Dimensional Modeling Techniques index; [source]
The 10 Essential Rules of Dimensional Modeling, 2009; [source]
Fact Tables and Dimension Tables, 2003. [source]

6. Methodology comparison — Inmon vs Kimball vs Data Vault 2.0

Data Vault 2.0 building blocks (Dan Linstedt): Hubs store unique business [source]
keys; Links store the relationships (often M:N) between hubs; Satellites [source]
store descriptive attributes and their history, are append-only (every change [source]
is preserved, like type-2 history), and hang off hubs or links. DV2.0 adds hash [source]
keys, hash diffs, and load metadata for parallel, scalable, auditable loads. The [source]
raw vault is not a query layer - you build dimensional marts on top for users. [source]
A common real-world pattern is hybrid: an Inmon/3NF or Data Vault integration [source]
core feeding Kimball star-schema marts for consumption. [source]
Sources: Keboola - Kimball vs Inmon; [source]
Scalefree - Data Vault 2.0 Definition; [source]
Data Vault modeling - Wikipedia; [source]
WhereScape - What is Data Vault 2.0. [source]

7. One Big Table (OBT) vs star schema in columnar cloud warehouses

Modern columnar MPP warehouses (BigQuery, Snowflake, Redshift, Databricks SQL) [source]
change the normalization math. Columnar storage with run-length/dictionary [source]
encoding makes repeated dimension values cheap, and join-heavy star queries incur [source]
cross-node shuffles. So a fully denormalized One Big Table (facts + all [source]
dimension attributes pre-joined into one wide table) is often faster than a star. [source]
Benchmarks: OBT ~10-45% faster than star across many queries; BigQuery ~49% [source]
average improvement (parallel engine, fewer shuffles). Snowflake is mixed — [source]
the star sometimes wins on simpler queries, so test on your engine. [source]
OBT cost: heavy redundancy and painful updates - renaming one product means [source]
rewriting millions of rows, vs a one-row dimension update in a star. SCD history [source]
is also awkward in an OBT. [source]
Recommended hybrid (2024-2026): keep a star schema in the silver layer [source]
for integrity, history (SCD), and exploration; **materialize OBT/wide tables in [source]
the gold layer** to power high-concurrency BI dashboards. dbt makes this a single [source]
extra model - build the OBT as a ref() over your dimensional marts. [source]
Don't reflexively denormalize: a star is still the better default when you need [source]
SCD history, frequent dimension updates, governed conformed dimensions, or [source]
ad-hoc exploration. Reach for OBT for read-heavy, high-concurrency dashboard [source]
Sources: Fivetran - Star Schema vs OBT; [source]
MotherDuck - Star Schema Guide; [source]
CloudQuery - 3NF vs Star Schema. [source]

8. Medallion architecture (bronze / silver / gold)

A lakehouse layering pattern that progressively improves data quality: [source]
Bronze - raw landing, source structure "as-is" plus ingest metadata [source]
(load timestamp, source file, process ID). Append/immutable; no business logic. [source]
Silver - cleansed, conformed, deduplicated, type-cast; matched and merged [source]
to an "enterprise view" of key business entities ("just-enough" cleaning). This [source]
is the natural home for normalized/3NF or dimensional integration models. [source]
Gold - business-level aggregates, dimensional marts, and OBT/wide tables [source]
ready for BI, reporting, and ML features. [source]
It is a reference architecture, not a mandate - add or remove layers to fit. [source]
Databricks guidance: don't write to silver directly from ingestion (schema [source]
drift / corrupt records); prefer streaming reads from bronze for append-only [source]
sources; use Unity Catalog with a separate catalog/schema per layer. Medallion [source]
is orthogonal to Kimball/Inmon/Data Vault - those describe how you model [source]
within a layer (typically silver and gold). [source]
Sources: Databricks - What is Medallion Architecture; [source]
Databricks docs - Medallion lakehouse architecture; [source]
Databricks - Lakehouse Data Modeling: Myths, Truths, Best Practices. [source]

9. dbt modeling layers & materializations

dbt's recommended 3-layer structure maps cleanly onto medallion silver/gold: [source]
Staging (stg_<source>__<entity>) - one model per source table, 1:1 [source]
with the source. Rename/recast/clean only; no joins. Materialize as views. [source]
Organize subfolders by source system. Define raw inputs as sources in [source]
YAML and reference them with source(). [source]
Intermediate (int_<concept>) - purpose-built transformation steps that [source]
combine a handful (~4-6) of staging models. Shift to business-conformed [source]
subfolders by area of concern. Ideal as ephemeral (interpolated as CTEs). [source]
Marts (fct_<process>, dim_<entity>, or plain entity names) - the [source]
business-defined fact and dimension tables, each at its own grain. Materialize [source]
as tables (or incremental). If a mart pulls together more than ~4-5 [source]
concepts, factor out intermediate models. [source]
ref() and source() build the DAG: models reference each other with [source]
ref('model_name') and raw tables with source('schema','table'), so dbt infers [source]
lineage and build order. Set defaults per folder in dbt_project.yml (e.g. [source]
staging→view, marts→table, separate schemas per layer). [source]
Five built-in materializations: view (default, logic only, instant/cheap), [source]
table (stores data), incremental (only transform new/changed rows), ephemeral [source]
(no DB object, inlined as a CTE), materialized_view (platform-managed refresh of [source]
incremental logic). Choose incremental for large append-mostly facts; views for [source]
staging; tables for marts users hit directly. [source]
Sources: dbt Labs, How we structure our dbt projects [source]
(Staging, [source]
Intermediate, [source]
Marts); [source]
Materializations; [source]
Best practices for materializations. [source]

10. Semantic vs physical modeling

Physical model - the actual tables/views in the warehouse: facts, dims, marts, [source]
OBTs, their grain, keys, types, and materializations. This is everything above. [source]
Semantic model - a metadata layer on top of the physical marts that defines [source]
metrics, dimensions, and join paths once, so every BI tool computes "revenue" or [source]
"active users" identically (avoids metric drift). In dbt this is the Semantic [source]
Layer / MetricFlow; semantic marts sit above physical marts. [source]
Keep physical marts clean and conformed; express reusable business metrics in the [source]
semantic layer rather than baking every aggregate into a physical table. **Deep [source]
semantic-layer / headless-BI work belongs to da-18-semantic-layer-headless-bi** — [source]
this skill stops at the modeling boundary and the handoff. [source]
Sources: dbt Labs, Semantic structure / semantic-layer marts; [source]
MotherDuck - Star Schema Guide. [source]

Practical patterns

Always declare the grain in one sentence before modeling anything. "One row [source]
per ___." If you can't, you don't understand the process yet. [source]
Model the atomic grain first. Aggregates are derivable; you can't drill into [source]
detail you didn't keep. [source]
Plan the dimension bus up front, deliver marts incrementally. Conform Date, [source]
Customer, Product early - retrofitting conformance is painful. [source]
Default dimension change handling = type 2 with surrogate keys, unless the [source]
business explicitly only wants the current value (type 1) or "original" (type 0). [source]
Store additive components, not ratios. Compute percentages/averages at query [source]
time from additive numerator+denominator facts. [source]
Hybrid layering: 3NF/Data-Vault or conformed star in silver for integrity + [source]
history; OBT/wide tables in gold for dashboard speed. [source]
In dbt: staging=views 1:1 with sources, intermediate=ephemeral, marts=tables; [source]
one fact/dim concept per mart at its own grain. [source]

Anti-patterns

Mixed grain in one fact table - the cardinal sin; forces double-counting. [source]
Split into separate fact tables. [source]
Storing non-additive ratios as facts - they can't be summed correctly. [source]
Snowflaking everything - normalizing dimensions for "cleanliness"; hurts [source]
usability and performance with negligible storage gain on columnar engines. [source]
Smart/natural keys as fact FKs - couples the warehouse to source key changes [source]
and breaks SCD type 2. Use surrogate keys. [source]
Reflexive OBT everywhere - denormalizing without considering update cost, [source]
SCD history, or that Snowflake sometimes favors the star. Benchmark your engine. [source]
Writing to silver directly from ingestion - schema drift and bad records [source]
leak in; always land in bronze first. [source]
Reports as the design driver - designing the fact table around a specific [source]
report instead of the physical measurement event; build at the atomic event [source]
grain and let reports aggregate. [source]
Baking every metric into physical tables - causes metric drift; define [source]
reusable metrics in the semantic layer. [source]

Troubleshooting

Numbers double-count when joining two facts → you joined fact-to-fact. Never [source]
do that; drill across via conformed dimensions and combine aggregated results. [source]
Totals wrong after summing a snapshot over time → the fact is semi-additive [source]
(balances/levels); don't sum across the time dimension - take a point-in-time or [source]
Dimension table row count exploding → a fast-changing attribute under type 2; [source]
split it into a type-4 mini-dimension. [source]
Can't reproduce a historical report → attributes were handled as type 1 [source]
(overwritten). Convert the relevant attributes to type 2. [source]
Star query slow / shuffling on columnar DW → consider a gold-layer OBT/wide [source]
table for that dashboard; keep the star for exploration. [source]
Same metric differs across dashboards → metric defined per-report in physical [source]
tables; move it to the semantic layer. [source]
dbt build slow / rebuilding huge facts every run → switch the large fact mart [source]
from table to incremental. [source]

References (selected, with years)

Kimball Group - Four-Step Design Process; [source]
Grain; [source]
Bus Matrix; [source]
SCD Design Tip #152 (2013); [source]
Type 7; [source]
Periodic Snapshot; [source]
Accumulating Snapshot; [source]
Factless Fact Tables, Design Tip #133 (2011); [source]
10 Essential Rules (2009). [source]
dbt Labs - How we structure our dbt projects; [source]
Staging; [source]
Intermediate; [source]
Marts; [source]
Materializations; [source]
Semantic-layer marts. [source]
Data Vault - Scalefree DV2.0 Definition; [source]
WhereScape - What is Data Vault 2.0; [source]
Data Vault modeling, Wikipedia. [source]
Inmon vs Kimball - Keboola (2024); [source]
Computer Weekly. [source]
Medallion - Databricks - What is Medallion Architecture; [source]
Databricks docs - Medallion; [source]
Databricks - Lakehouse Data Modeling (2024). [source]
OBT vs Star - Fivetran - Star Schema vs OBT; [source]
MotherDuck - Star Schema Guide; [source]
CloudQuery - 3NF vs Star Schema. [source]

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

← the whole tree · 3D view· how to read this page