DT3: Building reliable pipelines
The shape of a pipeline
🎯You'll be able to
Describe the stages of a pipeline, explain the difference between ETL and ELT, and decide where each kind of work belongs.
🏠The everyday picture
A milk cooperative collects milk from villages (extract), tests and chills it at the centre (transform), and sends it to the dairy (load). If the test fails, the can is set aside, not poured in with the rest.
📘What it really is
- Extract: read from the source: files, a database, an API.
- Transform: clean, check, join and reshape.
- Load: write into the place people query, usually a warehouse.
-
ETL transforms before loading. ELT loads the data first, nearly raw, then transforms inside the warehouse with SQL. Cloud warehouses made ELT common, because they can transform large tables quickly and keep the raw copy for re-running.
-
SabziSathi uses both on purpose:
- the Python loader (
pipeline/run.py) does the row-by-row cleaning that is easiest in Python: parsing three date formats and three money formats, and quarantining bad rows; -
dbt (DT7) does the modelling in SQL inside the warehouse: facts, dimensions and marts.
-
Layers keep each step simple: raw files →
cleantables →stagingviews →martstables. Each layer reads only from the one before it, so a problem can be traced back step by step. -
Good pipelines are boring: same input, same output; every run logged; failures visible.
📝A worked example
Input: run the solution's python -m pipeline.run --all, then cd warehouse and dbt build --profiles-dir . (with SABZI_KEY_SALT
set).
Expected output: the loader prints one line per day (for example 2026-10-05: 302 rows loaded, 3 quarantined) and finishes in about
2 seconds; dbt reports PASS=37 WARN=0 ERROR=0. Measured on 1 November 2026 on a 4-core Linux computer.
🌍Where it's used
- Retail: nightly ETL from shop systems to a sales warehouse.
- Fintech: ELT of payment events into BigQuery or Snowflake, modelled with dbt.
- Government: monthly loads of scheme data into a reporting database.
📝Case scenario
A school chain's report mixed cleaning and calculations in one 900-line script. When the attendance numbers looked wrong, nobody could tell whether the cleaning or the maths was at fault. Splitting it into a loader and separate SQL models made each step checkable, and the fault (a join counting siblings twice) was found in an afternoon.
🖱️Try it
Draw SabziSathi's pipeline as boxes and arrows from raw/ to the dashboard, naming each layer and the tool that writes it. Success: every
arrow goes one way, and you can say which step quarantines rows.
⚠️Common mistakes
- One giant script. Sign: nobody can say which step is wrong. Fix: separate stages and layers.
- Throwing away the raw copy. Sign: a rule change means asking the source to resend everything. Fix: keep raw data, transform from it.
- Layers that skip layers. Sign: a dashboard reads raw files. Fix: each layer reads only the one before.
🛠️For your project
Project 1 builds the loader stage; Project 2 builds the dbt layers; Project 3's dashboard reads only the marts.
✅Quick check
What's the difference between ETL and ELT?
Show the answer
ETL transforms before loading; ELT loads first and transforms inside the warehouse.
Why keep layers?
Show the answer
Each step stays simple and problems can be traced back one layer at a time.