Dimensional modeling

The demo below runs the most ordinary query a fact table receives, a request for total revenue. The business took 150 here, since order 101 sold 60 of goods and order 102 sold 90. Before you run the sum, add up the amount column yourself and predict whether the query will report the money the business actually took.

The query did nothing wrong. It read every row at face value and summed them, exactly as written, and it returned 210 against real revenue of 150. Order 101 entered the total twice, once through its two line rows and once through the order-total row that arrived from the second feed.

A mixed grain fails quietly because each row is individually true. The lines are real and the total is real, so no constraint can object, and every aggregate that touches the table inherits the double-count.

You can reproduce the failure with the sum itself.

select sum(amount)::int as total
from sales;

The inflated number looks plausible, and a plausible wrong number survives review. No error exists anywhere in the pipeline, so the discrepancy is usually found by a person reconciling reports long after the figure has been repeated.

Look at which rows caused the damage. The two line rows and the total row each describe order 101 truthfully, and the failure appeared only when one table held both descriptions, because a sum cannot tell which rows share meaning. A fact table therefore needs a single declared answer to the question this guide opened with, before any measure is added to it.

Dimensional modeling calls that answer the grain declaration. Ralph Kimball, whose design method shaped most modern warehouses, made declaring the grain the first step of building any fact table. The declaration comes ahead of choosing dimensions or measures, because every later choice is checked against it, and a measure that is not true at the declared grain has no honest place in the table.

The repaired table gives the opening question a single answer, and the sum from the failure section returns 150 on it. Grain repairs are cheap at design time and expensive afterward, because every downstream report inherits whatever the table declares.

Declaring a grain forces a choice about how fine the rows should be. One row per order line is finer than one row per order, which is finer in turn than one row per day. From line-grain rows you can still compute order totals and daily totals, while a table stored at daily grain can never answer which item sold, because aggregation destroys the detail it rolls up.

The cost of fine grain is volume, since one row per order line can be many times the rows of one row per order. Kimball’s advice remains the default for exactly this trade: store the finest grain the source supports and aggregate on the way out, because storage is usually cheaper than a question you can no longer ask.

Some questions genuinely live at a coarser grain, such as an account balance measured at the end of each day. Those are served by dedicated tables built for that purpose, and they arrive later in this track as snapshot fact types.

• A plausible total hides the damage. A double-counted sum is rarely absurd, and 210 against a true 150 survives a glance. Reconcile a fact table against its source of truth at load time, rather than trusting review to notice.
• Order-level values on line rows multiply. A shipping fee copied onto every line of its order is summed once per line. Allocate such values across the lines or keep them in an order-grain table.
• Counting orders from line grain needs distinct. At one row per order line, count(*) counts lines. Orders are counted with count(distinct order_id), and the difference stays invisible until an order has two lines.
• Averages change meaning with the grain. The average of line amounts differs from the average order value. Decide which average the question asks for, and compute order totals first when it asks for the order one.

The demo runs June’s depot report twice: before Priya’s transfer and after it. Before pressing play, predict the second result. All five deliveries happened in June, but the dimension will describe July. Watch Priya’s row change and compare the rerun with your answer.

The first run returns Northside 3 and Riverside 2. The update then replaces the only depot value Priya has. The same join reads her three June deliveries through that new row, so the rerun returns Riverside 5. Northside disappears from a month it actually served.

No error fires. The update did exactly what dispatch needed, and the report query is unchanged and valid. Its answer changes because a question about June is joined to whatever the dimension says today. With no row carrying Priya’s earlier depot, the warehouse can no longer reconstruct the original result.

Running the report against the updated tables reproduces the rewrite:

select d.home_depot as depot, count(*)::int as deliveries
from fact_deliveries f
join dim_courier d on f.courier_id = d.courier_id
group by d.home_depot
order by d.home_depot;

The single result, Riverside with 5, contains a correct total that can survive a quick review. What it loses is the location of the work, the only fact this depot report exists to explain.

The update destroyed a fact the business still needs. home_depot was never just one value: Priya worked from Northside through June and Riverside from July onward. Each value carried a period that one overwritable cell could not hold, so recording the new truth erased the old one.

That overwrite is correct in the operational dispatch system, which needs exactly the current roster. Normalization says to store a current fact once. The warehouse has a different job because its questions reach into the past, so current-state design alone cannot answer them faithfully.

Ralph Kimball’s dimensional method calls these attributes slowly changing dimensions, which gives this guide its SCD abbreviation. The method numbers the standard treatments. Type 1 is the overwrite you watched and is legitimate when no one will need the old value. Type 2 preserves the old value, and its rule is short:

When a tracked value changes, close the old row and add a new one.

Adding a row keeps both truths and binds each to the period it was valid. The dimension can now answer questions about then and now. The price is several rows per courier, which breaks the one-row-per-thing assumption. The method below makes those versions safe to use.

Priya now has two versions under one natural key. July 1 is the seam where one window closes and the next opens. The failed report changes in only one place: it joins through the stamped key.

select v.home_depot as depot, count(*)::int as deliveries
from fact_deliveries f
join dim_courier_versions v on f.courier_key = v.courier_key
group by v.home_depot
order by v.home_depot;

June’s three Northside deliveries retain courier_key 1, so they always resolve to the Northside version. The report remains Northside 3 and Riverside 2 after later transfers. Filtering is_current returns today’s roster from the same dimension.

Type 2 preserves history and charges for it. Every change adds a row, every consumer must understand that one courier can have several versions, and the load must detect changes and close windows correctly. Even count(*) needs care because it counts versions rather than couriers.

Ask whether anyone will need an attribute’s value as of an event date. A depot report asks exactly that of home_depot, so it earns Type 2. A courier’s phone number rarely does, so Type 1 is usually enough. Make the choice per attribute because one dimension can carry both treatments.

When the business needs every entity’s state at every period end, the question changes shape and calls for snapshot fact types. Type 2 remains the fit for history recorded when occasional changes occur rather than on a schedule.

• Counting versions as couriers. Once one courier can occupy several rows, count(*) answers how many versions exist. Count distinct courier_id, or filter is_current when the question is the present roster.
• Windows that overlap or gap. Use contiguous half-open windows where each effective_to equals the next effective_from. An overlap returns two versions for one date, while a gap returns none. Close the old row and open the new one in the same load step.
• A stale current flag. is_current repeats what an open window already says and can drift after a failed load. Derive or verify it from validity columns instead of trusting the flag alone.
• Versioning a value that never sits still. Type 2 assumes occasional change. A daily-changing value creates excessive versions, while a value that changes with every delivery is a measure and belongs on the fact table.