Designing Fact and Dimension Tables

A dimensional model is only as good as its tables. Fact tables hold the measurements of a business process; dimension tables hold the context used to filter and group them. Most reporting errors in warehouses trace back to a few design decisions: an unclear grain, a measure summed that should not be, or context scattered across too many tables. This guide covers those decisions, with the most common error demonstrated on real data.

Start with the grain

Kimball calls declaring the grain the step that establishes exactly what a single fact table row represents, and insists it comes before choosing dimensions or facts (Kimball Group: grain). Choose the most atomic grain the source supports, one row per order line, per trip, per click, because detailed rows can always be summarised and summaries can never be broken back down. Then keep one grain per fact table: a table mixing order lines with daily totals will double count the first time someone sums it.

Fact table types

• Transaction: one row per event, such as a trip or an order line. The most common and most flexible.
• Periodic snapshot: one row per entity per period, such as account balance per day. Kimball notes the grain is the period, and rows are usually written even when nothing happened, so the table is uniformly dense (Kimball Group: periodic snapshot).
• Accumulating snapshot: one row per process instance, such as an order, updated as it passes milestones like shipped and delivered, with a date key per milestone.
• Factless: rows that record that something happened or applied, such as attendance or a promotion being active, with no numeric measure.

Additivity: what may be summed

Measures differ in how they roll up. Additive measures can be summed across any dimension; semi-additive measures, such as balances, across all dimensions except time; non-additive measures, such as ratios, not at all (Kimball Group: additive facts). The consequences are easy to underestimate, so here they are on January 2024 New York taxi trips (NYC TLC: trip record data):

import urllib.request
from pathlib import Path
import duckdb

name = "yellow_tripdata_2024-01.parquet"
if not Path(name).exists():
    urllib.request.urlretrieve(f"https://d37ci6vzurychx.cloudfront.net/trip-data/{name}", name)
con = duckdb.connect()
con.execute(f"""CREATE TABLE trips AS SELECT * FROM '{name}'
                WHERE tpep_pickup_datetime >= '2024-01-01' AND tpep_pickup_datetime < '2024-02-01' AND fare_amount >= 0""")
q = lambda sql: con.sql(sql).fetchone()

# additive: sums roll up correctly from any grain
daily_sum, direct_sum = q("""SELECT (SELECT ROUND(SUM(s), 2) FROM (SELECT SUM(fare_amount) AS s FROM trips GROUP BY CAST(tpep_pickup_datetime AS DATE))),
                                    (SELECT ROUND(SUM(fare_amount), 2) FROM trips)""")
print(f"fares: sum of daily sums {daily_sum:,.2f} | direct sum {direct_sum:,.2f}")

# non-additive: averages and distinct counts do not
avg_of_avgs, true_avg = q("""SELECT (SELECT ROUND(AVG(a), 2) FROM (SELECT AVG(fare_amount) AS a FROM trips GROUP BY PULocationID)),
                                    (SELECT ROUND(AVG(fare_amount), 2) FROM trips)""")
print(f"average fare: average of zone averages {avg_of_avgs} | true average {true_avg}")
summed, distinct = q("""SELECT (SELECT SUM(n) FROM (SELECT COUNT(DISTINCT PULocationID) AS n FROM trips GROUP BY CAST(tpep_pickup_datetime AS DATE))),
                               (SELECT COUNT(DISTINCT PULocationID) FROM trips)""")
print(f"active pickup zones: sum of daily counts {summed} | distinct in the month {distinct}")

# a junk dimension: low-cardinality flags collapse into a few combinations
combos, rows = q("SELECT COUNT(*), SUM(n) FROM (SELECT store_and_fwd_flag, RatecodeID, payment_type, COUNT(*) AS n FROM trips GROUP BY ALL)")
print(f"flag combinations: {combos} across {rows} trips")
print(con.sql("""SELECT COALESCE(store_and_fwd_flag, 'null') AS stored, COALESCE(CAST(RatecodeID AS VARCHAR), 'null') AS rate,
                        payment_type AS pay, COUNT(*) AS trips
                 FROM trips GROUP BY ALL ORDER BY trips DESC LIMIT 4""").fetchdf().to_string(index=False))

fares: sum of daily sums 54,596,173.40 | direct sum 54,596,173.40
average fare: average of zone averages 32.58 | true average 18.65
active pickup zones: sum of daily counts 6948 | distinct in the month 260
flag combinations: 49 across 2927159 trips
stored rate  pay   trips
     N    1    1 2176263
     N    1    2  408634
  null null    0  138096
     N    2    1   79034

Sums roll up safely. Adding daily fare totals gives exactly the monthly total, whatever the grouping.

Averages do not. The average of the zone averages is 32.58 dollars; the true average fare is 18.65. Averaging averages gives every zone equal weight, so a zone with a handful of long, expensive trips counts as much as Midtown with a hundred thousand short ones. The fix is Kimball's: store the additive components, total fare and trip count, and divide after summing.

Distinct counts do not. Summing the number of active pickup zones per day gives 6,948; the month had 260 distinct zones. Distinct counts must be computed at the level they are reported, or approximated with sketches designed to merge.

Dimension design

• Denormalise. A product dimension carries category, subcategory, brand and supplier in one table, so reports need one join, not four.
• Use surrogate keys, integers generated by the warehouse, rather than source system IDs, so history can be versioned and sources merged; slowly changing dimensions shows why.
• Descriptive values, not codes. Store "Credit card", not 1.
• Junk dimensions for flags. Rather than a separate dimension for every flag, Kimball recommends combining low-cardinality flags into one junk dimension (Kimball Group: junk dimension). In the taxi data, three columns, the store-and-forward flag, rate code and payment type, form only 49 combinations across nearly three million trips, so one 49-row dimension replaces three foreign keys.
• Degenerate dimensions: identifiers such as an invoice number stay on the fact table with no dimension of their own, useful for grouping lines back into documents.
• Role-playing dimensions: one date or location table used in several roles, such as order date and ship date.

Nulls and unknown members

A fact row with a missing dimension key is a trap: inner joins drop it and totals shrink silently. The taxi data shows how real this is, since the third-largest flag combination above is entirely null, 138,096 trips with no store-and-forward flag or rate code. Give every dimension an explicit "unknown" row and point such facts at it, so they appear in reports under a visible label that someone can investigate, instead of vanishing.

A design checklist

1. Write the grain as one sentence and put it in the table's documentation.
2. List every measure with its additivity, and store components for ratios.
3. Check every foreign key has a matching dimension row, including an "unknown" member for missing values.
4. Decide the history rule for every dimension attribute.
5. Test totals against the source after each load.

How to build a data warehouse applies these steps end to end, and data modelling: star, snowflake and data vault sets them in context.

Data modelling techniques and star and snowflake schemas are part of the data warehousing module of the Data Engineering course.

Sources

• Grain (Kimball Group) — Kimball Group
• Additive, semi-additive and non-additive facts (Kimball Group) — Kimball Group
• Periodic snapshot fact tables (Kimball Group) — Kimball Group
• Junk dimensions (Kimball Group) — Kimball Group
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission