Page through a source until it runs out, and prove you got everything

short · 35 min · Objective 1.2

Task

Imitate an API that returns data a page at a time, using SQL's LIMIT and OFFSET against the practice database. Collect every page, then show what a collector that stops after the first page would have missed, and write the provenance record for the extract.

Steps

  1. Write lab/api/collect.py, which reads the orders table 100 rows at a time with ORDER BY order_id LIMIT 100 OFFSET n, increasing the offset until a page comes back empty, and writes every row to lab/api/orders-all.csv.
  2. Record in lab/api/pages.txt how many pages returned rows, as pages: N.
  3. Write the rows of the first page alone to lab/api/orders-page1.csv, as a collector that forgot pagination would.
  4. Write lab/api/provenance.txt with the lines source:, query:, collected: (the date and time) and rows:.

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 '^pages: ' lab/api/pages.txt
python3 -c "import csv; print(len(list(csv.reader(open('lab/api/orders-all.csv')))), 'lines in the full extract')"
python3 -c "import csv; print(len(list(csv.reader(open('lab/api/orders-page1.csv')))), 'lines in page one')"
grep -cE '^(source|query|collected|rows):' lab/api/provenance.txt

Twenty pages of 100 give 2000 orders; the first page alone gives 100, one twentieth of the data, with nothing about it that looks incomplete. Add one to each line count if your files include a header row. The provenance file carries all four lines.

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