Defining and Validating Fact Table Grain

Hello! Last lesson, you mapped analytical questions to business processes, candidate measures, and candidate dimensions. That map tells you what the model needs to explain. Before choosing fact-table columns, you need a more exact statement: what business observation does one row record?

This lesson focuses on declaring that statement—the grain—and testing whether it captures the most detailed observation available for the chosen process. That distinction is especially important when source systems contain both transaction headers and lines, or when a report asks for monthly totals even though the source records individual transactions. Allow about 40 minutes for the lesson and its two short resources.

A grain is a business statement, not a list of keys

Consider the invoicing question from last lesson: “What revenue did each product category generate by month?” A first attempt at a grain might be “product, customer, date.” Those are useful analytical coordinates, but they do not say whether a row represents an order line, an invoice line, or a monthly total. The same product can appear twice on one invoice, and an order line can be billed across multiple invoices.

For a fact table measuring invoicing, a stronger declaration is:

Each row records one issued invoice line, uniquely identified within its source system by invoice ID and line number, with the quantity and amount billed on that line.

The sentence names the activity, the observation, and its boundary. It also gives you something to check against source records. An invoice ID alone identifies a document; a line number distinguishes observations within it. Neither a generated sales_key nor the combination of product, customer, and date defines this business meaning.

Watch Data Modeling Grain Explained from Data with Baraa for a visual comparison of line-level, order-level, and monthly rows. The short second segment shows why counting rows in a line-level table does not count orders.

A useful declaration therefore begins “Each row records one…” and finishes with a recognizable business event or observation. “One invoice line issued” is precise; “one product sold” might not be. Did the source capture a checkout scan, an order line, or a consolidated line for a product on a receipt? The answer determines the grain.

Atomic means the finest captured observation for this process

An atomic grain is the finest meaningful observation the source captures for the business process you are modeling. It does not mean that every physical item must occupy its own row. If an invoice line bills three identical units and the source records one line with quantity = 3, that line can be atomic for invoicing. Inventing three unit-level events would add detail the source never observed.

Ralph Kimball’s Keep to the Grain in Dimensional Modeling, published by the Kimball Group, makes the connection between grain and the physical measurement event especially clear. Read the brief article before applying the test below.

Kimball’s checkout “BEEP” is memorable because it points to a capture process, not merely a preferred reporting level. Apply the same reasoning to an invoice system: locate where invoice lines are created, determine whether a line can be split or corrected, and identify the fields that distinguish one captured line from another.

Atomicity is relative to the process and available source detail. An invoice-line fact can answer invoice-line and higher-level invoicing questions. It cannot reveal three separate shipping events if the source records those events elsewhere. Nor can a monthly product total recover which customer received a particular invoice: that detail has been discarded.

This is why “the report only needs monthly revenue” does not, by itself, justify a monthly base grain. You can summarize invoice lines by month. You cannot reliably reverse a monthly summary to recover invoice lines when a new question arrives.

Test a declaration against rows, measures, and counterexamples

A grain sentence is a design claim. Test it against the source rather than accepting it because the table has a plausible name. Suppose you are considering “one issued invoice line.” Work through these checks:

1. Identify the event and its source boundary. Which source record marks an issued line? If a draft line later changes, is it the same observation, or are you intending to record each change? State whether credit-note lines are included and how they are identified.
2. Find a business identifier. Within a source system, does (invoice_id, line_number) identify one captured line? If invoice IDs can repeat across systems, include source_system in the business identifier. A warehouse-generated row key may be useful, but it cannot answer this question for you.
3. Try to divide a source observation further. Does the source actually distinguish multiple billed product lines within what you called one row? If yes, your proposed grain may be too coarse. If it records only one line with a quantity of three, do not manufacture three observations.
4. Check that each proposed measure belongs to that line. Line quantity and line amount may qualify. A shipping charge assessed once per entire order does not become a line-level measurement merely because a source join repeats it on every line.
5. Check that each proposed context describes that line unambiguously. Which product was billed, which invoice date applies, and which customer was billed? If a proposed attribute can have several values for one line, investigate what it describes before attaching it as though it had one value.

For a source table that is supposed to have one row per invoice line, your SQL experience gives you a quick identifier check:

SELECT source_system, invoice_id, line_number, COUNT(*) AS row_count
FROM source_invoice_lines
GROUP BY source_system, invoice_id, line_number
HAVING COUNT(*) > 1;

A result does not automatically mean “delete duplicates.” The rows might represent source corrections, versions, or a misunderstood line identifier. Conversely, an empty result proves only uniqueness of those columns in the current data; it does not prove that the grain is atomic or that every line was captured. Reconcile the record counts and business events as well as the key.

The invoice-line sentence also exposes a common trap with dimensional foreign keys. Two separate invoice lines can share the same product, customer, and invoice date. Those keys describe both observations correctly, but their combination does not necessarily distinguish the lines. Grain describes what happened; keys help identify and join its records.

A concrete double-counting failure

Imagine these issued invoice lines:

At the declared invoice-line grain, summing line amount gives 65. There are three invoice lines but only two invoices. A distinct invoice count, rather than a row count, is needed to count invoices from this table.

The shipping-charge column tells a different story. Its repeated value of 12 belongs to a whole order, not independently to both lines. Summing that column here gives 32, although the two order charges total 20. Choosing MAX(shipping_charge) within each order might happen to recover 20 for these tidy records, but it leaves the grain mismatch in place and can conceal inconsistent source values. The safe design decision is to record the charge at its genuine order-level grain or establish an explicit, business-approved allocation rule before treating any portion as a line-level measure. The design of separate fact tables for incompatible grains comes later in this module.

This illustrates Kimball’s warning about mixed granularity: a table can have a clearly stated row grain yet contain a measure copied from a coarser observation. Your test must cover every candidate measure, not just the row identifier.

What a successful grain declaration lets you decide

Before moving on, your design note for the invoicing process should be specific enough that another data engineer could determine whether a source record belongs in the fact table. A compact version could be:

Process: sales invoicing. Grain: one row per issued invoice line identified by source system, invoice ID, and line number. Included observation: the quantity and amount billed on that line. Not assumed to be line-level: an order-wide shipping charge. Source check: confirm how partial invoices and credit notes are recorded.

This is not a complete schema. It is the contract against which you will assess dimensions and measures. It also separates an analytical request—“revenue by category and month”—from the observation that makes many such requests answerable. If the source does not supply invoice lines, record that limitation; substituting order lines would change the process being measured, not merely the implementation.

Conclusion

A fact table’s grain states, in business language, what one row records. Declare it before selecting columns. To test atomicity, locate the actual capture event, verify how individual observations are identified, and determine whether finer meaningful detail exists in that source. Then ensure proposed measures and descriptive context genuinely belong to the declared observation. A row key proves uniqueness only if its meaning is understood; it does not prove atomicity.

In the next lesson, you will use these grain declarations to choose among transaction, periodic snapshot, accumulating snapshot, and factless fact-table patterns.