29  2026-08-09

29.1 Dead end: replacing the factor-value lift UPSERT with delete-then-insert

The lesson first: I validated a batch change with a single item, and a single item hid both a correctness regression and the real resource behaviour. This is an infrastructure dead end in the platform’s factor pipeline, not a research result — logged here so I don’t quietly re-attempt it.

Context. Factor values are lifted from each Gold feature table into one narrow fact table, gold.fact_factor_value (~1.02 billion rows), keyed by (date_sk, instrument_sk, factor_id). The lift had always been a single INSERT … SELECT … ON CONFLICT (date_sk, instrument_sk, factor_id) DO UPDATE. An operator backfill of the full factor set (the --all path) kept running out of memory on the largest source tables, and I blamed the UPSERT’s conflict-index bookkeeping over a billion rows.

What I tried. I replaced the UPSERT with a per-factor transactional DELETE … WHERE factor_id = ? followed by a plain INSERT … SELECT (no ON CONFLICT tail), reasoning that a full-history re-lift makes the two result-equivalent while dropping the conflict-index cost. I added a scoped connection memory limit and a thread cap on top, and I validated it with a single-factor lift, which succeeded.

Why it failed. The single-factor test was misleading in two ways, both of which only appeared at full --all scale:

  1. It broke the annual factors. The ON CONFLICT DO UPDATE had been silently absorbing intra-statement duplicate keys: pre-1990 annual rows all snap to the same earliest-market-day knowledge date, so one lift SELECT legitimately emits several rows with the same (date_sk, instrument_sk, factor_id). DO UPDATE collapses them (last value wins); a plain INSERT raises a primary-key violation. About twenty annual/quarterly factors — including ones the live pipeline depends on — began failing with a duplicate-key error, which would have starved their evidence panels on the very next nightly and risked them being auto-deprecated for “missing data.”
  2. It still ran out of memory. Memory accumulates across the many sequential per-factor lifts on one shared connection; the raised limit only moved the ceiling, and the run pinned against it about a dozen factors in. A fresh connection lifting a single factor never accumulates, so “peak is per-factor” — the whole premise of my single-factor validation — was simply false.

What I did. I reverted to the ON CONFLICT upsert and added a regression test that plants two source rows collapsing to one fact key and asserts the lift does not raise — it reproduces the exact production error against the delete-then-insert code and passes under the restored upsert, which also confirmed empirically that the engine’s ON CONFLICT genuinely absorbs intra-statement duplicates. The original --all out-of-memory failure is back to being a tolerated limitation of the operator backfill (it never affected the nightly, which lifts only the smaller active subset), pending a proper redesign — source-side de-duplication plus a per-factor connection/memory reset so peak really is per-factor.

Lessons.

  • Validate a batch / at-scale change at scale. A single-item happy path can hide both a correctness regression (intra-batch de-duplication) and the actual resource behaviour (cross-item memory accumulation).
  • ON CONFLICT DO UPDATE was doing two jobs — conflict resolution and silent intra-statement de-duplication. Removing it for the first reason quietly removed the second.
  • A guard the old code provided implicitly is invisible until something removes it; the regression test now makes that guard explicit.

See dead-ends.md.