Dimensional Modeling Design
Dimensional modeling structures data into fact tables of measurable events and dimension tables of descriptive context, so a query is a filter on dimensions and an aggregate on facts that the.
Definition
Dimensional modeling is the data warehouse design technique that separates measurable events (facts, with foreign keys and additive measures) from descriptive context (dimensions, with attributes and slowly-changing history), so that business queries are uniform filter-then-aggregate operations that the warehouse engine and the user both understand.
Context and scope
Applied in data warehousing, BI, and any analytical store where the query pattern is filter-by-attribute and aggregate-by-measure and the schema must be legible to analysts.
Common misconceptions
- Dimensional modeling is just star schema — star schema is one dimensional model; snowflake is another; the model is the fact-dimension separation, the physical layout is a choice
- Normalization is always better for analytics — normalization is better for update; analytics is read-heavy and the join cost of a normalized model is the penalty dimensional modeling avoids
Anti-patterns
- Putting attributes on the fact table (a customer name on every sale row), denormalizing into the fact and losing the single-source-of-truth dimension
- Modeling a many-to-many between facts and dimensions without a bridge table, producing a fact row per combination that double-counts
Related concepts
Authoritative source
https://www.kimballgroup.com/data-warehouse-business-intelligence-toolkit/
What it is
Dimensional modeling is the data warehouse design technique that separates measurable events (facts, with foreign keys and additive measures) from descriptive context (dimensions, with attributes and slowly-changing history), so that business queries are uniform filter-then-aggregate operations that the warehouse engine and the user both understand. Applied in data warehousing, BI, and any analytical store where the query pattern is filter-by-attribute and aggregate-by-measure and the schema must be legible to analysts. The data is the asset; the pipeline is the liability — it exists only to move and transform data correctly, on time, with a lineage that survives audit.
How it works
Schema is a contract enforced at ingestion, not discovered at query time: bad records are quarantined with their violation, not silently coerced. Transformations are deterministic and idempotent so re-runs repair rather than duplicate. Partitioning by event time (not processing time) makes late data correctable rather than lost. Lineage records every transformation so the path from source to dashboard is reconstructable.
When to apply and pitfalls
Apply when decisions are made from the data and a wrong decision has a cost. The pitfall is treating the pipeline as a one-way function and never validating its output against the source — silent drops and double-counts accumulate until a dashboard disagrees with the ledger. Another is optimizing for throughput at the cost of correctness, dropping the validation that would have caught the corruption.