ETL (Extract, Transform, Load)
A three-step process used to move data from one system to another, typically into a data warehouse.
Detailed Explanation
Extract: collecting raw data from sources (APIs, DBs, logs). Transform: cleaning, reformatting, and enriching the data (e.g., converting currencies, removing duplicates). Load: writing the final data into the destination. ETL is the backbone of data engineering and business intelligence. Modern 'ELT' reverses the last two steps, leveraging the power of modern cloud data warehouses.
Quick Summary
ETL is the pipeline that moves and reshapes data from operational systems into analytics. Modern cloud warehouses flipped it to ELT, load raw first, transform inside the warehouse, but the goal is the same.
Key Takeaways
- ETL transforms before load (older pattern); ELT loads raw then transforms in the warehouse (modern default).
- dbt is the dominant tool for ELT transformations, SQL models, tests, lineage, documentation.
- Ingestion tools (Fivetran, Airbyte, Stitch) handle E and L; teams own the T.
- Schedule with Airflow, Dagster, Prefect, or warehouse-native schedulers.
- Incremental loads beat full refreshes once data volume grows, both for cost and latency.
When to use it
- Loading product, finance, and CRM data into a warehouse for BI dashboards.
- Building a single customer 360 by joining data across SaaS tools.
- Preparing training data for ML models (feature engineering + clean labels).
- Reverse ETL: pushing warehouse-derived insights back into operational tools like Salesforce or HubSpot.
Common Mistakes
- Doing transformations in fragile bespoke scripts instead of a tool like dbt, they accumulate undocumented business logic that nobody can audit.
- Full refreshes when incremental would do, slow, expensive, and rate-limit-prone.
- No tests on transformed tables, silent data quality regressions compound.
- Orchestration via cron + bash; works briefly, then becomes the team's worst on-call.
ETL (Extract, Transform, Load), Frequently Asked
ETL or ELT?
ELT for any modern cloud-warehouse stack (Snowflake, BigQuery, Redshift, Databricks). The compute is fast and the lineage tooling (dbt) is mature. ETL still makes sense when the destination is small or the transformations are heavy and have to happen close to the source.
Do I need a dedicated data team?
Not until you have multiple sources and recurring stakeholder reporting. Early-stage: one engineer plus dbt and Fivetran covers a surprising amount. Build a team when data questions start blocking decisions across functions.