Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 36 ยท Projects and Practice

Project: A Batch ETL Pipeline End to End

Build a complete daily batch pipeline in Python: extract messy CSV, validate and quarantine, transform, load a star schema idempotently, and publish a mart with run metrics.

In this project you build a small but complete daily pipeline that uses most of what the course has covered: raw landing, validation with quarantine, cleaning, a star schema, idempotent loading by logical date, a mart and run metrics. Everything runs in your browser with pandas and SQLite, so you can rerun any step.

The scenario: an online shop drops a CSV of order lines every day. Analysts want daily revenue by product category and country, and they want to trust it.

flowchart LR
  A["Landing: orders_2026-03-01.csv"] --> B["Validate + quarantine"]
  B --> C["Clean + conform"]
  C --> D["Load dim and fact<br/>idempotent by day"]
  D --> E["Mart: daily revenue"]
  B --> Q["quarantine table"]
  D --> M["run metrics"]

Step 1: the raw input

Real files are messy. Ours has whitespace, mixed case, a bad amount, a duplicate line, a missing product and an unknown country code.

Python Step 1: the landing file

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice we read everything as strings. Typing happens deliberately in the next step, so bad values become findings instead of crashes.

Step 2: validate and quarantine

Python Step 2: split good rows from quarantined rows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Every rejected row has a reason. The pipeline does not stop, but the quarantine table (and its size) is monitored.

Step 3: clean and conform

Convert types, standardize text, derive the business date and the line amount. The logical date for this run is 2026-03-01.

Python Step 3: clean, type and derive columns

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Step 4: load a star schema idempotently

We load dimensions with upserts, resolve surrogate keys for the fact, and replace the fact partition for the day inside one transaction.

Python Step 4: dimensions, fact and a safe rerun

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Running load twice leaves 3 rows and the same total: the pipeline is idempotent. Dimension tables use upserts on the business key; the fact replaces its own day.

Step 5: build the mart

Python Step 5: daily revenue by category and country

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Step 6: metrics and reconciliation

A finished pipeline reports how it did, and proves the numbers match the input.

Python Step 6: run metrics and a reconciliation check

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The reject rate here is high (57%), which is exactly the kind of number that should wake someone up in a real pipeline.

Extensions to try

  1. Add dim_date and join the fact to it by date_key.
  2. Make dim_customer a Type 2 slowly changing dimension (see the earlier lesson).
  3. Add an unknown-member row (key -1) instead of quarantining unknown products.
  4. Backfill three days by looping load over several logical dates.
  5. Write unit tests for the cleaning function and a data test for key uniqueness.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why read the CSV as strings first?

    Explicit conversion with error handling lets you quarantine rows with reasons.
  2. 2. How does the fact load stay idempotent on rerun?

    Partition replacement inside a transaction yields the same final state however many times it runs.
  3. 3. What does the reconciliation assertion rows_read == rows_good + rows_quarantined prove?

    Every input row must be either loaded or quarantined.
  4. 4. Why monitor the reject rate?

    Success status alone does not show that the data quality has dropped.

Summary

  • A good batch pipeline lands raw data, validates with quarantine, cleans, loads idempotently and publishes a mart.
  • Resolve surrogate keys on load, replace partitions by logical date and upsert dimensions.
  • Emit metrics and reconcile counts and totals every run.

Next: Project: A Mini Lakehouse