ETL vs ELT: what each one is and which you actually need

ETL is the process that moves data from where it is created to where it is analysed: extract from the sources, transform it into the shape analysis needs, and load it into the destination. ELT is exactly the same thing with the last two letters swapped: you load the raw data first and transform it afterwards, inside the warehouse itself. It sounds like internal plumbing trivia, and for fifteen years it was. Not any more: that change of order decides who gets to touch business logic, what the warehouse costs you at the end of the month, and what happens when someone asks "why doesn't this number add up?".

This guide is about choosing with judgement, not about following a trend. You'll see what ETL is, what changes with ELT, when each one makes sense and what breaks if you pick wrong. No impossible architecture diagrams: a mid-sized company with an ERP, a CRM, an online shop and three business-critical spreadsheets already has enough complexity.

What is ETL and why was it built that way?

ETL is the classic data warehouse pattern. A scheduled job — usually overnight — connects to each source, pulls the new records, cleans and reorganises them on an intermediate server, and only then writes the result to the destination. What arrives at the warehouse is pre-chewed: tables in their final shape, columns with readable names, keys that match.

It was built that way for a very prosaic reason: the warehouse was expensive and slow. In the nineties and the early two thousands, compute on the destination database was the scarce resource. Doing the transformation outside, on a dedicated machine, was the only sensible way not to choke it. The design consequence is that the transformation lives in a separate tool, with its own language, maintained by a specialist.

A typical ETL in a mid-sized company today:

• Every night at 02:00 it reads the ERP invoice table for rows changed in the last 24 hours.
• Tax IDs get normalised, cancelled invoices are dropped, currency is converted and the rows are joined to the customer master.
• A clean `daily_sales` table is written to the warehouse, ready for the dashboard.
• If something fails halfway, the job stops and in the morning the dashboard shows the day-before-yesterday's figures.

What changes with ELT?

ELT flips the order: you extract from the source and load it as-is — or nearly — into a landing area in the warehouse. The transformation happens afterwards, inside the warehouse itself, with SQL queries that build derived tables from that raw data.

The reason for the change is that the constraint turned upside down. Modern cloud warehouses separate storage from compute, scale in seconds and bill by usage. Keeping raw data is no longer expensive; transforming inside the warehouse is no longer a capacity problem. If the scarce resource is no longer the machine, there's no point maintaining a middle piece of software purely to do what the destination does better.

The practical consequences matter more than the theory:

• You keep the original data. If six months from now you discover your "active customer" rule was wrong, you rebuild the transformation over history. In ETL that raw history doesn't exist: all you have is the output of the bad rule.
• Transformation is written in SQL. It stops being the exclusive property of a tool specialist and becomes reachable by any decent analyst.
• The logic lives in version control. Plain text files, reviewed and versioned like code, instead of boxes and arrows inside a graphical interface.
• You pay to compute. Every time someone rebuilds a model the meter runs. That becomes relevant faster than people expect.

ETL vs ELT: an honest comparison

| | ETL | ELT | |---|---|---| | Where transformation happens | In an intermediate tool | Inside the warehouse | | What reaches the destination | Clean, modelled data | Raw data + derived layers | | Dominant cost | ETL licences and server | Warehouse compute and storage | | Who maintains the logic | A specialist in the tool | Analysts with SQL | | Rebuilding history | Hard: you didn't keep the original | Straightforward: reprocess the raw layer | | Personal data | Can be filtered before loading | Enters the warehouse and must be governed there | | Odd or very dirty sources | Better: transform however you like | Worse: SQL doesn't fix everything | | Time to first dashboard | Weeks | Days |

Two readings worth keeping in mind. First: ELT wins on flexibility and speed, and loses on control over what data enters. Second: this isn't black and white. Almost everything that works in production is light ETL on extraction — a bit of normalising and filtering on the way out of the source — and ELT for everything else.

When does ETL make sense, and when ELT?

Pick ELT if this sounds like you: your destination is a cloud warehouse, your sources are systems with a reasonable API (ERP, CRM, shop, advertising), you want several people able to build metrics without a bottleneck, and you expect business definitions to keep changing. That's most projects starting from scratch today.

Pick ETL if any of these weigh on you:

• Sensitive personal data. If the source carries clinical records, payroll data or any special category, filtering or pseudonymising before loading is a design decision that saves you arguments. What never enters the warehouse doesn't need protecting inside it. We cover it in detail in data anonymisation.
• The destination is expensive or rigid to compute on. If the warehouse is a classic database in your own data centre, transforming inside it is exactly the problem ETL was invented to solve.
• Sources that aren't tables. Fixed-width files from a nineties system, XML nested five levels deep, PDFs. SQL isn't the tool. Transform outside and load the result.
• Huge volume with narrow usage. If you ingest a hundred times more data than you will ever query, loading it all raw means paying warehouse rates for junk. Filter first.

The useful question isn't "ETL or ELT?", it's "where do I want the definition of active customer to live, and who needs to be able to change it without opening a ticket?".

What really changes in cost?

This is where most comparisons online lie by omission. It isn't about comparing the price of two licences.

With ETL, cost is predictable and largely fixed: the tool licence, the server it runs on, and — the big line — the hours of the person who knows how to maintain it. That person is a bottleneck: every metric change goes through their queue. The real cost isn't on the invoice, it's in the three weeks a new metric takes to become available.

With ELT, cost is variable and blows up through carelessness. Patterns we've seen break budgets:

• Rebuilding every derived table hourly when the source data updates once a day.
• Reprocessing the full history on every run instead of only what changed.
• A dashboard with no caching that fires a heavy query every time someone opens it.
• Loading entire tables from systems nobody queries, "just in case".

None of this is the ELT model's fault: they're implementation decisions. But the model makes them easy to commit and slow to detect, because the bill arrives at the end of the month. Set a spend alert on day one, not in month three.

Who gets to touch the transformation?

This is the decisive argument, and the one least discussed in the committees where tools get chosen.

With ETL, business logic sits inside a product. To find out why margin is calculated the way it is, you have to open the tool, find the flow and read the right box. Anyone without a licence and training can't. In practice that means one person understands how the company's numbers are produced. If they leave, it leaves with them.

With ELT and transformation in versioned SQL, the logic is plain text anyone can read, search and comment on. Change control with a second pair of eyes stops being an aspiration and becomes the default procedure. That's real data governance: not a document, but a place where the definition lives and a history of who changed it and why.

The trade-off is honest: if anyone can create tables, in six months you have forty versions of "sales". You need naming conventions, one clear final layer the dashboard drinks from, and someone willing to say no. None of that is a tool: it's data quality and discipline.

How do you build the first one without over-equipping?

An order that works in a mid-sized company, from least to most commitment:

1. Pick a business question, not a system. "Margin by customer and month" forces you to bring in sales, costs and the customer master. "Let's integrate the ERP" never ends. 2. Inventory the sources behind that question. For each one: how it's extracted, how often it changes, which fields you need and whether it carries personal data. That table decides your architecture better than any comparison chart. 3. Load raw, with a timestamp. One table per source, fields as they come, plus the load date. Don't clean yet. 4. Transform in layers. Raw → clean (types, duplicates, keys) → business (named metrics with an owner). Three layers, not seven. 5. Define a freshness indicator. "This dashboard was refreshed at 06:12", visible on the screen itself. Half of all trust-in-data crises are really badly communicated freshness crises. 6. Put cost controls in before you expand. One alert and a monthly review of the most expensive queries. Ten minutes a month. 7. Only then add the second question. If the first one isn't being used, the second one won't be either.

If by the end of step 4 you find a source that demands acrobatics SQL can't do, that particular source gets extracted with transformation up front. Mixing is fine. What isn't fine is mixing without having decided to.

Mistakes that cost months

• Choosing the tool before the question. The platform gets bought and then someone goes looking for what to load into it. It always ends up expensive.
• Transforming on load "to save space". Keeping raw data costs cents. Losing it costs rebuilding six months of history that is no longer in the source, because the ERP only keeps two years.
• Replicating the ERP model in the warehouse. The ERP is designed to operate, not to analyse. Copying it table by table leaves you with the same maze, only further away.
• Not recording what was loaded and when. With no run log, the day data goes missing the investigation starts from zero.
• Letting personal data into the warehouse without deciding to. It arrives in the first "just testing" dump and three years later it's still there, replicated across four environments.

Frequently asked questions

Has ELT made ETL obsolete?

No. ELT is the reasonable default for new projects on a cloud warehouse, but ETL remains the right call when sensitive data has to be filtered before loading, when sources aren't tabular, or when the destination charges heavily for every computation. Most real architectures combine the two.

Do I need a data warehouse to do ELT?

Yes, or something that plays its part: a system that separates storage from compute and handles heavy queries without affecting operations. If your only database is the ERP's, transforming inside it is a bad idea. Before building anything, it's worth deciding whether you really need one: we cover that in data warehouse for SMEs.

How long until the first case is live?

With API-accessible sources and a single, well-scoped business question, three to six weeks to a dashboard management actually trusts. What stretches the timeline is almost never the technology: it's getting access to the systems and agreeing on what each metric means.

Who should maintain this, my team or a vendor?

The pattern that ages best is a vendor building the foundation and leaving the logic as versioned SQL inside your repository, with someone internal — usually an analyst, not a developer — owning the day-to-day changes. If the logic stays inside the vendor's tool, you depend on them for every new metric.

---

If you're deciding this right now and you're not sure which sources will hold up the model you want to build, the sensible order is to look at what you already have first: which systems exist, what data comes out of each, and what breaks today. That's exactly what we do in the audit, and you leave with a source map and a concrete architecture recommendation, not a product catalogue. If you already have it clear and want to pressure-test it with someone who has built a few, let's talk.