The ETL mental model: one diagram that survives every tool change

Most ETL explainers are glossaries. Here's extract, here's transform, here's load, three tidy steps, go build something. And then you build something, and a month later a number on a dashboard is wrong, and you spend an afternoon chasing it back through the pipeline until you land on some tiny decision nobody thought about — full load versus incremental, or which SCD type you picked back when you were in a hurry.

That's the part the glossaries skip. ETL isn't three steps. It's a tree of small decisions, and the boring ones are where everything breaks.

So this isn't another "what is ETL." I want to walk the map the way you actually live it: at each node, what are you choosing between, and what does the lazy choice cost you later. The tools change every couple of years — Spark, dbt, Databricks, whatever's next — but this map doesn't move.

Let's go branch by branch.

Extract: the stage that decides how much can go wrong

Extraction looks like the easy part. Pull some data out, move on. It stays easy right up until it doesn't, and the map gives you three questions in roughly the order they'll hurt you.

Pull or push?

Either you reach into the source on a schedule, or the source pushes changes to you as they happen.

Pull is easy to reason about. You own the timing, you can see exactly what ran when. The cost is that you're polling — so you're either checking too often and wasting reads, or not often enough and falling behind. Push gets you close to real time, but now the source's delivery guarantees are your problem, and you're running a listener that has to stay up.

Almost everyone starts with pull because it's simple, and that's fine. You move a source to push when latency starts actually costing something, not before.

Full or incremental?

This is the one that quietly eats your budget.

Full extraction re-reads everything, every run. It's correct without you having to think, and it's expensive without you having to think. Perfectly fine for a small reference table. A genuinely bad idea for a billion-row events table that you're reloading from scratch every night for no reason.

Incremental reads only what changed. Cheaper, faster, and a lot harder to get right — because now you own the watermark, the late-arriving rows, and what happens when a run dies halfway through. The classic way this goes wrong: you key the increment off a last_updated column, the source doesn't update that column reliably, and you drop rows for months without noticing. Nobody notices, actually. That's the worst kind of bug.

My rule of thumb is boring on purpose: stay on full while the table is small enough that nobody's complaining about cost or runtime. The day it comes up in standup, go incremental — and accept that you've just signed up for the watermark bookkeeping.

How you actually pull the bytes

The map lists the usual suspects: querying a database, parsing files, hitting APIs, CDC, event streaming, web scraping, and the one nobody admits to — manual extraction.

The one worth singling out is Change Data Capture. CDC reads the database's transaction log instead of querying the tables, which means you get inserts, updates, and deletes. That last one matters more than people expect. A plain SELECT ... WHERE updated_at > X will never see a deleted row — it's just gone, and your warehouse count slowly drifts away from the source. If you've ever stared at two row counts that should match and don't, uncaptured deletes are usually hiding in there somewhere.

And manual extraction — look, every "just for now" manual CSV export ends up load-bearing. If a human is downloading a file on the first of every month, that's not a pipeline. It's a risk with a calendar reminder attached.

Transform: where the data goes from there to usable

This is the widest branch, and it should be. Most of the real work lives here. I think of it as three different jobs.

Cleansing: making it trustworthy

The unglamorous stuff. Dropping duplicates, filtering out junk, dealing with missing data, fixing invalid values, trimming the stray whitespace, casting types properly, catching outliers.

None of this is clever. All of it is where the bugs are. A leading space on a join key. A date that's secretly a string. Some -999 that a developer used as a "null" in 2014 and never documented. These are exactly the things that produce dashboards that are wrong in a confident, professional-looking way.

The skill here isn't writing the cleaning code — anyone can do that. It's deciding the rule out loud instead of letting every transformation quietly make its own guess. Say what "valid" means. Reject the bad stuff loudly. Don't silently paper over it, because silent fixes are how you end up debugging the same problem three times in three places.

Enrich and integrate: making it richer

Now you're adding value instead of just fixing things. Enrichment — joining in reference data, lookups, geocoding. Integration — getting the same entity to line up across systems that flatly disagree about its name and ID. And derived columns, the computed fields the downstream queries actually use.

Integration is the hard one, and it's hard for a non-technical reason. Two systems will both insist they hold the real customer record, and they'll contradict each other. Somewhere in your transform layer you have to decide who wins — and that's a business call dressed up as an engineering task, usually made by one engineer, alone, late in the day. Worth flagging that out loud when it happens.

Shape and aggregate: making it fit

Last job: normalizing and standardizing (units, formats, casing, currency), encoding the actual business logic, and rolling things up to the grain your serving layer needs.

One trap worth naming — don't aggregate too early and throw away detail you'll want next quarter. Aggregate in a layer you can rebuild from scratch. Never in one you can't.

Load: where the mistakes become permanent

Load is different from the other two because you're writing to the thing people read. Get it wrong and it's not a quiet internal error anymore — it's in front of someone. Three decisions.

Batch or stream?

Batch runs in scheduled chunks. Simple, cheap, easy to think about, and completely fine as long as nobody needs the data sooner than the next run. Stream runs continuously, which you need for genuine real-time cases — and which costs you state, ordering, exactly-once headaches, the whole bill.

Here's the honest version most of the time: "we need streaming" turns out to mean "we need this to run every 15 minutes instead of once a day." Those are very different commitments. Figure out which one you actually have before you sign up for the streaming tax.

How you write the rows

Full load (truncate-and-insert, upsert, drop-create-insert) versus incremental load (upsert, append, merge).

MERGE is the workhorse — match on a key, update what's there, insert what's new. APPEND is fast and a little dangerous: re-run a failed job and now you've got duplicates, unless the whole thing is idempotent. TRUNCATE AND INSERT is brutally simple and totally fine right up until the table's too big to rewrite, at which point it stops being fine overnight. The right call comes down to table size and how much you trust your own re-runs. Be honest about that second part.

SCD: do you remember the past or not?

This is the most important node on the whole map, and it's the one people skip most.

A Slowly Changing Dimension is how you handle an attribute that changes over time — a customer moves city, a product gets recategorized. The type you pick decides whether your history stays correct.

• Type 0 — never changes. Frozen at first load.
• Type 1 — overwrite. You keep the current value and lose everything before it. Cheap, and quietly wrong for anything that involves time.
• Type 2 — historize. A new versioned row for every change, with validity dates. This is what lets you ask "what was true back then," which is what most real analysis actually needs, even when nobody says so up front.
• Type 3 — keep one previous value in its own column. A reasonable middle ground when all you care about is current versus one prior.

The expensive version of this story is always the same. Someone picks Type 1 because it's the easy one. Months later finance wants revenue by the customer's region as it was last year — and that's gone. Overwritten. Not "hard to get," just genuinely not there anymore. SCD is a decision you make once and then pay for forever, in either storage or regret. Pick which one you'd rather owe.

What the map is actually for

ETL was never three steps. It's a tree of decisions, and the diagram is really just a checklist of the ones you don't get to skip:

• Extract — pull or push, full or incremental, CDC or a naive query that'll miss your deletes.
• Transform — what counts as valid, who wins when two systems disagree, what grain you aggregate to.
• Load — batch or stream, which write survives a re-run, which SCD type keeps the history someone's going to ask for.

You don't need to memorize tools. They'll be different soon anyway. What's worth internalizing is the set of forks — because the gap between a senior data engineer and a junior one isn't knowing what MERGE does. It's looking at a new task and knowing, before writing anything, which node of this map you're standing on and what it's going to cost three quarters from now.

The boring leaves are the ones that wake you up at 2am. Learn those first.