Declare the grain of an analytical table

The grain of an analytical table states what one row represents. Declare it as a business observation before choosing keys or measures: one finalized receipt line, one payment attempt, or one warehouse’s closing stock for a day. A unique row identifier alone does not answer that question.

For an analyst or developer who can read a basic SQL join and aggregation, grain is the contract that makes a sum interpretable. A line-level amount and a receipt-level discount can each be correct in isolation, yet placing both in one row may make a later sum wrong. The problem begins with the meaning of the row, before a query optimizer or charting tool sees it.

Start with the observation, then choose its identity

Consider a synthetic finalized receipt extract. A receipt can have several lines, and the same SKU can appear on more than one line. The source guarantees that (receipt_id, line_number) identifies one line within this fixed extract. It does not guarantee that SKU identifies a line, nor that a receipt has only one item.

Microsoft’s dimensional-modeling guidance describes fact-table granularity as the level of detail represented by its rows. This is more specific than a list of foreign keys. Once the observation is clear, keys should preserve its identity and measures should describe that same observation.

The following SQLite-compatible SQL keeps receipt headers and delivered line rows separate. Amounts are integer minor units, with two decimal places assumed for the example currencies. Prices and discounts come from the finalized source; the fixture does not perform currency conversion or tax calculation.

CREATE TEMP TABLE receipts (
  receipt_id INTEGER PRIMARY KEY,
  currency TEXT NOT NULL,
  discount_minor INTEGER NOT NULL
);
CREATE TEMP TABLE line_import (
  delivery_id INTEGER PRIMARY KEY,
  receipt_id INTEGER NOT NULL,
  line_number INTEGER NOT NULL,
  sku TEXT NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price_minor INTEGER NOT NULL
);
INSERT INTO receipts VALUES (10, 'EUR', 300), (11, 'EUR', 300), (12, 'USD', 0);
INSERT INTO line_import VALUES
  (1, 10, 1, 'A', 1, 1000),
  (2, 10, 2, 'A', 1, 1000),
  (3, 11, 1, 'B', 1, 1000),
  (4, 12, 1, 'C', 1, 2500);

SELECT l.receipt_id,
       SUM(l.quantity * l.unit_price_minor) AS gross_minor,
       SUM(r.discount_minor) AS copied_discount_minor
FROM line_import l
JOIN receipts r ON r.receipt_id = l.receipt_id
GROUP BY l.receipt_id
ORDER BY l.receipt_id;

Receipt 10 has gross 2,000 and copied discount 600. Its real receipt discount is 300. The join has repeated a header-level measure on both line rows. Receipt 11 has only one line, so its copied discount happens to remain 300. A test set containing only single-line receipts would hide the modeling defect.

View full-size image ↗

Put each measure at the grain where it is defined

Keep gross line amounts at line grain and the receipt discount at receipt grain. To compute receipt net amount, first summarize lines to one row per receipt, then combine the header once. The next view also keeps an explicit line count so absent lines remain distinguishable from a known zero amount.

CREATE TEMP VIEW receipt_totals AS
WITH line_totals AS (
  SELECT receipt_id, COUNT(*) AS line_count,
         SUM(quantity * unit_price_minor) AS gross_minor
  FROM line_import
  GROUP BY receipt_id
)
SELECT r.receipt_id, r.currency,
       COALESCE(l.line_count, 0) AS line_count,
       l.gross_minor, r.discount_minor,
       l.gross_minor - r.discount_minor AS net_minor
FROM receipts r
LEFT JOIN line_totals l ON l.receipt_id = r.receipt_id;

SELECT * FROM receipt_totals ORDER BY receipt_id;
SELECT currency, SUM(net_minor) AS net_minor
FROM receipt_totals
GROUP BY currency
ORDER BY currency;

The receipt nets are 1,700 EUR minor units, 700 EUR minor units and 2,500 USD minor units. The currency totals are 2,400 EUR and 2,500 USD minor units. Adding those two numbers into one unlabeled total would mix monetary units. A receipt’s currency can safely repeat as a descriptive attribute on its lines; that does not make its discount additive across those lines.

This design does not answer net revenue by SKU. The discount belongs to the receipt, and the source has not assigned it to individual products. A product report needs a source-provided line allocation or an explicit allocation policy that reconciles to the header. Copying the whole discount to every SKU would invent an attribution rule while breaking conservation. Keep that unresolved decision visible instead of naming a line-level column net_revenue prematurely.

Test the business identity independently of the storage key

The import table uses a unique delivery ID for traceability. It can still contain two deliveries of the same receipt line. A grain check therefore groups by the declared business identity:

SELECT receipt_id, line_number, COUNT(*) AS delivered_copies
FROM line_import
GROUP BY receipt_id, line_number
HAVING COUNT(*) > 1;

The original fixture returns no rows. Add (5, 10, 1, 'A', 1, 1000) on a fresh copy and the storage primary key still accepts it, but the grain check finds receipt 10, line one, twice. The pipeline should resolve delivery identity and source revision semantics before publishing a fact table that promises one row per finalized line. The query detects a violation; it does not establish whether the second payload is a repeat or a correction.

Conversely, grouping by (receipt_id, sku) would flag the two A lines in the original valid fixture. They are distinct source lines even though the product is the same. An overly broad duplicate rule destroys valid detail. Write the row definition beside its tested key so a future maintainer does not replace the business contract with whichever columns happen to look unique in a small sample.

The dbt data-test documentation describes assertions such as uniqueness and non-nullness. Such tests can enforce parts of a declared grain, but a passing uniqueness test on delivery_id cannot infer the intended observation. The analyst must first decide which identity matters. A separate relationship check should reject a line whose receipt header is absent; otherwise a header-driven report can drop the line even when its grain key is unique.

Distinguish an absent observation from a zero-valued one

On a fresh baseline, add receipt 13 in EUR with discount zero but no line rows. Add receipt 14 in EUR with discount zero and one line whose quantity is one and unit price is zero. The view reports line_count zero and null gross/net for receipt 13; it reports line_count one and numeric zero for receipt 14.

View full-size image ↗

A zero-price line can be a legitimate free item under the fixture’s contract. A header without lines violates this extract’s completeness expectation. Do not replace both gross values with zero just to make a chart render. The PostgreSQL aggregate reference explains that sums ignore null inputs and return null on no input rows; a grouped sum can therefore hide incomplete components if publication checks only the total.

For this report, require every included finalized receipt to have at least one valid line before publishing its currency total. If a source permits truly empty or void receipts, model that status and decide whether it belongs in the reporting population. The line count is evidence for the decision, not a universal rule that all commercial receipts must contain a charged product.

A later extract can revise a previously finalized line. Appending that revision to the published table would violate its current one-line promise. Either replace the selected snapshot as a unit, or preserve revisions in a separate history and select the intended version before aggregating. Recording the extraction boundary makes those two designs distinguishable; adding a delivery ID does not resolve the choice.

A useful grain contract records the observation, identity, source snapshot or revision scope, units, and which dimensions permit adding each measure. It also states what missing detail means. Those choices make it possible to explain why a row exists, why another row is a duplicate, and why two individually valid numbers must sometimes remain in separate tables.