An Idempotent Daily Load in Python

The interviewer asks: "Your daily job ran twice. How do you make that harmless?" The answer they want: make the load idempotent, so running it twice leaves the table exactly as running it once. Schedulers retry failed jobs, and people re-run a day after its file is fixed. A load that only appends counts that day's orders twice in every report.

Two wrong answers: INSERT alone appends again, so loading 2026-05-02 twice leaves 24 rows for a day whose file has 12. DROP TABLE alone (drop the table, create it again, load the day) stops the double count but throws away every other day: 2026-05-01's 10 rows are gone.

The right answer: delete-then-insert, in one transaction. Each row of stg_orders keeps the run_date that loaded it, so a run first deletes the rows of its own day, then inserts the day's file. A transaction makes the two count together: after BEGIN nothing is final until COMMIT, and if a statement fails, ROLLBACK puts the table back as it was before BEGIN. A ? in a query is a parameter: con.execute(query, [value]) puts the value in its place. The same pattern on another table:

con.execute("BEGIN")
con.execute("DELETE FROM page_views WHERE day = ?", ["2026-04-30"])
con.execute("INSERT INTO page_views SELECT * FROM read_csv('views/2026-04-30.csv')")
con.execute("COMMIT")

Without the transaction, a re-sent file that fails to load is worse than no re-run: the delete has already happened, and the day disappears from every report. Schedulers like Airflow hand each run its date, so re-running a day always deletes and reloads the same rows.

The starting code creates stg_orders and loads 2026-05-01, then 2026-05-02 twice. The check counts rows per day, re-sends 2026-05-02's file with the quantity two in one row, rolls the failed load back, and counts again:

run_date | orders
2026-05-01 | 10
2026-05-02 | 12
(2 rows)
Re-run of 2026-05-02 with a broken file: ConversionException
run_date | orders
2026-05-01 | 10
2026-05-02 | 12
(2 rows)

Your task: in load_day, write the query that deletes the run day's rows and run it before the insert, then wrap the whole load in BEGIN and COMMIT.

Assignment
Follow the Coding Tutorial to make load_day delete its day before inserting it, inside one transaction.

Hint
Build the DELETE like the INSERT below it: one string piece per clause, each ending with a space, run with con.execute(query, [run_date]). BEGIN goes on the first line of load_day and COMMIT on the last; a BEGIN without its COMMIT makes the next call fail.