Data rarely lives in the format you need it. Your production database stores normalized rows optimized for transactions. Your analytics team needs denormalized tables optimized for queries. Your machine learning models need clean, feature-engineered datasets. Your data warehouse needs aggregated historical data.

Getting data from where it is to where it needs to be, in the format it needs to be in, is the job of ETL: Extract, Transform, Load. ETL pipelines are the plumbing of data infrastructure. They run silently in the background, moving terabytes of data every night, and only get attention when something breaks.

Understanding ETL is essential for system design. Almost every data architecture involves moving data between systems, and the patterns you learn here apply whether you are using batch processing, streaming, or hybrid approaches.

What is ETL?

ETL stands for Extract, Transform, Load. It describes the process of moving data from source systems to destination systems while changing its format.

The Three Phases

Why ETL Matters

Without ETL, every system would need to:

ETL centralizes these concerns:

Extract Phase

The Extract phase reads data from source systems. This sounds simple but involves significant complexity.

Common Data Sources

Full Extract vs Incremental Extract

Incremental Extract Strategies

Timestamp-based: Query rows where updated_at > last_run_time

Sequence-based: Track a monotonically increasing ID

Change Data Capture (CDC): Read from database transaction log

CDC is the most reliable for incremental extraction because it captures all changes, including deletes, without modifying the source schema.

Handling Source System Impact

Extracting data should not degrade production performance:

Transform Phase

The Transform phase converts raw data into the format needed by destination systems. This is where most of the business logic lives.

Common Transformations

Transformation Types

Data Quality Checks

Transform is where you enforce data quality:

Common quality checks:

Load Phase

The Load phase writes transformed data to destination systems. The strategy depends on the destination type and use case.

Load Strategies

Bulk Loading Techniques

For large data volumes, row-by-row inserts are too slow:

Staging Tables

A common pattern is to load into staging tables first:

Benefits:

ETL vs ELT

Modern data platforms have shifted from ETL to ELT.

ETL: Transform Before Load

Transformations happen on ETL servers before loading.

ELT: Transform After Load

Raw data is loaded first, transformations happen in the warehouse.

Comparison

Why ELT is Gaining Popularity

Modern cloud warehouses (Snowflake, BigQuery, Redshift) have:

With ELT:

ETL Pipeline Patterns

The Medallion Architecture

A popular pattern for organizing data in lakes and warehouses:

Change Data Capture Pipeline

For real-time or near-real-time data sync:

CDC captures every insert, update, and delete from the source database's transaction log, enabling near-real-time replication.

Dependency DAG

Complex ETL involves many interdependent jobs:

Orchestration tools (Airflow, Dagster, Prefect) manage these dependencies.

Handling Failures

ETL pipelines fail. Networks drop, sources become unavailable, data has unexpected formats. Robust pipelines handle failures gracefully.

Failure Points

Failure Handling Strategies

Idempotent Loads

An idempotent operation produces the same result whether run once or many times:

Making ETL idempotent allows safe retries:

ETL Tools

Traditional ETL Tools

Modern ELT Tools

Orchestration Tools

Best Practices

1. Design for Failure

Assume every component will fail:

2. Monitor Data Quality

Track metrics over time:

3. Version Control Everything

Treat ETL code like application code:

4. Document Lineage

Know where data comes from:

5. Use Incremental Processing

Avoid full refreshes when possible:

Summary

ETL pipelines are the backbone of data infrastructure:

ETL moves data between systems, but where does all this data ultimately live? The next chapter explores Data Lakes, the foundational storage layer for modern big data architectures, where raw data lands before being refined for analytics.