Fact Table Design

Chapter 8 established what a fact table is. This chapter is about the decisions made inside one: which measures earn a column, what an empty measure means, which identifiers get a dimension table, how a header charge reaches line grain, and what happens when a row arrives late or has to be corrected.

Every column below was an argument somebody had. Open each one to see the decision behind it, what it makes answerable, and the bug that ships when it goes the other way.

measure

A numeric value in a fact that you aggregate (amount, quantity, balance).

additivity

Across which dimensions a measure can be safely summed (additive / semi / non).

degenerate dimension

A dimension key (like a bill number) stored on the fact with no dimension table.

restatement

A correction to a fact value that has already been reported.

-- GRAIN: one row per vending machine per trading day.
create table fact_vending_day (
  machine_key    bigint not null references dim_machine(machine_key),
  date_key       int    not null references dim_date(date_key),
  machine_serial text   not null,      -- id only: no dimension table
  units_sold     int    not null,      -- additive
  stock_on_hand  int    not null,      -- semi-additive: not across days
  fault_minutes  int,                  -- null = no telemetry, 0 = no fault
  takings_local  numeric(12,2) not null,
  fx_rate        numeric(10,6) not null,
  takings_gbp    numeric(12,2) not null,
  primary key (machine_key, date_key)
);