ETL vs ELT: the difference that actually matters

The short answer

ETL means extract, transform, load. You read data from a source, reshape or validate it in a processing step, and then write the result to a destination. The main transformation happens before the final load.

ELT means extract, load, transform. You land source data in the destination first, then use that platform’s compute to turn it into useful tables. A cloud warehouse is a common destination, although ELT is not limited to warehouses.

Real systems often mix approaches. You might remove sensitive fields before loading, then join and aggregate the remaining records inside the warehouse. Calling that ETL or ELT matters less than documenting which work runs where.

One shop, two pipelines

Imagine a small online shop. Its orders table contains an order ID, customer ID, update timestamp, status, currency, and amount in cents. A separate payments table records successful charges and refunds. The finance team wants daily net sales by currency. This is an illustrative example; no real customer data is involved.

The ETL version

A job reads changed orders and payments. In a separate processing environment, it standardizes timestamps, keeps the latest version of each order, joins payments, and applies a documented refund rule. It loads a daily summary into the reporting database.

The reporting destination receives a compact, prepared dataset. That can be helpful when its storage or compute is constrained, or when certain raw fields must never enter it. The trade-off appears when finance asks a new question. If the summary discarded the necessary detail, you need the original source or a separate retained copy to rebuild it.

The ELT version

A connector copies orders and payments into a restricted raw area of the warehouse. Transformation jobs build cleaned intermediate tables and then a daily sales model. Analysts query the model rather than the raw records.

Now a change to the refund rule can be applied to retained raw history without reading everything from the shop again. That only works if you kept enough history. A table containing today’s latest order state cannot magically reconstruct every previous change.

-- Illustrative warehouse SQL; syntax may vary.
-- amount_cents is positive for charges, negative for refunds.
SELECT
  CAST(settled_at AS DATE) AS sales_day,
  currency,
  SUM(amount_cents) / 100.0 AS net_sales
FROM cleaned_payments
WHERE payment_status = 'settled'
GROUP BY CAST(settled_at AS DATE), currency;

This deliberately small example assumes a cleaned payments model with one row per payment event and a defined reporting timezone. In a real model, also specify which currencies use a hundred minor units per major unit. Do not add different currencies together and label the result revenue without a currency conversion policy.

Compare the trade-offs

Neither approach wins automatically on speed or cost. An efficient ETL job can beat a poorly written warehouse query. ELT can simplify operations if you already operate a warehouse, but repeated full-table scans can be expensive. Compare the actual data volume, frequency, retention, and query plan.

What happens when it breaks?

Consider a retry after a timeout. The destination might have accepted the data even though the job never received confirmation. If a retry simply appends the same records, daily sales double. Both ETL and ELT need a strategy for that problem.

Use stable record identifiers, define the grain of each table, and make repeated processing safe. Depending on the system, that may mean merging by a key, replacing a partition, or tracking processed events. A successful job exit is not proof that the dataset is correct.

Late data creates another wrinkle. A refund arriving on Tuesday might change Monday’s report. Decide whether to update the original day or report the adjustment on Tuesday, then make the rule visible to readers of the table. A watermark based only on order creation time could miss that refund completely.

At minimum, check freshness, duplicate keys, required fields, and sensible totals. Alert when the pipeline is late, not just when a task crashes. Keep logs useful enough to tell an access failure from a business-rule failure.

How to choose without chasing a trend

1. Start with access boundaries. Which fields are allowed in the destination, and who may read them? If raw personal data cannot land there, remove or protect it earlier.
2. Check the destination’s strengths. Can it perform the required joins and transformations economically? If not, a separate processing step may be a better fit.
3. Plan for a rule change. Work through how you would rebuild last month’s report. Identify which history exists and how long recovery would take.
4. Match the team’s skills. SQL models can make transformations easier to review in a SQL-heavy team. Other workloads may fit a general-purpose processing engine.
5. Measure a representative workload. Include ingestion, storage, compute, operations, and recovery effort. A platform’s headline price is not your pipeline’s total cost.

For the shop in our example, ELT is a reasonable starting point if a capable warehouse already exists and raw access can be controlled. ETL may fit better if the reporting database is small or the data must be heavily filtered before entry. Those are conditional choices, not universal recommendations.

A few common questions

Is ETL outdated?

No. Transforming before loading remains useful when privacy boundaries, processing requirements, or destination limits call for it. Older and newer platforms can support either pattern.

Does ELT mean loading messy data and forgetting it?

No. A landing area is the start of the transformation workflow. Curated models still need tests, ownership, documentation, and access controls.

Is streaming a third alternative?

Streaming describes when and how continuously data is processed. ETL and ELT describe the order of transformation and loading. A streaming pipeline can still transform before it writes to its analytical destination.

What should I remember?

Choose the place where transformations can run safely, affordably, and visibly. Then put more thought into correctness and recovery than into defending the acronym.

Further reading

For the underlying definitions, see AWS’s comparison of ETL and ELT. The shop scenario, SQL illustration, and decision questions above are explanatory examples prepared for this article.