On this page
Declare the row grain before the join
Write a row-grain sentence for every input and for the intended output. “Order” and “order line” are different units even when both have order_id. A customer table may have one row per customer, an order table one row per order, and a line table many rows per order. A join between order and line therefore changes row count by design. The worksheet should name the measure, its unit, its denominator, and the grain at which it is expected to be counted.
The PostgreSQL, (opens in a new tab) tutorial describes joins as pairing rows under an expression. That is a useful mechanical definition, not a guarantee of business uniqueness. Keep join keys and cardinality expectations separate from the aggregate question: the key review can say whether a relationship is unique, while this guide asks what that relationship does to a measure.
| Input or output | One row means | Measure at this grain |
|---|---|---|
| customers | one customer record | customer count |
| orders | one order | order total |
| order_lines | one line item | line amount |
| order_customer | one joined order-customer pair | order-level fields repeated |
| aggregated result | one chosen grouping key | sum, count, or other aggregate |
| unknown table | not documented | do not compare totals |
Map multiplicity and filters as a separate step
Draw the join path with expected multiplicity on each edge. A one-to-many edge repeats parent attributes; a many-to-many edge can multiply both sides. A left join also preserves unmatched rows, while an inner join removes them. Record filters before and after each join because a condition on a child table can turn a left join into an effective inner filter. Do not infer multiplicity from a small sample alone.
Use a distinct synthetic key count only as evidence for the chosen fixture. A real export may contain duplicate keys, late-arriving children, or multiple valid versions. Link to the key and cardinality guide for uniqueness evidence, then return here to document the effect on measures and denominators.
| Join question | Record | Hold condition |
|---|---|---|
| relationship | one-to-one, one-to-many, many-to-many, or unknown | Expected multiplicity is missing |
| join type | inner, left, right, or other declared type | Query text is unavailable |
| filter placement | before join, after join, or both | Filter boundary changed between exports |
| unmatched behavior | kept, dropped, or separately counted | Orphans are silently removed |
| measure grain | input, joined, or aggregate grain | The numerator unit is unclear |
| denominator | rows, entities, or groups | The denominator changes after join |
Choose aggregate timing deliberately
If a parent measure is repeated on every child row, summing after the join counts it once per child. Aggregate child values to the parent grain before joining the parent measure, or use a measure that is defined at the joined grain. Similarly, counting rows after a bridge join is not the same as counting distinct entities. The review should show the intended sequence in plain language, even when the production query is more complex.
PostgreSQL describes aggregates as operating on a set of input values and documents grouping operations beside them PostgreSQL, (opens in a new tab). Keep null behavior explicit: an empty input, a null value, and a zero amount are not interchangeable. Record whether an aggregate ignores nulls, returns null for no rows, or is coalesced by a transformation.
Timing checks
- Aggregate children to the parent grain before adding parent-level measures.
- Count the entity at its declared grain, not merely the number of joined rows.
- Keep bridge tables and many-to-many paths visible in the calculation note.
- Record whether filters apply before aggregation or after it.
- State how null, empty, and zero inputs affect the reported result.
Use a text fixture that exposes multiplication
A synthetic fixture can prove that the worksheet catches row multiplication without querying a real database. Give one order two lines, a second order one line, and a customer with two contact labels. Keep amounts invented and neutral. Write the expected order-level total before the customer join and compare it with the joined row count. The fixture is an explanatory table, not an executable query or a claim about any customer collection.
Record the expected grain beside every output. If an analyst cannot say whether the result is per order, per line, per customer, or per contact, stop before comparing totals. Preserve the fixture and the decision table in the packet; do not paste production rows to demonstrate a join.
| Fixture result | Disposition | Next evidence |
|---|---|---|
| Line sum equals parent total at order grain | continue | Compare filters and null rules |
| Parent amount repeats per line | hold | Aggregate parent before child join |
| Customer has two matched child rows | hold aggregate | Declare entity counting rule |
| Unmatched customer is dropped | conditional | Report inner-join exclusion |
| Output grain is unstated | stop | Name grain before measuring |
Synthetic grain ledger
orders: O1=order_total 10, O2=order_total 7
lines: O1/L1=6, O1/L2=4, O2/L1=7
contacts: C1/email, C1/phone
expected: line_sum_by_order {O1:10,O2:7}; order_count=2
warning: joining contacts before summing order_total repeats parent valuesReport a measure with its denominator
A comparison should carry the grouping key, input row counts, joined row counts, unmatched counts, aggregate order, null policy, and denominator. Say “order-level amount after child aggregation” instead of “revenue.” That wording makes a reviewable claim and avoids turning a query result into a broad business conclusion. If the source does not expose the needed counts, mark the measurement unavailable.
Keep join logic separate from a data-quality or permission conclusion. A stable aggregate may still be based on an incomplete export or an unauthorized field. Use the schema and dictionary guide for field definitions and the provenance log for transformations.
| Finding | Disposition | Do not infer |
|---|---|---|
| Grain and aggregate sequence documented | bounded pass | The source is complete |
| Join multiplicity known but measure repeats | rewrite or hold | The total is merely larger |
| Unmatched rows counted separately | conditional comparison | Missing parents are errors |
| Null policy not documented | hold | Null equals zero |
| Only final total retained | incomplete evidence | No row multiplication occurred |
Close with a qualified preparation step
Once input grain, join paths, aggregate timing, filters, and denominator behavior are documented, keep the packet structural. Do not upload raw tables to prove a join. A review note can use synthetic keys, aggregate counts, and redacted metadata while leaving private values outside the worksheet.
For a supported non-health collection, with authority to describe the tables and a controlled handling boundary, the qualified appraisal path may be a next preparation step. It does not run joins, verify totals, create a quote, match a buyer, issue a legal opinion, or grant a license. A reproducible query plan also does not prove the underlying records are accurate or shareable.
Takeaways
- Define input and output grain before writing a join comparison.
- Map multiplicity, filters, unmatched rows, and denominators explicitly.
- Aggregate parent and child measures at the intended grain.
- Use a synthetic fixture to expose repeated parent values.
- Keep structural evidence separate from accuracy, rights, and demand.