ETL vs ELT: How Big Data Changed the Way We Load Data

ETL vs ELT is a question of order. Do you reshape the data before it lands in the platform, or after? ETL transforms first and loads clean rows. ELT loads the raw data first and transforms it inside the platform, usually with SQL.

Three Steps, Two Orders

Both patterns use the same three steps. Extract pulls data out of a source, such as an order system or an app. Transform cleans it, fixes types and joins it to other data. Load writes it into the place where people will query it.

In ETL, the transform runs on a separate engine, such as an SSIS package, a Spark job or a script. Only clean rows reach the warehouse. In ELT, the raw data lands first, exactly as it arrived, and the transform runs inside the platform as SQL.

Why the Order Changed

The ETL vs ELT debate started with hardware. Cost and team skills played a part too. Older warehouses were hard to scale, so their compute was precious. Teams did the heavy work elsewhere and sent in only what the warehouse needed. That made sense when every extra query slowed everyone down.

Modern platforms scale out. Spark clusters, lakehouses and cloud warehouses add compute when you ask for it, and storage is easy to add. Once the platform can do the work, a separate transform server is one more hop for the data.

Where the Compute Runs and Where the Raw Copy Lives

The diagram below shows two lanes. The top lane is ETL. Data is extracted, then a separate engine transforms it, and only clean rows are loaded. The raw data stays behind at the source or in a temporary staging area. The bottom lane is ELT. Data is extracted and loaded as is. Then the transform runs inside the platform as SQL. The raw copy lives in the platform, next to the shaped tables, and the demo below uses this lane.

The raw copy is where the two part ways. With ETL, a rule that’s wrong is already baked into the loaded rows. With ELT, you fix the rule and run the transform again from the raw copy.

When Each One Fits

Neither side wins every time in ETL vs ELT. The right choice depends on the data, the rules and the platform.

ETL fits when data must be changed before it lands. Masking card numbers or removing personal fields before they reach the platform is a good example. It also fits when the target is small and strict. Parsing binary files is another case, because SQL is a poor tool for it.

ELT fits when sources change a lot and you want to replay history with new rules. It also fits when analysts know SQL and the platform has the muscle. When you don’t yet know which rules you’ll need, ELT keeps your options open. The raw landing area is usually a data lake. For more on that idea, see Data Lake vs Data Warehouse: What Each One Is For.

ELT in T-SQL

The demo uses a bakery with two stores that send orders as JSON. The script creates a small database called SqlBigDataElt, used only here. The first step is the L in ELT. Each payload lands exactly as it arrived, with no checks beyond valid JSON.

IF DB_ID(N'SqlBigDataElt') IS NULL CREATE DATABASE SqlBigDataElt;
GO
USE SqlBigDataElt;
GO
DROP TABLE IF EXISTS dbo.RawOrder, dbo.OrderLine, dbo.OrderReject;
DROP VIEW IF EXISTS dbo.StoreRevenue;
CREATE TABLE dbo.RawOrder
(
    RawID int IDENTITY(1,1) PRIMARY KEY,
    LoadedAt datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
    Payload json NOT NULL
);
INSERT INTO dbo.RawOrder (Payload)
VALUES
(N'{"orderId":101,"store":"Seattle","lines":[{"sku":"sourdough","qty":2,"price":6.50},{"sku":"oat bar","qty":3,"price":2.25}]}'),
(N'{"orderId":102,"store":"Portland","lines":[{"sku":"sourdough","qty":1,"price":6.50}]}'),
(N'{"orderId":103,"store":"Seattle","lines":[{"sku":"bagel","qty":"two","price":3.00}]}'),
(N'{"orderId":104,"store":"Portland","lines":[{"sku":"oat bar","qty":4,"price":2.25},{"sku":"bagel","qty":2,"price":3.00}]}');

Order 103 has a problem. Its quantity is the word two, not the number 2. A strict ETL step would have to decide what to do with it before loading. Here it landed. Next come the shaped tables, a table for rejected lines, and a view that totals revenue per store.

CREATE TABLE dbo.OrderLine
(
    OrderID int NOT NULL,
    Store nvarchar(30) NOT NULL,
    Sku nvarchar(30) NOT NULL,
    Qty int NOT NULL,
    Price decimal(6,2) NOT NULL,
    PRIMARY KEY (OrderID, Sku)
);
CREATE TABLE dbo.OrderReject
(
    RawID int NOT NULL,
    OrderID int NOT NULL,
    Sku nvarchar(30) NOT NULL,
    QtyText nvarchar(20) NULL
);
GO
CREATE VIEW dbo.StoreRevenue AS
SELECT Store, COUNT(*) AS Lines, SUM(Qty * Price) AS Revenue
FROM dbo.OrderLine
GROUP BY Store;

Now the T in ELT. OPENJSON opens each payload, and a second OPENJSON opens its list of lines. TRY_CAST turns the quantity into a number, or returns NULL when it can’t. Good lines go to OrderLine, and the rest go to OrderReject.

INSERT INTO dbo.OrderLine (OrderID, Store, Sku, Qty, Price)
SELECT o.OrderID, o.Store, l.Sku, TRY_CAST(l.QtyText AS int), l.Price
FROM dbo.RawOrder AS r
CROSS APPLY OPENJSON(r.Payload) WITH (OrderID int '$.orderId', Store nvarchar(30) '$.store', Lines nvarchar(max) '$.lines' AS JSON) AS o
CROSS APPLY OPENJSON(o.Lines) WITH (Sku nvarchar(30) '$.sku', QtyText nvarchar(20) '$.qty', Price decimal(6,2) '$.price') AS l
WHERE TRY_CAST(l.QtyText AS int) IS NOT NULL;

INSERT INTO dbo.OrderReject (RawID, OrderID, Sku, QtyText)
SELECT r.RawID, o.OrderID, l.Sku, l.QtyText
FROM dbo.RawOrder AS r
CROSS APPLY OPENJSON(r.Payload) WITH (OrderID int '$.orderId', Lines nvarchar(max) '$.lines' AS JSON) AS o
CROSS APPLY OPENJSON(o.Lines) WITH (Sku nvarchar(30) '$.sku', QtyText nvarchar(20) '$.qty') AS l
WHERE TRY_CAST(l.QtyText AS int) IS NULL;

SELECT RawID, OrderID, Sku, QtyText FROM dbo.OrderReject;

SELECT Store, Lines, Revenue FROM dbo.StoreRevenue ORDER BY Store;

Five lines loaded and one was rejected. The reject table holds order 103 with its text quantity. Revenue per store comes from the five good lines.

Seattle looks low, because order 103 is missing. With ELT you don’t edit the data. You change the rule and run the load again. The raw copy is still there, so the rule below also accepts the word two.

TRUNCATE TABLE dbo.OrderLine;
TRUNCATE TABLE dbo.OrderReject;
INSERT INTO dbo.OrderLine (OrderID, Store, Sku, Qty, Price)
SELECT o.OrderID, o.Store, l.Sku, COALESCE(TRY_CAST(l.QtyText AS int), CASE l.QtyText WHEN N'two' THEN 2 END), l.Price
FROM dbo.RawOrder AS r
CROSS APPLY OPENJSON(r.Payload) WITH (OrderID int '$.orderId', Store nvarchar(30) '$.store', Lines nvarchar(max) '$.lines' AS JSON) AS o
CROSS APPLY OPENJSON(o.Lines) WITH (Sku nvarchar(30) '$.sku', QtyText nvarchar(20) '$.qty', Price decimal(6,2) '$.price') AS l
WHERE COALESCE(TRY_CAST(l.QtyText AS int), CASE l.QtyText WHEN N'two' THEN 2 END) IS NOT NULL;

SELECT OrderID, Store, Sku, Qty, Price, Qty * Price AS LineTotal FROM dbo.OrderLine ORDER BY OrderID, Sku;

SELECT Store, Lines, Revenue FROM dbo.StoreRevenue ORDER BY Store;

SELECT COUNT(*) AS RawRows FROM dbo.RawOrder;

Now all six lines are there, and order 103 shows 2 bagels for a line total of 6.00. The revenue view moves Seattle from 19.75 to 25.75, with Portland unchanged at 21.50. The raw table still holds all four payloads. In real work the rule would be a small lookup table of words to numbers. Another fix is a request back to the source.

Does ELT Make a Mess?

Critics say ELT is a polite name for dumping everything and sorting it out later. There’s truth in that. A raw landing area with no owner turns into a swamp, and nobody trusts what comes out of it.

ELT doesn’t remove the rules. It moves them into SQL that you can version, test and run again. Many teams keep both: ETL for sensitive fields, ELT for the rest. Load patterns for a landing area are covered in Data Ingestion: Batch Loads, Change Data Capture and Streams.

What to Remember

In the ETL vs ELT choice, ETL transforms before the load and keeps the platform clean. ELT loads first, keeps the raw copy and transforms inside the platform. The raw copy is what lets you fix a rule and replay it.

Before you approve a pipeline, find out where the raw copy lives and who can read it. If the answer is nowhere, the first bad rule becomes permanent. When you finish testing, remove the example database.

USE master;
GO
ALTER DATABASE SqlBigDataElt SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SqlBigDataElt;

ELT is not a way to skip the transform, it is a way to keep the raw copy.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.