Data Engineering Foundations
Beginner 14 min read Data Engineering Foundations

ETL vs ELT

A schema shift driven by cheap, fast warehouses

ETL transforms data before it lands in the warehouse; ELT loads raw data first and transforms it in place. Warehouses got fast and cheap enough to make ELT the modern default.

ETLELTtransformationloadingdbtreverse ETLpipelines
Prerequisites: OLTP vs OLAP
Before we start

What you will learn

  • Compare ETL and ELT architecture and failure modes
  • Explain why ELT became the default in modern data stacks
  • Identify when classic ETL still wins
Introduction

What is this?

ETL (Extract, Transform, Load) transforms data before it reaches the target store, so the warehouse only ever holds cleaned data. ELT (Extract, Load, Transform) loads raw data first, then transforms it in place with the warehouse's own compute — typically SQL. ELT became dominant because modern columnar warehouses are cheap enough to store raw and powerful enough to transform at scale.

Motivation

Why do you need it?

  • Loading raw preserves the source truth, so you can re-transform (backfill) without re-extracting.
  • Warehouse compute lets SQL do transforms without a separate, expensive transform server.
  • Transforms become code with tests and version control (dbt, SQLMesh).
  • ETL still matters where you must scrub data before it can legally or functionally land (PII filtering, file format conversion).
Think about it

Mental model

Imagine

Washing groceries at the store vs in your kitchen

Think of

ETL vs ELT

ETL cleans before it comes in the house because you can't keep raw produce. ELT brings everything in and washes it in the kitchen (the warehouse), where you have all the water and space you need.

Mechanics

How it works

  • ETL: an extractor pulls data, a transform engine (often a dedicated job/host) scrubs and reshapes it, and a loader writes the final shape into the warehouse.
  • ELT: an extractor copies raw data verbatim into staging tables or object storage; a modeling layer (SQL, dbt) transforms it in place.
  • ELT separates concerns: extract/load is one team's 'get the data in', modeling is another's 'shape the data'.
  • Reverse ETL pushes modeled warehouse data back into operational tools (CRM, ad platforms).
  • Failure handling differs: ETL failures stop mid-transform (the warehouse is unchanged), ELT failures leave raw data loaded and only staging/modeled tables stale.

ETL vs ELT pipelines.

Sources
ETL: Transform before load
ELT: Load raw, then transform
Warehouse
Consumers
Real world

In production

  • A SaaS loads daily exports raw into staging (ELT) and models revenue with dbt — backfills are a one-line 'run this model for that window'.
  • A payments startup still uses ETL to mask PANs before anything lands in the warehouse, because raw cards must never exist at rest.
  • Reverse ETL syncs the 'account health score' model back into Salesforce every hour.
Guidelines

Best practices

  • Prefer ELT unless you must remove data (PII, secrets) or massively reshape before storage.
  • Keep a raw staging layer immutable; never let transforms clobber the source copy.
  • Version your transforms like code; the modeled layer should be reproducible from raw.
  • Choose the transformation step's compute explicitly — warehouse (ELT) or dedicated engine — and know the cost per run.
Watch out

Common mistakes

  • Building an ETL server to do work the warehouse could do for a fraction of the ops burden.
  • Modeling in air with unportable SQL that wasn't tested, then 'fixing' in the BI tool.
  • Forgetting reverse ETL is a pipeline too — it needs monitoring and idempotency.
Root cause analysis

Common issues

Error
Modeled table is stale but load succeeded
Root cause

Transform step (dbt run) failed or wasn't scheduled

Fix

Make modeling part of the same DAG and alert on model-level freshness.

Error
ETL job consumes all memory during transform
Root cause

Transform is CPU/RAM-bound in a separate engine; volume outgrew the box

Fix

Move to ELT and push the transform into warehouse compute, or partition the transform.

Error
Backfill takes hours because raw is re-fetched
Root cause

ELT skipped the raw store, transforms read from source again

Fix

Load raw once, then always transform from staging/raw tables.

When things break

Debugging tips

  • In ELT, first confirm the raw/load step ran; modeling can't fix a missing load.
  • Diff raw vs modeled counts to see exactly where rows drop.
  • For ETL, check the transform engine logs and the exact field the transform choked on.
  • Re-run a single model rather than the whole pipeline when only one table is wrong.
Interviews

Interview checkpoint

Practice

Hands-on lab

Build an ELT Flow in SQL
30 min · beginner
In progress
Objectives
  • Load raw data verbatim into staging
  • Write SQL transforms that model raw into a clean shape
  • Demonstrate that modeling can be re-run (backfill) from raw without re-extracting

Build an ELT Flow in SQL

30 min · beginner

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which pattern stores the raw data in the warehouse?

2

ELT makes transformations more reproducible because raw data is retained in the platform.

Go deeper

Official documentation

dbt — best practice ELT workflow
docs