Skip to content
Courses

ETL (extract, transform, load)

ETL (extract, transform, load) copies data out of the systems where it's created, cleans and reshapes it, and loads it into a data warehouse so it can be analyzed together.

Select any words here to ask your Maestro about them.

Every night, an ETL job appends the day's orders to a table in the data warehouse. Tuesday's load failed after appending 106 of its 212 orders, and on Wednesday someone reran it. What does the warehouse show for Tuesday?

An online store that copies its orders to its data warehouse every night. Example data.

What it is: ETL stands for extract, transform, load. You extract data from the systems where it's created (the online store, the CRM, accounting), clean and reshape it, and load it into a data warehouse where it can be analyzed together.

ETL vs. ELT: in ELT you load the raw data first and transform it inside the warehouse with SQL. That's the more common setup today, and tools like dbt handle the transform step.

Where it breaks: a job that runs every night can fail quietly: it duplicates rows when rerun, or skips rows that arrive late. Reconcile the totals against the source system after every load.

Build a pipeline like this in the course Data Modeling for AI Agents.

How it's used

The three letters

Nightly orders ETL

  1. marked: Extract: read yesterday's orders from the store's APIIt copies the data without touching the system the store runs on.
  2. Transform: drop test orders, convert dates, one row per orderThis is where the rules everyone will use get decided.
  3. Load: write them to the orders table in the data warehouseAppend, replace or merge on a key: the choice that matters most when a load runs twice.
Swap the last two steps and you have ELT: load the raw data, then transform it inside the warehouse.

When to look closer

Read what's new since the last run

Incremental extract

  1. marked: Tuesday 2:00 a.m. run: read orders created after the previous run

Each run reads the orders created since the last one. Which orders could it never read?

Ask your Maestro about ETL

It answers from the free course page closest to this word, the way it answers while you study.

Example

You: What does "ETL" mean, in plain words?

Your Maestro: I'll explain it with a case of yours: it sticks better than a definition. How many systems do you copy data from by hand each week?

Asking about: Answers from the page "What is exploratory data analysis, and what do I do first with a new table?"

Only this browser keeps what you ask here. How it works

Learn more

Related terms

  • Data warehouse

    A data warehouse is a database built for analysis: it brings data from many systems into one place, keeps its history and organizes it for queries.

  • dbt (data build tool)

    dbt (data build tool) turns SQL SELECT queries into tables or views in your data warehouse, runs them in order and tests the results: dbt run builds the models, dbt test checks them.

  • SQL

    SQL is the language for asking a database for data: which columns, from which table, which rows and how to summarize them. You get a table back.

  • Reconciliation

    Comparing a total you computed with the same total from an independent source, such as a bank statement or a register report, and explaining any difference.

The whole glossary