Transform before or after loading? A practical ETL versus ELT decision for mid-sized teams

ETL and ELT move the same data to the same place. The only difference is where the transformation runs, and that one choice decides who can change the logic, what breaks when a source changes, and which line of the budget grows. For most mid-sized teams with a cloud warehouse, ELT is the right default, with a small amount of transformation kept before the load for sensitive data.

One letter, three consequences

In ETL, you extract data from the source, transform it on a separate processing system, and load only the finished result into the warehouse. In ELT, you extract, load the raw data into the warehouse more or less as it arrived, and transform it there using the warehouse's own engine, usually in SQL.

ETL came first for a practical reason. Warehouses used to be expensive, fixed-size machines. Storage was scarce and compute was shared, so you cleaned and reduced data before letting it in. ELT became practical when cloud warehouses separated storage from compute and made both elastic. Keeping raw data became cheap, and the warehouse became powerful enough to do the transformation itself.

That history matters because it explains what actually changes with the order of the letters:

The second row from the bottom and the last row are the ones that decide most real cases.

The best argument for ELT: you get to be wrong

Every transformation encodes an assumption. "Revenue excludes refunds." "A customer is active if they ordered in the last 90 days." Six months later, finance changes the definition, or somebody discovers the join double-counted split shipments.

In an ETL design, the raw input is gone. The pipeline consumed it, produced an aggregate and moved on. To fix history you have to go back to the source system, and the source may have overwritten the old values, may only keep 90 days through its API, or may throttle you badly on a full re-pull.

In an ELT design, the raw data is still sitting in the warehouse. You change the SQL, rebuild the model and history is corrected. For a team that is still learning what its metrics should be, which is most teams, that ability to replay is worth more than any performance argument.

The second practical advantage is who can do the work. Transformation in SQL, kept in version control and run by a modeling tool, is within reach of analysts. That removes a queue in front of a small data engineering team. It also creates a risk covered below.

The best argument for ETL: some data should never land

There are cases where you do not want raw data in the warehouse at all.

• Regulated or sensitive fields. If a source contains card numbers, health information or government identifiers that analytics has no need for, the cleanest control is to drop, hash or tokenize them before load. Data that never lands cannot leak from the warehouse, does not need to be covered by its access policies, and does not have to be found and deleted later.
• Residency and contractual limits. If certain data may not leave a region or a system, the filtering has to happen at the edge.
• Formats the warehouse handles badly. Fixed-width mainframe files, proprietary binary formats, heavily nested documents, images and audio usually need to be parsed outside. The warehouse is a poor place to decode a packed decimal.
• Extreme volume with low value density. If you produce billions of low-level events and only ever query hourly aggregates, pre-aggregating before load can cut cost by orders of magnitude. Be sure you will never need the detail, because you are giving up replay.
• Operational targets. If the destination is an application database or an API that expects valid, shaped records, there is no raw landing zone. You must transform first.

Notice that these are reasons to transform some data before loading. None of them is a reason to run everything through a separate engine.

The bill nobody forecasts

Each approach has a cost that does not show up in the first estimate.

With ELT, it is warehouse compute. Transformation that used to run on a server you had already paid for now runs on metered compute. The usual ways the bill grows:

• Models that rebuild entire tables every hour when only the last hour of data changed.
• A long chain of views on views, so one dashboard refresh recomputes everything beneath it.
• Raw tables with no partitioning or clustering, so every query scans all of history.
• Dozens of exploratory models someone built once, still scheduled, read by nobody.
• Ingestion tools priced by rows or by volume, syncing large tables that nobody uses.
• Orchestration that keeps compute awake between jobs instead of letting it suspend.

None of this is exotic. Incremental models, partitioned tables, a habit of pruning unused models and a budget alert fix most of it. The point is that someone has to own the cost, and in the first year usually nobody does.

With ETL, it is engineering time. Custom pipeline code needs someone to maintain it. Every new field requested by the business becomes a ticket for an engineer, a change to the pipeline, a deployment and often a backfill. The infrastructure invoice looks modest. The real cost is the queue of requests and the two people who are the only ones able to clear it.

With both, it is the cost of not knowing something broke. When a job crashes with an obvious error, the damage is small and someone fixes it that morning. The expensive case is the job that reports success after loading half the rows, or after loading yesterday's file twice, because decisions get made on the result. Budget for monitoring, whichever letters you choose.

The problems both approaches share

Day to day, pipeline pain rarely comes from having picked the wrong acronym. It comes from a short list of problems that both approaches share.

Schema drift. A source team renames a column, changes a type or adds a status value. ETL tends to fail hard at the transform. ELT tends to load the new shape quietly and produce wrong numbers downstream. Either way you need a check that detects the change and a known person who gets the alert.

No tests on the data. Unique keys that are not unique, required fields that are null, a foreign key that points nowhere, a row count that dropped by half overnight. A handful of assertions that run with every load catches most incidents before a stakeholder does.

Model sprawl. The accessibility of SQL is also how you end up with five definitions of "active customer" in five folders. Decide where a metric is defined, define it once, and make everything else read from that.

Late and changed data. Orders are edited after they are placed. Events arrive two days late. Records are deleted at the source. An incremental load keyed on "created today" misses all three. You need a strategy for updates and deletes (change data capture, an updated-at watermark with a lookback window, or periodic full reconciliation) and you need it from the start.

Nobody owns the pipeline. The contractor who built it left. The alerts go to a channel that is muted. Assign an owner and a response expectation for each pipeline the business depends on.

Raw data with no governance. ELT makes it easy to land everything, including personal data you did not mean to collect in analytics. Restrict the raw layer, mask sensitive columns and know how you would honor a deletion request across raw tables, models and backups.

Reverse flow by accident. A spreadsheet export from the warehouse becomes the input to an operational process. Now a modeling change can break billing. Treat any warehouse output that feeds an operational system as a product with a contract.

A sensible default

For a mid-sized company with a handful of SaaS tools, one or two operational databases and a cloud warehouse, this setup holds up well:

1. Managed extraction and load for standard sources. Connectors for common SaaS applications and databases are a solved problem. Writing your own for a mainstream CRM is rarely a good use of engineers.
2. Custom extraction only where you must. Legacy systems, odd file formats and internal APIs get purpose-built loaders.
3. A thin transform before load for sensitive fields. Drop, hash or tokenize what analytics must not hold. This is the one place where the "T" comes first.
4. Raw landing, untouched. Keep source data as it arrived, in a schema that few people can read.
5. Layered SQL models in version control. A staging layer that renames and types, an intermediate layer for joins and business logic, and a small set of published tables that dashboards are allowed to use.
6. Tests and freshness checks on every run. Start with keys, nulls, row counts and "this table was updated in the last N hours".
7. Incremental processing for anything large. Full rebuilds are fine for small tables and expensive for big ones.
8. A named owner and a monthly look at the cost. Fifteen minutes with the warehouse usage report is usually enough.

The pattern is ELT in its overall shape, with a deliberate ETL step where a rule requires it. Most real pipelines end up as this kind of hybrid, and that is fine.

How to tell which one you need

Answer these honestly:

• Do you have a cloud warehouse with elastic compute? If yes, lean ELT. If your target is a fixed-capacity on-premises database, heavy in-database transformation will compete with your users, so lean ETL.
• Does the source contain data that must not be stored downstream? If yes, transform that part before load, regardless of everything else.
• How often do your metric definitions change? Often means you want raw data kept and replayable.
• Who will write and maintain the logic? Analysts with SQL point to ELT. A platform team with strong software practices can run either.
• Is the destination analytical or operational? Operational destinations need shaped data on arrival.
• Is your raw volume far larger than what you ever query? Then consider pre-aggregation, knowingly.
• Can your sources replay history on demand? If not, keeping raw copies is your only insurance.

If you answer "cloud warehouse, frequent changes, SQL-fluent team", the question is settled and the remaining work is discipline: tests, ownership and cost control.

Do your numbers break more often than your pipelines do?

That is usually the real symptom: jobs report success and the dashboard is still wrong. If that sounds familiar, the fix is rarely a new tool. Trivo can review where your transformation runs, who owns it and what it costs, and give you a written plan to make it dependable.