Pipelines: ETL vs ELT, Parquet & Idempotency
DSC3103 · Lab

Parquet Columnar I/O & Idempotency Simulator

Two of the biggest wins in data engineering, as numbers: how much less Parquet scans than CSV, and how many corrupt rows a non-idempotent load creates every time Airflow retries.

  1. 1 · Predict0/1
  2. 2 · Experiment0/2
  3. 3 · Explain0/1
  4. 4 · Code it0/2
  5. 5 · Challenge
  6. 0/4 tasks done

Predict first

Commit to a guess before you touch anything. Being wrong here is how it sticks.

A 12-column table; a query reads 2 columns. Before compression, how many times less data does Parquet read?

Experiment

Move the controls and watch the outputs. Goals tick off automatically when you reach them.

CSV vs Parquet: bytes scanned

DuckDB scans
16.00 MB (vs 240 MB of CSV)
240 MB × (2/10 columns) ÷ 3× = 16.00 MB → 15.0× less I/O
CSV (row store) reads everything240 MB
Parquet (column pruning + compression)16.0 MB

Idempotent upsert vs naive append

A load task fails and Airflow retries it. What does the table look like afterwards?

Upsert (idempotent)
11,300
Append ×3
14,500
Corrupt extra rows
3,200

The upsert inserts 1,300 new rows and updates 200 existing keys — no matter how many times it runs. f(f(x)) = f(x).

Explain it

Put what you saw into words. This is what the exam will ask you to do.

Explain idempotency and how you'd make both a file write and a database load idempotent.

Code it

Write real code against real data. Python and SQL run right here in your browser and hidden tests check your work; the AI reviews it like an examiner.

Pythoncore

An idempotent upsert

Write upsert(table, batch, key) for two pandas DataFrames:

  • rows of batch whose key already exists in table replace those rows;
  • rows with new keys are added;
  • the result has no duplicate keys, and inputs are not modified.

Then prove it's idempotent: upsert(upsert(t, b, k), b, k) must equal upsert(t, b, k). (key is a list of column names.)

Ctrl/⌘ + Enter to run

Challenge

Fixed variations, a new problem every day, and unlimited AI challenges built from your current settings.

Columnar Pruning + Compression I/O Savings

A dataset has 10 columns of equal byte width and occupies 240 MB as an uncompressed CSV. Stored in Parquet with 3× Snappy compression, how many MB does DuckDB read to scan just 2 columns?