ETL vs ELT: Where the Transform Happens

Scenario

Example company: an online shop runs a nightly Python job. It extracts yesterday's orders from the production PostgreSQL database, converts currencies, joins customers and products in memory, and loads a table of daily revenue per country into the data warehouse.

It worked well at 20,000 orders a day.

What you see in production

• The job now handles 400,000 orders a day and takes seven hours. On bad nights it finishes after the business day starts, and the morning dashboard shows yesterday's numbers as empty.
• The marketing team asks for revenue by city. The warehouse only has revenue by country, so the answer needs a new job and re-extracting two years of history from production, which the database team refuses to allow during business hours.

The naive code

rows = extract("SELECT * FROM orders WHERE order_date = :yesterday")   # all in one process
for row in rows:
    row["amount_usd"] = convert(row["amount"], row["currency"])
    row["country"] = customers[row["customer_id"]]["country"]          # lookups held in RAM
daily = aggregate(rows, by="country")                                   # raw rows are thrown away
load("analytics.daily_revenue_by_country", daily)

Reproduce it

The simulation models the two shapes. ETL keeps only the aggregate; ELT lands raw rows first and transforms them in the warehouse. Then a new question arrives.

orders = [
    {"id": 1, "country": "DE", "city": "Berlin", "usd": 120},
    {"id": 2, "country": "DE", "city": "Munich", "usd": 80},
    {"id": 3, "country": "FR", "city": "Paris", "usd": 200},
    {"id": 4, "country": "DE", "city": "Berlin", "usd": 50},
]

# ETL: transform in the job, load only the result
etl_warehouse = {}
for o in orders:
    etl_warehouse[o["country"]] = etl_warehouse.get(o["country"], 0) + o["usd"]

# ELT: load raw rows, transform later inside the warehouse
elt_raw = list(orders)

print("ETL table:", etl_warehouse)

# A new question arrives: revenue by city
def by_city(rows):
    out = {}
    for r in rows:
        out[r["city"]] = out.get(r["city"], 0) + r["usd"]
    return out

print("ELT answers 'revenue by city' from data already loaded:", by_city(elt_raw))
print("ETL can't: the warehouse only kept", list(etl_warehouse), "- re-extract history from production")

# Runtime model: one Python process vs a warehouse that scales out
for daily_orders in [20_000, 100_000, 400_000]:
    single_process_min = daily_orders / 1_000          # ~1,000 rows/minute with in-memory joins
    warehouse_min = 2 + daily_orders / 200_000          # set-based SQL across many workers
    print(f"{daily_orders:>7} orders/day: python ETL ~{single_process_min:.0f} min, warehouse ELT ~{warehouse_min:.0f} min")

ETL table: {'DE': 250, 'FR': 200}
ELT answers 'revenue by city' from data already loaded: {'Berlin': 170, 'Munich': 80, 'Paris': 200}
ETL can't: the warehouse only kept ['DE', 'FR'] - re-extract history from production
  20000 orders/day: python ETL ~20 min, warehouse ELT ~2 min
 100000 orders/day: python ETL ~100 min, warehouse ELT ~2 min
 400000 orders/day: python ETL ~400 min, warehouse ELT ~4 min

Root cause

Two decisions made sense at a small scale and stopped working:

1. Transforming in one process. Joins and conversions ran row by row on one machine, so runtime grew linearly with data.
2. Loading only the result. The raw rows were discarded, so every new question needed a new extract from production.

The fix

Move to ELT: extract and load raw data first into staging tables in the warehouse, then transform with SQL inside the warehouse, usually managed with a tool like dbt.

• Raw tables (raw.orders, raw.customers) are a faithful copy of the source.
• Transformations are SQL models (stg_orders → fct_orders → daily_revenue_by_country) that the warehouse runs in parallel.
• New questions are new SQL models over data you already have. History can be recomputed without touching production.

Keep a transform before loading only where it's needed: removing or masking personal data that must never reach the warehouse, decoding binary formats, or reducing truly huge raw volumes.

Trade-offs

• Warehouse cost: storing raw data and running transforms there costs money (compute and storage). It is usually far cheaper than engineering time spent on re-extracts.
• Governance: raw tables may contain personal data; restrict access and mask columns.
• ETL still fits when the target can't transform (an operational database), or when data must be cleaned before it lands for legal reasons.

Detect it in production

• Track pipeline duration per run and alert on trends, not just failures.
• Track freshness: the time the newest row landed in each reporting table.
• Count requests that need a "historical re-extract". Frequent ones mean you are not keeping raw data.

Key points

• ETL transforms before loading; ELT loads raw data first and transforms inside the warehouse.
• Keeping raw data lets you answer new questions and recompute history without touching production.
• Set-based SQL in a warehouse scales better than row-by-row transforms in one process.
• Transform before loading only for privacy, format decoding or extreme volume.
• Further reading: Extract, transform, load.