Model Anatomy · Episode 12 · 2:21
ETL vs ELT explained: what each data pipeline keeps
ETL cleans data before it lands; ELT loads it raw and transforms it in the warehouse. Run both in DuckDB and pandas and both reach $1,117.15, but only ELT keeps the raw table with the personal data.
What you’ll needPython 3.10+, pandas, DuckDB
What you’ll need
- python.org/downloads →
Python 3.10 or newerRuns the code. Check yours with python3 --version. - code.visualstudio.com →
VS Code or CursorEither works; so does any editor you like. - git-scm.com/downloads →
gitGets the code from GitHub. 
pandas 2.2+Tablespip install "pandas>=2.2"
DuckDB 1.0+SQL on files and tables, no database serverpip install "duckdb>=1.0"
Set it up
- 1Get the code
git clone https://github.com/DayanEbrar0X/data-anatomy.ai.git cd data-anatomy.ai
- 2Make a virtual environmentIt keeps this project's packages separate from the rest of your computer.
python3 -m venv .venv source .venv/bin/activate
- 3Install the packagesrequirements.txt installs every lesson's packages. This one needs only: pandas, DuckDB.
pip install -r requirements.txt # or just this lesson: pip install "pandas>=2.2" "duckdb>=1.0"
- 4Run it
cd data-engineering/12-etl-vs-elt cd src python3 pipeline.py
Project structure12-etl-vs-elt/
Project structure
data-engineering/12-etl-vs-elt/Open on GitHub →
- scripts/
- make_orders.pywrites src/orders.csv (seeded, 8 rows)
- src/
- orders.csvthe raw export: order_id, amount, email
- pipeline.pymainthe code from the video
- report.pyprints what each warehouse holds
- README.mdthe idea, how to run it, a line-by-line walkthrough, things to try
data-engineering/12-etl-vs-elt/src/pipeline.pyOpen on GitHub →
import duckdb
import pandas as pd
from report import report
raw = pd.read_csv("orders.csv", dtype=str)
# ETL: transform in Python, then load
clean = raw.drop_duplicates()
del clean["email"] # PII never lands
usd = clean["amount"].str.strip("$")
clean["amount"] = usd.astype(float)
etl = duckdb.connect()
etl.sql("CREATE TABLE orders AS FROM clean")
# ELT: load raw as-is, then transform in SQL
elt = duckdb.connect()
elt.sql("CREATE TABLE raw_orders AS FROM raw")
elt.sql("""CREATE TABLE orders AS
SELECT DISTINCT order_id,
CAST(LTRIM(amount, '$') AS DOUBLE) AS amount
FROM raw_orders""")
report(etl, elt)Transcript
Same three letters, different order, and it changes your whole data platform. Extract, transform, load. Or extract, load, transform. Here's a raw export of seven orders.
It's messy. Amounts are text, with a dollar sign. Order 104 was exported twice. And every row carries an email.
That's personal data, or PII. ETL cleans it first, outside the warehouse, then loads only the result. ELT loads the raw rows as they are, then cleans them with SQL, inside the warehouse. In code, both paths start from the same CSV, read as plain text.
The ETL path runs in pandas. First, drop the duplicate, then delete the email column, so the PII never lands. Strip the dollar sign, and turn the text into real numbers. Only then does it load.
DuckDB, in memory, is our warehouse. The ELT path loads first. Raw rows, duplicate and email included. Then the transform runs as SQL, inside the warehouse itself.
DISTINCT drops the duplicate. CAST turns the text into a number. And email? It's simply never selected.
Last, a small helper reports what each warehouse holds. Let's run it. ETL: seven rows, 1,117 dollars and 15 cents. One table.
ELT: the same seven rows, the same total. But two tables. Same answer. But ELT also kept the raw data, and the PII.
So why did ELT take over? Cloud storage got cheap, and warehouses got powerful. Keep the raw data, and when business logic changes, you re-run the SQL. No new extract.
That's how dbt works: ELT, with the T as SQL in the warehouse. But ETL still wins when sensitive data must be masked before it lands anywhere, or when the target is small or expensive. Many teams mix both. Same three letters.
The real question is where the T lives.
Read the lesson on GitHub →- #etl
- #dataengineering
- #sql
- #duckdb
