---
name: join-grain-guard
description: "Reviews or writes a multi-table SQL join for grain and fan-out safety — confirms the join key is unique on one side before an aggregate, catches many-to-many row multiplication, spots a LEFT JOIN silently turned INNER by a WHERE on the right table, and states the grain of the result. Use when a query joins two or more tables and then sums, counts, or averages, or when a reported total looks inflated. Do NOT use for single-table queries, index/performance tuning (that is postgres-query-review), or ORM/query-builder API questions."
metadata:
  decimalai-skill-id: "a27499bc-a2a6-4512-9cdb-6109aebdedb6"
  decimalai-version-id: "6490bc22-2a5a-4d93-a18f-86276a0559d1"
  decimalai-version: "1"
  decimalai-body-sha256: "28f42e368ba1d341642f0cb859311f4fda8b217d80b9e7802618c6e5e79ab51d"
  decimalai-name: "join-grain-guard"
  decimalai-format: "1"
  decimalai-invocation: "model"
  decimalai-category: "data"
  decimalai-source: "https://app.decimal.ai/s/join-grain-guard@1/SKILL.md"
---
# Join Grain & Fan-out Guard

Reviews or writes multi-table SQL joins for the silent-correctness bug the base model ships by default: it checks syntax, indexes, and NULLs, but does not verify that an aggregate is being applied over the *number of rows it thinks it is*. When a join multiplies rows and a `SUM`/`COUNT`/`AVG` runs over the multiplied set, the result is wrong with no error, no warning, and a plausible-looking number. This skill makes the row-multiplication check mandatory.

## The one question the base skips

**Grain** = what a single output row represents (one customer, one order, one order-line). Before trusting any aggregate over a join, answer:

> For each join, is the ON key unique on the table being joined **to** (the right side)?

- **Unique on one side** (1:1 or many:1) → that join adds columns without adding rows. Safe to aggregate.
- **Not unique on either side** (many:many) → rows multiply (a cartesian product *per key*). Every measure and every count is now inflated.

A join to a child table (one parent, many children) fans out the parent: each parent row is repeated once per child. Any parent-side measure summed after that join is multiplied by the child count.

## The checks

### 1. Fan-out on an additive measure
`SUM(parent.amount)` after joining parent→child (one:many) counts `parent.amount` once per child row. Symptom: a total that is "too high," often a near-integer multiple of the truth.

**Fix — collapse the child to the target grain BEFORE joining:**
```sql
-- WRONG: books.price summed once per review row
SELECT au.id, SUM(bk.price) AS catalog_value
FROM authors au
JOIN books bk        ON bk.author_id = au.id
JOIN book_reviews rv ON rv.book_id  = bk.id       -- fans each book out by its reviews
GROUP BY au.id;

-- RIGHT: pre-aggregate the many-side to one row per key, then join 1:1
SELECT au.id, COALESCE(ba.catalog_value, 0) AS catalog_value
FROM authors au
LEFT JOIN (
  SELECT author_id, SUM(price) AS catalog_value
  FROM books
  GROUP BY author_id
) ba ON ba.author_id = au.id;
```

### 2. The `DISTINCT`-inside-aggregate trap
- `COUNT(DISTINCT child.id)` correctly counts children across a fanned-out join — use it for counts.
- `SUM(DISTINCT amount)` does **not** fix a fanned-out sum: it silently drops legitimately-equal values (two $10 orders become one). For an additive measure, pre-aggregate; do not reach for `SUM(DISTINCT …)`.
- `COUNT(*)` over a fanned-out join counts multiplied rows, not entities; `COUNT(DISTINCT entity_key)` counts entities.

### 3. Two child joins off the same parent
Joining a parent to *two* independent child tables (e.g. orders→payments and orders→shipments) multiplies rows by `payments × shipments` per order — a double fan-out. Aggregate each child in its own subquery first, then join both results to the parent.

### 4. LEFT JOIN silently becoming INNER
A `LEFT JOIN` keeps unmatched left rows with NULLs on the right. A predicate on a right-side column placed in the **WHERE** clause discards those NULL rows — turning the LEFT JOIN back into an INNER JOIN, dropping the very rows the LEFT was meant to keep.
```sql
-- Silently INNER: stores with no web visit vanish
FROM stores st
LEFT JOIN visits v ON v.store_id = st.id
WHERE v.channel = 'web';

-- Preserves the LEFT: move the right-side condition into ON
FROM stores st
LEFT JOIN visits v ON v.store_id = st.id AND v.channel = 'web';
```
Exception: `WHERE right_col IS NULL` is the deliberate anti-join pattern (find left rows with no match) and is correct.

### 5. Many-to-many
If the ON key is non-unique on both sides (two child tables sharing a foreign key, a join through a bridge used wrongly), rows multiply by the product of matches. There is no aggregate that rescues this after the fact — restructure so at least one side is unique per key, usually by pre-aggregating one side.

## Output shape

When reviewing or writing a join, always end with the grain line:

```
Grain in: <what one row of each input represents>
Fan-out:  <which join multiplies rows, and by what>  (or: none — every join key is unique on one side)
Risk:     <which aggregate is inflated, or "safe">
Fix:      <pre-aggregate / COUNT(DISTINCT) / move predicate to ON>  (omit if safe)
Grain out: <what one row of the result represents>
```

If every join key is unique on one side and no LEFT/WHERE trap is present, say so plainly — do **not** invent a fan-out that isn't there.

## Worked example

Input:
```sql
SELECT q.id, q.fee, SUM(ln.qty * ln.unit_price) AS subtotal
FROM quotes q
JOIN quote_lines ln ON ln.quote_id = q.id
GROUP BY q.id, q.fee;
```
Review:
```
Grain in: quotes = one row per quote; quote_lines = many rows per quote.
Fan-out:  the quote_lines join repeats each quote row once per line.
Risk:     subtotal is correct (it sums a line-level measure), but q.fee is a
          per-quote value repeated per line — any later SUM(q.fee) double-counts.
Fix:      subtotal here is fine; if you also total fee, take MAX(q.fee) per
          quote or pre-aggregate lines, so fee is counted once.
Grain out: one row per quote.
```

## Edge cases

- **`GROUP BY` hides nothing.** Grouping by the parent key does not undo fan-out; the multiplied rows are still summed before the group collapses.
- **Filtered join that happens to be 1:1 today** — if the child *can* have multiple rows per key (no unique constraint), treat it as many even if current data is 1:1; the bug appears when a second child row arrives.
- **`AVG` over a fanned-out join** is wrong in both numerator and denominator — weighted toward parents with more children.
- **Window functions** over a fanned-out set inherit the same multiplication; the fix is still to establish grain first.
- **Self-joins / range joins** (`ON a.ts BETWEEN b.start AND b.end`) are many-to-many by nature — check multiplicity explicitly.

## Evaluation criteria
A good execution of this skill should:
- [ ] State the grain of the result (what one output row represents)
- [ ] Identify which join multiplies rows, or confirm none does
- [ ] Flag any additive aggregate summed over a fanned-out join as inflated
- [ ] Recommend pre-aggregating the many-side (or COUNT(DISTINCT) for counts) rather than SUM(DISTINCT)
- [ ] Catch a right-table predicate in WHERE that turns a LEFT JOIN into an INNER JOIN
- [ ] Not invent a fan-out when every join key is unique on one side