Ravi Chandrakant

Also question the cadence. Ask who consumes it and how fresh they actually need it. Half the hourly models I inherit are hourly because somebody typed hourly, and moving to every four hours cuts the bill by 75 percent with nobody noticing.

This is the one that bites people six months after they fix the write path.

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:

• Write to a staging location keyed by run, not by table. Something like 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.
• Make the publish into the warehouse one atomic operation over the whole partition: delete-then-insert inside a transaction, a partition swap, or a MERGE on a real primary key. Not row by row appends.
• Clean up orphaned staging prefixes on a schedule or with a lifecycle rule.

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.

That is the whole bug. Immutable staging plus atomic publish, and a lot of downstream problems get easier as a side effect.

Rolling seven days nightly plus a weekend sweep is nearly free for us. I do not know why I was thinking in terms of one day or all of it.

They are sequential. That is embarrassing and also very fixable.

Add a contract test on the upstream schema. Ten lines that fail loudly when a column type changes turn a mysterious 3am mess into a clear message before anything downstream runs.

Fail on a type change or a missing column, warn on a new column. New columns are usually harmless and blocking on them trains people to ignore the alert.

Full rebuild, at your size and your team size.

Twenty five minutes for a dimension that is correct by construction is a very good trade when there are two of you. Merge logic drifts silently, and the failure mode is not a red pipeline, it is a slightly wrong customer count nobody catches for a quarter. Rebuilds have no drift because there is no state to drift.

The threshold to switch is when the rebuild starts affecting an SLA, or when it gets expensive enough that somebody asks about it. At 40 million rows in 25 minutes you are nowhere near either.

Also, you have told us the keys are imperfect across three systems. The merge is asking you to be certain about the one thing you have said you are unsure about.