In this post we are going to cover the difference between ETL and ELT. Both are ways of moving data from source systems into a data warehouse, and the only difference in the name is the order of two letters. That order matters a lot.
ETL stands for Extract, Transform, Load: data is cleaned in a separate engine before it is loaded into the warehouse. ELT stands for Extract, Load, Transform: raw data is loaded into the warehouse first, and transformed there, usually with SQL.
We will walk through how each one works, then run a real ELT transform in SQL: load a messy CSV export as is, and clean it inside the warehouse.
Question 4 of the Real DWH Question and Answers series on TechBrothersIT.
The interview answer
ETL (Extract, Transform, Load) transforms data in a separate engine before it is loaded into the warehouse. ELT (Extract, Load, Transform) loads raw data first, then transforms it inside the warehouse, usually with SQL. Modern cloud warehouses favor ELT because their compute scales on demand.
How ETL works: transform, then load
ETL is the classic approach. It has three steps, in this order:
- Extract - the pipeline reads data from the source systems, like the shop database and a CSV export from the payment provider.
- Transform - a separate ETL engine cleans the data, fixes data types, removes duplicates, joins sources together and calculates totals. Tools like SSIS, Informatica or DataStage did this for years.
- Load - only the clean, finished tables are written into the warehouse.
The benefit is that the warehouse only ever sees clean data, and sensitive fields can be masked before they arrive. The cost is that the raw data is not in the warehouse, and every new question means changing the pipeline.
How ELT works: load, then transform
ELT is the modern approach. Extract is the same, but then we load the data straight into the warehouse, exactly as it arrived. This first layer is often called raw, or staging.
Then we transform inside the warehouse, with SQL. The warehouse engine does the heavy lifting, and the clean result goes into new tables. Tools like dbt organize these SQL transformations.
Because the raw data is kept, we can fix a bug and simply re-run the transform, or build a new table for a new question, without touching the sources again. This fits cloud warehouses like Snowflake, BigQuery, Redshift and Databricks, where storage is cheap and compute scales up when you need it.
Let's create some sample data
This setup is the Load step of ELT. We create a raw_orders table where every column is text, and load a CSV export into it as is. We also create an empty, properly typed orders_clean table for the analysts.
DROP TABLE IF EXISTS raw_orders;
DROP TABLE IF EXISTS orders_clean;
-- ELT step 1 (Load): the CSV export is loaded AS IS. Everything is text.
CREATE TABLE raw_orders (
order_id VARCHAR(20),
customer VARCHAR(50),
city VARCHAR(50),
amount VARCHAR(20),
order_date VARCHAR(20)
);
INSERT INTO raw_orders VALUES
('1001', 'Sara Khan ', ' toronto', '35.00', '2026-01-05'),
('1002', 'Omar Ali', 'Dallas ', '25.00', '2026-01-09'),
('1002', 'Omar Ali', 'Dallas ', '25.00', '2026-01-09'),
('1003', 'Lina Park', 'seattle', '30', '2026-02-02'),
('1004', 'Mei Chen', 'VANCOUVER', '27.00', '2026-03-03');
-- the clean, typed table the analysts will use
CREATE TABLE orders_clean (
order_id INT,
customer VARCHAR(50),
city VARCHAR(50),
amount DECIMAL(10,2),
order_date DATE
);Let's look at the raw data closely.
SELECT * FROM raw_orders ORDER BY 1;| order_id | customer | city | amount | order_date |
|---|---|---|---|---|
| 1001 | Sara Khan | toronto | 35.00 | 2026-01-05 |
| 1002 | Omar Ali | Dallas | 25.00 | 2026-01-09 |
| 1002 | Omar Ali | Dallas | 25.00 | 2026-01-09 |
| 1003 | Lina Park | seattle | 30 | 2026-02-02 |
| 1004 | Mei Chen | VANCOUVER | 27.00 | 2026-03-03 |
There is a lot wrong here. Sara Khan has a trailing space and toronto a leading one, the cities are in mixed case, Lina's amount is 30 with no decimals, every column is stored as text, and order 1002 appears twice.
Step 1: Transform the raw data with SQL
Now the Transform step, inside the warehouse, in plain SQL. DISTINCT removes the duplicate, TRIM removes the spaces, UPPER standardizes the city, and CAST turns text into proper numbers and dates.
INSERT INTO orders_clean
SELECT DISTINCT
CAST(order_id AS INT),
TRIM(customer),
UPPER(TRIM(city)),
CAST(amount AS DECIMAL(10,2)),
CAST(order_date AS DATE)
FROM raw_orders;Now let's check the result.
SELECT * FROM orders_clean ORDER BY order_id;| order_id | customer | city | amount | order_date |
|---|---|---|---|---|
| 1001 | Sara Khan | TORONTO | 35.00 | 2026-01-05 |
| 1002 | Omar Ali | DALLAS | 25.00 | 2026-01-09 |
| 1003 | Lina Park | SEATTLE | 30.00 | 2026-02-02 |
| 1004 | Mei Chen | VANCOUVER | 27.00 | 2026-03-03 |
We get a clean, typed table: four orders, one per row. Order 1002 appears only once, every city is upper case with no spaces, and Lina's amount is now 30.00. The raw table is still in the warehouse, so if we change a rule tomorrow, we just run the transform again.
Step 2: Analysts query the clean table
Because the data is clean and typed, the report is a simple GROUP BY.
SELECT city, SUM(amount) AS sales
FROM orders_clean
GROUP BY city
ORDER BY sales DESC;| city | sales |
|---|---|
| TORONTO | 35.00 |
| SEATTLE | 30.00 |
| VANCOUVER | 27.00 |
| DALLAS | 25.00 |
Each city appears once: TORONTO 35.00, SEATTLE 30.00, VANCOUVER 27.00 and DALLAS 25.00. Dallas is 25.00, not 50.00, because the duplicate order 1002 was removed in the transform. The analyst did not need to know about any of the problems in the raw file.
ETL vs ELT side by side
Here is the comparison to walk through in the interview.
| ETL | ELT | |
|---|---|---|
| TRANSFORM | Separate engine, before loading | Inside the warehouse, after loading |
| RAW DATA | Usually not kept in the warehouse | Kept: easy to re-process |
| COMPUTE | ETL servers or service | Warehouse compute (scales) |
| TOOLS | SSIS, Informatica, DataStage, ADF | Snowflake, BigQuery, Databricks + dbt |
| SENSITIVE DATA | Mask before loading | Mask and control access in the warehouse |
Choosing between them
- ELT - a cloud warehouse or lakehouse, lots of data, and analysts who know SQL. Today, that is most new projects.
- ETL - sensitive data must be removed before it lands, the target system has limited compute, or you already run a mature ETL platform.
- Both - real pipelines often do light cleanup on the way in and heavy modeling in the warehouse. Some people call that ETLT.
Don't say "ETL is dead." It is not. The real question is where the transform runs best.
Follow-up questions interviewers ask
Is ETL dead?
No. It still fits when sensitive data must be removed before landing, when target compute is limited, or with mature ETL platforms.
What is ETLT?
A mix: light cleanup during extract/load, heavy modeling inside the warehouse.
Where does dbt fit?
dbt organizes, tests and documents the SQL transformations of an ELT pipeline inside the warehouse.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #4 - ETL vs ELT
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q04; SET search_path TO dwh_q04;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS raw_orders;
DROP TABLE IF EXISTS orders_clean;
-- ELT step 1 (Load): the CSV export is loaded AS IS. Everything is text.
CREATE TABLE raw_orders (
order_id VARCHAR(20),
customer VARCHAR(50),
city VARCHAR(50),
amount VARCHAR(20),
order_date VARCHAR(20)
);
INSERT INTO raw_orders VALUES
('1001', 'Sara Khan ', ' toronto', '35.00', '2026-01-05'),
('1002', 'Omar Ali', 'Dallas ', '25.00', '2026-01-09'),
('1002', 'Omar Ali', 'Dallas ', '25.00', '2026-01-09'),
('1003', 'Lina Park', 'seattle', '30', '2026-02-02'),
('1004', 'Mei Chen', 'VANCOUVER', '27.00', '2026-03-03');
-- the clean, typed table the analysts will use
CREATE TABLE orders_clean (
order_id INT,
customer VARCHAR(50),
city VARCHAR(50),
amount DECIMAL(10,2),
order_date DATE
);
-- Step 1: ELT step 2 (Transform): clean the raw data with SQL inside the warehouse
INSERT INTO orders_clean
SELECT DISTINCT
CAST(order_id AS INT),
TRIM(customer),
UPPER(TRIM(city)),
CAST(amount AS DECIMAL(10,2)),
CAST(order_date AS DATE)
FROM raw_orders;
-- check the result
SELECT * FROM orders_clean ORDER BY order_id;
-- Step 2: Analysts query the clean table
SELECT city, SUM(amount) AS sales
FROM orders_clean
GROUP BY city
ORDER BY sales DESC;
Key points
- ETL: transform in a separate engine, then load clean data
- ELT: load raw data first, transform inside the warehouse with SQL
- ELT keeps raw data, so transforms can be re-run any time
- ETL can mask sensitive data before it lands
- Cloud projects mostly use ELT; many pipelines mix both
Question 5 — What is a data lake? What is a data lake, and why put it in front of the warehouse?
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.