ETL vs ELT

A data pipeline is a program that copies data from where it is made to where people use it. It runs again and again, on a schedule.

The shop in this chapter keeps its orders in an app database. Every night, a pipeline copies them to a warehouse. A warehouse is a database built for analysis.

The pipeline has three jobs: Extract, Transform, Load. People write them as ETL or ELT. The only difference is the order.

In this chapter you will learn:

1. What each of the three jobs moves.
2. How the same daily report is built with ETL, and then with ELT.
3. Why a raw table may hold duplicates, and a curated table may not.
4. Why ELT is common in cloud warehouses.
5. When ETL is still the better choice.

Extract means you read rows out of the source. Here the source is the shop database.

Transform means you change the rows. You remove bad rows, fix values and add up totals.

Load means you write the rows into the warehouse.

ETL does the jobs in that order. ELT swaps the last two.

Look at the bold box in each line of the picture. Only the Transform box changes place.

This looks like a small change, but three things move with the T:

• What lands in the warehouse. ETL lands clean rows. ELT lands raw rows, exactly as the source had them.
• Where the work runs. ETL transforms in a program outside the warehouse. ELT transforms inside it, with SQL.
• What you can fix later. In ELT, a wrong rule is a new query on rows you already have.

The next two sections show these three things on one report: daily paid revenue. That is the total of paid orders for each day.

In ETL, a program reads the orders, applies the rules, and loads only the result. The rules are: count an order only if its status is paid and it has an amount.

Here the program is Python. Here, extract and load are small helper functions. They read from the shop and write to the warehouse.

The first line reads the orders.

The loop is the transform. The last line is the load.

rows = extract("SELECT order_id, status, amount, order_date FROM orders")

totals = {}
for order_id, status, amount, order_date in rows:
    if status == "paid" and amount is not None:
        totals[order_date] = totals.get(order_date, 0) + amount

load("daily_paid_revenue", totals)

The loop skips order 103 because its status is refunded. It skips order 105 because its status is pending and its amount is NULL, which means unknown.

In Python, NULL arrives as None. The other four orders each have their own date, so each date has one total.

The warehouse now holds four tidy rows. Nobody who uses the warehouse can see orders 103 and 105.

In ELT, the pipeline copies the rows as they are. The first step is a plain copy, with no rules. It only adds a loaded_at column, which records when the row arrived.

The table that holds this copy is called a raw table. After the copy, the warehouse holds all six orders.

The query below is the transform. It runs inside the warehouse:

SELECT order_date, SUM(amount) AS paid_revenue
FROM raw_orders
WHERE status = 'paid'
GROUP BY order_date
ORDER BY order_date;

The WHERE line keeps the four paid orders. GROUP BY puts them in groups by date, and SUM adds up each group. The answer is the same as in ETL.

Now finance asks the question from the last section: how much was refunded? The rows are still in the warehouse, so one new query answers it.

SELECT SUM(amount) AS refunded_total
FROM raw_orders
WHERE status = 'refunded';

This is what moved with the T. You spend money to store rows nobody asked about yet. In return, a new question or a wrong rule costs one query.

A raw table is append-only. Append-only means the pipeline only adds rows. It never edits or deletes them.

Suppose the job loads order 106 and then fails before it can finish. The scheduler retries only that last step.

So order 106 arrives a second time. Look at the last two rows of the input table:

SELECT order_date, SUM(amount) AS paid_revenue
FROM raw_orders
WHERE status = 'paid'
GROUP BY order_date
ORDER BY order_date;

The raw table is allowed to hold the duplicate. It records what arrived, and a duplicate did arrive. If you deleted rows here, you could not see what really happened.

A curated table is a cleaned table that people query. It must not hold duplicates. You build it from raw and keep the newest copy of each order.

CREATE TABLE orders_clean AS
SELECT order_id, customer_id, status, amount, order_date
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY order_id ORDER BY loaded_at DESC
         ) AS rn
  FROM raw_orders
) AS ranked
WHERE rn = 1;

ROW_NUMBER gives each copy of an order a number. The newest copy gets 1. The WHERE line keeps only number 1.

Run the same daily query on orders_clean instead of raw_orders. The 21st is 80.00 again.

Many teams now use ELT. Two things about modern cloud warehouses make it practical.

1. Storage is separate from compute. Compute is the power that runs queries. In warehouses such as BigQuery and Snowflake, you pay for storing data and for running queries as two separate things. Keeping raw rows costs only storage. Queries on other tables do not get slower.
2. The warehouse is good at SQL. Many analysts already write SQL. They can build and fix the transform themselves, without waiting for a Python developer.

ELT also has a cost. The warehouse now holds every raw column, including data you may not want there. You must also make sure people query curated tables, not raw ones.

A popular tool for the T in ELT is dbt.

In dbt, a model is one SELECT query saved in a file. dbt builds a table from each model. It runs them inside the warehouse, in the right order.

Week 8 of the plan gives you practice with it.

ETL is the better choice in two cases. The first is privacy.

Say the warehouse must not hold customer names. In ELT, the names would land in a raw table first. In ETL, you drop the name before the load, so it never arrives:

rows = extract("SELECT customer_id, name, country FROM customers")

safe = [(customer_id, country) for customer_id, name, country in rows]

load("customers_safe", safe)

The warehouse never held the names, so nobody can copy them out of it by mistake. Rules about personal data differ by country and by company. Ask your privacy team which fields may land.

The second case is heavy work that SQL cannot do well. Examples are reading image files or running a machine learning model. A Python program does this work better, so it runs before the load.

A team does not have to pick one order for everything. Ask two questions about each dataset.

Is any of it data that must not land? Does the work need more than SQL?

One pipeline can use both. It can drop the name before the load. Then it loads the rest raw and transforms it with SQL in the warehouse.

Real pipelines rarely match the pictures exactly. Many tools do a small clean step while they load. For example, they change a date format, or turn a CSV file into the own format of the warehouse.

Some warehouses can read files straight from cloud storage, a big online folder for files. Then the load step is very small.

The question to ask does not change. Ask what lands in the warehouse, where the transform runs, and who can fix a wrong rule.

The three jobs are the same in both. ETL transforms before the load. ELT transforms after it.

• ETL lands clean rows. Dropped rows never reach the warehouse.
• ELT lands raw rows and transforms them with SQL in the warehouse. A wrong rule is a new query.
• A raw table may hold duplicates. A curated table keeps one row per order.
• ETL still wins when data must not land, or when the work needs more than SQL.
• dbt is a popular tool for the T in ELT.

Each exercise opens on the pipeline canvas. You build a pipeline and it is checked against the rules of that story.

Before you build, write one sentence: "The transform sits here because of this." Then build it.