ETL stands for Extract, Transform, Load—the traditional process for moving and preparing data for analytics. It's the backbone of data warehousing and has been used for decades to integrate data from multiple sources.
The Three Stages
1. Extract
Pull data from various source systems:
- Databases (MySQL, PostgreSQL, Oracle)
- SaaS applications (Salesforce, HubSpot)
- APIs (REST, GraphQL)
- Files (CSV, JSON, XML)
- Streaming sources (Kafka, webhooks)
2. Transform
Apply business logic to prepare data:
- Cleaning: Remove duplicates, handle nulls
- Standardization: Unify formats (dates, currencies)
- Aggregation: Summarize data for reporting
- Enrichment: Add calculated fields or lookups
- Validation: Apply business rules
3. Load
Write transformed data to the target:
- Data warehouses (Snowflake, BigQuery)
- Data lakes (S3, Azure Data Lake)
- Operational databases
ETL vs ELT
The rise of cloud data warehouses has popularized ELT:
| Aspect | ETL | ELT |
|--------|-----|-----|
| Transform Location | Before loading (staging server) | After loading (in warehouse) |
| Best For | Limited warehouse compute | Powerful cloud warehouses |
| Flexibility | Less flexible (predefined) | More flexible (transform anytime) |
| Tools | Informatica, SSIS, Talend | dbt, Snowflake, BigQuery |
Modern data stacks often use ELT: load raw data first, then transform using tools like dbt.
Popular ETL/ELT Tools
- Fivetran: Automated, fully managed connectors
- Airbyte: Open-source data integration platform
- Stitch: Simple, developer-friendly pipelines
- dbt: Transformation layer (the T in ELT)
- Apache Airflow: Workflow orchestration for custom ETL
Best Practices
1. Incremental Loading: Only process new/changed data
2. Idempotency: Running the same job twice should produce the same result
3. Monitoring: Track data quality and pipeline health
4. Documentation: Maintain data lineage and transformation logic