ETL vs ELT: What Actually Matters When You're Building a Data Pipeline

If you have spent any time in data engineering, you have probably heard the ETL vs ELT debate. It gets brought up in every data platform discussion, and honestly, most of what you read about it is pretty abstract. You get Venn diagrams, lists of pros and cons, and phrases like “ELT is the modern approach” without much context.

In this article, I want to take a different route. Instead of theory, let us look at what ETL and ELT actually look like when you build them, where each one makes sense, and the problems you run into with both. We will walk through concrete examples using tools you are likely to encounter in the wild — Python, Airflow, dbt, BigQuery, Snowflake.

First, What Are We Even Comparing?

Let me skip the textbook definitions and just say what these patterns mean in practice.

ETL (Extract → Transform → Load): You pull data from a source, clean and reshape it somewhere in the middle, and then load the finished result into your target database or warehouse. The transformation happens before the data hits the destination.

ELT (Extract → Load → Transform): You pull data from a source and dump it into your warehouse in whatever shape it arrives. Then you use the warehouse itself to do all the cleaning and reshaping. The transformation happens after the data is already sitting in the destination.

The difference really comes down to one question: where does the heavy lifting happen?

Here is a quick comparison to ground the discussion:

Aspect	ETL	ELT
Transform location	External compute (Python, Spark, etc.)	Inside the warehouse (SQL, dbt)
Raw data in warehouse?	No — only clean data lands	Yes — raw data lives there first
Best for	Sensitive data, complex logic, legacy sources	Cloud warehouses, fast iteration, SQL-friendly teams
Latency	Slower to set up, controlled	Faster to land data, transform on demand
Governance	Easier to enforce before anything hits the target	Needs discipline — raw layer can become a dumping ground

ETL: A Concrete Example

Let us say you work for an e-commerce company and you need to ingest order data from a Postgres database into BigQuery. The source has PII (customer emails, phone numbers) and you need to hash those before they leave your controlled environment.

Here is what a simple ETL pipeline looks like using Python and Airflow:

import hashlib
import pandas as pd
from airflow.decorators import dag, task
from datetime import datetime

@dag(schedule='@daily', start_date=datetime(2026, 1, 1))
def orders_etl():

    @task
    def extract_orders():
        # Pull from source Postgres
        conn = get_postgres_connection()
        df = pd.read_sql("SELECT * FROM orders WHERE updated_at > yesterday", conn)
        return df.to_dict()

    @task
    def transform_orders(data):
        df = pd.DataFrame(data)
        # Hash PII before it goes anywhere
        df['email_hash'] = df['email'].apply(lambda x: hashlib.sha256(x.encode()).hexdigest())
        df['phone_hash'] = df['phone'].apply(lambda x: hashlib.sha256(str(x).encode()).hexdigest())
        # Drop raw PII columns
        df = df.drop(columns=['email', 'phone'])
        # Normalise status values
        df['status'] = df['status'].str.lower().str.strip()
        return df.to_dict()

    @task
    def load_to_bigquery(data):
        df = pd.DataFrame(data)
        client = bigquery.Client()
        job = client.load_table_from_dataframe(
            df, 'my_project.warehouse.orders'
        )
        job.result()

    extract_orders() >> transform_orders() >> load_to_bigquery()

This is ETL in its simplest form. The transformation step sits between extraction and loading, running on whatever compute Airflow gives it (Kubernetes pods, VMs, whatever you configured). The key thing is that raw PII never touches BigQuery.

That last point matters. If you work in healthcare or finance, or deal with GDPR requirements, ETL gives you a clear boundary. You can say with confidence: “sensitive data is hashed before it leaves the source environment.” That is harder to guarantee with ELT.

ELT: The Same Pipeline, Different Approach

Now let us imagine the same use case but the company has already decided that Snowflake is the single source of truth, and the analytics team wants raw data available for ad-hoc exploration. PII hashing will still happen, but it happens inside the warehouse.

Step one — land the raw data with minimal processing:

# This is basically a pass-through — extract and load, no transform
@task
def raw_orders_to_snowflake():
    conn = get_postgres_connection()
    df = pd.read_sql("SELECT * FROM orders WHERE updated_at > yesterday", conn)
    # Load raw — yes, including email and phone
    write_to_snowflake(df, 'RAW.ORDERS', mode='append')

Then dbt does the actual transformation:

-- models/staging/stg_orders.sql


WITH source AS (
  SELECT * FROM 
  WHERE updated_at > current_date - interval '1 day'
)

SELECT
  order_id,
  customer_id,
  order_amount,
  LOWER(TRIM(status)) AS status,
  SHA2_HEX(email, 256)    AS email_hash,
  SHA2_HEX(phone, 256)    AS phone_hash,
  -- Deliberately exclude raw email and phone from this model
  updated_at
FROM source

And then the downstream model the analytics team queries:

-- models/marts/fct_orders.sql
SELECT
  order_id,
  customer_id,
  order_amount,
  status,
  email_hash,
  phone_hash,
  updated_at
FROM 
WHERE status IN ('completed', 'shipped', 'delivered')

Notice the pattern. The raw data sits in RAW.ORDERS with everything intact. The staging model creates a cleaned, hashed version. Downstream models only reference the clean version. The analytics team never sees raw PII because you control access at the model level.

This is where ELT shines. You can iterate on transformations without re-extracting data. If someone wants a new column, you add it to the dbt model and run dbt run. No need to touch the pipeline code or re-pull from the source.

Where Each Approach Trips You Up

These things are never as clean as the blog posts make them look. Here is what I have actually seen go wrong.

ETL problems I have run into:

1. Someone changes the source schema and your Python transform breaks silently. You do not notice for three days because the Airflow task retries and eventually skips. Now you have missing data in production.
2. Transform logic gets spread across Python scripts, stored procedures, and random notebooks. Six months later nobody knows where the revenue_total column actually gets calculated.
3. Debugging is harder than it should be. If the loaded data looks wrong, you cannot just query the raw source — it was never stored.

ELT problems I have run into:

1. The raw layer becomes a dumping ground. Teams start loading everything because “we can sort it out later.” Later never comes, and your Snowflake bill goes through the roof from storing garbage nobody uses.
2. Row-level access control gets complicated. Sure, you can restrict access to the raw schema, but someone with admin privileges accidentally shares a raw table in a dashboard, and now customer emails are visible. This has happened.
3. dbt models multiply fast. What started as five clean models becomes 200 with circular dependencies. I have seen dbt DAGs that look like spaghetti, and good luck onboarding a new engineer onto that.

So Which One Should You Pick?

Honestly, most real projects use a mix of both. Here is the mental model I use:

Lean toward ETL when:

• You have PII or compliance requirements that demand data is cleaned before it leaves a controlled environment.
• You are pulling from APIs or sources where the raw payload is massive and you want to slim it down before storage.
• Your transformation logic is complex and SQL is not the best fit (nested JSON parsing, ML feature engineering, streaming).

Lean toward ELT when:

• You are on a modern cloud warehouse (BigQuery, Snowflake, Redshift) and want analytics teams to self-serve.
• You need to iterate fast on transformations without replaying full extracts.
• Your team is strong in SQL and you want transformations version-controlled in dbt alongside the rest of your codebase.

What I actually do in most projects: Use ELT as the default pattern but put ETL-style checks at the ingestion layer. Land raw data, but validate schemas and hash PII columns in the extraction task before they hit the raw table. That way you get the speed of ELT with the safety boundaries of ETL. It is not as neat as the diagrams, but it works.

At the end of the day, the choice between ETL and ELT is not a religious one. It is about where your compute lives, who needs access to the data, and what your compliance requirements look like. Pick the pattern that keeps you out of trouble, not the one that sounds better in a conference talk.