What is ETL? ETL vs ELT explained for beginners, with a runnable example

A shop's orders live in an app database. The finance team wants a clean report in a data warehouse. Someone has to take the data from the first place, tidy it up, and put it in the second place. A data warehouse is simply a database built for reports and analysis, rather than for running the shop. Data engineers call that job ETL or ELT, depending on the order in which they do the steps. It is the most common idea in the whole field, and the words are easy once you see them side by side.

The 3 letters

• E is for Extract. Copy the data out of where it lives: a database, a file, an API.
• T is for Transform. Clean it and reshape it: fix text, change types, remove duplicates, join tables, calculate columns.
• L is for Load. Put the result into the place where people will use it, usually a data warehouse.

ETL: transform before you load

In classic ETL, a separate tool or server does the transforming. The data is extracted, cleaned on that middle server, and only the finished result is loaded into the warehouse. The warehouse never sees the messy version.

This made sense when warehouses were expensive and slow, so you did not want to waste their power on cleaning. It also helps when you must remove private data, such as card numbers, before it ever lands in the warehouse.

ELT: load first, transform inside

In ELT, the raw data is loaded straight into the warehouse, and the transforming happens there, using SQL. Today's cloud warehouses such as Snowflake, BigQuery, Redshift and Databricks are powerful and storage is cheap, so it is easier to land everything and clean it in place.

The big benefit is that the raw data is kept. If you find a mistake in your cleaning logic next week, you fix the SQL and run it again over the raw data that is still there. With ETL, the raw data may be gone.

A small ELT example you can run

Here is the load step done for you: five sales rows loaded exactly as they arrived, including the mess. Every column is text, because the load step does not decide what anything means. The queries ran in DuckDB, and each output is exactly what it printed.

CREATE TABLE raw_sales (
  sale_id  VARCHAR,
  customer VARCHAR,
  amount   VARCHAR,
  sold_on  VARCHAR,
  status   VARCHAR
);

INSERT INTO raw_sales VALUES
  ('S1', 'asha ',  ' 1,200', '2026-03-01', 'PAID'),
  ('S2', 'Ravi',   '800',    '2026-03-01', 'paid'),
  ('S2', 'Ravi',   '800',    '2026-03-01', 'paid'),
  ('S3', 'Meena',  'n/a',    '2026-03-02', 'PAID'),
  ('S4', 'Kiran',  '2,500',  '2026-03-02', 'refunded');

SELECT * FROM raw_sales;

sale_id | customer | amount | sold_on    | status
--------+----------+--------+------------+---------
S1      | asha     |  1,200 | 2026-03-01 | PAID
S2      | Ravi     | 800    | 2026-03-01 | paid
S2      | Ravi     | 800    | 2026-03-01 | paid
S3      | Meena    | n/a    | 2026-03-02 | PAID
S4      | Kiran    | 2,500  | 2026-03-02 | refunded
(5 rows)

Look at the problems. The name asha has a trailing space and no capital. The amounts have commas, and one is n/a. S2 arrived twice. The status is written two ways. Now the transform step, run inside the database with SQL: trim and fix the names, turn the amounts into numbers, convert the dates, lower-case the status, and keep 1 row per sale.

CREATE TABLE clean_sales AS
SELECT sale_id, customer, amount, sold_on, status
FROM (
  SELECT sale_id,
         UPPER(LEFT(TRIM(customer), 1)) || LOWER(SUBSTR(TRIM(customer), 2)) AS customer,
         TRY_CAST(REPLACE(TRIM(amount), ',', '') AS INTEGER)                AS amount,
         CAST(sold_on AS DATE)                                              AS sold_on,
         LOWER(status)                                                      AS status,
         ROW_NUMBER() OVER (PARTITION BY sale_id ORDER BY sale_id)          AS rn
  FROM raw_sales
) AS cleaned
WHERE rn = 1;

SELECT * FROM clean_sales ORDER BY sale_id;

sale_id | customer | amount | sold_on    | status
--------+----------+--------+------------+---------
S1      | Asha     | 1200   | 2026-03-01 | paid
S2      | Ravi     | 800    | 2026-03-01 | paid
S3      | Meena    | NULL   | 2026-03-02 | paid
S4      | Kiran    | 2500   | 2026-03-02 | refunded
(4 rows)

4 rows, one per sale. The names are tidy, the amounts are real numbers, and the duplicate S2 is gone. TRY_CAST turns a value it cannot convert, such as n/a, into NULL instead of crashing the whole job. Now the report finance wanted becomes simple:

SELECT SUM(amount) AS paid_revenue
FROM clean_sales
WHERE status = 'paid';

paid_revenue
------------
2000
(1 row)

2000, which is the 1200 from S1 plus the 800 from S2. Order S3 is marked paid as well, but its amount became NULL, so SUM skipped it. That is worth noticing: a real pipeline would flag a paid order with no amount instead of letting it pass quietly. And the raw table is still there, untouched:

SELECT sale_id, amount FROM raw_sales WHERE sale_id = 'S3';

sale_id | amount
--------+-------
S3      | n/a
(1 row)

If the business later decides that n/a should count as zero, you change one line of the transform and rebuild clean_sales from the raw data. That is the practical advantage of ELT.

ETL vs ELT at a glance

Which one will I meet?

Both. Many companies use ELT for analytics and still use ETL where data must be filtered or masked first. The ideas also appear in medallion architecture, where raw data lands in a bronze layer and is refined into silver and gold, which is ELT organised into layers. Tools such as dbt are built around the "T" of ELT: you write the transform as tested SQL models.

You do not choose ETL or ELT once. You choose per pipeline, depending on where the cleaning is easiest and safest. The skill to build first is the same for both: writing the transform correctly, which is mostly SQL. Read what is a data pipeline for how these steps run on a schedule, and practise a cleaning step with the free problem Deduplicate events with ROW_NUMBER.