ETL vs ELT: differences, a worked example and how to choose

The only structural difference between ETL and ELT is where the transformation runs, but it decides whether you can ever change a metric definition retroactively. This guide compares the two, walks through restating 13 months of active users after a bot-filter change, explains why "load everything raw" has a privacy cost, and covers EtLT and reverse ETL.

ETL vs ELT in one sentence

ETL (extract, transform, load) cleans and reshapes data before loading it into the destination; ELT (extract, load, transform) loads raw data first and transforms it inside the destination, usually a cloud data warehouse, with SQL. The difference is where the "T" happens, and it decides how easily you can change definitions later, how much raw data you keep, and what lands in your warehouse.

Both are patterns for building a data pipeline.

How each one works

ETL

1. Extract data from sources (application database, CRM, analytics events).
2. Transform it on a separate processing server: clean, deduplicate, join, aggregate, mask sensitive fields.
3. Load the finished tables into the warehouse.

ELT

1. Extract data from sources.
2. Load it as-is (or nearly as-is) into raw tables in the warehouse.
3. Transform it with SQL models inside the warehouse into clean, analysis-ready tables, often managed with a tool like dbt.

ETL vs ELT comparison

Most mature pipelines are EtLT: mask at extraction, model in the warehouse.

Worked example: a metric definition changes

A product team counts "active users" as anyone with a tracked event. After a bot audit, they redefine it to exclude sessions flagged as bots and internal traffic, and want the last 13 months restated so year-over-year comparisons stay fair.

With ETL, the pipeline loaded only daily aggregates (active users per day by country and device). The raw events behind them were discarded after 30 days. The new definition can be applied from today, but the history cannot be rebuilt: the team can only annotate the break and compare carefully. Year-over-year growth for the next 12 months mixes two definitions.

With ELT, 13 months of raw events sit in the warehouse (about 47 million rows for a product sending 120,000 events a day). Changing one SQL model and re-running it takes minutes. Every chart shows the new definition all the way back. Storage for that raw data is small: at roughly 3–4 GB a year compressed, it costs a few dollars a month at typical object-storage prices. The real costs are warehouse compute and governance.

This is the main reason analytics teams moved to ELT: definitions change more often than anyone plans for, and only raw data lets you change them retroactively. See raw data and single source of truth.

The privacy catch, and EtLT

Loading "everything raw" also loads personal data you may not need: emails in URLs, IP addresses, free-text form fields. Under GDPR's data minimization principle, that is a liability.

Most mature pipelines are really EtLT: a light, mandatory transformation at extraction (drop or hash identifiers, truncate IP addresses, remove tokens from URLs), then load, then heavy modeling in the warehouse. Decide what must never reach the warehouse, enforce it in code, and record it in your data lineage.

Reverse ETL

Reverse ETL sends modeled data from the warehouse back into operational tools: lead scores into the CRM, churn-risk segments into email tools, account health into support software. It closes the loop so that the clean definitions built in the warehouse are used where people act. A customer data platform covers part of the same job.

How to choose

Choose ELT if:

• You use a cloud warehouse with elastic compute.
• Analysts write SQL and metric definitions evolve.
• You want to keep raw history for audits and re-processing.

Choose ETL (or EtLT with a heavy first step) if:

• Regulations require that certain data never reaches the warehouse.
• Destinations have limited compute, or downstream systems need fixed, pre-shaped files.
• Sources are messy enough that loading them raw would create huge, unusable tables.

In practice most teams combine them: EL tools for SaaS sources, a privacy-focused transformation at collection, ELT modeling in the warehouse, reverse ETL to operational tools.

Where TapCub fits

TapCub applies the privacy-critical transformations at collection, before storage: IP addresses are truncated, visitor identifiers are salted hashes, and login emails are lowercased and hashed; bot detection and channel parsing run on the way in. That makes its event export a reasonable "raw" layer for ELT: export events as gzipped JSONL with raw data export (up to 365 days per job), load them into your warehouse, and model them alongside billing and CRM data. Keep personal data out of event properties in the first place; see keep personal data out.

See also: Lakehouse vs warehouse.

Sources

• Reis, J. and Housley, M. (2022). Fundamentals of Data Engineering. O'Reilly, chapter 8.
• dbt Labs. What is ELT (extract, load, transform)?
• European Union. GDPR Article 5: principles relating to processing of personal data (data minimisation).