Load incrementally with a watermark, and run it twice safely

short · 45 min · Objective 2.1

Task

Build a tiny pipeline that loads orders into a target table in two batches, using a watermark to pick up only new rows, then run the second batch again to prove the load is idempotent: a retry must not duplicate anything.

Steps

  1. Create lab/etl/target.db with a table orders whose order_id is the primary key, and a table watermark holding the highest order_id loaded.
  2. Load batch 1, the orders up to and including O01000, from data/orders.csv, and set the watermark.
  3. Load batch 2: every order whose order_id is greater than the watermark, using INSERT OR REPLACE so an existing row is updated rather than duplicated. Update the watermark.
  4. Run batch 2 again from the OLD watermark, O01000, as if the job had crashed after loading but before saving the new watermark -- the retry that really happens -- and save the watermark afterwards.
  5. Record in lab/etl/log.txt the lines after batch 1: N, after batch 2: N and after rerun: N, counting the rows in the target each time.

Verify

These checks run in a POSIX shell with Python 3: Terminal on macOS or Linux, and on Windows Git Bash (it comes with Git for Windows) or WSL. On Windows, python3 is often a Microsoft Store placeholder that prints “Python was not found”; run alias python3=py in Git Bash first.

grep -E '^after (batch 1|batch 2|rerun): ' lab/etl/log.txt
python3 -c "import sqlite3; c=sqlite3.connect('lab/etl/target.db'); print(c.execute('SELECT value FROM watermark').fetchone()[0], 'is the watermark')"

1000 rows after batch 1, 2000 after batch 2, still 2000 after the rerun, and a watermark of O02000. The rerun reloaded 1000 rows that were already there; because the key made each load an update, nothing doubled. A pipeline that appended blindly would have shown 3000, and every report built on it would have counted half the orders twice.

This is an independent study companion for CompTIA Data+ DA0-002 and is not produced by or endorsed by CompTIA.