Dimensional Modeling Facts Dimensions and Grain

Published by Programming Line in Data Engineering in Depth. Last updated .

Purpose: choose facts, dimensions, and grain before writing SQL

Dimensional modeling is the part of data engineering where event-shaped source data becomes tables that analysts can query without reverse-engineering operational systems. The model is built around three decisions. A fact table stores measurements from a business process, such as order line amount, shipment duration, or support ticket count. A dimension table stores descriptive context, such as customer, product, date, store, or channel. The grain is the exact meaning of one row in a fact table. If grain is vague, every downstream metric becomes fragile, because joins and aggregations can silently count the wrong thing.

The outcome of this lesson is a practical method for designing a star schema: name the business process, declare the grain in one sentence, choose facts that match that grain, attach dimensions at the same level of detail, and test the result with queries that expose double counting. This connects directly to data engineering because warehouse tables are not just storage. They are interfaces between ingestion, transformation, semantic layers, dashboards, finance reports, and machine learning features.

How a dimensional model works internally

A star schema places a narrow, usually long fact table at the center and surrounds it with wider dimension tables. The fact table contains foreign keys to dimensions plus numeric facts. The dimension tables contain one row per business entity or per version of that entity. Query engines like this shape because filters on descriptive attributes can reduce dimension rows first, then join to the fact table and aggregate compact numeric columns.

The most important internal rule is additivity. A fact is additive when it can be summed across every dimension, such as revenue at order-line grain. It is semi-additive when it can be summed across some dimensions but not time, such as account balance. It is non-additive when summing is meaningless, such as margin percentage. Non-additive values are often stored as numerator and denominator facts so the correct ratio can be calculated after aggregation.

Dimensions provide slicing language. A product dimension may contain SKU, brand, category, launch date, and active flag. A customer dimension may contain region, segment, signup cohort, and customer status. The warehouse usually uses surrogate keys, such as product_key or customer_key, because source-system natural keys can change, collide across systems, or need multiple historical versions. The fact table keeps those surrogate keys so it joins to the correct dimension row.

Grain controls every other choice. At order-line grain, one row means one product line on one order. Quantity and extended amount fit naturally. Order-level discount may need allocation, because it does not belong to a single line unless a business rule distributes it. At daily inventory snapshot grain, one row means one product at one location at the end of one day. Ending quantity fits, but individual sale price does not. When a source contains multiple processes, such as order placement and shipment fulfillment, separate fact tables are usually clearer than one mixed table.

Schema anatomy

A typical dimensional schema has predictable parts: dimension surrogate keys, natural keys, descriptive attributes, effective dates for history when needed, fact foreign keys, degenerate dimensions such as order_number stored directly on a fact, and measures. Naming should make the grain visible. A table named fact_order_line is better than fact_sales when the row is specifically one order line.

CREATE TABLE dim_product (
  product_key INTEGER PRIMARY KEY,
  sku TEXT NOT NULL,
  product_name TEXT NOT NULL,
  category TEXT NOT NULL,
  brand TEXT NOT NULL
);

CREATE TABLE dim_date (
  date_key INTEGER PRIMARY KEY,
  calendar_date TEXT NOT NULL,
  month_name TEXT NOT NULL,
  fiscal_quarter TEXT NOT NULL
);

CREATE TABLE fact_order_line (
  order_line_key INTEGER PRIMARY KEY,
  order_number TEXT NOT NULL,
  line_number INTEGER NOT NULL,
  order_date_key INTEGER NOT NULL,
  product_key INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  extended_amount NUMERIC NOT NULL,
  FOREIGN KEY (order_date_key) REFERENCES dim_date(date_key),
  FOREIGN KEY (product_key) REFERENCES dim_product(product_key)
);

This definition declares the grain through the key columns and table name: one row per order line. The order_number is a degenerate dimension because analysts often filter or drill to an order, but creating a separate order dimension with only an order number would add little value. The product and date keys point to descriptive tables that can be reused across facts.

Worked example 1: one order line per row

The first example shows the cleanest case: source data already arrives at the intended grain. Each order line has a product, quantity, and extended amount. A monthly category report can sum the fact without special handling.

SELECT
  d.month_name,
  p.category,
  SUM(f.quantity) AS units_sold,
  SUM(f.extended_amount) AS revenue
FROM fact_order_line AS f
JOIN dim_date AS d ON f.order_date_key = d.date_key
JOIN dim_product AS p ON f.product_key = p.product_key
GROUP BY d.month_name, p.category
ORDER BY d.month_name, p.category;

If the data contains two January apparel lines for 2 units at 40.00 and 1 unit at 25.00, the deterministic output for January Apparel is 3 units and 65.00 revenue. The aggregation is safe because quantity and extended_amount are additive at order-line grain.

Worked example 2: header discounts need allocation

Now add an order-level discount. A source order may have two lines and a 10.00 coupon on the whole order. If that coupon is copied onto every line, summing discount by product produces 20.00, not 10.00. The fact grain exposes the problem: the discount is at order grain, while the table is at order-line grain.

WITH order_lines AS (
  SELECT 'A100' AS order_number, 1 AS line_number, 60.00 AS line_amount, 10.00 AS order_discount
  UNION ALL
  SELECT 'A100', 2, 40.00, 10.00
), allocated AS (
  SELECT
    order_number,
    line_number,
    line_amount,
    order_discount * line_amount / SUM(line_amount) OVER (PARTITION BY order_number) AS allocated_discount
  FROM order_lines
)
SELECT
  order_number,
  line_number,
  line_amount,
  ROUND(allocated_discount, 2) AS allocated_discount,
  ROUND(line_amount - allocated_discount, 2) AS net_amount
FROM allocated
ORDER BY line_number;

The output is line 1 discount 6.00 and net amount 54.00, then line 2 discount 4.00 and net amount 36.00. The allocated discounts sum back to the order coupon. The design choice is explicit: either create a separate fact_order table for order-level discounts, or allocate the discount into fact_order_line using a documented rule.

Worked example 3: periodic snapshots are different facts

Inventory is commonly modeled as a periodic snapshot, not a transaction fact. A row represents product, location, and date at a measurement point. The ending quantity is semi-additive: it can be summed across products or locations for one day, but summing seven daily balances does not mean weekly inventory.

CREATE TABLE fact_inventory_daily_snapshot (
  snapshot_date_key INTEGER NOT NULL,
  product_key INTEGER NOT NULL,
  warehouse_key INTEGER NOT NULL,
  ending_on_hand_quantity INTEGER NOT NULL,
  PRIMARY KEY (snapshot_date_key, product_key, warehouse_key)
);

SELECT
  snapshot_date_key,
  SUM(ending_on_hand_quantity) AS total_units_on_hand
FROM fact_inventory_daily_snapshot
GROUP BY snapshot_date_key
ORDER BY snapshot_date_key;

The expected behavior is one inventory total per day. A weekly report should average daily totals, take the final day, or show minimum and maximum depending on the question. It should not sum the daily totals unless the metric is explicitly unit-days.

Design choices and trade-offs

The first trade-off is star schema versus a highly normalized warehouse model. Stars duplicate descriptive text across dimensions and often flatten hierarchies, but they make analytic SQL shorter and reduce accidental many-hop joins. Normalized models can preserve source structure and reduce update anomalies, but analysts may need complex joins before they can answer basic questions.

The second trade-off is transaction facts versus snapshots. Transaction facts preserve each event and support detailed reconciliation. Snapshots make state easy to query when the source only stores current values or when state over time matters. Snapshots cost more storage because unchanged values are repeated, but columnar warehouses compress repeated keys and low-cardinality attributes well.

The third trade-off is slowly changing dimensions. Type 1 overwrites attributes such as customer_segment, which keeps the dimension simple but changes historical reporting. Type 2 inserts a new dimension row for a changed customer and uses effective dates, which preserves history but requires the fact loading process to choose the dimension version valid at event time. Type 2 is worth the complexity when reports must answer questions like revenue by segment as it was known on the order date.

Failure modes and troubleshooting

Symptom: revenue doubles after joining customer attributes. The cause is usually a dimension with multiple current rows per natural key or a fact joining on natural customer_id instead of the surrogate key. Diagnose by counting matches per fact row: group by the fact primary key after the join and look for counts above one. Correct it by enforcing one current dimension row per natural key or by resolving the proper surrogate key during the fact load.

Symptom: monthly order count changes when product is added to the query. The cause is mixed grain: order count is stored on every order line. Diagnose by comparing COUNT(*) with COUNT(DISTINCT order_number). Correct it by storing line_count facts only at line grain, counting distinct orders in a semantic layer, or creating a separate fact_order table with one row per order.

Symptom: historical segment reports shift after a customer profile update. The cause is a Type 1 dimension where the business expected Type 2 behavior. Diagnose by checking whether the dimension has effective_from, effective_to, and current flag columns, then sample facts before and after an attribute change. Correct it by implementing Type 2 history and rekeying facts if the old version can be reconstructed.

Symptom: inventory appears inflated in weekly totals. The cause is summing semi-additive snapshot balances across dates. Diagnose by reviewing the fact grain and checking whether the metric is a balance or an event quantity. Correct it by using end-of-period, average balance, or another aggregation rule that matches the business question.

Reliability and performance implications

Dimensional models improve reliability when grain is written down and tested. A useful pipeline test verifies uniqueness of the fact grain, referential integrity to every dimension, nonnegative constraints where business rules require them, and reconciliation totals against source extracts. Late-arriving dimensions need a deliberate strategy: load an unknown member key, retry dimension resolution later, or infer a minimal dimension row and enrich it when details arrive.

Performance depends on fact size, partitioning, clustering, and filter paths. Large facts are often partitioned by event date or snapshot date. Frequently filtered keys, such as customer_key or product_key, may be clustering candidates in warehouses that support it. Dimensions should remain query-friendly: very wide JSON attributes inside dimensions make ad hoc analysis harder, while separate bridge tables may be needed for many-to-many relationships such as a product belonging to multiple campaigns.

Hands-on lab: build and test a small star

Prerequisites: a SQL engine that supports common table expressions and window functions, such as SQLite with a recent command-line shell, DuckDB, PostgreSQL, or a cloud warehouse worksheet. The lab uses temporary sample rows, so cleanup is simply dropping the created tables or closing an in-memory session.

  1. Create dim_date and dim_product using the schema shown earlier.
  2. Create fact_order_line with one row per order line.
  3. Insert two products, one January date, and three order lines. Include order A100 with two lines and order A101 with one line.
  4. Run the category revenue query and verify that totals equal the sum of the inserted line amounts.
  5. Add an order_discount column to a staging query, not directly to the fact, and allocate the discount proportionally before loading net_amount.
  6. Run a duplicate-grain check: group by order_number and line_number and return groups having count greater than one. Verification passes when the query returns no rows.
SELECT
  order_number,
  line_number,
  COUNT(*) AS row_count
FROM fact_order_line
GROUP BY order_number, line_number
HAVING COUNT(*) > 1;

For rollback, drop the three lab tables in reverse dependency order: fact_order_line, dim_product, and dim_date. In a shared development warehouse, use a scratch schema so table names do not collide with team models.

Assessment exercises

  1. A subscription system emits one row per customer per month with ending active seats and monthly recurring revenue. Which measures are additive, semi-additive, or non-additive, and what grain sentence would you write?
  2. An orders source has order headers, order lines, payments, and shipments. Propose fact tables for the four processes and explain one metric that would be wrong if they were merged into a single fact.
  3. A customer changes region in March. Finance asks for January revenue by the region assigned in January, while Sales asks for all historical revenue by the current region. Which dimension handling would support both?
  4. A dashboard joins fact_order_line to a campaign table and revenue increases by 18 percent. What diagnostic query would you run first, and what modeling structure might fix the relationship?

Summary

Facts, dimensions, and grain are the control points of dimensional modeling. Facts record measurable events or snapshots, dimensions describe the who, what, where, and when, and grain states exactly what one fact row means. Once grain is explicit, design decisions become testable: measures either fit the grain or need allocation, a separate fact, or a different aggregation rule. A good data engineering pipeline protects that model with uniqueness checks, dimension key resolution, reconciliation, and documented handling for history and late-arriving data.