ETL and ELT

ETL and ELT move data from source systems into analytical or operational destinations.

The letters describe placement:

ETL: extract → transform outside the target → load curated data
ELT: extract → load source-shaped data      → transform in the target

Both patterns need extraction, transformation, loading, orchestration, testing, lineage, security, and recovery. The difference is where transformation runs and which system holds the source-shaped data.

Start with the data contract

A pipeline is an agreement between a source, a transformation, and a consumer.

Define:

• source owner and extraction interface;
• record identity and business key;
• field names, types, nullability, and meaning;
• event time, update time, and timezone;
• snapshot or change semantics;
• expected volume and arrival pattern;
• destination model and consumer freshness target;
• handling for deletes, corrections, late data, and schema changes;
• privacy, retention, and access requirements.

Without a contract, a successful copy can still produce incorrect analytics.

Extract without harming the source

Extraction reads a source through files, queries, APIs, logs, queues, or change streams.

Full extraction reads the complete source scope. It is simple and useful for small datasets, initial loads, and reconciliation. It can be expensive and can place substantial load on the source.

Incremental extraction reads records that changed since a stored position. That position may be a timestamp, monotonically increasing key, file marker, log position, or source-specific token.

Change data capture, or CDC, reads inserts, updates, and deletes from a database change mechanism. Many CDC systems take an initial snapshot, record a log position, and then stream later changes.

Choose an extraction boundary that is repeatable. Record the high-water mark only after the corresponding destination work is committed. Otherwise a failure can create a gap.

ETL transforms before the destination

In ETL, a separate transformation engine applies business rules before loading the target.

source → landing/staging → transformation engine → curated target