Capstone: A Daily Load You Can Re-run

Until now every run read the whole orders/ folder. At work, a scheduler runs the load once a day, for one day's file: an incremental load. The starting code has it as load_day(run_date). It builds the file's path from the date, adds that file to raw_orders with INSERT INTO … SELECT, and rebuilds staging and the star. To load the days that already passed, you call it for each one, in order: a backfill.

Runs get repeated: the scheduler retries after a failure, or a fixed file is sent again. A plain INSERT then adds the day a second time, and its orders count twice. The fix is delete-then-insert: first remove the rows an earlier run of that day put in, then insert the file. A load that leaves the same tables however many times it runs is idempotent. Without that, every retry at work quietly makes the numbers bigger.

A ? in a query is a parameter. con.execute(query, [value]) puts the list's item in its place, so you never glue values into the SQL text yourself:

query = (
    "SELECT COUNT(*) "
    "FROM fact_orders "
    "WHERE status = ? "
)
cancelled = con.execute(query, ["cancelled"]).fetchone()[0]

LIMIT 0 keeps none of the rows a query finds. So CREATE TABLE … AS SELECT … LIMIT 0 makes a table with the right columns and no rows: raw_orders starts empty, and the days come in through load_day. After the backfill and a second run of 05-07, your code prints the four checks and the table counts. Then the check runs 05-06 once more, and the counts must not move:

fact keys unique: PASS
no missing customer: PASS
every product_key in dim_product: PASS
source and fact totals agree: PASS
raw_orders | stg_orders | rejected_orders | fact_orders
78 | 73 | 2 | 73
(1 row)
The check calls load_day("2026-05-06") once more, then run_checks():
fact keys unique: PASS
no missing customer: PASS
every product_key in dim_product: PASS
source and fact totals agree: PASS
filename | rows
orders/2026-05-01.csv | 10
orders/2026-05-02.csv | 12
orders/2026-05-03.csv | 12
orders/2026-05-04.csv | 9
orders/2026-05-05.csv | 13
orders/2026-05-06.csv | 10
orders/2026-05-07.csv | 12
(7 rows)
raw_orders | stg_orders | rejected_orders | fact_orders
78 | 73 | 2 | 73
(1 row)

Your task: make raw_orders start empty, backfill 2026-05-01 to 2026-05-07 with a loop, run 2026-05-07 a second time, and make load_day delete the rows whose filename is this day's path before it inserts the file.

Assignment
Follow the Coding Tutorial to turn the build into a daily load that you can run again without changing a single count.

Hint
range(1, 8) gives 1 to 7, and "2026-05-0" + str(3) is "2026-05-03". The DELETE needs a WHERE on filename, or it empties every day; run it with con.execute and [path], the way the INSERT below it runs.