# Roadmap: v0.12.0 Per-Grain Metric Aggregation (Phases 69-71)

**Milestone goal:** close TECH-DEBT #35 — compute each metric at its own grain,
Snowflake-style, so multi-grain queries return correct numbers instead of raising
a fan-trap error.

Requirements: `.planning/milestones/v0.12.0-REQUIREMENTS.md` (GRAIN-01..08,
VER-01..04, DOC-01..03, REL-01).

## Design summary

The generated query stays base-anchored **wherever that is correct**. The
per-grain path is entered only for the shapes v0.11.0 rejects, so single-grain
SQL is byte-identical to v0.11.0.

```
                     ┌── plan_grains(def, dims, metrics) ──┐
  expand()  ─────────┤                                     ├── None  → existing base-anchored path
                     └── Some(plan) when a base-anchored ───┘         (+ full fan-trap fence)
                         FROM would inflate AND the query
                         is per-grain eligible
                                    │
                                    ▼
                     WITH __sv_grain_0 AS (   -- anchored FROM <metric grain table>
                              SELECT <dims>, <metrics at this grain> ... GROUP BY <dims>
                          ),
                          __sv_grain_1 AS ( ... )
                     SELECT COALESCE(g0.d, g1.d) AS "<dim>", g0.m0, g1.m1, <derived over both>
                     FROM __sv_grain_0
                     FULL OUTER JOIN __sv_grain_1 ON <null-safe dim equality>
```

**Eligibility** (all must hold, else the v0.11.0 error stands): no window metric,
no *active* semi-additive metric, no role-playing/`USING` resolution in play, and
every queried dimension reachable from every metric grain through non-fanning
edges (a dimension below a metric's grain remains a `FanTrap` error).

## Phases

### Phase 69: Per-grain aggregation core

**Goal:** multi-grain **aggregate** metric queries (base + single-grain derived)
are answered per-grain. GRAIN-01/02/03/05/06/07/08.
**Depends on:** —

- [x] 69-01 — `src/expand/per_grain.rs`: grain partitioning + routing predicate
  (`plan`), anchored join resolution, CTE + `FULL OUTER JOIN` emission;
  `check_fan_traps` gains a `per_grain` mode that suppresses exactly the two
  checks per-grain supersedes (metric-grain × root, metric × metric — the
  intra-metric grain span folds into the same decomposition) while keeping
  metric × dimension live.
- [x] 69-02 — Test wave: `src/expand/tests_per_grain.rs` (22 cases: anchoring,
  CTE/join shape, determinism, the unchanged flat path, and the shapes that
  still error), `test/sql/phase69_per_grain.test` (numeric, via the full
  extension path), `tests/star_schema_proptest.rs` +
  `tests/multi_hop_join_proptest.rs` invariants strengthened (VER-03).
- [x] 69-03 *(unplanned, found by 69-02)* — **Fence gap**: two tables that both
  reference a third are siblings, not ancestors, so the parent-chain path walk
  found no path between them and the metric × dimension check silently skipped
  the pair — a metric on one grouped by a dimension on the other returned
  inflated numbers. All three checks now walk the undirected relationship
  graph. The dead `JoinTree` path helpers were removed; the fan-in weakness
  this exposed in the *fact*-path validator is TECH-DEBT #37 (a false
  rejection, deliberately left for a separate change).

### Phase 70: Multi-grain derived metrics

**Goal:** a derived metric whose components span grains is decomposed into
per-grain component columns and re-assembled in the outer SELECT. GRAIN-04.
**Depends on:** Phase 69

- [x] 70-01 — Component decomposition (`inline_references` over per-grain column
  refs), on-demand resolution of intermediate derived metrics with a depth cap,
  emission + tests. Two defects caught by the tests before landing: components
  collected by iterating a `HashSet` made the CTE numbering vary between
  expansions of the same query (now declaration order), and a rebuild that
  remembered "already inlined" across sibling branches leaked raw metric names
  into the SQL.

### Phase 71: Documentation, examples, release

**Goal:** DOC-01..03, REL-01, VER-04.
**Depends on:** Phase 70

- [x] 71-01 — Multi-grain differential coverage (VER-04):
  `tests/multi_hop_join_proptest.rs` gained a per-grain oracle spanning up to
  three grains at once, and `test/integration/test_differential.py`'s
  multi-grain section now compares values against a hand-written per-grain
  reference instead of asserting the fan-trap error.
- [x] 71-02 — Docs (`how-to/fan-traps.rst` §Multi-Grain Queries,
  `explanation/snowflake-comparison.rst` §Metric Grain — the docs statement
  TECH-DEBT #35 asked for), TECH-DEBT #35 → ✅ plus #36 (residual: window /
  semi-additive / role-playing) and #37 (JoinTree fan-in), MAINTAINER.md module
  table + proptest description.
- [x] 71-03 — CHANGELOG `[0.12.0]`, `examples/per_grain_metrics.py`, version bump.

## Verification

`cargo test` (19 binaries) · `make test_debug` 81/81 sqllogictests ·
`cargo clippy` (pedantic) + the `extension`-feature clippy · `cargo fmt` ·
`sphinx-build -W` · `cargo +nightly check` on the fuzz crate ·
`test_differential.py` (195 combinations) · `test_ducklake_ci.py` ·
`test_adbc_queries.py` · `test_chunk_boundary.py` · `examples/per_grain_metrics.py`.
