A scheduled task pulls from an API, writes to staging, then merges into the warehouse. If the task dies halfway through the write and the retry runs, we get duplicate rows for the records that landed before the crash. Right now the on-call person deletes them by hand in the morning. About 2 million rows a night across roughly 40 partitions. How do people make this genuinely safe?
Whatever else you do, get a real key and put a uniqueness check on it. Half of these threads end with somebody discovering that the 'key' they merge on was never unique in the source.
Also check whether the API pull is deterministic. If you request 'the last 24 hours' at retry time you get a different window than the original attempt, and then even a perfect merge produces different data. Pin the window to the logical execution time, never to now().
This is the one that bites people six months after they fix the write path.
Add a dedup step as a stopgap this week - a window function keeping the latest row per key per partition - so nobody is doing manual deletes while you build the real fix. Just do not let the stopgap quietly become the architecture.
Two million rows a night is small enough that full partition rebuilds are affordable. Take the simple correctness win while you can still afford it.
@dbt_and_dust · 5mo ago · 4 replies
Make the write idempotent rather than trying to make it not fail. Retries are a fact of life; a pipeline that only works when nothing crashes is not a pipeline, it is a streak.
The standard pattern for what you describe:
staging/dt=2026-07-30/run_id=abc123/. A retry writes to a new run_id and the partial output of the failed attempt is simply orphaned.The key shift is that the unit of work is 'this partition is now correct', not 'these rows have been added'. Once any task can be rerun any number of times and land in the same state, on-call stops deleting rows by hand.
At 2 million rows over 40 partitions, delete-insert per partition costs almost nothing and is by far the easiest of the three to reason about.
Reply
Report
@dbt_and_dust · 5mo ago
That is the whole bug. Immutable staging plus atomic publish, and a lot of downstream problems get easier as a side effect.
Reply
Report
@coldstorage_cy · 5mo ago
The run_id in the staging path is the piece we are missing. We overwrite the same staging prefix every night, which is exactly why a partial write is poison.
Reply
Report
@etcd_eli · 5mo ago
It also means you can keep the last few runs and diff them when something looks wrong, which is worth the storage on its own.
Reply
Report