ETL vs ELT: Choosing the Right Approach

ETL (extract, transform, load) and ELT (extract, load, transform) differ in one thing: where and when the transformation happens.

	ETL	ELT
Order	Extract → transform → load	Extract → load raw → transform
Transformation runs in	A separate processing engine	The warehouse or lakehouse itself
Raw data kept in the target	Often not	Yes, as a raw or “bronze” layer
Typical transformation language	Code (Python, Spark, a tool’s GUI)	SQL

Why ELT became common

Cloud warehouses and lakehouses separate cheap storage from scalable compute. Loading raw data first and transforming with SQL inside the warehouse means:

• raw data is preserved, so you can re-run or change transformations without re-extracting from the source;
• transformations are plain SQL that analysts can read and review;
• the warehouse’s own engine does the heavy lifting in parallel.

Tools such as dbt are built around this pattern: they manage SQL transformations that run inside the warehouse, in dependency order, with tests.

When ETL is still the better fit

• Data must be changed before it lands, for example removing or pseudonymising personal data so it is never stored raw in the target.
• The source or target is not SQL-friendly, such as parsing binary or deeply nested files, or heavy non-SQL processing (images, ML feature extraction).
• Volume must be reduced early to cut storage or network costs, for example filtering or aggregating high-volume events before loading.
• The target cannot do the compute, such as a small operational database.

A simple ELT flow

Source system ──extract/load──▶ raw.orders      (as received)
                                    │ SQL
                                    ▼
                              staging.orders     (typed, deduplicated)
                                    │ SQL
                                    ▼
                         marts.fct_daily_sales   (modelled for analytics)

Each arrow is a rerunnable step. If a rule changes, you rebuild from the raw table, not from the source. Here is the same flow in PostgreSQL. The loader does no transformation at all: it stores each source record as JSON with load metadata.

CREATE SCHEMA raw;
CREATE SCHEMA staging;
CREATE SCHEMA marts;

CREATE TABLE raw.orders (
    payload    jsonb NOT NULL,
    _source    text NOT NULL,
    _loaded_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO raw.orders (payload, _source) VALUES
  ('{"id": "1", "customer": "asha", "total": "50.00", "ts": "2026-10-05T09:00:00Z", "status": "PAID"}', 'shop_api'),
  ('{"id": "2", "customer": "ben",  "total": "20.00", "ts": "2026-10-05T10:00:00Z", "status": "paid"}', 'shop_api'),
  ('{"id": "2", "customer": "ben",  "total": "22.00", "ts": "2026-10-05T10:05:00Z", "status": "paid"}', 'shop_api'),
  ('{"id": "3", "customer": "chen", "total": "75.00", "ts": "2026-10-06T08:00:00Z", "status": "cancelled", "coupon": "AUTUMN"}', 'shop_api');

The transformations are SQL inside the database. Staging types the fields, normalises values and keeps the latest version of each order; the mart is shaped for the question analysts ask.

CREATE VIEW staging.orders AS
SELECT DISTINCT ON (order_id) *
FROM (
    SELECT (payload->>'id')::bigint           AS order_id,
           payload->>'customer'               AS customer,
           (payload->>'total')::numeric(10,2) AS amount,
           (payload->>'ts')::timestamptz      AS ordered_at,
           lower(payload->>'status')          AS status
    FROM raw.orders
) AS typed
ORDER BY order_id, ordered_at DESC;

CREATE VIEW marts.fct_daily_sales AS
SELECT (ordered_at AT TIME ZONE 'UTC')::date AS order_date,
       count(*)    AS orders,
       sum(amount) AS revenue
FROM staging.orders
WHERE status = 'paid'
GROUP BY 1;

SELECT * FROM marts.fct_daily_sales ORDER BY order_date;

 order_date | orders | revenue
------------+--------+---------
 2026-10-05 |      2 |   72.00

Two things show why teams like ELT. The extra coupon field on order 3 landed without anyone changing the loader; it is in raw.orders the day someone wants it. And when the business decides that cancelled orders should be counted separately, you change a view and every historical day is recomputed from raw, with no re-extraction from the source. In production the models would usually be materialised as tables (typically by dbt) rather than views, but the principle is the same.

When the transformation must happen first

If policy says customer identifiers must never be stored in clear text in the analytics platform, the raw layer above is not allowed. Some transformation must run before the load; that part of the pipeline is ETL. A common technique is keyed pseudonymisation: replace the identifier with an HMAC computed with a secret key held outside the warehouse. The same input always maps to the same token, so joins and distinct counts still work, but the warehouse cannot reverse it.

import hashlib
import hmac
import json

SECRET_KEY = b"load-from-a-secret-manager-not-from-code"

def pseudonymise(value: str) -> str:
    return hmac.new(SECRET_KEY, value.strip().lower().encode(), hashlib.sha256).hexdigest()[:16]

def transform_before_load(record: dict) -> dict:
    out = dict(record)
    out["customer"] = pseudonymise(record["customer"])
    out.pop("email", None)              # not needed downstream: drop it entirely
    return out

source = [{"id": "1", "customer": "asha", "email": "[email protected]", "total": "50.00"},
          {"id": "4", "customer": "Asha ", "email": "[email protected]", "total": "12.00"}]
tokens = [transform_before_load(r) for r in source]
print(sorted(tokens[0]))
print(tokens[0]["customer"] == tokens[1]["customer"], len(tokens[0]["customer"]))

['customer', 'id', 'total']
True 16

Both orders still belong to the same pseudonymous customer (the normalisation makes "Asha " and "asha" match), so “orders per customer” works, but the name and email never reach the warehouse. A plain unsalted hash would not be enough: names and emails are easy to guess, so anyone could hash a list of candidates and match them. Pseudonymised data is generally still personal data under GDPR, so access control and retention rules still apply.

After this step the rest of the pipeline can be ordinary ELT: light, mandatory transformation on the way in, modelling after load. This hybrid is sometimes called EtLT, and it is what most real pipelines look like.

How to decide

Ask these questions in order:

1. Do compliance or privacy rules require transformation before storage? If yes, transform (at least that part) before load.
2. Where is compute cheapest and most scalable? If the warehouse scales well, push SQL transformations there.
3. Do you need to reprocess history? Keeping raw data (ELT) makes backfills far easier.
4. Is the data SQL-friendly? Tabular and semi-structured data suit ELT; binary formats and heavy non-SQL processing suit a code step.
5. Who maintains the transformations? SQL-first teams favour ELT; teams with strong software-engineering practice may accept code-based ETL.

Related terms you will hear

• Reverse ETL: copying modelled data from the warehouse back into operational tools (CRM, marketing, support), the opposite direction from classic pipelines.
• Zero-ETL: managed integrations that replicate an operational database into an analytics store without a pipeline you build. Transformation still has to happen somewhere; it usually becomes ELT in the target.
• Streaming ETL: transformation applied to events in flight, for example in Flink or Spark Structured Streaming, before they land. See batch vs streaming.

Common mistakes

1. Treating ELT as “dump everything and clean it later” with no staging layer or tests.
2. Loading personal data raw when policy requires it to be masked first.
3. Using an unsalted hash as “anonymisation” for guessable values such as emails.
4. Choosing ETL tooling out of habit when SQL in the warehouse would be simpler.
5. Forgetting that raw storage grows; retention and deletion rules apply to the raw layer too.

Interview relevance

“ETL versus ELT” is a classic question. A strong answer states the definition, gives two or three deciding factors (compliance, compute location, reprocessing needs) and admits that real systems often combine both: mandatory masking or filtering before load, modelling in SQL after it. Expect follow-ups on PII (“how would you keep emails out of the warehouse but still count unique customers?”) and on reprocessing (“what happens when a business rule changes?”). Practise with ETL vs ELT: deciding factors and handling PII in pipelines.

Key takeaway

ETL and ELT are about where transformation runs. Keep raw data and model in SQL where you can, transform before load where you must (privacy, format, volume), and expect most pipelines to be a hybrid.