Append vs Upsert: duplicate key violations in time-series pipelines

A 9% failure rate on our daily data pipeline. The errors were all UniqueViolation: duplicate key value violates unique constraint. They clustered at specific hours — 20:xx, 08:xx, 15:xx — and then disappeared for days.

The root cause was a two-destination write pattern. The script wrote the same data to two databases: production used if_exists='replace' (truncate and re-insert, always succeeds), and the backtest database used if_exists='append'. When retries or scheduler jitter caused overlapping run windows, the second run tried to append rows that the first run had already inserted. The production database never showed the error — it was silently re-creating tables. The backtest database caught the conflict and failed.

Any time-series pipeline where run windows can overlap must use upsert semantics, never plain append. The fix was a staging temp table pattern with INSERT ... ON CONFLICT DO UPDATE, which handles retries, overlapping runs, and backfills without data duplication.

We applied this across the fleet. But the broader pattern is worth naming: whenever your pipeline writes to multiple destinations with different write strategies, you're one deployment away from a non-obvious failure that only affects some outputs.