Load Only What's New in Python

A new file lands in orders/ every day. Inserting the whole folder again each day would put the old days in the table twice, and the work would grow with every file. An incremental load brings in only the new rows. To know which rows are new, it keeps a watermark: the newest ordered_at the table already holds, which SELECT MAX(ordered_at) FROM stg_orders gives you. A row placed after the watermark is new.

Between SQL and Python: con.execute(…) gives back an answer. fetchone() takes its first row as a tuple, and [0] the first value of that row. The other way round, a ? in a SQL string is a placeholder: the list you pass with the query fills it, one value per ?, so a Python value can go into the SQL.

answer = con.execute("SELECT COUNT(*) FROM raw_orders")
print(answer.fetchone()[0])   # 78

sql = "SELECT COUNT(*) FROM raw_orders WHERE filename = ?"
print(con.execute(sql, ["orders/2026-05-02.csv"]).fetchone()[0])   # 12

The starting code builds raw_orders from the week's files (every column text) and stg_orders from the days up to 05-03: 33 orders, the newest placed at 2026-05-03 18:30. query already inserts 05-04's file into stg_orders; you add the rule that keeps only the rows after the watermark. Since raw_orders holds text, the rule compares CAST(ordered_at AS TIMESTAMP) with the watermark. With the check's lines, your program prints:

Watermark: 2026-05-03 18:30:00
row_count | newest
41 | 2026-05-04 20:42:00
(1 row)
Rows of 05-04's file that are not in stg_orders:
order_id | ordered_at
5036 | 2026-05-02 21:47:00
(1 row)

Eight orders go in. The ninth, order 5036, was placed on 05-02 but only sent in 05-04's file: it is older than the watermark, so this load skips it, and so will every later one. Late rows are the watermark's weak spot; the next chapters come back to them.

Your task: find the watermark with MAX(ordered_at), take it into watermark with fetchone()[0] and print it, then add the rule to query and run it with [watermark] filling the ?.

Assignment
Follow the Coding Tutorial to find the watermark of stg_orders and load only the newer rows of 05-04's file.

Hint
The watermark query answers one row with one value: fetchone() takes the row and [0] the value. The ? in query is filled by the list you pass to con.execute.