Fact Tables vs Dimension Tables (Grain, Types, SQL)

Grain: the decision that comes first

Grain is a precise sentence, not a vague idea — "one row per order" and "one row per order line item" are two different grains, and mixing them in the same fact table breaks every aggregate query built on top of it. Deciding grain before writing a single CREATE TABLE statement is the single most consequential decision in dimensional modeling, because changing it later usually means rebuilding the table from scratch.

Structural comparison

Additive, semi-additive, and non-additive facts

-- Additive: safe to SUM across every dimension, including time
SELECT SUM(revenue) FROM fact_sales;

-- Semi-additive: safe to SUM across accounts, NOT across time
SELECT SUM(account_balance) FROM fact_daily_balance WHERE date_key = 20260810;  -- valid
-- SUM(account_balance) across all dates would NOT represent a meaningful total

-- Non-additive: must be recalculated from components after aggregation, never summed directly
SELECT SUM(profit) * 100.0 / NULLIF(SUM(revenue), 0) AS margin_pct
FROM fact_sales;

Treating a non-additive measure like margin percentage as though it were additive — averaging a column of pre-computed percentages instead of recomputing from the underlying revenue and profit — is one of the most common silent errors in warehouse reporting, since it looks like a valid query and returns a plausible-looking wrong number.

Three types of fact tables

• Transaction fact table — one row per discrete event (an order, a click, a shipment). The most common type, and typically the largest table in a warehouse.
• Periodic snapshot fact table — one row per entity per fixed time interval, such as a daily balance or a monthly inventory count, useful for point-in-time trend reporting.
• Accumulating snapshot fact table — one row per instance of a process with a defined start and end (an order moving through placed → shipped → delivered), with the same row updated in place as each milestone completes.

A complete SQL example

CREATE TABLE dim_date (
  date_key    INT PRIMARY KEY,   -- e.g. 20260810
  full_date   DATE,
  day_of_week VARCHAR(10),
  month_name  VARCHAR(10),
  fiscal_quarter INT
);

CREATE TABLE dim_customer (
  customer_key   INT PRIMARY KEY,
  customer_name  VARCHAR(100),
  region         VARCHAR(50)
);

-- Grain: one row per order line item
CREATE TABLE fact_order_lines (
  order_line_id  BIGINT PRIMARY KEY,
  date_key       INT REFERENCES dim_date(date_key),
  customer_key   INT REFERENCES dim_customer(customer_key),
  product_key    INT,
  quantity       INT,
  unit_price     DECIMAL(10,2),
  line_revenue   DECIMAL(12,2)
);

SELECT c.region, d.month_name, SUM(f.line_revenue) AS revenue
FROM fact_order_lines f
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_date d     ON f.date_key = d.date_key
GROUP BY c.region, d.month_name;

Common mistakes

• Not writing down the grain explicitly before building the table, leading to mixed-grain rows that quietly double-count in aggregates.
• Averaging a non-additive measure directly instead of recomputing it from its additive components after aggregation.
• Storing descriptive text in the fact table instead of a dimension, bloating the largest table in the warehouse with repeated strings.
• Letting a dimension change without an SCD strategy — see SCD Type 1 vs 2 vs 3 vs 4 for how to handle a dimension attribute that changes over time.