Kimball Data Modeling Cheat Sheet

Updated 2026-04-21

Next Topic: Kusto Query Language (KQL) Cheat Sheet

🧠Study flashcards on this topic95 cards · spaced repetition→

Kimball dimensional modeling is a data warehouse design methodology introduced by Ralph Kimball in 1996, focused on creating business-driven, user-friendly star schemas that optimize query performance and analytical reporting. At its core, the approach organizes data into fact tables (measurable business events) and dimension tables (descriptive context), with a bottom-up implementation strategy that delivers rapid, incremental value to specific business processes. The methodology's enduring influence lies in the conformed dimension concept — shared, standardized dimensions that enable enterprise-wide consistency and cross-process analysis through a technique called drilling across, which remains essential even in modern cloud data platforms like Snowflake, Databricks, and BigQuery for maintaining semantic coherence across distributed data marts.

What This Cheat Sheet Covers

This topic spans 12 focused tables and 104 indexed concepts, 95 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Core Design MethodologyTable 2: Fact Table TypesTable 3: Fact Table Design PatternsTable 4: Dimension Table Core ConceptsTable 5: Slowly Changing Dimension TypesTable 6: Dimension Design PatternsTable 7: Advanced Dimension TechniquesTable 8: ETL and Data Quality PatternsTable 9: Query and Analysis PatternsTable 10: Fact Table Advanced PatternsTable 11: Special Purpose SchemasTable 12: Best Practices and Guidelines

Quick IndexSubscribe to unlock

A jump-to index of every table row in this cheat sheet.

Mind MapSubscribe to unlock

An interactive map of every table and concept in this topic.

Table 1: Core Design Methodology

These are the load-bearing ideas that everything else in Kimball rests on — the four-step design process, the star schema, and above all the grain declaration and conformed dimensions that hold an enterprise warehouse together. Get these right and the rest of the model tends to fall into place; get the grain wrong and no amount of later cleverness will save you.

Concept	Example	Description
Four-Step Design Process	`1. Select business process` `2. Declare grain` `3. Identify dimensions` `4. Identify facts`	• Sequential design steps forming the foundation of every dimensional model • grain declaration is the pivotal step that determines fact table row uniqueness
Star Schema	`FactSales` → joins to → `DimProduct, DimDate, DimCustomer, DimStore`	• Denormalized structure with a central fact table surrounded by dimension tables • optimizes query performance and simplifies business user comprehension
Snowflake Schema	`DimProduct` → `DimCategory` → `DimBrand`	• Normalized variant where dimension hierarchies are broken into secondary tables • Kimball recommends avoiding snowflakes because they are harder for users to navigate and can hurt query performance
Grain Declaration	`One row per product sold per transaction per store per day`	• Precise statement of what a single fact table row represents • must be the lowest atomic level to enable maximum flexibility for slicing and aggregation
Bus Matrix	Rows = business processes Columns = dimensions Shaded cells = shared dimensions	Enterprise planning tool showing which conformed dimensions are used by which business processes, enabling integrated incremental development
Conformed Dimensions	Same `DimCustomer` used in FactSales, FactReturns, FactServiceCalls	• Standardized master dimensions shared across multiple fact tables • essential for cross-process analysis and consistent business definitions enterprise-wide
Conformed Facts	`Revenue` defined identically in FactSales and FactOnlineSales	• Same measurement appearing in separate fact tables must have identical technical definitions • identically named if consistent; differently named if incompatible
Value Chain	Retailer: Purchasing → Warehousing → Retail Sales	• Identifies the natural flow of primary business processes • each process typically spawns at least one atomic fact table with unique metrics, grain, and dimensionality
Drilling Across	Query FactSales + FactInventory using identical `DimProduct, DimDate` row headers	Combining metrics from separate fact tables by making queries with matching conformed dimension attributes, then stitching results — core benefit of bus architecture
Bottom-Up Approach	Start with Sales data mart → add Marketing mart → add Inventory mart	• Kimball's incremental methodology delivering rapid business value through focused data marts connected by conformed dimensions • contrasts with Inmon's top-down enterprise warehouse
Graceful Extensions	Add new `PromotionKey` FK to existing FactSales without breaking queries	• Dimensional models are resilient to change — new facts, dimensions, and attributes can be added without altering existing BI queries • grain can be made more atomic by restating the fact table
Opportunity/Stakeholder Matrix	Bus matrix rows replaced with business stakeholder groups to show who benefits from each process	Variant of the bus matrix where dimension columns are replaced by business stakeholder groups, used to communicate data warehouse value and prioritize development

Table 2: Fact Table Types

Fact tables hold the measurements, but how you capture those measurements over time gives you distinct flavors — a row per event, a row per period, or a row that gets revisited as a process moves through milestones. Knowing which type fits the business question is half the design work, since the wrong choice forces awkward queries later.

Type	Example	Description
Transaction Fact Table	`OrderID, ProductID, CustomerID, Quantity, Revenue` (one row per line item sold)	• Records individual business events at the most atomic grain • most common fact table type representing measurements as they occur

The rest of this section is part of the full cheat sheet

Table 3: Fact Table Design Patterns

Once the fact table type is settled, these patterns handle the recurring details that trip people up — whether a measure can be summed, how to allocate header amounts down to line items, where to stash an order number, and why a fact table should never carry a NULL foreign key. They're the small decisions that separate a model that aggregates cleanly from one that quietly produces wrong totals.

Pattern	Example	Description
Additive Facts	`SalesAmount, Quantity, Cost`	Facts that can be summed across all dimensions — most desirable and common fact type enabling unrestricted aggregation

The rest of this section is part of the full cheat sheet

Table 4: Dimension Table Core Concepts

Dimensions supply the who, what, where, and when that makes facts meaningful, and these are the building blocks every dimension relies on — surrogate keys, the all-important date dimension, and the handful of structural tricks like role-playing and junk dimensions that keep the model clean. The recurring theme is denormalize wide and never store a NULL where a descriptive label belongs.

Concept	Example	Description
Surrogate Key	`ProductKey = 12345` (integer sequence)	• System-generated integer primary key replacing natural keys • enables SCD handling, protects from source system changes, and improves join performance

The rest of this section is part of the full cheat sheet

Table 1: Core Design Methodology

Concept	Example	Description
Four-Step Design Process	`1. Select business process` `2. Declare grain` `3. Identify dimensions` `4. Identify facts`	• Sequential design steps forming the foundation of every dimensional model • grain declaration is the pivotal step that determines fact table row uniqueness
Star Schema	`FactSales` → joins to → `DimProduct, DimDate, DimCustomer, DimStore`	• Denormalized structure with a central fact table surrounded by dimension tables • optimizes query performance and simplifies business user comprehension
Snowflake Schema	`DimProduct` → `DimCategory` → `DimBrand`	• Normalized variant where dimension hierarchies are broken into secondary tables • Kimball recommends avoiding snowflakes because they are harder for users to navigate and can hurt query performance
Grain Declaration	`One row per product sold per transaction per store per day`	• Precise statement of what a single fact table row represents • must be the lowest atomic level to enable maximum flexibility for slicing and aggregation
Bus Matrix	Rows = business processes Columns = dimensions Shaded cells = shared dimensions	Enterprise planning tool showing which conformed dimensions are used by which business processes, enabling integrated incremental development
Conformed Dimensions	Same `DimCustomer` used in FactSales, FactReturns, FactServiceCalls	• Standardized master dimensions shared across multiple fact tables • essential for cross-process analysis and consistent business definitions enterprise-wide
Conformed Facts	`Revenue` defined identically in FactSales and FactOnlineSales	• Same measurement appearing in separate fact tables must have identical technical definitions • identically named if consistent; differently named if incompatible
Value Chain	Retailer: Purchasing → Warehousing → Retail Sales	• Identifies the natural flow of primary business processes • each process typically spawns at least one atomic fact table with unique metrics, grain, and dimensionality
Drilling Across	Query FactSales + FactInventory using identical `DimProduct, DimDate` row headers	Combining metrics from separate fact tables by making queries with matching conformed dimension attributes, then stitching results — core benefit of bus architecture
Bottom-Up Approach	Start with Sales data mart → add Marketing mart → add Inventory mart	• Kimball's incremental methodology delivering rapid business value through focused data marts connected by conformed dimensions • contrasts with Inmon's top-down enterprise warehouse
Graceful Extensions	Add new `PromotionKey` FK to existing FactSales without breaking queries	• Dimensional models are resilient to change — new facts, dimensions, and attributes can be added without altering existing BI queries • grain can be made more atomic by restating the fact table
Opportunity/Stakeholder Matrix	Bus matrix rows replaced with business stakeholder groups to show who benefits from each process	Variant of the bus matrix where dimension columns are replaced by business stakeholder groups, used to communicate data warehouse value and prioritize development

Table 2: Fact Table Types

Type	Example	Description
Transaction Fact Table	`OrderID, ProductID, CustomerID, Quantity, Revenue` (one row per line item sold)	• Records individual business events at the most atomic grain • most common fact table type representing measurements as they occur

Table 3: Fact Table Design Patterns

Pattern	Example	Description
Additive Facts	`SalesAmount, Quantity, Cost`	Facts that can be summed across all dimensions — most desirable and common fact type enabling unrestricted aggregation

Table 4: Dimension Table Core Concepts

Concept	Example	Description
Surrogate Key	`ProductKey = 12345` (integer sequence)	• System-generated integer primary key replacing natural keys • enables SCD handling, protects from source system changes, and improves join performance