# Requirements: DuckDB Semantic Views — v0.12.0 Per-Grain Metric Aggregation

**Defined:** 2026-07-28
**Core Value:** A DuckDB user can define a semantic view once and query it with any combination of dimensions and metrics, without writing GROUP BY or JOIN logic by hand — the extension handles expansion, DuckDB handles execution.

## Milestone Goal

Close the last open **query-semantics** divergence from Snowflake semantic views:
TECH-DEBT #35 — *"Fan-trap fix errors on multi-grain queries; per-grain
('own-grain') metric aggregation — the Snowflake-parity real fix — is deferred"*.

Today every generated query is anchored `FROM <base table>` with `LEFT JOIN`s
outward. A metric whose grain is not the base grain is therefore aggregated over
the *multiplied* join. v0.11.0 closed the silent-wrong-answer half of that: such
shapes now raise `RootGrainFanTrap` / `MetricFanTrap` rather than inflating. They
still do not **answer**.

Snowflake computes each metric at its **own grain**: pre-aggregate each metric's
source table to its grain in a per-metric CTE, then join the pre-aggregated
results on the shared dimensions. This milestone implements that, so the shapes
that error today return correct numbers.

### In scope

The three shapes TECH-DEBT #35 names as answerable-by-Snowflake:

1. A metric whose grain table is a **parent/ancestor of the base table** —
   queried alone, or with dimensions at or above its own grain.
2. **Two or more metrics at different grains** whose join path crosses a fan-out
   edge (fan trap between a parent and the base, chasm trap between two children
   of one parent).
3. A **single derived metric that internally fuses two grains** (e.g.
   `avg AS order_total / item_count`).

### Out of scope (stays an error)

- A dimension **below** a metric's own grain (`SUM(o.amount)` grouped by
  `li.status`). Per-grain aggregation does not make this well-defined: the
  metric's rows genuinely fan across the dimension's values. Snowflake requires
  dimensions to be reachable from the metric's table through many-to-one
  relationships; the existing `FanTrap` error is the right answer and is
  unchanged.
- Multi-grain queries involving **window metrics**, **active semi-additive
  metrics**, or **role-playing (`USING`) resolution**. These keep the v0.11.0
  fan-trap error. Documented as a residual limitation with a TECH-DEBT entry.

## Requirements

### Per-Grain Aggregation (GRAIN)

- [x] **GRAIN-01**: A metric whose source table is a parent/ancestor of the base
  table can be queried **alone** and returns that table's own aggregate (not the
  base-grain-inflated value, and not an error).
- [x] **GRAIN-02**: The same metric can be queried with dimensions at or above
  its own grain, grouped at its own grain.
- [x] **GRAIN-03**: Two metrics at different grains separated by a fan-out edge
  can be queried together; each is computed at its own grain and the results are
  joined on the queried dimensions (`FULL OUTER JOIN`, NULL-safe key comparison,
  so a group present on only one side is preserved).
- [x] **GRAIN-04**: A derived metric whose components span grains is computed by
  evaluating its expression over the per-grain pre-aggregates.
- [x] **GRAIN-05**: Queries with no dimensions produce one row per grain group,
  combined with `CROSS JOIN`.
- [x] **GRAIN-06**: Single-grain queries are **unchanged** — byte-identical SQL
  to v0.11.0 (the per-grain path is entered only where v0.11.0 raised a fan-trap
  error).
- [x] **GRAIN-07**: A dimension below a metric's grain still raises `FanTrap`;
  multi-grain queries involving window / active semi-additive / role-playing
  metrics still raise the v0.11.0 fan-trap error rather than silently producing
  a wrong shape.
- [x] **GRAIN-08**: `COUNT(*)` on a table with no declared `PRIMARY KEY` is
  answerable on the per-grain path (the metric's table anchors its own CTE, so
  the `CountStarRequiresPrimaryKey` guard — which exists only because of
  base-anchored `LEFT JOIN` NULL-extension — does not apply).

### Verification (VER)

- [x] **VER-01**: Rust unit tests cover grain partitioning, per-grain SQL
  emission, and the routing predicate (including the single-anchor no-change
  guard).
- [x] **VER-02**: sqllogictest fixtures execute per-grain queries against real
  data and assert the numeric results, not just the SQL shape.
- [x] **VER-03**: `tests/star_schema_proptest.rs`'s "parent-table metric ⇒
  rejected" invariant is **strengthened**, not deleted: the parent metric must
  now be accepted and equal an independently written per-grain oracle over the
  same random data.
- [x] **VER-04**: A differential proptest covers two-fact-table (multi-grain)
  queries against a hand-written pre-aggregate-then-join oracle.

### Documentation & Release (DOC/REL)

- [x] **DOC-01**: `docs/how-to/fan-traps.rst` documents which shapes are now
  answered per-grain and which still error.
- [x] **DOC-02**: `docs/explanation/snowflake-comparison.rst` states the
  per-grain semantics (closing the "worth an explicit statement in the docs"
  action on TECH-DEBT #35).
- [x] **DOC-03**: TECH-DEBT #35 flips to ✅ RESOLVED with the residual
  limitations recorded as their own entry.
- [x] **REL-01**: CHANGELOG `[0.12.0]` section, `examples/` demo, version bump
  in `Cargo.toml` + `description.yml`.
