ETL vs ELT

• Two dominant patterns for moving data from source systems into an analytical destination.

Data is transformed before it reaches the destination.

1. Extract — pull raw data from sources (DBs, APIs, files)
2. Transform — clean, join, aggregate in an intermediate engine (e.g. Spark job, custom script)
3. Load — write the clean, structured result to the warehouse/data lake

• Originated when storage was expensive and warehouses were rigid
• Transformation logic lives outside the warehouse
• Tools: Apache Spark, Talend, Informatica, custom Python scripts

Data is loaded raw first; transformation happens inside the warehouse.

1. Extract — pull raw data from sources
2. Load — dump raw data into the warehouse/lake (cheap columnar storage)
3. Transform — use SQL (e.g. dbt) to model data directly in the warehouse

• Made practical by cheap cloud storage and powerful MPP warehouses (Snowflake, BigQuery, Redshift, Databricks)
• Transformation logic lives inside the warehouse as SQL models
• Enables reproducibility, version control, and easy re-runs

• ETL — when raw data must not touch the warehouse (compliance), when transformation is computationally heavy (ML feature engineering), or when the destination is not SQL-capable
• ELT — most modern cloud analytics workloads; preferred pattern with dbt and cloud warehouses