Lesson 7 of 15 · Lab

ETL vs ELT: what's the difference?

ETL extracts data, transforms it, then loads it for use. ELT loads it first and transforms it where it lands, usually in a warehouse. The order is a practical choice about where the work runs, and the test for either is the same: run it again and the answer holds.

ETL and ELT side by side
ETLELT
OrderTransform, then loadLoad, then transform
Transform runs inA separate tool or flowThe warehouse, often in SQL
Raw data in the targetUsually left outUsually kept, so you can rework it
SuitsFiles and small sources, or removing sensitive fields before they landA warehouse with capacity to spare, and questions that keep changing

Neither order guarantees correct data. Both still need a definition and a check.

At fictional Vandelay Coffee Supply, Q3's €11,478 is settled, and next quarter someone has to build it again. This flow holds the 13 finished rows, and September's 7 are about to arrive a second time.

Sort and Unique are already in place. What will net, the total net value, read after the repeat?

The same. Unique keeps one row per invoice, so a repeated file can't add anything. By hand, the same mistake would add thousands, and nobody would see it happen.

Load the same file twice

  1. This flow runs live in Strata, Oplema's browser app for data prep. Select Summarize, the last block, and note net and rows.
  2. Drag from the right dot of the lower Manual Input to the left dot of Union. Drop on the dot, not the block.
  3. Select Union. Its preview has 20 rows now.
  4. Select Summarize again.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

What a repeat run should do

Union grew from 13 rows to 20. net stays at 11,478 and rows at 13.

A repeatable flow survives its own mistakes

The three steps at Vandelay
StepAt Vandelay
ExtractSydney exports invoices, Don Tremendous supplies the register
TransformParse, clean, keep one row per invoice, filter, join, net off rebates
LoadPublish the table for dashboards

Without Unique, this mistake adds €6,030 to a total nobody is checking. Hand-built reports fail that way, and it's why the flow matters more than the tool. You can rerun it when a file arrives late, or arrives twice. In Vista, Oplema's browser app for dashboards, the load step is a Source block, which hands the prepared table to a dashboard.

Snapshot or append: decide what a run does

A snapshot load replaces the whole table each run, so a repeat does no harm and old values disappear. An append load adds each new batch, which keeps history and needs a key to stop a repeated batch counting twice.

Use a snapshot when the source can restate the past, as Vandelay's invoice statuses do. Use append when records never change, or when the source is too large to reload.

Common ETL and ELT mistakes

  • Editing by hand between runs. The fix disappears next quarter, and nobody knows it existed.
  • No stated period. Say which snapshot a run reads, or a late file changes the answer silently.
  • Treating a finished run as a right answer. A later lesson on checking a dashboard after a refresh shows why.

In SQL, a plain INSERT doubles the same way, and a MERGE on invoice_id holds. An ETL tool can't save you from a missing key. Excel's Power Query covers the same ground on a small scale, since Refresh reruns its recorded steps, which beats pasting data in.