Practice — ETL vs ELT & Pipeline Design (5 questions)

Your team's hourly revenue pipeline reads new rows from a staging table and appends them into warehouse.revenue_fact:

INSERT INTO warehouse.revenue_fact
SELECT transaction_id, account_id, amount, transaction_ts
FROM staging.transactions_new;

staging.transactions_new is truncated and repopulated by an upstream extract job before each run. Last Tuesday, the warehouse connection dropped 15 seconds after the INSERT began executing but before the job received a success response. The orchestrator's task had already timed out and been configured to auto-retry once, so it ran the same INSERT again two minutes later against the same (unchanged) staging.transactions_new contents. Finance flagged that Tuesday's revenue total was roughly double every other day's.

1. Explain exactly what happened, step by step, including why nothing errored during either run.
2. Redesign the load step so this failure mode cannot recur, and justify why your redesign is safe under an arbitrary number of retries, not just one.
3. The upstream extract truncates and repopulates staging. transactions_new before every run. Is that part of the design also a risk, independent of the fix in part 2? Explain.