Transactions: Atomic Loads

# Transactions: Atomic Loads A pipeline load must be all-or-nothing. Half-loaded tables are how dashboards lie. ```sql 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 ROLLBACK, table untouched. ``` ## Rules 1. **Wrap restate-and-reload in one transaction.** Crash between DELETE and INSERT without a transaction = missing partition in production. 2. **Keep transactions short:** no API calls, no sleep, no user input inside BEGIN..COMMIT. Long transactions hold locks and bloat. 3. **Know your engine:** Postgres DDL is transactional (a failed migration rolls back); most warehouses auto-commit DDL — test rollback behavior per engine. 4. **Idempotency beats transactions for replays:** transaction for atomicity + watermark/ON CONFLICT for safe re-runs. You need both.