A pipeline load must be all-or-nothing. Half-loaded tables are how dashboards lie.
BEGIN;
DELETE FROM mart_daily_revenue WHERE day = CURRENT_DATE - 1; -- restate partition
INSERT INTO mart_daily_revenue (day, revenue_cents)
SELECT date_trunc('day', ordered_at), SUM(amount_cents)
FROM stg_orders
WHERE ordered_at >= CURRENT_DATE - 1 AND ordered_at < CURRENT_DATE
GROUP BY 1;
COMMIT;
-- Any error before COMMIT -> ROLLBACK, table untouched.
Rules
- Wrap restate-and-reload in one transaction. Crash between DELETE and INSERT without a transaction = missing partition in production.
- Keep transactions short: no API calls, no sleep, no user input inside BEGIN..COMMIT. Long transactions hold locks and bloat.
- Know your engine: Postgres DDL is transactional (a failed migration rolls back); most warehouses auto-commit DDL — test rollback behavior per engine.
- Idempotency beats transactions for replays: transaction for atomicity + watermark/ON CONFLICT for safe re-runs. You need both.