ETL and ELT

Definition

ETL transforms data before loading it into the target: the pipeline owns the transformation, and the warehouse receives modelled data. ELT loads raw data first and transforms inside the target using its compute.

Why the industry moved toward ELT

Three shifts, in order of importance:

1. Warehouse compute became cheap and elastic. The original reason to transform before loading was that target compute was scarce and expensive. That constraint largely disappeared.
2. Raw data retained is optionality. If the transformation is wrong — and it will be — you can reprocess from raw. In ETL, the raw data was discarded and the error is permanent.
3. Transformations became analysts' work. Expressed in SQL inside the warehouse, version controlled and tested, they are owned by the people who understand the semantics rather than by a pipeline team who do not.

ETL still wins where data must be filtered or masked before it lands — personal data that may not enter the warehouse at all, or volumes where loading everything is genuinely uneconomic.

Orchestration is the real architecture

Whichever letter order you choose, the hard part is dependency management: hundreds of interdependent transformations, each of which may fail, some of which must not run before their inputs are complete.

Airbnb's development of a general workflow orchestrator came from exactly this pain — a growing web of dependent data jobs that cron could not express, could not retry sensibly, and could not make visible. The architectural insight was to represent the pipeline as an explicit dependency graph that is code, so it can be reviewed, tested and reasoned about, rather than as a schedule where dependencies exist only implicitly in the chosen times.

The properties that matter in any orchestrator:

• Idempotent, parameterised tasks. Re-running a task for a given date produces the same result, so backfills and retries are safe.
• Explicit dependencies, not implicit timing. "Run at 3am and hope the upstream finished" is the failure mode being replaced.
• Backfill as a first-class operation, because a logic fix always needs history reprocessed.
• Data quality checks as tasks in the graph, failing the pipeline rather than propagating bad data to a dashboard someone will act on.

Failure scenarios

• Silent partial failure. A task succeeds having processed 60% of its input, and nothing counts the difference.
• Non-idempotent tasks, so a retry double-counts.
• Dependencies expressed as timing, so a slow upstream produces incorrect downstream results rather than a delay.
• No freshness SLA, so a stalled pipeline is discovered by a business user rather than by monitoring.
• Transformations with no tests, so a subtle logic change alters last year's numbers and nobody notices for a quarter.

Trade-offs

ELT: flexible, reprocessable, cheaper to change, and it puts potentially sensitive raw data in the warehouse and moves cost into query time. ETL: controlled, minimal target storage, and expensive to change with no ability to recover from a transformation bug.

Interview question

"Your daily pipeline has a logic bug that has been producing wrong numbers for three weeks. What does your architecture need to have in it for the fix to be a one-day job?"