Difference Between ETL and ELT Patterns in Data Engineering: A Practical Guide

ETL transforms data before it reaches the warehouse using external compute resources, while ELT loads raw data as-is into the warehouse and performs transformations using the warehouse's native SQL engine.

The choice between ETL (Extract → Transform → Load) and ELT (Extract → Load → Transform) defines where data processing occurs in your pipeline. According to the DataTalksClub/data-engineering-zoomcamp repository, this architectural decision determines your infrastructure costs, scalability, and tooling requirements. The course demonstrates both patterns using Kestra workflow orchestration, providing concrete YAML implementations that highlight when each approach dominates modern data engineering.

What Are ETL and ELT Patterns?

Both patterns describe the movement of data from source systems to a data warehouse, but they diverge on the sequence of the Transform step.

ETL extracts data from sources, transforms it using external compute (Python scripts, Spark, or dedicated ETL engines), then loads the cleaned results into the warehouse. This approach assumes transformation logic requires programming languages or complex libraries unavailable in SQL.

ELT extracts raw data and loads it immediately into cloud storage or the warehouse, then performs transformations using the warehouse's compute engine (BigQuery, Snowflake, or Redshift). This pattern leverages cheap cloud storage and massively parallel SQL processing.

Where the "T" Happens: Key Architectural Differences

The fundamental distinction lies in compute allocation.

ETL performs transformations outside the warehouse. In 02-workflow-orchestration/flows/03_getting_started_data_pipeline.yaml, the pipeline downloads JSON data, runs Python code to filter and reshape the data, then loads it into DuckDB for final aggregation. The heavy lifting occurs in a Python container before the warehouse sees the data.

ELT performs transformations inside the warehouse. As shown in 02-workflow-orchestration/flows/08_gcp_taxi.yaml, the pipeline extracts CSV files, uploads them raw to Google Cloud Storage, and registers external tables in BigQuery. All cleansing, deduplication, and business logic execute via SQL MERGE statements and CREATE TABLE ... AS SELECT operations within BigQuery itself.

Aspect	ETL	ELT
Transformation Location	External compute (Python, Docker containers)	Warehouse SQL engine
Data State at Load	Cleaned, filtered, aggregated	Raw, unmodified
Infrastructure	Requires separate processing cluster	Leverages warehouse scalability
Flexibility	Full programming language support	Limited to SQL (or dbt abstractions)

ETL Implementation: Python and External Processing

The Zoomcamp's ETL example demonstrates classic extract-transform-load sequencing using Kestra tasks. The workflow in 03_getting_started_data_pipeline.yaml processes API data through distinct stages:

# 02-workflow-orchestration/flows/03_getting_started_data_pipeline.yaml

id: 03_getting_started_data_pipeline
namespace: zoomcamp
tasks:
  - id: extract
    type: io.kestra.plugin.core.http.Download
    uri: https://dummyjson.com/products               # ← Extract

  
  - id: transform
    type: io.kestra.plugin.scripts.python.Script
    containerImage: python:3.11-alpine
    script: |
      import json
      # Python transformation logic runs here

      # Filter columns, handle missing values, enrich data

  
  - id: query
    type: io.kestra.plugin.jdbc.duckdb.Queries
    sql: |
      SELECT brand, round(avg(price),2) AS avg_price   # ← Final aggregation

      FROM read_json_auto('{{workingDir}}/products.json')
      GROUP BY brand

This pattern suits small-to-medium datasets where you need Pandas, custom libraries, or complex business logic that SQL cannot express efficiently. The warehouse receives only the final, aggregated results.

ELT Implementation: Cloud Storage and SQL Transformation

Modern cloud architectures favor ELT for large-scale data. The 08_gcp_taxi.yaml workflow illustrates this pattern by minimizing data movement and maximizing BigQuery's processing power:

# 02-workflow-orchestration/flows/08_gcp_taxi.yaml

id: 08_gcp_taxi
namespace: zoomcamp
tasks:
  - id: extract
    type: io.kestra.plugin.scripts.shell.Commands
    commands:
      - wget -qO- https://github.com/DataTalksClub/nyc-tlc-data/releases/download/{{inputs.taxi}}/{{render(vars.file)}}.gz |
        gunzip > {{render(vars.file)}}               # ← Extract raw CSV

  
  - id: upload_to_gcs
    type: io.kestra.plugin.gcp.gcs.Upload
    from: "{{render(vars.data)}}"
    to: "{{render(vars.gcs_file)}}"                 # ← Load to cloud storage

  
  - id: bq_yellow_table_ext
    type: io.kestra.plugin.gcp.bigquery.Query
    sql: |
      CREATE OR REPLACE EXTERNAL TABLE `{{kv('GCP_PROJECT_ID')}}.{{render(vars.table)}}_ext`
      OPTIONS (
        format = 'CSV',
        uris = ['{{render(vars.gcs_file)}}'],
        skip_leading_rows = 1
      );                                          # ← Register external table

  
  - id: bq_yellow_table_tmp
    type: io.kestra.plugin.gcp.bigquery.Query
    sql: |
      CREATE OR REPLACE TABLE `{{kv('GCP_PROJECT_ID')}}.{{render(vars.table)}}`
      AS SELECT * FROM `{{kv('GCP_PROJECT_ID')}}.{{render(vars.table)}}_ext`
      WHERE trip_distance > 0;                    # ← Transform in BigQuery

As documented in 04-analytics-engineering/class_notes/4_1_1_analytics_engineering_basics.md, this approach "is the dominant approach now" because cloud storage is inexpensive and warehouses like BigQuery offer virtually unlimited compute for SQL transformations.

When to Choose Each Pattern

Use ETL when:

• Transformation logic requires Python libraries (machine learning, complex parsing)
• Data volumes are small-to-medium and fit in memory
• You must enforce strict data quality before any data touches the warehouse
• Working with legacy systems that cannot handle raw data storage

Use ELT when:

• Processing terabyte-scale datasets where moving transformed data is costly
• Leveraging cloud data warehouses (BigQuery, Snowflake, Redshift) with elastic compute
• Teams prefer SQL-first transformations using dbt
• Raw data retention is required for compliance or reprocessing

Summary

• ETL transforms data before loading using external compute engines, offering full programming flexibility but requiring separate infrastructure.
• ELT loads raw data immediately and transforms inside the warehouse using SQL, leveraging cloud scalability and reducing data movement overhead.
• The Data Engineering Zoomcamp implements ETL in 03_getting_started_data_pipeline.yaml (Python + DuckDB) and ELT in 08_gcp_taxi.yaml (GCS + BigQuery SQL).
• Choose ETL for complex, library-dependent transformations; choose ELT for high-volume cloud warehouse environments.

Frequently Asked Questions

What is the main difference between ETL and ELT in data engineering?

The primary difference is the location and timing of data transformation. ETL transforms data using external compute resources (Python, Spark) before it enters the warehouse, while ELT loads raw data directly into the warehouse and performs transformations using the warehouse's SQL engine. According to the Data Engineering Zoomcamp source code in 4_1_1_analytics_engineering_basics.md, this shift from external processing to in-warehouse processing represents the dominant modern pattern.

When should I use ETL over ELT?

You should use ETL when your transformations require programming languages and libraries that SQL cannot support, such as complex JSON parsing, machine learning inference, or sophisticated data enrichment. The Zoomcamp's ETL example in 03_getting_started_data_pipeline.yaml demonstrates this by using Python to process API data before it reaches DuckDB. ETL is also preferable when you must prevent any raw or non-compliant data from entering your warehouse due to strict governance requirements.

Can I use dbt with both ETL and ELT patterns?

Yes, though dbt is primarily designed for the ELT pattern. In ELT workflows, dbt runs SQL transformations inside the warehouse (BigQuery, Snowflake) after the raw data has been loaded. For ETL pipelines, dbt would only operate on the already-transformed data that arrives in the warehouse. The Data Engineering Zoomcamp utilizes dbt in Module 4 specifically for the ELT pattern, transforming raw data that was loaded into BigQuery during the extraction phase.

How does the Data Engineering Zoomcamp implement these patterns differently?

The repository implements ETL as a code-driven sequence in Kestra where distinct tasks handle extraction (HTTP download), transformation (Python script), and loading (DuckDB query). Conversely, the ELT implementation minimizes external processing by extracting data to Google Cloud Storage, registering it as external tables in BigQuery, then using SQL queries to create clean production tables. As noted in 02-workflow-orchestration/README.md, the ELT approach reduces pipeline complexity by relying on the warehouse's compute power rather than maintaining separate transformation clusters.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}