What are Kimball's ten essential rules of dimensional modeling?
Margy Ross of the Kimball Group published this ten-rule checklist in May 2009 as a single rulebook to use when designing or reviewing dimensional models. The rules are short and each one closes off a common design mistake.
- Rule 1. Load detailed atomic data into dimensional structures. Aggregate later, never earlier.
- Rule 2. Structure dimensional models around business processes. One model per real business event, not per report.
- Rule 3. Ensure that every fact table has an associated date dimension table. Time analysis without a proper date dimension is unreliable.
- Rule 4. Ensure that all facts in a single fact table are at the same grain or level of detail. Mixed grain breaks aggregation.
- Rule 5. Resolve many-to-many relationships in fact tables. Use bridge tables when needed.
- Rule 6. Resolve many-to-one relationships in dimension tables. Keep the dimensions denormalized enough to query cleanly.
- Rule 7. Store report labels and filter domain values in dimension tables. Labels do not belong in fact rows.
- Rule 8. Make certain that dimension tables use a surrogate key. Natural keys change, surrogates do not.
- Rule 9. Create conformed dimensions to integrate data across the enterprise. Shared dimensions are what let separate marts talk to each other.
- Rule 10. Continuously balance requirements and realities to deliver a DW/BI solution that's accepted by business users and that supports their decision-making.
Rules 1 through 4 address common first-project mistakes. Rule 9, conformed dimensions, is the one that stops a warehouse from becoming a collection of disconnected marts as it grows.
Why start from a business process rather than a report?
The second Kimball rule is easy to skip. Teams that start from a specific report end up with fact tables that answer only that report, then need a full redesign when the next question arrives. A model built around a business process (order taken, shipment sent, invoice paid, meter reading captured) supports every report that touches that process, including ones nobody has asked for yet.
A useful test: name the fact table after the verb of the business event, not the noun of the report. A sales_order_line fact serves the weekly revenue report, the daily backlog report, and the quarterly customer cohort analysis. A weekly_revenue_summary fact serves only the report in its name.
What does grain discipline look like in practice?
Grain is the level of detail in one fact row. Rule 4 says every row in a fact table must sit at the same grain. In a retail sales fact, that grain is usually one row per transaction line at one point in time. Mixing daily totals into the same table as line-level rows makes every aggregate query wrong until the table is split.
Grain declaration is a one-line document, but it drives everything downstream: the primary key, the loading pattern, the storage size, and the query patterns. Warehouses that end up with fact tables of unknown grain almost always need a full reload before they can be trusted again.
Which pipeline practices keep a warehouse trustworthy?
Pipeline guidance comes down to a short list. Pipelines should be reproducible, idempotent, and immutable, which means running the same pipeline on the same input produces the same result and does not corrupt earlier loads. Incremental loading (change data capture or a modified-timestamp watermark) becomes the usual pattern once full reloads take too long or cost too much.
Automatic retry logic prevents a single transient failure from blocking the entire warehouse. Monitoring with alerts on row counts, freshness, and schema drift catches source-system changes before analysts see broken dashboards. Modular transformations (short SQL models that build on each other) are testable and reusable, which is the approach tools such as dbt are built around.
How should governance and data quality be handled?
Governance turns a warehouse from a technical asset into an organizational one. The minimum set is documented data ownership per table, a business glossary that defines every widely used metric, and a data catalog that makes tables discoverable. Access should be role-based rather than user-based, and access reviews should run at least once a quarter so leavers and role changes do not accumulate stale permissions.
Data quality checks belong at both ends of the pipeline. Source-side checks catch bad data before it enters the warehouse: null rates on required fields, referential integrity against known keys, and range checks on numeric measures. Warehouse-side checks catch modeling drift: row-count reconciliation against sources, uniqueness on primary keys, and cross-table totals that must match. Automating these checks and failing the pipeline on breach is more effective than a monthly manual review.
Snowflake, BigQuery and Redshift encrypt data at rest by default, but transparent data encryption must be turned on manually for Azure Synapse dedicated SQL pools, so check the setting on your platform. Sensitive fields (personal identifiers, medical records, financial account numbers) should be masked, tokenized, or dropped before they land in analytics tables that a wide audience can query.
What performance work pays off first?
The first performance lever is layout. Partition large fact tables by a natural time column so that queries for a single month or day scan only that partition. Cluster or sort within the partition on the columns that most queries filter or join on. On a columnar warehouse, both moves cut query cost sharply because the engine can skip entire storage blocks.
The second lever is materialization. Precompute aggregates that many dashboards read, and refresh them on a schedule that matches how fresh the data needs to be. Snowflake (Enterprise Edition and above) and BigQuery maintain materialized views automatically in the background, while Amazon Redshift refreshes them with REFRESH MATERIALIZED VIEW or an optional auto refresh setting.
The third lever is workload separation. Give heavy transformation jobs their own compute so a slow ELT run cannot starve the BI queries that analysts run interactively. All four major cloud warehouses support this with separate warehouses, reservations, or workload management queues.
How do lakehouse patterns change the practices?
Lakehouse platforms add a layer to the picture but do not replace the older rules. Databricks describes a data lakehouse as one that combines the openness and scalability of data lakes with the reliability and governance of data warehouses in a single platform. Grain, conformed dimensions, surrogate keys, and quality checks apply on top of Delta or Iceberg tables the same way they apply on Snowflake or BigQuery.
The main practical shift is that raw and modeled data now live in the same storage layer, which makes it easier to reprocess history when a modeling rule changes. Teams still need a clear boundary between the raw bronze layer, the cleaned silver layer, and the modeled gold layer, otherwise the shared storage becomes a swamp.