ETL and ELT
Every PedalCity pipeline does three things: it takes data out of a source, reshapes it, and puts it somewhere useful. The only question is the order of the last two. That order has a name, and it shapes the whole platform.
The rule
ETL shapes data before loading; ELT loads raw data first, then shapes it inside.
The three steps:
- Extract: read data from the source, such as the app database, the payments API or sensor files.
- Transform: clean, deduplicate, convert types and time zones, join, apply business rules and summarise.
- Load: write the result into the target, usually a warehouse or lake.
In ETL, transformation happens on a separate processing server before loading. Only finished tables reach the warehouse. This was the norm when warehouse compute was expensive.
In ELT, raw data is loaded first into staging tables, and transformation runs inside the warehouse, usually as SQL:
-- T happens after L, inside the warehouse
CREATE TABLE fact_rides AS
SELECT r.ride_id,
CAST(r.ended_at AT TIME ZONE 'Asia/Kolkata' AS DATE) AS ride_date,
r.start_station AS start_station_id,
r.fare,
EXTRACT(EPOCH FROM r.ended_at - r.started_at) / 60 AS duration_min
FROM stg_rides AS r
WHERE r.ended_at IS NOT NULL;Modern cloud warehouses have cheap, scalable compute, so ELT is now the common default. Tools such as dbt manage these SQL transformations as version-controlled code. ELT's biggest gift is that the raw data stays. When a business rule changes, you rerun the SQL over the raw staging data instead of extracting months of history again.
ETL still wins in some cases:
- Sensitive data that must never land in the warehouse, such as full card numbers, which should be masked before loading.
- Heavy non-SQL work, such as parsing binary sensor files or images.
- Targets with little compute of their own.
The shortcut
Ask "Is it allowed, and useful, to keep the raw data in the target?" If yes → ELT, and keep the raw data. If the raw data must not land there, or cannot be processed there → transform first with ETL. Many platforms mix the two per source.
rides
Harmless raw data that SQL can handle. ELT: load to
stg_rides, then transform in SQL.
Drills
Answer fast — trust the rule.
In ELT, where does transformation happen?
What is ELT's biggest advantage when a business rule changes?
Payment records contain full card numbers that must never be stored in the warehouse. Which pattern fits?
Which of these is a transform step?
Remember
- ETL shapes before loading; ELT loads raw first, then shapes inside the warehouse.
- Ask whether raw data may and can live in the target.
- ELT keeps raw data for replays; ETL protects data that must not land raw.