ETL and ELT: Where Data Transformation Happens

Analytical data rarely arrives ready for decisions.

Source systems use different schemas, identifiers, time zones, currencies, and quality rules. Data pipelines move and reshape that information.

ETL transforms before loading into the analytical destination; ELT loads source data first and transforms it inside the destination platform.

Both patterns extract, move, validate, and model data.

A concrete example: order reporting

A cloud warehouse receives raw order changes.

An ELT pipeline:

extracts changes,
loads source-shaped records,
deduplicates,
converts currency,
joins customer dimensions,
builds a net-revenue table,
tests totals,
publishes the model.

Raw history remains available for reprocessing.

Extract

Extraction reads from:

databases,
APIs,
files,
event streams,
SaaS products,
and logs.

It should minimize source load and record a reproducible position or time range.

Full extraction

A full extract copies the entire dataset.

It is simple and useful for initial load or small tables, but becomes expensive and slow as data grows.

It can also miss deletes unless the full snapshot is compared.

Incremental extraction

Incremental methods use:

updated timestamp,
sequence ID,
change log,
or source cursor.

They reduce volume but must handle late updates, clock issues, deletes, and cursor loss.

Persist progress only after destination acceptance.

Transform

Transformation includes:

type conversion,
validation,
deduplication,
normalization,
joins,
aggregation,
masking,
and business calculation.

Each transformation should have defined inputs, outputs, and owner.

Load

Loading writes into:

warehouse,
lake,
database,
search index,
or model store.

Use bulk APIs and partition-aware writes. Avoid exposing partially loaded tables to consumers.

ETL

Traditional ETL uses a separate processing system before the destination.

Advantages:

control before data enters,
reduced destination load,
early masking,
suitable for constrained targets.

It can discard raw detail unless explicitly retained.

ELT

ELT loads raw or lightly processed data into a scalable analytical platform.

Advantages:

preserve source history,
transform with destination compute,
support new models without re-extracting,
use SQL for collaboration.

It can increase storage and privacy scope.

Choosing between them

Consider:

source sensitivity,
destination capability,
data volume,
transformation language,
latency,
raw retention,
governance,
and cost.

One platform may use ETL for sensitive fields and ELT for the remaining data.

Staging

A staging area holds extracted data before publication.

It supports:

replay,
validation,
audit,
and atomic model replacement.

Staging needs retention and access controls; it can contain the least-clean data.

Atomic publication

Consumers should not see half of a pipeline run.

Build output in temporary tables or partitions, run quality checks, then switch a view, pointer, or transaction to the complete version. If validation fails, retain the previous published dataset while operators investigate.

Pipeline dependencies

Time-based scheduling alone can start a transformation before its source is ready.

Use data-aware dependencies and freshness checks. A job should know which exact source partitions or log positions it consumed, making rerun and lineage precise.

Idempotency

Rerunning the same pipeline interval should not duplicate rows or totals.

Use:

source event ID,
merge keys,
partition replacement,
transactional publish,
and run identifiers.

Recovery depends on safe repetition.

Orchestration

An orchestrator schedules tasks and dependencies:

orders loaded -> customers loaded -> revenue model -> dashboard refresh

It tracks retries, state, deadlines, and alerts.

Passing orchestration does not prove data correctness.

Data contracts

Source owners should communicate:

schema,
meaning,
allowed changes,
freshness,
and quality.

Without contracts, a renamed status or changed currency unit can silently corrupt downstream models.

Schema evolution

Pipelines must handle:

new columns,
removed columns,
type changes,
renamed values,
and nested schema changes.

Compatible additions can flow automatically; semantic changes need coordinated migration.

Data testing

Tests include:

schema,
null limits,
uniqueness,
referential integrity,
accepted values,
row-count reconciliation,
and business totals.

Test at source, staging, and published model according to ownership.

Backfills

A corrected transformation may need to recompute history.

Backfills require:

bounded date range,
isolated compute,
versioned code,
idempotent output,
validation,
and publication plan.

They should not starve current daily processing.

Code and environment reproducibility

Record transformation code, dependency versions, runtime image, configuration, and input snapshot for every material run.

Without this context, rerunning last year's model with today's package versions can produce a different result even from the same source data.

Late-arriving data

Events can arrive after a reporting partition is published.

Pipelines may:

update recent windows,
maintain correction jobs,
or mark historical reports revised.

Freshness and finality are separate concepts.

Slowly changing dimensions

Dimension changes need historical handling.

The pipeline can overwrite current value or create a version with effective dates.

The correct strategy depends on whether reports ask "current category" or "category at event time."

Privacy

Transform sensitive data as early as practical:

drop unused fields,
tokenize identifiers,
aggregate,
enforce retention,
restrict raw zones.

ELT convenience should not grant broad analyst access to raw personal data.

Observability

Track:

source cursor,
extracted rows,
loaded rows,
rejected rows,
transformation duration,
freshness,
test failures,
and published version.

Data incidents need lineage from dashboard back to source run.

Knowledge check

Where does transformation occur in ETL versus ELT?
What can make incremental extraction unreliable?
Why should pipeline reruns be idempotent?
What does a data contract protect?
Why do backfills need isolation from current processing?

The one idea to remember

ETL transforms data before destination load; ELT loads source data first and transforms with destination compute. Reliable pipelines also need reproducible extraction, staging, idempotency, schema contracts, tests, backfills, privacy, lineage, and atomic publication.