ETL vs ELT

Concepts covered: ELT vs ETL

Where the transform runs

ETL and ELT differ in where the transform runs, and that single fact decides what the loaded data looks like. In ETL, an engine between source and warehouse (Informatica PowerCenter, SSIS, a Spark cluster) reads the source, cleans and reshapes rows in its own memory, and loads only finished tables. The warehouse never holds raw data. In ELT, the loader copies source data into the warehouse as close to verbatim as the types allow, and the transform runs inside the warehouse as SQL, usually versioned and scheduled by dbt. The raw copy stays and can be queried.

The ETL default came from the economics of the 2000s warehouse. A Teradata or Oracle appliance was bought by the rack, its compute fixed and shared by every analyst, so a transform scanning 2TB of raw clickstream inside it slowed every dashboard in the company. A separate ETL server protected the expensive box. The price was flexibility: a question the loaded tables could not answer needed a new extract, a change request to the ETL team and often weeks of waiting.

Cloud warehouses broke the constraint in 2 places. BigQuery (2011) and Snowflake (2015) separated storage from compute, so stored bytes cost object-store prices and compute is sized per workload, with Snowflake billing isolated virtual warehouses by the second. Redshift (2013) made cluster compute rentable and resizable by the hour, and separated storage only with RA3 nodes in 2019. The 2TB transform now runs on its own compute for a few dollars, and the raw copy it reads costs around $46 a month. With storage that cheap and compute that elastic, loading raw first became the default, and dbt (2016) gave SQL transforms the version control and tests ETL tools had kept inside their GUIs.

ETL: transform before load
  • A separate engine owns the compute; the warehouse holds only finished tables
  • Raw data is gone after load; a new question needs a new extract
  • Masking, parsing and model inference run in general-purpose code
  • Fits data that must be reduced or protected before it lands
ELT: transform after load
  • Warehouse compute does the work, sized per job
  • Raw tables persist; a new model is a new SQL file over old rows
  • Transforms are SQL, tested and versioned with dbt
  • Fits any transform SQL can express over data allowed to land

Choose per transform

The retailer's curated layer holds both. Casting amount to NUMERIC, deduplicating on order_id and removing test accounts are ELT: dbt models over raw tables that keep every row, so a rule change reruns against history without touching the source. The model below does the cast, filter and deduplication for curated orders, and it runs in Snowflake in about 40 seconds over 1 hour of raw data.

select
  order_id,
  cast(amount as numeric(12, 2)) as amount,
  customer_id,
  sku,
  date_trunc('hour', ordered_at) as order_hour
from {{ source('raw', 'orders') }}
where customer_id not in (select customer_id from {{ ref('test_accounts') }})
qualify row_number() over (
  partition by order_id order by ingested_at desc
) = 1

The rule that falls out: a transform that must happen before the data may exist in the warehouse, or that SQL cannot express, runs before load. Everything else runs after, where it is cheaper to change and leaves the raw rows in place to answer the next question.

An ingestion job pulls support tickets from the Zendesk API into Snowflake. Ticket bodies contain customer emails and, a few times a week, card numbers typed into free text. Analysts need the ticket text searchable. Where does the masking run?

About this lesson

This section is part of the Pipeline Anatomy: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.