# Canonical Key Documentation — NFL Data Warehouse

**Decision (Collin, Gate B, 2026-10-24):** the canonical within-snapshot play identifier is **`(game_id, play_id)`**. `drive` is retained as a nullable validated attribute, not a key component. For any future table that combines multiple snapshots, the unique record identity is **`(snapshot_id, game_id, play_id)`**.

## Evidence (Stages 2 and 4, 2026-10-24)

- Tested on all **1,296,179 plays** in the canonical snapshot (`nflverse-pbp/retrieved=2026-10-24`): `(game_id, play_id)` produced **zero duplicate groups in all 28 seasons (1999–2026)**, and both components are NULL on **0 rows**.
- `(game_id, drive, play_id)` is also collision-free, but `drive` is NULL on **13,702 rows** (2001 onward, ~1.1–1.3% per season, concentrated in non-play/marker rows) — a null-bearing component cannot serve as part of a primary key.
- The participation datasets carry **no `drive` column**; their only possible play key is `(nflverse_game_id, play_id)`, which is the same value pair as `(game_id, play_id)`. Canonical-key joins to participation validated at 100% unique for 2023–2025 and ≥98.3% (with documented exceptions) for 2016–2022.

## Rules

1. Within one snapshot, a play is identified by `(game_id, play_id)`. `play_id` is only meaningful inside its game; never join on `play_id` alone.
2. `game_id` is the nflverse human-readable identifier (e.g. `2023_01_ARI_WAS`). The schedules file's `old_game_id` (e.g. `2023091007`) is preserved as an attribute, never used as the play key.
3. Cross-snapshot tables must carry `snapshot_id` and treat `(snapshot_id, game_id, play_id)` as the record identity. Plays from different snapshots are different records even when their values agree (see the 2026 EPA revision, exceptions E6).
4. `drive` is a validated attribute: populated on ~98.8% of rows; NULLs concentrate on non-play rows (kickoffs' surrounding markers, extra points, no-plays). It may be used as a secondary discriminator in investigations, never as a silent join key.
5. If a future snapshot ever produces a `(game_id, play_id)` collision, the collision is reported and the key question re-opened — keys are never silently widened.
