ETL: Transform Before the Data Lands
Extract, transform, load. The destination only ever sees rows that already conform — a real guarantee for its readers, bought with the original that nobody kept.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
If the destination is perfectly capable of storing the raw rows, why would anyone transform the data before it gets there?
The analyst querying the destination, who receives a stable schema, conformed types and no source-shaped weirdness — and who never sees the fields that were dropped on the way, because they cannot be asked for. Also the owner of the destination's security boundary, who cares specifically that the raw payload containing a customer email never crossed it at all.
One record in flight. ETL operates on a batch of extracted records held outside the destination — in a worker's memory, on its local disk, in a staging area the pipeline owns — and the unit that matters is the record as it exists mid-transform: after the source has stopped tracking it, before the destination has agreed to keep it. Nothing else in the platform can see a record in that state, which is exactly why bugs there are hard to reconstruct.
One script. It connects to the source, pulls the rows it needs, cleans them in the same loop — parse the timestamps, map the status codes, drop the columns nobody asked for — and inserts the result into the destination table. It is one file, a new engineer can read it in a sitting, and for a single source feeding a single report it is genuinely the right answer. Most working data pipelines started here and many should never have left.
A mapping is wrong: status = 3 meant refunded, not shipped. Fixing it requires the original rows, and the script never kept them — the source has since updated those orders, and the only copy of what status was at extract time was the one the transform consumed and threw away. Correcting six months of history becomes archaeology rather than a re-run (Reprocessing vs Retrying).
- A mapping is wrong:
status = 3meant refunded, not shipped. Fixing it requires the original rows, and the script never kept them — the source has since updated those orders, and the only copy of whatstatuswas at extract time was the one the transform consumed and threw away. Correcting six months of history becomes archaeology rather than a re-run (Reprocessing vs Retrying). - The transform holds the whole batch in one worker's memory. The source grows, the batch stops fitting, and the failure is an out-of-memory kill partway through — after some rows were already inserted, with nothing anywhere recording which ones (Atomic Publish).
- A column appears at the source. The extract selects an explicit column list, so nothing breaks: it silently continues without the field, and the gap is discovered months later by someone whose question needed it (Schema Evolution).
- Extract, transform and load share a process, so a retry after a network blip re-extracts, re-transforms and re-inserts. Without an idempotent write the destination now holds the first half of the batch twice, and every count downstream is quietly high (Duplicate Rows).
- The definition of "active customer" changes. Every historical row in the destination was computed under the old definition and the input needed to recompute it under the new one was never stored, so the trend line has a discontinuity that no one can explain or remove (Semantic Changes).
- The tool that owns the transform is a GUI. The logic is real, it is business-critical, and it cannot be diffed, reviewed, tested or reverted, so nobody can say what changed when the number moved (Data Platform Anti-Patterns).
What is actually happening
- The three letters name three genuinely separate concerns, and ETL runs them in the order that puts the destination last. Extract reads from a system you usually do not control. Transform parses, coerces, filters, maps, joins and aggregates. Load writes the result into the destination.
- The defining property is not the order of the words — it is *where the transform's compute lives*. In ETL it lives outside the destination, in something you provision and operate separately: a worker, a container, a Spark cluster, an integration product's runtime (Where the Transformation Actually Runs).
- That placement decides what the destination is ever able to answer. It receives only the transformed result, so rows and fields that did not survive the transform are not merely absent from a table — they are absent from the system, and the transform source code is the only surviving record that they ever existed.
- ETL was the historical default because destination compute was scarce and expensive. An analytical database of that era was a fixed appliance whose capacity you had bought outright and could not grow this quarter; spending CPU cycles anywhere else was strictly cheaper. That constraint, not a principle, is what put the T in the middle (Separating Storage from Compute is the change that removed it).
- So ETL trades a reprocessing property for a boundary property. Everything past the load is smaller, conformed and privacy-reduced, and it is irrecoverable without going back to a source that has moved on. Both halves of that sentence are the design; neither is an accident.
Three steps, and what each one is entitled to promise
Written as one script, ETL looks like one operation. Written as a pipeline it becomes three operations with three different failure behaviours, and the reason to separate them is not tidiness — it is that each one is expensive to repeat in a different way. Re-extracting hits a system you do not own. Re-transforming burns compute you rent. Re-loading risks the copy consumers are reading.
The stage table below adds one column that a plain flow diagram cannot: what each step actually promises the next. Read that column downward and the shape of ETL becomes obvious — the strongest promises are at the ends, and the middle promises only that it did what its code said.
The optional staging step between extract and transform is the single highest-return addition to a naive ETL script. It costs one write to cheap storage and it is what makes every subsequent mistake a re-run rather than a negotiation with the source system's owner.
- 1Extract
Reads a bounded set of records from the source using a predicate — a date range, a high-water mark, a full snapshot.
guarantees Only that the source answered the query it was asked. Not that the predicate captured everything that belongs in the period.
fails by A time-based predicate missing rows whose timestamp was assigned before their transaction committed, so they fall between two runs forever (The High-Water Mark).
- 2Stage (raw landing)
Writes exactly what was extracted to immutable storage, untouched, partitioned by extract run.
guarantees That the input to every later step is reconstructable byte for byte.
fails by Being skipped because "we transform anyway" — which is precisely the decision that makes the next bug unrecoverable.
- 3Transform
Parses, coerces, filters, maps, joins and aggregates on compute the pipeline owns.
guarantees That output rows satisfy the rules in the code. Nothing about whether those rules are the right ones.
fails by Silent coercion — an unparseable value becoming null or a default, then flowing into an average as though it were a measurement.
- 4Load
Writes the transformed result into the destination table.
guarantees Durability, and atomicity only if written as a single transaction or a partition swap.
fails by A partial multi-statement write that a scheduled report happens to read, producing a number that was never true of any moment.
The staging step is the only one that is optional in principle and mandatory in practice. Every other row describes a promise that already exists; that row describes one you have to decide to buy.
Why the transform sat outside the destination
It is tempting to read ETL as a mistake that ELT corrected. It was not a mistake; it was a correct response to a hardware and licensing reality. The analytical database was a fixed-capacity appliance, its compute was the scarcest resource in the building, and anything that could be done elsewhere was done elsewhere. Load only what the business had agreed to keep, because the appliance had room for exactly that much.
What changed was not the wisdom of the field but the shape of the destination: warehouses that grow compute on demand, storage that is separately priced and effectively unbounded, and a SQL engine that is no longer the bottleneck it was. Once that is true, the argument for transforming outside becomes an argument you have to make rather than one you inherit.
The version of ETL worth writing today is not the one-script version. It is the one where the extract is landed first, so the transform is a pure function of something durable. That single change moves ETL from "we hope this is right" to "we can fix this", and it costs one write to object storage (Object Storage as Data Infrastructure).
A scheduled job connects to the source, streams rows through a transform loop, and inserts the results. Nothing is persisted between reading a row and writing its transformed form. A bug found next month can only be fixed by extracting the period again.
The same job writes the extracted rows verbatim to dated storage first, then a separate step reads that storage, transforms and loads. The transform never touches the source. Fixing a bug means changing code and re-running the second step over a chosen range.
The source is mutable and outside your control: an operational table has overwritten the state you would need, and a SaaS API may rate-limit or refuse a large historical re-read. Persisting the extract converts your recovery story from a dependency on someone else's system into a dependency on your own storage — and it is the cheapest resource in the chain.
1-- Wrong: a retry after a partial failure duplicates the period.2INSERT INTO fct_orders3SELECT * FROM transformed_batch;4 5-- Better: the run owns a bounded partition and replaces it whole.6-- Re-running for the same date produces the same table state.7BEGIN;8 DELETE FROM fct_orders WHERE order_date = DATE '2026-03-14';9 INSERT INTO fct_orders10 SELECT * FROM transformed_batch11 WHERE order_date = DATE '2026-03-14';12COMMIT;13 14-- Better still where the destination supports it: one atomic statement,15-- keyed on the business key rather than on arrival.16MERGE INTO fct_orders t17USING transformed_batch s18 ON t.order_id = s.order_id19 WHEN MATCHED THEN UPDATE SET *20 WHEN NOT MATCHED THEN INSERT *;Notice what the second form assumes: that the batch is bounded by the same predicate as the delete. A MERGE on the business key is stronger still, because it survives a batch whose boundaries were wrong — but it cannot remove a row the source deleted unless deletes are represented in the stream at all (What a CDC Event Contains).
Whether a destination offers MERGE, multi-statement transactions or atomic partition replacement varies by warehouse and changes over time. Check the current documentation for the engine you are on rather than assuming the shape above is available — the fallback of writing to a side table and swapping is the portable version.
Where transforming first is still the right answer
ETL is the correct shape whenever loading raw is impossible, forbidden, or wasteful — and each of those is a specific, checkable condition rather than a preference. If none of them applies to a given source, the ordering argument is genuinely open and should be made on reprocessing grounds instead.
Notice that the conditions are per source, not per platform. The same organisation routinely transforms one feed before load because it contains health data that must not cross a boundary, and loads another raw because it is a clean event stream nobody has legal concerns about. A platform that insists on one answer for both is applying a policy, not a design.
The strongest of these conditions is the boundary one, because it is the only case where the ordering is doing something no downstream control can replicate. Masking after load protects against the analyst; filtering before load protects against the breach.
Provisioned capacity is charged for its lifetime, not its utilisation. A cluster sized for the annual peak dominates every other line the rest of the year (Idle Capacity: Headroom or Waste?).
A transform that rebuilds all history nightly scales with the age of the dataset instead of with the change rate — the cost grows even when the business does not.
Source to transform, then transform to destination. The leg that surprises people is the one that crosses accounts, regions or clouds.
The cheapest line and the one most often cut first, which is exactly backwards: it is the line that buys recoverability (Storage Lifecycle).
Deliberately small — reducing this is the thing ETL is *for*, and it is why the shape still wins where the destination is the expensive system.
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Relative weights for a typical batch ETL pipeline, shown to establish an ordering rather than a magnitude. The teaching is the inversion: the line people cut is retained raw, and the line that dominates is compute they provisioned for a peak.
What specifically prevents this data from being loaded as it arrived?
when Fields are classified such that they must not enter the analytical environment at all — special-category personal data, another tenant's identifiers, data that may not leave a jurisdiction.
cost The filtered fields are permanently unavailable for any future question, and the filter itself becomes a compliance-critical piece of code that needs review, tests and an audit trail.
when The source emits a proprietary binary, a fixed-width mainframe extract, deeply nested XML, or something whose parsing needs a library the warehouse does not have.
cost You now operate a parsing runtime, including its dependency upgrades. Land the original bytes alongside the parsed output so a parser bug is still recoverable (CSV, JSON and Their Limits).
when The transform calls a geocoder, an ML model, or a service that holds a lookup the warehouse cannot see.
cost The transform stops being a pure function of its inputs, so re-running it later can produce a different answer. Persist the enrichment result, not just the enriched row.
when The source emits high-volume, low-value records — debug telemetry, per-frame sensor readings — where the aggregate is the product and the detail is never queried.
cost Aggregation is lossy and irreversible. Decide the aggregation grain deliberately, because it is the finest question anyone will ever be able to ask (Grain: What Does One Row Represent?).
when The data is already tabular, the destination can read it, no classification forbids it, and storage there is not the binding constraint.
cost Storage for a copy you may rarely query, and the discipline to keep transformations out of the raw layer once it exists (ELT: Load First, Transform Where the Data Lives).
How to build it
Most important first.
- Land what you extracted before you transform it, even under ETL. A raw extract file on cheap object storage costs little and converts every transform bug from a re-extract into a re-run — which is the whole difference between a bad afternoon and a lost quarter (The Raw Landing Zone, Keeping Raw History: The Recovery Position and the Liability).
- Make the load idempotent: a merge on a business key, or a full replace of a bounded partition. A retry must produce the same destination state as a clean first run, or every transient network error becomes a data incident (Upserts and Merges, Idempotent Data Pipelines).
- Split the three concerns into three steps with a durable handoff between each, so a failure in transform does not force a re-extract and a failure in load does not force a re-transform. Re-extracting is the step you least control and most want to avoid repeating (Partial Failure).
- Keep the transformation in version control with tests against fixed inputs. The transform is where the meaning of every destination column is defined; logic that lives only inside a vendor UI is a definition nobody can review and nobody can revert (Data Contracts).
- Where ETL exists to enforce a boundary — dropping PII before data leaves a jurisdiction, a tenant or a VPC — make the drop the *first* operation, assert it, and test it. A redaction at the end of a long transform is a redaction that an innocent refactor can reorder (PII in Pipelines, Data Minimization).
- Emit what you discarded, in aggregate and per rule. "This run dropped rows failing the date parse, and here is the count and three examples" is a signal; silence about discards is how a pipeline loses a tenth of its input for a year (Pipeline Metrics).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- The destination is guaranteed to contain only rows that passed the transform. That is a real and useful promise: consumers need not defend against source-shaped data, because such data structurally cannot arrive.
- Nothing guarantees the transform was right. "Only conformed rows" and "only correct rows" are different claims and the pipeline can only make the first (The Pipeline Succeeded. The Data Is Wrong.).
- Completeness is bounded by the extract predicate, not by the transform. Rows the extract never selected are missing from the destination *and* from every check that compares the destination against itself (Incremental Extraction).
- Atomicity holds only where you built it. A transform followed by a multi-statement load has exactly as many observable intermediate states as it has statements, and a dashboard refreshing between two of them reports a number that was never true.
- If the raw extract is not retained, the pipeline actively guarantees the *opposite* of reproducibility: re-running it next year against the same source will produce a different answer, because the source is mutable and has moved on (Trusting Data).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- Compare rows leaving the extract with rows entering the load and account for the difference *explicitly*, rule by rule: this many filtered by the status rule, this many failed the date parse, this many deduplicated. An unexplained gap between those two counts is the highest-value single check an ETL pipeline can have.
- It misses every row that passed through with a wrong value, which is most transform bugs. A bad status mapping preserves counts perfectly and changes every metric built on it (Data Tests).
- It also misses rows the extract never selected, because both counts are taken after the extract. Only a reconciliation against the source itself can see those, and only for a period the source considers closed (Reconciliation).
- ETL inserts the transform's full duration between extract and visibility. The destination is stale by at least one batch, and that duration scales with total data volume rather than with the number of changed rows unless the transform is explicitly incremental (Incremental Processing).
- In exchange the destination gains a property consumers value: everything visible is complete with respect to the transform. There is no window in which someone can query half-conformed data, because unconformed data has no representation there.
- The transform is a serialisation point. Running the extract more often does not make the destination fresher if the transform can only complete end to end at a coarser interval — a fact that survives every attempt to describe such a pipeline as near-real-time (Cost vs Freshness).
- A new source field is invisible under ETL unless the extract asks for it. That is a feature when the transform is a deliberate contract and a liability when it is an accident of whoever wrote the
SELECTlist — and from the outside the two are indistinguishable. - A renamed or retyped source field breaks the transform loudly, which is the good case. A field whose *meaning* changed while its name and type stayed put breaks nothing and corrupts everything downstream of the mapping.
- Changing the transform changes the meaning of the destination table from that run forward, while history keeps the old meaning. Unless you can reprocess history, an ETL platform accumulates strata of definitions, each valid for a date range nobody has written down (Data Lineage).
- Recovery under ETL is bounded by the earliest immutable copy you actually kept. With a retained raw extract, a transform bug is a bounded re-run: fix the code, reprocess the affected range, republish (Planning a Backfill).
- Without one, recovery means re-extracting from the source — which works only if the source still holds the affected period unmutated. For an append-only event table it usually does; for a mutable
orderstable with in-place updates it emphatically does not. - Make the re-run bounded and side-effect-free: an explicit date range, output written where consumers are not reading, validated before the swap. "Re-run the whole job" is how a fix for last March overwrites this morning (What Backfills Break).
- Never let a transform read
now()for anything that lands in a row. A transform that is a pure function of its inputs can be re-run in a year and produce the same output; one that reads the clock cannot be re-run at all, only re-executed (Determinism: Same Input, Same Output?).
What can go wrong
- A transform that succeeds on an empty extract. Zero rows in, zero rows out, everything green, and the destination quietly reports the quietest day in company history.
- Out-of-memory or timeout partway through a monolithic batch, leaving the destination in a state no code path anticipated.
- A retry that duplicates, because the load was an
INSERTand the mitigation for failure was "run it again". - The mitigation failing: a staging table used for atomic publish that is itself left half-written when the process dies, so the swap publishes a partial dataset with full confidence.
- A filter written to exclude test accounts that also excludes a legitimate customer segment whose records happen to match the pattern — invisible in every count-based check because the exclusion was intentional.
- The transform tool's runtime being the only place a business rule exists, and that tool being deprecated by its vendor.
- "ETL is the legacy one." ETL is the right shape wherever the destination must not receive the raw data, cannot read the source format, or charges for holding bytes you would not query. Those conditions are common now and were not invented by nostalgia (ETL vs ELT: Choosing by Constraint, Not by Fashion).
- "Transform-before-load means the data is clean." It means the data conforms to the transform. A pipeline that coerces every unparseable date to
1970-01-01produces beautifully conformed, systematically false rows (Nullability & Defaults). - "We do ETL, so we do not need a raw layer." These are independent decisions. Landing the raw extract and then transforming outside the destination is still ETL, and it is the version that is recoverable.
- "The T is the hard part." The E is the hard part. Transformation is code you own and can test; extraction is a negotiation with a system that changes without telling you (Data Ingestion).
- ETL is the only shape that can keep data out of a destination entirely. Where a legal or contractual boundary says a field must not cross into the analytical environment, filtering before load is the enforcement point, and anything after the load is mitigation rather than prevention (Data Classification).
- That enforcement is only as good as its position in the code and its test. A redaction step that runs after a debug write to a temporary location has already leaked (Data Masking, Tokenisation & Encryption).
- Discarding raw data reduces privacy exposure and destroys audit reconstruction in the same motion. If you must later prove what the source said on a given day, a pipeline that kept only its own conclusions cannot (Audit Trails).
Operating it
- Rows extracted, rows discarded per rule, rows loaded — three counters per run, on one chart. The shape of the gap between them is a better health signal than task status will ever be.
- Peak memory and duration of the transform step against input volume, because the classic ETL failure is a batch that grew past what one worker can hold and the trend was visible for months (Capacity or Efficiency: Which Problem Are You Solving?).
- A per-run manifest: which source, which predicate, which code version, which output partitions. During an incident the first question is "which code produced this row", and a pipeline that cannot answer it turns a twenty-minute investigation into a day (Debugging a Data Incident).
- At 10x volume the single-worker transform is the first thing to break, and the choice is to make it incremental or to make it distributed. Incremental is nearly always the better first move because it attacks the volume rather than renting capacity to absorb it.
- At 100x, holding a batch outside the destination stops being a neutral choice: you are now operating a distributed processing system with its own shuffle, skew and straggler behaviour, which is a full engineering commitment rather than a script (The Shuffle, Data Skew).
- Source count scales worse than volume. Ten sources with bespoke transforms are ten separate schema-drift surfaces, and the marginal cost of the eleventh is not smaller than the tenth unless the extract is standardised (Ingestion Sources).
- The dominant driver is the compute you provision for the transform, which is charged for the time it is held rather than the rows it processed. A cluster sized for the annual peak and idle the rest of the year is the classic ETL cost shape (Compute Waste).
- Moving data twice — out of the source, into the transform, out again into the destination — costs network on both legs, and cross-boundary egress is the leg people forget when the transform runs in a different account or region (Egress: Moving Data Costs Money, Not Just Storing It).
- ETL reduces destination storage and scan cost by construction, because filtered and aggregated data is smaller. That saving is real, and it is the reason ETL is still right when the destination charges for what it holds and scans (Scan Cost).
- The hidden cost is re-extraction. Every bug that cannot be fixed from a retained copy is paid for in source load, engineering time and, occasionally, in the admission that history cannot be fixed at all.
- ETL buys a clean destination and a hard boundary; it costs the ability to answer any question the transform did not anticipate. Every field dropped is a question foreclosed, and you find out which questions mattered years later.
- Running compute you control means you can do things the destination cannot — call an external service, parse a proprietary format, apply a model. It also means you operate, patch, scale and pay for that compute forever (Scoring Operational Complexity in Cloud terms).
- Transforming early reduces bytes downstream and increases the blast radius of a logic error, because the error is baked into the only copy that exists. Landing raw first recovers most of that safety at the cost of storing data twice — and that is nearly always the right purchase.
ETL, ELT, and the one in between
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
| Extract → Load → Transform | |
|---|---|
| What survives | Everything that arrived. A transformation bug is fixed by rewriting the model and re-running it over data you still have. |
| What it costs | Storage for data nobody has read, a warehouse bill that includes the transformation work, and a raw layer that becomes a swamp without a catalog and a retention policy. |
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThe ordering of extract, transform and load, and the fact that whatever the transform discards is gone from everything downstream, hold regardless of tooling. What varies is whether the transform runs on a script, a cluster or a managed connector.
- TOOL-SPECIFICWhether the transform is reviewable code depends entirely on the tool: a Python or SQL transform in version control can be diffed and tested, while the same logic expressed in a drag-and-drop integration product usually cannot be, which changes how a wrong number gets investigated.
- SOURCE-SPECIFICWhether re-extraction is a viable recovery path depends on the source: an append-only event table or a log-backed API can usually be re-read for a past period, while a mutable operational table has overwritten the state you would need and a rate-limited SaaS API may not permit the volume at all.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns what a retry actually means when the extract, the transform and the load are on different machines — and why "the load may have succeeded" is the normal state after a timeout rather than an edge case.
- — DevOps / Production Engineering owns how transformation code is versioned, tested in CI and rolled back. A transform is software with a deploy history, and treating it as configuration is how a number changes with no commit to point at.