Kimball Dimensional Modeling

Practitioner's guide for designing and auditing Kimball-style dimensional models.

Workflow Decision Tree

Determine the task, then follow the appropriate path:

"Create a dimensional model" -> Follow the Four-Step Design Process

1. Select the business process
2. Declare the grain
3. Identify the dimensions
4. Identify the facts

"What fact table pattern should I use?" -> See Fact Table Patterns

• Transaction (default) — one row per discrete event
• Periodic snapshot — one row per entity per time period
• Accumulating snapshot — one row per process lifecycle
• Factless — event occurrence or many-to-many associations

"How should I handle changing dimensions?" -> See SCD Reference

• Default to Type 1 (overwrite) for most attributes
• Upgrade to Type 2 (add new row) only when point-in-time analysis is needed

"Help me with special dimension patterns" -> See Dimensions Reference

• Date dimension, conformed dimensions, junk dimensions, role-playing dimensions, bus matrix

"Audit my existing model" -> Use the Audit Checklist below, with detailed anti-patterns in Anti-Patterns Reference

"Plan an engagement end-to-end" -> See Engagement Playbook for Discovery -> Design -> Implementation -> Validation phases

Core Principles

• One business process per fact table. Never combine different processes.
• Declare the grain first. Write: "One row represents one [thing]." Every column must be consistent with this statement.
• Go atomic. Store the finest grain the source system provides. Aggregate in marts, not fact tables.
• Surrogate keys on all dimensions. Never use operational/natural keys as PKs.
• Wide dimensions. A good dimension has 20-50+ columns of descriptive attributes.
• Facts are numeric. Text belongs in dimensions or as degenerate dimensions.
• Store components, not ratios. Keep numerator and denominator so ratios can be recalculated at any aggregation level.
• Conformed dimensions enable cross-process analysis. Share dim_date, dim_customer, dim_product across all fact tables.

Naming Conventions

Audit Checklist

Quick checklist for reviewing existing models. See Anti-Patterns Reference for detailed explanations.

1. Grain statement exists and is documented
2. No mixed grains in any fact table
3. Surrogate keys on all dimensions
4. Natural keys preserved as attributes for traceability
5. Facts are numeric — no text in fact columns
6. Facts are consistent with the declared grain
7. Additive facts are truly additive across all dimensions
8. Semi-additive facts use snapshot tables
9. Non-additive facts store components, not just ratios
10. Conformed dimensions are shared (not duplicated)
11. Date dimension exists and is conformed
12. Degenerate dimensions live in the fact table
13. No One Big Table patterns
14. Referential integrity — all FKs resolve

dbt Project Layout

models/
├── staging/           -- stg_{source}_{entity}.sql (clean, rename, cast)
├── intermediate/      -- int_{entity}_{transform}.sql (business logic)
├── dimensions/        -- dim_{entity}.sql (conformed, shared)
├── facts/             -- fct_{process}.sql (one per business process)
└── marts/             -- mart_{domain}_{use_case}.sql (wide, denormalized)

Build order: staging -> dimensions -> facts -> marts.