Facts, dimensions, and picking a grain

Your source systems are modelled for writing. A checkout service is normalised so that updating a product price touches one row. That is correct for the application and hostile for analysis, where a simple question requires an eleven-table join and knowing which of the three status columns is the real one.

Dimensional modelling reorganises the same data for reading.

Facts and dimensions#

A fact table holds things that happened: measurements, at a grain, with foreign keys. Long, narrow, append-mostly. Its columns are numbers you would add up, plus keys.

A dimension table holds things that exist: customers, products, dates, stores. Wide, short, full of descriptive text. Its columns are things you would filter or group by.

The test is a sentence: facts are what you measure; dimensions are how you slice it. Revenue is a fact. Country is a dimension. "Revenue by country" is a fact aggregated across a dimension, and a dimensional model makes that query obvious.

select
    d.country,
    date_trunc('month', f.ordered_at) as month,
    sum(f.revenue_usd)                as revenue,
    count(distinct f.order_id)        as orders
from fct_order_lines f
join dim_customers   d on d.customer_key = f.customer_key
group by 1, 2

That is a star schema doing its job: one hop from the fact to any dimension. No chain of joins, no ambiguity about which path to take.

Grain first, always#

Before columns, before keys, before names: declare the grain in one sentence. Everything else follows from it.

One row per order line.

Now the rules write themselves. Anything true of an order line is a column. Anything true of an order — shipping address, order status — belongs in either an order dimension or an order-grain fact table, not smuggled in here where it will be double-counted. Anything true of a customer goes in the customer dimension.

Three flavours of fact#

Transaction facts. One row per event. The default. ride-hail-trips is one.

Periodic snapshot facts. One row per entity per period, whether or not anything happened. "Account balance at month end." Essential for questions like "how many active accounts did we have in March", which a transaction table answers only awkwardly.

Accumulating snapshot facts. One row per process instance, with columns for each milestone, updated in place as the process advances. An order with placed_at, picked_at, shipped_at, delivered_at. Perfect for funnel and cycle-time analysis, and the one flavour people forget exists.

Additive, semi-additive, non-additive#

A measure's arithmetic is part of its definition and belongs in the documentation.

• Additive — sums across every dimension. Revenue, units, clicks. Easy.
• Semi-additive — sums across some dimensions but not time. Account balance: summing balances across customers is meaningful; summing one customer's balance across twelve months is not.
• Non-additive — never sums. Ratios, percentages, averages. You cannot add conversion rates. You must re-derive them from their numerator and denominator at every level of aggregation.

The date dimension earns its keep#

Every warehouse should have one: a row per calendar day, with year, quarter, month, week, day of week, weekend flag, fiscal period, holiday flag, and whatever else your business cares about.

It seems redundant — SQL has date functions. It is not, for three reasons. It encodes your fiscal calendar, which no date function knows. It makes "compare against the same weekday last year" a join instead of a puzzle. And it lets you left-join to get zero-filled rows for days when nothing happened, which is the difference between a chart with a gap and a chart with a misleading straight line.

-- Days with no orders still appear, with zero. This is the point.
select d.date_day, coalesce(sum(f.revenue_usd), 0) as revenue
from dim_date d
left join fct_order_lines f on f.order_date = d.date_day
where d.date_day between '2026-01-01' and '2026-03-31'
group by 1 order by 1

Star or snowflake#

A star keeps each dimension in one denormalised table: dim_products contains category and department as plain columns, repeated. A snowflake normalises them out into dim_category, dim_department.

Use the star. Storage is cheap, joins are not free, and the whole point is making the analyst's query short. Snowflake only when a sub-dimension is genuinely shared and genuinely large.

Naming, which matters more than it should#

Pick conventions and never deviate:

• dim_ and fct_ prefixes, so the shape is visible in an autocomplete list.
• _key for surrogate keys, _id for natural keys from the source. When someone joins on the wrong one, the name is your only warning.
• _at for timestamps, _date for dates, _on for dates in dimension tables. Never a bare date.
• Booleans as is_ or has_.
• Fully spelled words. customer_acquisition_channel, not cust_acq_chan. You will read it ten thousand times and type it fifty.