One loan chain, three different grains
Contract CT-1001 is worth 600,000. It has two disbursements (LN-01, LN-02) and three repayments (RP-01,RP-02, RP-03). The same business relationship can be stored at the contract grain, the disbursement grain or the repayment grain — and each choice changes what the numbers mean.
The row is one disbursement under the contract, and its amount matches the principal actually released.
note_id / note_iddisbursed_principal- Disbursement count and principal
- Disbursement-level analysis
- Listing every repayment row directly
- Copying the contract amount onto each note and summing it
note_id | contract_id | disbursed_principal |
|---|---|---|
| LN-01 | CT-1001 | $300,000 |
| LN-02 | CT-1001 | $150,000 |
Why can't the contract amount be summed after the join?
The contract amount is copied onto every disbursement row, so the same 600,000 now appears twice.
contract_amountcontract_id | note_id | contract_amount |
|---|---|---|
| CT-1001 | LN-01 | $600,000 |
| CT-1001 | LN-02 | $600,000 |
SELECT SUM(contract_amount) -- over contract ⋈ disbursementsLook at the joined rows first, then run the aggregate.
Why grain comes before dimensions and measures
Dimensions describe the context of a row, and measures quantify it. Both depend on what the row is. If the grain is “one disbursement”, then disbursed_principal is a valid measure and contract_amount is not: the contract amount belongs to the contract, not to each disbursement.
- Identity. The primary key follows the grain:
contract_id,note_idorrepayment_id. - Measure validity. A measure is safe only when it is defined at the same level as the row.
- Join behavior. Knowing the grain tells you whether a join will multiply rows before you run it.
Every downstream query relies on it. When the grain is implicit, each analyst guesses differently and the same table produces different totals in different reports.
What a wrong grain looks like in numbers
The demo above joins the contract amount to the two disbursements and then sums it. The result is 1,200,000 instead of 600,000, because the same contract row was counted once per disbursement.
This is the same failure mode as a SQL join duplicating rows. The grain error is the root cause; the inflated SUM is only the symptom. A query-level workaround such asDISTINCT hides the symptom and usually keeps the modeling error in place.
How to decide and test the grain
- Name the business event. Is a row a contract, a disbursement, a repayment, a daily snapshot or a monthly balance?
- Write the sentence. “One row = one actual disbursement under one contract.” Keep it short and specific.
- Test the key. Compare
COUNT(*)withCOUNT(DISTINCT key). If they differ, the key does not define the grain. - Audit the measures. For each measure, ask whether it can be repeated per row without changing its meaning. If not, it belongs to a different grain.
- Check the questions. List the questions the table must answer. A grain that cannot answer them will be joined or aggregated anyway — often incorrectly.
If two grains are genuinely needed, model them as two tables or use a bridge table. Storing both in one table creates a mixed-grain table where no aggregate is safe.
Edge cases and trade-offs
- Transaction grain vs periodic snapshot grain. A balance snapshot has one row per account per day; a transaction fact has one row per event. Mixing them in one table makes
SUM(balance)andCOUNT(*)mean different things per row. - Aggregated tables. A summary table has its own grain, such as “one row per branch per day”. It must be declared too, and it cannot be joined to detail rows without re-aggregating first.
- Late-arriving facts. The grain stays the same when a fact arrives late; the loading strategy changes, not the row meaning.
- Bridge tables. Many-to-many relationships between dimensions belong in a bridge table with an explicit grain, not in a fact table with duplicated measures.