CompTIA Data+ DA0-002 · Free study guide

ETL vs ELT — choose from the architecture

ETL means extract, transform, load; ELT means extract, load, transform. The useful skill is identifying the sequence and explaining why it fits the destination, controls, performance requirements, and recovery design.

The defining sequence

Both patterns move data from a source into a destination. The question is where the transformation work happens.

ETL transforms data before it lands in the analytical target. The work may run in a source-side process, integration service, transient stage, or separate engine. The target receives data that already matches the intended shape.

ELT loads data first and transforms it in the destination. Source-shaped data enters an authorized landing area, and transformations run there, often with SQL or a destination-native compute engine.

That relocation of the work drives the main architectural tradeoffs.

Why ELT became common in cloud analytics

ELT is common in cloud warehouses for two reasons.

Destination compute can scale. A cloud warehouse may provide parallel, elastic compute and keep transformation close to stored data. That can simplify operations and reduce movement, but it is not automatically faster or cheaper; workload, service limits, query design, and pricing still determine the result.

The destination can retain a raw layer. Because ELT lands untransformed data before transforming it there, the target can preserve the original. When business logic changes or a transformation bug is found, the team can reprocess from that layer without returning to the source. An ETL architecture can also retain raw data in a separate landing zone, so raw retention alone does not define the pattern; the defining question is whether transformation occurs before or after loading into the analytical destination.

When a scenario says transformation runs inside the destination and can be rerun against data already loaded there, it is describing ELT.

When ETL is still correct

ETL remains useful in several situations:

Data that the target is not authorized to receive in source form. If law, contract, classification, or policy forbids particular fields in the destination, minimize, aggregate, mask, or tokenize them before that load. Regulated data does not automatically require ETL: a properly scoped and controlled destination may be permitted to receive it, so the governing requirement and architecture decide.

A destination that cannot do the work. If the target is a modest relational database, a legacy appliance, or anything without the capacity to transform at scale, pushing the work into it is not an option.

There is a third, softer case: when the transformation requires tooling the destination lacks — complex parsing, machine-learning enrichment, or calls to an external service that SQL cannot express.

Common misconceptions

• "ETL proves no raw copy exists." An ETL design can retain source-shaped data in a governed landing zone, backup, or source system. Retention should follow purpose, classification, access, and life-cycle rules.
• "ELT removes the need for data quality checks." No pattern removes that need. ELT changes where validation runs, not whether it runs.
• "Cloud platforms only support ELT." They support both.
• "ETL is faster because less data moves." Sometimes reducing transfer volume helps, but performance depends on source, network, transformation engine, target, and workload; the acronym alone does not determine speed.

The staging layer, and why it appears in both

A staging layer is an intermediate area between extraction and curated tables. It can be durable or transient and can appear in either pattern. It lets transformation, validation, and reprocessing happen repeatedly without re-hitting the source system and without leaving the curated layer in a half-loaded state when it is designed and retained for recovery. A transient stage may not support replay after failure.

A source system is usually a production application. Every avoided re-extraction is load you did not put on something customers are using.

Idempotency, the concept that shows up next to this one

Closely related: a load is idempotent when running it twice produces the same end state as running it once. The classic failure is a nightly job that dies halfway, gets re-run from the start, and inserts every row a second time.

Common controls include merge/upsert logic, a deduplication key, checkpoints, or atomically replacing a validated partition. More parallelism does not by itself prevent duplicates, and removing a uniqueness control hides rather than repairs the defect.

What to drill

Read every pipeline scenario for where transformation happens, what the target is authorized and able to store or process, whether source-shaped data is retained, and how a partial run is recovered. Know that staging can serve both patterns and that idempotency is a repeatable end-state property, not a backup.