One Row Per What?

Before the SELECT, before the join diagram, one question: one row per what?

Every table has an answer. It's called the table's grain, and it is the single highest-value fact you can know about data you've been handed. The last post showed what happens when a query ignores it: joins fan out and totals inflate. This post is about the question itself, because almost every wrong query I've seen started before the first keystroke, with someone who assumed a grain instead of asking.

Run the CellarDB answers quickly (setup here): VineyardBlocks is one row per block. FruitLots is one row per fruit delivery, which means block × vintage × pick, not one per block and not one per vintage; some blocks deliver twice in a year. WineLots is one row per lot in the cellar. BlendComponents is one row per blend-lot pairing. BarrelFills is one row per fill event. At least three of those are not guessable from the table's name, which is the point.

What a row in BarrelFills means

BarrelFills is the table people guess wrong. It is not one row per barrel, and not one row per wine lot. It's one row per fill event: barrel X received wine from lot Y on a date, and was emptied on another date or hasn't been. A lot fills many barrels; a barrel gets filled over and over across vintages; a racking that moves a lot barrel-to-barrel closes one fill and opens another. About 2,600 fills across 900 barrels, roughly three per barrel over six vintages.

Guess the grain wrong and the queries are wrong in ways that parse fine:

-- "How many barrels is lot 23-PN-HR07 in right now?"
SELECT COUNT(*) AS BarrelsHolding
FROM BarrelFills bf
JOIN WineLots wl ON wl.WineLotID = bf.WineLotID
WHERE wl.LotCode = '23-PN-HR07';

This counts every fill event in the lot's history, including barrels it was racked out of months ago. The "right now" lives in a column, and "barrels" is not the table's grain, so both ideas have to be stated explicitly:

SELECT COUNT(DISTINCT bf.BarrelID) AS BarrelsHolding
FROM BarrelFills bf
JOIN WineLots wl ON wl.WineLotID = bf.WineLotID
WHERE wl.LotCode = '23-PN-HR07'
  AND bf.EmptyDate IS NULL;

Nothing in that fix is advanced syntax. All of it is grain knowledge: knowing what a row means, what makes a row "current," and which column carries each idea. That knowledge comes from documentation when you're lucky and from interrogating the table when you're not.

The key is not the grain

BarrelFills has a primary key, BarrelFillID. It's a surrogate: an auto-incrementing row number that promises the rows are distinct as rows and nothing else. The grain is a claim about meaning: there should be one row per (barrel, lot, fill date). The primary key does not enforce that claim. Surrogate keys never do.

So test the claim. This is the grain check, and it's three lines:

SELECT BarrelID, WineLotID, FillDate, COUNT(*) AS Copies
FROM BarrelFills
GROUP BY BarrelID, WineLotID, FillDate
HAVING COUNT(*) > 1;

Empty result: the declared grain holds, proceed. In CellarDB, one group comes back: two rows, same barrel, same lot, same fill date, different BarrelFillIDs, created a day apart. A cellar hand logged the fill, and someone re-keyed it off the paper worksheet the next morning. The primary key accepted both without complaint, and now there are 59.4 phantom gallons flowing into every downstream SUM, including the lot-volume reconciliation this series gets to in post 9.

Two habits fall out of this. When you create a table whose grain matters, declare it with a unique constraint on the grain columns, so the second entry fails at the door instead of in a report. And when you inherit a table, assume nobody did that, because mostly nobody did. The grain check is the first query I run against any table I haven't met before; if it returns rows, either my understanding of the grain is wrong or the data is, and both of those are findings worth having before lunch rather than after publishing.

Removing the duplicate cleanly is a ROW_NUMBER pattern, which is the next post. Finding it doesn't need to wait.

When the grain is "one row per change"

BulkContracts is one row per contract as originally signed. But contracts get renegotiated, and the winery's answer to that is ContractAmendments: one row per amendment, carrying only the columns that changed and NULL for the rest. This is slowly-changing data, and its grain is one row per change, which means no table in the database holds the current terms. The current terms are a query.

Contract BC-2024-007 makes it concrete: signed for 12,000 gallons at $8.50, price cut to $7.90 in March (volume column NULL), volume raised to 15,000 in June (price column NULL). The intuitive query takes the latest amendment and falls back to the contract:

-- wrong: the latest amendment wins for every column
SELECT c.ContractNumber,
       COALESCE(la.NewVolumeGal,   c.VolumeGal)   AS CurrentVolumeGal,
       COALESCE(la.NewPricePerGal, c.PricePerGal) AS CurrentPricePerGal
FROM BulkContracts c
OUTER APPLY (
    SELECT TOP 1 a.NewVolumeGal, a.NewPricePerGal
    FROM ContractAmendments a
    WHERE a.ContractID = c.ContractID
    ORDER BY a.AmendmentDate DESC
) la
WHERE c.ContractNumber = 'BC-2024-007';

It returns 15,000 gallons, correct, at $8.50, wrong. The June amendment is the latest row, its price column is NULL, and COALESCE falls through to the original price as if March never happened. The bug is a grain misunderstanding: when amendments are sparse, each column has its own latest value, and "the latest amendment" is not a meaningful unit. The honest query overlays the latest non-NULL value per column:

SELECT c.ContractNumber,
       COALESCE(lv.NewVolumeGal,   c.VolumeGal)   AS CurrentVolumeGal,
       COALESCE(lp.NewPricePerGal, c.PricePerGal) AS CurrentPricePerGal
FROM BulkContracts c
OUTER APPLY (
    SELECT TOP 1 a.NewVolumeGal
    FROM ContractAmendments a
    WHERE a.ContractID = c.ContractID
      AND a.NewVolumeGal IS NOT NULL
    ORDER BY a.AmendmentDate DESC
) lv
OUTER APPLY (
    SELECT TOP 1 a.NewPricePerGal
    FROM ContractAmendments a
    WHERE a.ContractID = c.ContractID
      AND a.NewPricePerGal IS NOT NULL
    ORDER BY a.AmendmentDate DESC
) lp;

15,000 gallons at $7.90. Add AND a.AmendmentDate <= @AsOf to both APPLYs and the same query values the order book on any date in history, which is the entire reason the winery stores changes instead of running UPDATEs. An UPDATE would answer "what are the terms" and destroy "what were the terms in April" forever. When you reconcile shipments against this contract, it's the amended 15,000 they should tie to, not the original 12,000; the full tie-out discipline is post 9's subject.

If a table like this crosses your desk, the grain question has a follow-up: one row per change, fine, but which columns change together, and what does NULL mean here? You did the NULL drill two posts ago. It compounds.

The star schema, in one honest section

Hang around analysts and you'll hear fact and dimension. The vocabulary is from Kimball's warehouse-modeling tradition, there are 600-page books, and you need about a paragraph of it to do good work.

A fact table records measurements at a declared grain: BarrelFills measures gallons per fill event, WorkOrders measures hours per cellar operation, Shipments measures gallons per shipment. A dimension describes the things being measured, one row per thing, full of the attributes you group by: Barrels (cooper, origin, toast), VineyardBlocks (region, variety, acres), Customers. A star schema is just the arrangement: facts in the middle, dimensions around them, every report an aggregation of a fact grouped by dimension attributes. When the same dimension is shared across facts, labor by block and volume by block can sit in one report and mean the same "block."

The working rules, minus the 600 pages: state every fact table's grain out loud before using it; join facts to dimensions freely, since that direction can't fan out; and never join two fact tables to each other directly, because that is the double fan-out from the last post with better vocabulary. Aggregate each fact to a shared grain first, then join the summaries.

CellarDB itself is not a tidy star, on purpose. WineLots acts as a dimension in one query and a fact in the next; the contracts thread needs its amendment overlay before it behaves like anything. That's what operational schemas look like, and operational schemas are what analysts are actually handed. Warehouses with clean stars are derived from tables like these, by someone who asked the grain question table by table. Knowing that is most of what "data modeling for analysts" amounts to.

The habit

Before writing FROM, say the grain of every table you're about to touch, and then say the grain of the answer: "one row per customer per vintage." If they don't match, your query must either aggregate or fan out somewhere in the middle, and you want to choose where, deliberately, rather than discover it in a total that won't tie.

Grain is the cheapest discipline in this series. No new syntax, no new functions. One question, asked before each query, and an embarrassing fraction of production wrong numbers never happen.

— Lo