ETL pipelines explained: extract, transform, load, and what breaks

An ETL pipeline extracts data from a source, transforms it into the shape you want, and loads it into a destination. The three steps are that simple, which explains why the acronym has lasted and why it says little about why pipelines are difficult. The difficulty is in details the acronym omits: what happens when the source changes shape, how to avoid reprocessing everything each night, what a deleted record looks like downstream, and how anybody knows the numbers are right.

Key takeaways

• ETL (extract, transform, load) moves data from a source to a destination, and ELT (extract, load, transform) does the transform inside the warehouse after loading raw data.
• Keeping the raw data lets you fix a wrong transform and reprocess without returning to the source.
• Incremental extracts cannot see deleted rows, so deletes need soft-delete flags, a change feed or a periodic full comparison.
• Idempotent loads, merged on the source's key, make reruns safe.
• Report data quality checks on every run, including when they pass.

What we'll cover

• ETL and ELT, and why the order moved
• Extraction, and the full-versus-incremental choice
• Deletes, which incremental loads cannot see
• Where the transform belongs
• Loading without corrupting what is there
• Schema changes at the source
• Knowing whether the numbers are right
• Backfills, which are their own project
• Building pipelines with CodeWords
• What to get right first
• Frequently asked questions

ETL and ELT, and why the order moved

ELT (extract, load, transform) loads raw data first and transforms it inside the warehouse. ETL (extract, transform, load) transforms before loading. Teams moved to ELT because warehouse compute became cheap enough to make it practical.

	ETL	ELT
Order	Transform, then load	Load raw, then transform in the warehouse
Suited to	Destinations where storage and compute were expensive	Warehouses with cheap compute
Raw data kept	Often not	Yes

The practical advantage of ELT is that the raw data is there. When a transform turns out to be wrong, you can fix it and reprocess without going back to the source, which may no longer have the history.

Land raw, transform in the warehouse, keep both. The raw layer is your insurance, and storing it usually costs little next to a transform bug you cannot correct later.

The discipline matters more than the order. Whichever order you use, the question is whether you can reproduce today's numbers tomorrow, and only keeping the raw input guarantees that.

Extraction, and the full-versus-incremental choice

A full extract reads everything on every run, and an incremental extract reads only what changed. Full extracts are simple and self-correcting, but they become impractical past a certain size. Incremental extracts scale, and they introduce every problem below.

A cursor has to be durable. Store the high-water mark after the run succeeds, not before, or a failure mid-run skips records permanently.

Overlap the window slightly. Reading from a little before the last cursor catches records whose timestamps were assigned before their transaction committed, a confusing source of missing rows.

Timestamps are not always reliable. A source that updates rows without touching the modified column stops appearing in your extracts without any warning.

Keep a periodic full reconciliation even after going incremental, because it is the only check that catches what the cursor missed.

Deletes, which incremental loads cannot see

An incremental load cannot see a deleted row, because a deleted row simply stops appearing. No event, no flag, and no modified-since query shows it ever existed. This failure produces the most confusing downstream symptoms.

The destination keeps the row forever, and every count, sum and join downstream includes a record that no longer exists at the source.

Three fixes exist:

• Soft deletes, where the source supports them: a deleted flag your extract reads like any other column.
• A change feed, where one is available, since deletions appear as events.
• A periodic full comparison otherwise: pull the full set of identifiers, compare against what you hold, and mark the difference.

Decide what "deleted" means downstream. Removing the row loses history; marking it as deleted keeps it and requires every query to filter. Marking is usually right, and you need to apply it consistently.

Where the transform belongs

Put analytical transforms in the warehouse, and shape-only transforms in the pipeline. The warehouse holds the compute and the raw data, and the logic is reviewable as SQL alongside everything else.

In the warehouse for anything analytical.

In the pipeline for anything about shape rather than meaning: flattening nested structures, normalising encodings, parsing dates.

Not in the report. A transform living in a dashboard or a spreadsheet formula is a definition that only one artefact uses and nobody else can see, which is how two reports come to disagree.

Not in the source system. Adding views to a production database for reporting couples your pipeline to somebody else's deployment.

Version it. Transform logic is business logic, and business logic that cannot be compared between versions cannot be changed safely.

Loading without corrupting what is there

Load idempotently: a rerun after a partial failure must produce the same result as a clean run, which means merging on a key rather than appending.

Load into a staging location first, then swap or merge. A failure halfway through writing directly to the target leaves the target partially updated and in use.

Use the source's key. Generating a key during load means a rerun creates duplicates of everything it already loaded.

Decide about history. Overwriting a row loses what it used to say; keeping versions makes point-in-time questions answerable and makes every query more complicated. Choose deliberately per table.

Make partial success visible. A run that loaded three tables and failed on the fourth leaves the warehouse internally inconsistent, and the report built on it will be wrong with no announcement.

Schema changes at the source

Schema changes at the source will happen, and nobody will tell you. The team owning the source system does not know your pipeline exists, which is not their fault.

Somebody will add a column, which is harmless if your pipeline ignores unknown fields and breaks it if the load is strict.

Somebody will rename a column, which silently produces nulls where data used to be.

Somebody will change a type, which either fails loudly or coerces quietly, and quiet coercion is worse.

Detect and report rather than adapt. A pipeline that automatically accommodates a renamed column hides a change somebody needed to know about. Failing clearly, with the specific difference named, is more useful than continuing with nulls.

Knowing whether the numbers are right

Know the numbers are right by running automated checks on every load. These checks separate a pipeline people trust from one they check by hand.

• Row counts against the source, per run, as the cheapest possible check.
• Freshness: how old the newest record is, per table, which catches a pipeline that stopped running.
• Null rates on columns that should not be null, which catches a renamed source column before anybody builds a report on it.
• Referential checks: orphaned foreign keys after a load usually mean one table loaded and another did not.
• Reconciled totals against the source system's own reporting for anything financial, since matching row counts does not mean matching values.

Report these on every run, including when they pass, because a silent check cannot be distinguished from a broken one.

Backfills, which are their own project

A backfill reprocesses history, and it behaves nothing like a nightly run. Expect to do one: a transform bug, a new column or a source that was wrong for a fortnight all mean reprocessing a range.

Run it separately from the scheduled pipeline, so a long backfill does not delay tonight's load or get killed by the same timeout.

Chunk by time and make each chunk resumable, because a backfill over two years that fails near the end should not start again.

Rate limit deliberately. A backfill hitting a source system at full speed can affect somebody else's production.

Write to a separate location and swap, rather than rewriting the live table in place while reports are reading it.

Say what you reprocessed and when. Figures that changed retrospectively need an explanation, or somebody will find a number that no longer matches their screenshot and assume the pipeline is broken.

Building pipelines with CodeWords

You build a pipeline in CodeWords by describing the source, the destination and the schedule to Cody, the automation builder. CodeWords connects to 3,000+ integrations (CodeWords pricing page, 2026).

1. Open CodeWords and start a new automation.
2. Describe what should happen in plain language to Cody: which source, what shape the destination needs, and how often.
3. Describe the exceptions: a source that is unavailable, a schema that has changed, a row count far outside the usual range.
4. Connect source and destination once, and reuse the connections.

You describe the outcome; Cody builds it, connects it, and deploys it. The Free plan costs $0 and covers light use, Pro is $39 per month ($33 billed yearly) and Business is $100 per month (CodeWords pricing page, 2026). Details are on the pricing page.

What to get right first

Get idempotent loading right first, before incrementality and before transforms. A pipeline that can be rerun safely can be fixed, rerun and trusted. With a pipeline that cannot, every failure requires somebody to work out what state the destination is in.

Two habits make the difference. Merge on the source's key rather than appending, since that single choice makes reruns safe. And report the checks on every run including when they pass, because a pipeline that only speaks up when something breaks looks the same as one that has stopped running.

Frequently asked questions

ETL or ELT?

Use ELT for most analytical work: land the raw data and transform it in the warehouse. The reason is not speed. The raw data is still there when a transform turns out to be wrong, and the source may no longer hold the history.

Why does the destination have records the source deleted?

An incremental extract cannot see deletions, because a deleted row simply stops appearing. Use soft deletes, a change feed or a periodic full comparison of identifiers, and decide how "deleted" is represented downstream.

How do I avoid reprocessing everything every night?

Use an incremental extract with a durable cursor, stored after success, and a small overlap window to catch records whose timestamps preceded their commit. Keep a periodic full reconciliation, since the cursor will eventually miss something.

Should the pipeline adapt to a renamed source column?

No, the pipeline should detect the rename and fail clearly, naming the difference. A pipeline that silently accommodates a rename hides a change somebody needed to know about, and produces a column of nulls that looks like real data.

What is the minimum set of data quality checks?

Run row counts against the source, freshness per table, and null rates on columns that should not be null. These three cheap checks catch most failures. Report them on every run rather than only on failure.

Where should transform logic live?

Put transform logic in the warehouse for analytical work and in the pipeline for shape changes. Never put it in a dashboard or spreadsheet formula, because that is how two reports come to disagree and nobody can find the definition.

Why did a report look wrong after a partial failure?

Some tables loaded and others did not, leaving the warehouse internally inconsistent. Report partial success loudly, because a report built on a half-loaded warehouse is wrong with no announcement.

How should a backfill be run?

Run a backfill separately from the scheduled pipeline, chunked by time so it can resume, and rate limited so it does not affect the source system. Write to a separate location before swapping, rather than rewriting a live table under a report.

Do I need to tell anybody about a backfill?

Yes. Figures that change retrospectively look like a broken pipeline to whoever has a screenshot from last week. A short note saying what was reprocessed and why prevents the investigation.

Related reading

• Data orchestration
• Data lineage
• Automating report generation