ETL vs ELT

Overview

ETL and ELT differ in where the transform step runs. ETL transforms in a dedicated engine before loading; ELT loads raw data first and transforms inside the destination using its own compute. The shift toward ELT followed cheap cloud storage and scalable warehouses.

How it works

In ELT, an ingestion tool copies tables or files into a raw schema. Transformation models then build layered views and tables.

ETL:  source --> [transform engine] --> warehouse (modeled only)
ELT:  source --> warehouse.raw --> [SQL models] --> warehouse.analytics

An ELT model is a SELECT statement stored in version control:

-- models/orders_clean.sql
SELECT order_id, customer_id,
       CAST(total AS DECIMAL(12,2)) AS total,
       LOWER(status) AS status
FROM raw.orders
WHERE order_id IS NOT NULL

The orchestrator builds models in dependency order, runs tests for uniqueness and nulls, and records lineage. ETL remains valuable when data must be tokenized or filtered before landing, or when transforms need general-purpose code.

Pitfalls

• Loading raw sensitive data into broadly readable schemas. Restrict raw access and mask columns.
• Full refreshes of large models every run; prefer incremental models keyed on a watermark.
• No tests or code review on SQL models, so metric changes go unnoticed.
• Mixing styles: some logic in the ingestion tool, some in SQL, some in dashboards, so nobody knows where a number comes from.
• Ignoring warehouse cost: schedule heavy transforms and monitor spend per model.

Key takeaways

• ELT is the default for analytics on elastic warehouses; ETL for pre-landing compliance or complex code.
• Keep transformations in version control with tests.
• Retain raw data, but control who can read it.
• Use incremental builds to control cost.