Trading a runtime join for a materialized column, then finding the 79% my own migration left behind

A per-request warehouse join was scanning 658.8 MB against a hard 953.7 MiB per-job ceiling, on a table growing with every new customer. I measured it, moved the join into the pipeline that already ran it, deleted the cache that was hiding the problem, then audited my own migration and found 79% of production rows never backfilled, plus 9,299 alerts that had never appeared in the product at all.

impact658.8 MB per cache miss removed; quota cliff removed with itCold latency 3.4s to ~0.8s (~77%)Warehouse jobs per cold request 3 to 270,502 production rows repaired, idempotently9,299 invisible alerts (10.4%) recovered into the product

Context

This is my clearest evidence of two senior behaviours in one arc. First, deciding on measurements rather than instinct: the fix wasn't "the query feels slow", it was "this query is at 69% of a hard ceiling on a table that grows with every customer we sign, and here are the bytes". Second, treating my own shipped change as something to be audited. Nobody reported either bug. I went looking, found that my migration had left most of production stale, wrote up the root cause and the accepted residual risk in public tickets with my name on them, and fixed it. The capability I'd point to is not the SQL. It's being willing to be the person who finds your own residue.

Problem

A reporting service serves an anomaly-management view: for every pending inspection alert, the asset and the chain of organizational folders above it, so an operator can locate the equipment in a plant.

That chain was assembled per request. The service issued an extra warehouse query that joined alert rows to a reference table of organizational nodes by path prefix. The reference table is clustered on its identifier, not on path, so a prefix join has no pruning available to it and reads the table end to end. Measured in production, that one query scanned 658.8 MB per cache miss, while the main query for the same endpoint scanned 53 MB. The bytes barely moved between a one-path request and a twenty-path batch, because the cost was the full scan, not the batch.

The platform caps each warehouse job at 953.7 MiB. So a single supporting query was consuming 69% of the ceiling, on a reference table already at 962 MB and 2.67M rows that grows every time a customer creates a workspace. At roughly 45% more growth the endpoint stops being slow and starts returning a hard quota error. What was holding the line was a 15-minute in-memory LRU cache I had added earlier in the same epic: a mitigation, not a fix, since every miss still paid the 658.8 MB.

Constraints

The hard part wasn't the query. It was seeing the failure mode correctly and sequencing the fix.

—The symptom lied. Latency looked like a caching problem. The real risk was a *quota cliff*: not a gradual slowdown, but a hard failure at a threshold set by a table growing on customer signups, so it arrives fastest exactly when the business is doing well.

—Seven writers, one schema. The column had to be added to the table schema, the table function's declared return type, the incremental load, the daily merge, the backfill, the consistency check, and a migration script. Miss one and rows land incomplete, silently.

—The ordering was load-bearing. If the service started reading the new column before the backfill had run in production, every breadcrumb in the product would go blank. That constraint is what turned this into a staged migration rather than a pull request.

—The safety net could not see the failure. The pipeline's consistency check compares the raw event against the curated row, but it reads the path from *the same event field* the pipeline reads. When that field was absent, both sides were NULL and the check reported perfect agreement. A divergence checker structurally cannot detect absence.

Decision

I measured before proposing anything. Real query executions with cluster pruning in both staging and production: bytes for the supporting query, bytes for the main query, the size and row count of the reference table, the number of warehouse jobs per cold request, and cold latency. Those numbers are what made the argument, and they are what told me which lever mattered.

I asked where the work was already being done. The pipeline's incremental load already joined the same alert rows against the same reference table to compute four fixed depth columns. So materializing the full chain wasn't new work; it was one more aggregation inside a join that already ran. That reframing is what made the change cheap rather than a trade of request cost for pipeline cost.

I wrote the migration as four ordered steps with their own acceptance criteria: schema and pipeline in staging, then production, then the service, then an optional cleanup, with the dependency stated explicitly: the service must not read the column until production rows are populated. I also flagged, in writing, that the existing depth columns filtered nothing by node type while the new chain had to exclude machine-level nodes, so it was a new aggregation and not a reuse of the old one.

Then I audited my own change. While modelling the next feature I checked whether any alert would fall outside a workspace scope, and found 70,684 of 88,995 pending alerts (79%) with an empty location chain. The backfill script existed, handled the new column, and had simply never been executed in production.

I proved it was a temporal gap, not a data fault, before touching anything: 61,385 rows had the old depth column populated and the new chain empty. Both come from the same join, in the same expression. If the join had failed, both would be empty. Those rows passed the join at a time when the new column did not yet exist. A second signal agreed: 733 paths had rows in *both* states, the empty ones stopping on the day of the migration and the populated ones continuing.

I repaired with a targeted, idempotent statement rather than a reload. The obvious move, truncate and re-run the backfill over a ten-year window, was more blast radius than the problem justified. Instead I wrote an `UPDATE` restricted to rows with an empty chain, and reused the pipeline's own logic with one change: resolving ancestors by enumerating path prefixes and joining on equality, instead of the prefix-match join the pipeline uses, which took over four minutes here. I validated the two forms produced identical output on 500 sampled paths (500/500) before running it. The empty-chain predicate appears in both the target and the filter deliberately, so the statement is idempotent and cannot overwrite a good value. 61,203 rows repaired.

The residue was the real find. 9,299 rows did not move, and all of them had a NULL path, from a period before the event producer started sending the field. The table function filters scope with `path = workspace OR STARTS_WITH(path, workspace || '/')`. With a NULL path both comparisons evaluate to NULL, `NULL OR NULL` is NULL, and `WHERE NULL` drops the row. These alerts weren't showing up without a location. They weren't showing up at all: 10.4% of pending alerts, including 5,469 at the highest severity band, across 1,588 checklists, 91% of them inside a single tenant. Staging was proportionally worse at 31.9%. No customer had reported it, because the product's answer to "how many pending alerts do I have?" was simply and quietly wrong.

I recovered those paths from a materialized source keyed by checklist, and validated before executing rather than after: a 1:1 join check (2,167,356 rows to 2,167,356 distinct ids), zero NULL paths in the source, byte-identical formatting on samples, 99.57% agreement (32,846 of 32,988) on the subset where both values existed, and a full rehearsal in staging that fixed 139 of 139 with zero residue.

Trade-offs

—Materializing the chain in the pipeline over raising the per-job byte ceiling. Raising the cap converts a hard, visible failure into a silent, growing cost. I wrote it down as an acceptable stopgap if the work slipped, explicitly not as the solution.

—Materializing over keeping the request-time cache and tuning it. The cache was real mitigation and I had built it, but every miss still paid the full scan. Keeping it meant keeping the TTL, LRU and negative-caching logic that existed *only* because of this one query. Removing the cause let me delete all of it.

—A targeted idempotent `UPDATE` over truncate-and-reload from the backfill script. The reload was the sanctioned path and would have been simpler to justify. I chose the narrower statement because it touches only broken rows, is safe to re-run, and cannot clobber a correct value; the cost is that I had to prove my rewritten ancestor resolution was equivalent to the pipeline's.

—Prefix enumeration with an equality join over the pipeline's prefix-match join. Faster by orders of magnitude at repair scale, at the price of a second implementation of the same rule. I paid that price down with a 500-sample equivalence check rather than an argument.

—Recovering paths from the current materialized source over leaving 9,299 alerts invisible. The source holds a checklist's *current* location, while the report held its location *at answer time*. On the comparable base, 143 rows (0.43%) disagreed, 14 of them under a different root: checklists that had been moved. Extrapolated to the recovered set, that is roughly 7 moved internally and possibly 1 across roots, and because those rows had no path at all, there is no way to identify which. I took visible-and-possibly-stale over invisible, and wrote the residual risk into the ticket so that the next person to be asked "why is this old alert filed there?" has the answer.

—No snapshot before the repair over a defensive copy. A conscious call, not an oversight. The statements were idempotent, narrow, rehearsed in staging, and the warehouse's 7-day time travel was the fallback. I recorded that the decision was made and why, because the pre-repair numbers in the ticket are now the only record of the prior state.

Impact

—Removed 658.8 MB of scanned bytes per cache miss and with it the quota cliff. The endpoint no longer sits at 69% of a hard ceiling on a table that grows with customer count.

—Cold latency 3.4s → ~0.8s (~77%), by cutting warehouse jobs per cold request from 3 to 2.

—Deleted the location cache entirely, along with its TTL, LRU and negative-caching logic, since the code existed only to compensate for the query that no longer runs. The breadcrumb also stopped being capped at four levels: chains now run 1 to 10 deep.

—Repaired 70,502 production rows: 61,203 with a stale empty chain, 9,299 that were being dropped from the product altogether. Verified end state: 89,067 pending alerts, zero missing a path, zero with an empty chain.

—Recovered 9,299 alerts (10.4% of pending alerts) into the product, including 5,469 at the highest severity band, in a report customers use to prioritize maintenance. Counters and listings had been understating the real backlog.

—Unblocked the feature that found the bug. An accumulated-alerts view scoped by workspace would have seen only 20% of the data.

—Filed the prevention work honestly as *to do*, not as done: a pipeline fallback when the event field is absent, a completeness assertion in the consistency check, and a decision on whether the table function should exclude NULL paths explicitly and count them rather than let them vanish.

Lessons Learned

—A quota is a cliff, not a slope. Byte-billing limits and similar caps don't degrade, they fail hard at a threshold. When the driver of that threshold is a table that grows with business success, "it's fine today" is not a measurement, it's a countdown.

—Measure the lever, not the suspicion. Two plausible optimizations were on the table. Measurement said one was 69% of the ceiling and the other was 18% of a query that wasn't the problem. Writing "not the lever, revisit if the base grows 10x" into the out-of-scope section is part of the deliverable.

—A divergence checker cannot detect absence. If your validation reads the same source as the thing it validates, NULL agrees with NULL and the check passes. Integrity checks need at least one *completeness* invariant, asserted independently.

—NULL-dropping filters fail closed and silently. `WHERE prefix_match(NULL, x)` doesn't return an unlocated row, it returns no row. A record that disappears is far worse than one that renders incomplete, and nothing in the system will tell you.

—Adding a derived column to a pipeline is a migration, not an edit. Historical rows don't rebuild themselves. "Run the backfill" belongs in the acceptance criteria of the PR that adds the column, not in someone's memory.

—Prefer the idempotent narrow write to the sanctioned wide one when you can prove equivalence. Restating the guard in both the target and the filter is what makes a repair safe to re-run at 3am.

—Publishing the residual risk is part of shipping the fix. I could not identify which recovered alerts might show a stale location. Saying so, with the measured rate, converts an unknown liability into a documented one.

Evidence

—Owned end to end and alone: measurement, the written decision with its rejected alternatives, a four-step staged rollout across two environments with per-step acceptance criteria, the service-side change, and the two forensic repairs.

—Measurements taken as real executions with cluster pruning in both staging and production, recorded in the ticket alongside the ceiling they were compared against, including the counter-measurement that killed the alternative optimization.

—Both defects were found by me during modelling of the next feature, not reported by a customer or by monitoring; both were written up as public root-cause tickets with reproduction queries, the applied fix, the accepted residual risk, and prevention items.

—Every repair rehearsed in staging before production, with the pre-flight validation table (join cardinality, source completeness, format equality, agreement rate) recorded before execution rather than after.

—Source (private): Jira epic and root-cause tickets in the inspection domain (2026-08), plus the corresponding infrastructure-as-code and service pull requests.