---
title: Register a drift check for the play-stat-derived columns
type: task
status: planned
description: >-
  Twelve nfl_plays columns are a cached projection of nfl_play_stats and no registered data check
  covers any of them. Add one grading all twelve against their GSIS stat ids in both directions,
  modelled on the is_fumble_lost check.
base_uri: user:task/league/register-drift-check-for-play-stat-derived-columns.md
created_at: '2026-09-21T15:39:39.863Z'
entity_id: 12e26100-bec8-46a2-b941-e87b4c1af828
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
relations:
  - blocked_by [[███████████████████████-████-████-███████-███████-████-████-████████]]
  - relates [[user:task/league/repair-is-tackle-for-loss-undercount.md]]
  - follows [[user:guideline/software/design-data-checks.md]]
tags:
  - user:tag/league-xo-football.md
  - user:tag/software-task.md
updated_at: '2026-10-05T15:36:43.030Z'
visibility_analyzed_at: '2026-11-06T00:18:00.240Z'
---

# Implementation Plan: Register a drift check for the play-stat-derived columns

## Overview

- Twelve `nfl_plays` columns are a CACHED PROJECTION of `nfl_play_stats` — eleven outcome booleans plus field-goal `kick_distance`, each an exact function of GSIS stat ids. Nothing verifies the cache matches its source.
- Measured 2026-09-21: not one registered data check in the private check registry names any of the twelve. The canonical columns got `play-canonical-column-drift` for exactly this reason; these are in the same position and have no equivalent.
- Expected outcome: a registered check that recomputes all twelve from `nfl_play_stats` and reports every row where the stored column disagrees, in BOTH directions.

## Background

The template already exists in the registry: the `is_fumble_lost` check grades that column against GSIS `stat_id` 106 rows. Its own note is the design argument for this task, and it should be read before writing anything:

> the natural invariant to reach for is `is_fumble_lost implies is_fumble`, and that one is SOUND but nearly blind. Measured 2026-09-19, it saw 25 of the 333 defective 2025 rows and read GREEN over the other 308. A check that passes while the defect it names is live is worse than no check.

So the check must grade against the stat rows — the definition — rather than against a correlate, and it must see the UNDER-read direction, where a play carries the stat id and the column reads false or null.

## Design

One check covering all twelve, recomputing from `nfl_play_stats` filtered `is_valid = true`:

| column | stat ids |
| --- | --- |
| `is_third_down_converted` | 6 |
| `is_third_down_failed` | 7 |
| `is_fourth_down_converted` | 8 |
| `is_fourth_down_failed` | 9 |
| `is_first_down_penalty` | 5 |
| `is_fumble` | 52, 53, 54 |
| `is_field_goal_attempt` | 69, 70, 71 |
| `is_extra_point_attempt` | 72, 73, 74, 301 |
| `is_two_point_conversion_attempt` | 75, 76, 77, 78, 104, 105 |
| `is_tackle_for_loss` | 95, 120 |
| `is_incompletion` | 14 |
| `kick_distance`, field goals only | `coalesce(70, 69, 71)` |

### Scope the check to the CURRENT season, and say why

The historical seasons are not clean and will not be until the repair task runs. Scoping to the current season makes the check an oracle on the live derivation rather than a standing red over known history. Widen it once `repair-is-tackle-for-loss-undercount` has landed.

### Known seams that must be excluded or declared, not tolerated

- **2005 to 2007**: stat 120 is effectively absent from the feed — 155, 33 and 181 plays carry it against 2,397 in 2004. Unrepairable from stored data.
- **2002**: a broader collection gap, 898 plays with stat 95 and 1,158 with stat 120 against roughly 1,400 and 2,400 either side. Both ids down.

Neither is reachable while the check is scoped to the current season, but both must be named in the check's note so a later widening does not read them as drift.

## Plan

- [ ] **Read the registered `is_fumble_lost` check end to end** — in the private check registry. It is the template for the both-directions predicate and for the note's standard of evidence.
- [ ] **Read [[user:guideline/software/design-data-checks.md]]** — required before adding, changing or removing a registered check.
- [ ] **Write the check** covering all twelve, scoped to the current season, grading against `nfl_play_stats` in both directions.
- [ ] **Verify by perturbation, as a PAIR.** Corrupt one row per column through `db:exec` and require the check to report exactly that row, then restore and require zero. A check whose control has not fired is not evidence, and a zero over a scanned population of zero is the failure mode this repo names explicitly.
- [ ] **Record the scanned population** in the note alongside the violation count, so a future reader can tell a clean result from a check that reached nothing.