The BrokenPipeError in Meltano's log writer is the tail of the failure, not
its cause. In a Singer run the tap writes to the target's stdin; when the target
dies, the tap's next write hits a closed pipe. The traceback is several frames
away from what actually went wrong.
while row_to_record returns urn: None for the England rows. target-postgres
turns primary_keys into a NOT NULL constraint, so the first national row of
the run failed to insert and took the loader with it. Every other tap in this
repo keys on columns that cannot be null.
The fix
Carrying two grains in one stream was the real mistake, so this separates them
rather than papering over the null. Four streams:
The national streams carry no urn column at all — a school identifier that
is null in every row is a grain mismatch, not a column. The staging models split
the same way, and fact_destination_national reads the new pair instead of
filtering where urn is null. The school staging models go back to a strict where urn ~ '^[0-9]+$'.
The annual DAG's dbt selector picks up all four.
Verified against the live API
school stream: 135,240 rows over 4,508 schools, zero duplicate primary
keys, zero null key columns, all 31,382c suppression sentinels intact
national streams: 30 rows (KS4) and 33 (16-18), no urn column, no null keys
Five new tap tests cover the grain split, including one asserting no stream can
key on a column it may leave null — the specific shape of this bug.
Backend suite unaffected: 177 passing.
Re-running
Once this deploys, re-trigger school_data_annual_ees. The raw tables are
recreated by the loader, so no manual cleanup is needed — though the two new *_national tables will be created on first run.
Fixes the `school_data_annual_ees` failure.
## What the error was, and wasn't
The `BrokenPipeError` in Meltano's log writer is the tail of the failure, not
its cause. In a Singer run the tap writes to the target's stdin; when the target
dies, the tap's next write hits a closed pipe. The traceback is several frames
away from what actually went wrong.
The cause is mine. The tap declared:
```python
primary_keys = ["urn", "time_period", "pupil_group", "destination_measure"]
```
while `row_to_record` returns `urn: None` for the England rows. `target-postgres`
turns `primary_keys` into a `NOT NULL` constraint, so the first national row of
the run failed to insert and took the loader with it. Every other tap in this
repo keys on columns that cannot be null.
## The fix
Carrying two grains in one stream was the real mistake, so this separates them
rather than papering over the null. Four streams:
| Stream | Grain | Primary key |
|---|---|---|
| `ees_ks4_destinations` | school | `urn, time_period, pupil_group, destination_measure` |
| `ees_ks5_destinations` | institution | same |
| `ees_ks4_destinations_national` | England | `time_period, pupil_group, destination_measure` |
| `ees_ks5_destinations_national` | England | same |
The national streams carry **no `urn` column at all** — a school identifier that
is null in every row is a grain mismatch, not a column. The staging models split
the same way, and `fact_destination_national` reads the new pair instead of
filtering `where urn is null`. The school staging models go back to a strict
`where urn ~ '^[0-9]+$'`.
The annual DAG's dbt selector picks up all four.
## Verified against the live API
- school stream: **135,240 rows over 4,508 schools**, zero duplicate primary
keys, zero null key columns, all **31,382** `c` suppression sentinels intact
- national streams: 30 rows (KS4) and 33 (16-18), no `urn` column, no null keys
Five new tap tests cover the grain split, including one asserting no stream can
key on a column it may leave null — the specific shape of this bug.
Backend suite unaffected: 177 passing.
## Re-running
Once this deploys, re-trigger `school_data_annual_ees`. The raw tables are
recreated by the loader, so no manual cleanup is needed — though the two new
`*_national` tables will be created on first run.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
https://claude.ai/code/session_01BvdDKvFFSZuMVDH5fEyTob
The annual DAG died with a BrokenPipeError from Meltano's log writer, which
is several frames from the cause: target-postgres exited first and the tap
saw its stdout close.
The tap declared primary_keys = [urn, ...] while emitting urn=None for the
national rows, and target-postgres turns primary_keys into a NOT NULL
constraint. The first national row of the run failed the insert and took
the loader with it. Every other tap in this repo keys on non-null columns.
Carrying two grains in one stream was the actual mistake, so the fix is to
separate them rather than paper over the null: four streams now, with
ees_ks4/ks5_destinations_national carrying no urn column at all — a school
identifier that is null in every row is a grain mismatch, not a column.
The staging models split the same way and the national mart reads the new
pair instead of filtering `where urn is null`.
Verified against the live API: the school stream yields 135,240 rows over
4,508 schools with no duplicate keys, no null key columns and all 31,382
suppression sentinels intact; the national streams yield 30 and 33 rows
with no urn column.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01BvdDKvFFSZuMVDH5fEyTob
This PR splits the tap-uk-ees-destinations tap's school-level and England-reference rows into separate Singer streams and dbt staging models, fixing a real production bug where target-postgres's NOT NULL constraint on primary keys caused the loader to die on the first national row (surfacing only as a BrokenPipeError). The refactor (mixin-based Stream subclasses, new staging models, updated mart, updated dbt select list and meltano config) is internally consistent and well covered by new tests; no correctness, security, or deploy issues were found.
🟡 Minor
pipeline/transform/models/staging/_stg_sources.yml: The existing descriptions for the ees_ks4_destinations and ees_ks5_destinations raw sources still say they contain 'plus England rows with a null urn', which is no longer true now that national rows live in separate _national source tables. These weren't updated when the grain was split out.
## 🤖 AI Code Review (Claude Code)
This PR splits the tap-uk-ees-destinations tap's school-level and England-reference rows into separate Singer streams and dbt staging models, fixing a real production bug where target-postgres's NOT NULL constraint on primary keys caused the loader to die on the first national row (surfacing only as a BrokenPipeError). The refactor (mixin-based Stream subclasses, new staging models, updated mart, updated dbt select list and meltano config) is internally consistent and well covered by new tests; no correctness, security, or deploy issues were found.
### 🟡 Minor
- **pipeline/transform/models/staging/_stg_sources.yml**: The existing descriptions for the `ees_ks4_destinations` and `ees_ks5_destinations` raw sources still say they contain 'plus England rows with a null urn', which is no longer true now that national rows live in separate `_national` source tables. These weren't updated when the grain was split out.
tudor
merged commit b0c4ea8282 into main2026-08-31 20:43:27 +00:00
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
Fixes the
school_data_annual_eesfailure.What the error was, and wasn't
The
BrokenPipeErrorin Meltano's log writer is the tail of the failure, notits cause. In a Singer run the tap writes to the target's stdin; when the target
dies, the tap's next write hits a closed pipe. The traceback is several frames
away from what actually went wrong.
The cause is mine. The tap declared:
while
row_to_recordreturnsurn: Nonefor the England rows.target-postgresturns
primary_keysinto aNOT NULLconstraint, so the first national row ofthe run failed to insert and took the loader with it. Every other tap in this
repo keys on columns that cannot be null.
The fix
Carrying two grains in one stream was the real mistake, so this separates them
rather than papering over the null. Four streams:
ees_ks4_destinationsurn, time_period, pupil_group, destination_measureees_ks5_destinationsees_ks4_destinations_nationaltime_period, pupil_group, destination_measureees_ks5_destinations_nationalThe national streams carry no
urncolumn at all — a school identifier thatis null in every row is a grain mismatch, not a column. The staging models split
the same way, and
fact_destination_nationalreads the new pair instead offiltering
where urn is null. The school staging models go back to a strictwhere urn ~ '^[0-9]+$'.The annual DAG's dbt selector picks up all four.
Verified against the live API
keys, zero null key columns, all 31,382
csuppression sentinels intacturncolumn, no null keysFive new tap tests cover the grain split, including one asserting no stream can
key on a column it may leave null — the specific shape of this bug.
Backend suite unaffected: 177 passing.
Re-running
Once this deploys, re-trigger
school_data_annual_ees. The raw tables arerecreated by the loader, so no manual cleanup is needed — though the two new
*_nationaltables will be created on first run.🤖 Generated with Claude Code
https://claude.ai/code/session_01BvdDKvFFSZuMVDH5fEyTob
🤖 AI Code Review (Claude Code)
This PR splits the tap-uk-ees-destinations tap's school-level and England-reference rows into separate Singer streams and dbt staging models, fixing a real production bug where target-postgres's NOT NULL constraint on primary keys caused the loader to die on the first national row (surfacing only as a BrokenPipeError). The refactor (mixin-based Stream subclasses, new staging models, updated mart, updated dbt select list and meltano config) is internally consistent and well covered by new tests; no correctness, security, or deploy issues were found.
🟡 Minor
ees_ks4_destinationsandees_ks5_destinationsraw sources still say they contain 'plus England rows with a null urn', which is no longer true now that national rows live in separate_nationalsource tables. These weren't updated when the grain was split out.