Start learning free

LESSON 106 OF 107 · DATA ENGINEERING: CAPSTONE

Capstone: A Daily Load You Can Re-run in Python

Practice

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. Your program prints only the four check lines, by calling run_checks() after the second 05-07 run. Do not print the counts yourself. The check prints both count tables and the filename | rows table. It reads your query variable for that last table, so keep the given query at the end of your program.


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.


STEP 1 OF 7
    Ctrl + Enter to check

    De Cap Incremental in Python

    Keep going with Python1,600+ step-by-step lessons, from your first line of code to coding interviews. Free to start.
    Start learning free