Module 1: Ingest anything · Lesson 1.1 · about 30 min
The shape of a data platform
Every data platform has the same six stages, from ingest to dashboard. Learn what each stage does, ETL versus ELT, and why raw data is never edited.
Module 1 · about 30 minutes · the first lesson. You’ll be able to draw the six stages of a data pipeline, name a tool for each, explain ETL and ELT, and say why the raw layer is never edited. You’ll also generate the course data you will use for the next seven modules.
1. Why this matters
When someone asks “walk me through a pipeline you built”, they are not asking for a list of tools. They want to hear that you know where data comes from, what can go wrong at each step, and how you would notice. This lesson draws that map. Everything else in the course hangs on it.
Think of a restaurant kitchen. Food arrives from many suppliers at different times. Some boxes are damaged. Some suppliers are late. The chef still has to serve lunch at noon. A data team is the same: files and API answers arrive from many places every morning, some are broken or late, and the reports are due at 08:00.
2. The six stages
| Stage | The question it answers | Tool in this course | Others you will meet |
|---|---|---|---|
| Ingest | What arrived, from where, and when? | Python (email, folders, SFTP, APIs) | dlt, Airbyte, rclone |
| Clean | Is it typed, valid and free of duplicates? | Polars, DuckDB, pandera | pandas, Great Expectations, Soda |
| Orchestrate | What runs when, in what order, and what happens on failure? | Dagster | Airflow, Prefect, cron |
| Load | Where does the data live for good? | Azure SQL Database | Postgres, DuckDB, ClickHouse, BigQuery |
| Model | What shape makes questions easy and answers consistent? | SQL and dbt | SQLMesh, plain views |
| Deliver | Who sees what, how and how often? | Power BI | Metabase, Superset, emailed PDFs |
Two things run across all six stages:
- Observability: logs, row counts, freshness checks and lineage (which file fed which number). It is how you know the pipeline is healthy.
- Governance: who may see what, where passwords live, and how changes are reviewed.
3. ETL and ELT
ETL means Extract, Transform, Load: you clean the data in Python first, then write finished tables to the warehouse. ELT means Extract, Load, Transform: you load the raw data first, then clean and shape it inside the warehouse with SQL.
Most modern teams do ELT with a Python edge. Python does what SQL is bad at: reading emails, Excel files, odd text encodings and APIs. It loads lightly typed raw tables. Then SQL (often with dbt) does the business logic, where it is visible, testable and fast. In the kitchen, Python is the person unpacking the delivery boxes; SQL is the chef working at a clean bench.
4. Layers: raw, staging and marts
- Raw (also called bronze or landing): an exact copy of what arrived, plus a note of where it came from, when it arrived and a fingerprint of its bytes. You only ever add to it. You never edit it.
- Staging (silver): one cleaned, typed table per source thing (sales lines, bookings, ad stats). Column names are tidy, duplicates removed, bad rows set aside.
- Marts (gold): tables shaped for questions, such as
fct_sales,dim_storeanddim_date. These feed the dashboards.
Here is one fact, one store’s sales for one day, travelling through the layers:
sales_R104.csv. Inside, the date is written day first and the amount has a comma: R104,14/09/2026,"1,234.50".raw/retail_email/business_date=2026-09-14/, and its fingerprint (a hash) goes in a manifest. Nobody edits this copy, ever.Maths and principle
Principle: a pipeline is a chain of steps, and each step should give the same output for the same input, so any step can be rerun safely.
Equation:
where lands raw data at time , standardises it and models it. If what produced is kept unchanged, you can recompute and at any time without going back to the source.
Analogy: a scientist keeps the raw readings from an instrument and reruns the calibration when a better constant is found. The readings are never overwritten. Where it breaks: some sources only show the current state (today’s price list). Unless you save a copy each day, yesterday’s prices are gone.
Try it: a bug in was live for 10 days. With raw kept, how many times must you ask the source to send data again? (Zero. Rerun and over the 10 days of raw.)
Keeping everything sounds expensive. Work it out:
- Pipeline
- The chain of steps that moves data from where it is born to where people use it.
- ETL / ELT
- Transform before loading, or load raw first and transform inside the warehouse.
- Raw layer
- An untouched copy of everything that arrived, with where and when it came from.
- Grain
- What one row means: one sale line, one booking version, one store-day.
- Physical meaning
- When the finance team says "your dashboard said $41,200 for a store, but the store says $43,900", the raw layer is your evidence: the exact files you received, when, and what was in them. Teams without it end up arguing from memory. It is also your safety net, because some sources (email, API history windows) cannot be asked again after 30 to 90 days.
“Raw data is messy, so we should clean it as we save it.” Clean a copy, never the original. If you fix the data on the way in and your fix has a bug, the original is gone and the bug is permanent. Keep raw exactly as it arrived; put the cleaning in staging, where it can be rerun.
5. What a morning looks like
At 06:00 the sales emails start arriving. At 06:30 the restaurant tills (POS, point-of-sale systems) sync their exports to a shared drive. The hotel booking system (PMS, property management system) pushes its file to an SFTP server at about 04:00, but some days the upload is still running at 06:00. The ad platform’s numbers for yesterday keep changing until about noon, as late sales are credited to ads. Reports must be in managers’ inboxes by 08:00.
That timeline is the real design brief. It decides when jobs run, what they wait for, which freshness checks you need, and the “data as of” label on every report. You will build each piece in Modules 1 to 3. The dashboard end is covered in depth in the Power BI course, and when your data outgrows one machine, Data at Scale is the next step after this course.
6. Try it: generate and explore the course data
All the data in this course is made up by one script, with faults planted on purpose. Download generate_data.py, then:
mkdir ~/p2d && cd ~/p2d # put generate_data.py here
python -m venv .venv && source .venv/bin/activate # Windows: .venv\Scripts\activate
pip install pandas numpy openpyxl
python generate_data.py --start 2026-09-01 --days 30 --out ./data
It creates four landing areas for four made-up businesses: a 12-store grocery chain (sales emails), an 18-restaurant fast-food group with three brands (till exports on a shared drive), a 5-hotel group (booking files on SFTP) and an ad agency (paginated JSON, like an API). Explore them and fill in this table in notes/sources.md:
| Source | Path | Format | Grain (one row = ?) | Arrives | Faults you can spot |
|---|---|---|---|---|---|
| Grocery sales | landing/email_inbox |
.eml with CSV/XLSX | ? | ? | ? |
| Restaurant tills | landing/shared_drive/qsr/<date> |
CSV | ? | ? | ? |
| Hotel bookings | landing/sftp/hotel |
pipe-delimited text | ? | ? | ? |
| Ads | landing/api/ads |
paginated JSON | ? | ? | ? |
Then open one .eml file in a text editor and find the headers, the MIME boundary (the line that separates parts) and the base64 attachment. Finally, draw the six stages for these businesses on paper and photograph it into notes/. Commit both: git init, then git commit -m "docs: map sources and pipeline stages".
What to check: every row of your table has a grain. If you wrote “one row per booking” for the hotel file, look again: search for a booking_id that appears twice.
7. Practice
- Put these in pipeline order: model, ingest, deliver, clean, load.
- A store’s sales file had a typo that you fixed in staging last week. The store now sends a corrected file. Which layers change?
- Name one job that belongs in Python and one that belongs in SQL.
Answers
- Ingest, clean, load, model, deliver (with orchestration running the whole chain).
- Raw gains a new file (the old one stays). Staging and the marts are rebuilt from raw and now use the corrected file.
- Python: reading an email attachment or an Excel file. SQL: joining sales to stores, or totals per day.
New words: pipeline, ingest, orchestrate, ETL, ELT, raw / staging / mart, grain, observability, governance.
- Every platform has six stages: ingest, clean, orchestrate, load, model, deliver, with observability and governance across all of them.
- Most teams do ELT with a Python edge: Python for awkward inputs, SQL for business logic.
- Raw is an untouched copy of everything that arrived. Never edit it; rebuild staging and marts from it.
- The morning timeline (who sends what, when) is the real design brief for schedules and freshness checks.
Check yourself
Sources
- Reis, J. and Housley, M. (2022), Fundamentals of Data Engineering, O'Reilly Media, Chapters 1 to 3 (the data engineering lifecycle)
- Kleppmann, M. (2017), Designing Data-Intensive Applications, O'Reilly Media, Chapter 10 (batch processing and immutable inputs)
- Dagster documentation: Software-defined assets https://docs.dagster.io/
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All company data in the examples is synthetic. Tool versions and prices change: check the official docs before you rely on them.