ETL or ELT, and how to choose ETL tools

Line drawing of three source system blocks piped into a layered data store, with one gear on the pipe highlighted in blue

Where the transformation should happen, which kinds of tools do which part of the job, and the questions that separate a good fit from a good demo.

In short

ETL transforms data in a separate engine before loading it into the target; ELT loads raw data first and transforms it inside the warehouse or lakehouse. ELT suits modern cloud platforms that keep raw history; ETL suits constrained targets and strict pre-load controls. Choose ETL tools by your sources, your target platform, your team's skills and how changes are tested.

Every analytics platform depends on a data pipeline that moves data out of ERP, CRM, finance and operational systems. The first design choice is where the data is reshaped, and the second is which ETL tools (etl verktyg) do the moving, the reshaping and the scheduling. This comparison from Devies, a Gothenburg-based product and engineering consultancy, is for data leads and CTOs who are about to choose or replace their data integration stack and want to understand the trade-offs before they look at vendors.

What are ETL and ELT?

ETL and ELT are two orders of the same three steps, extract, transform and load, and the only real difference is where the transformation runs.

ETL (extract, transform, load) is a data integration process where data is extracted from sources, transformed in a dedicated engine, often through staging tables, and then loaded into the target store. ELT (extract, load, transform) is a process where raw data is loaded into the target first and transformed there, using the target platform's own compute.

Microsoft's architecture guide to ETL and ELT puts it plainly: ELT differs from ETL solely in where the transformation takes place. Removing the separate engine simplifies the architecture, and scaling the target scales the pipeline, but ELT only works well when the target can transform data efficiently.

Two related terms come up in the same conversations. Reverse ETL moves modelled data from the warehouse back into operational tools such as the CRM. Change data capture (CDC) reads only the rows that changed in a source database, instead of copying whole tables each time.

ETL vs ELT: how do they compare?

ETL vs ELT is a choice about where control and compute sit, and for most companies building on a cloud warehouse or lakehouse today ELT is the default, with ETL kept for specific cases.

Two lanes from source to warehouse: in the top lane the transform gear sits before the warehouse, in the bottom lane inside it

Criterion

ETL

ELT

Where transformation runs

ETL

Separate engine before the target

ELT

Inside the target warehouse or lakehouse

Raw data kept in the target

ETL

Usually not

ELT

Yes, which allows rebuilds and new uses

Load on the target system

ETL

Low

ELT

Higher, needs elastic compute

Typical transformation language

ETL

Tool-specific, often visual mappings

ELT

Mostly SQL, sometimes Python or Spark

Fit with sensitive data

ETL

Can filter or mask before data lands

ELT

Needs access control on the raw layer

Change and testing

ETL

Inside the ETL tool's own environment

ELT

Version-controlled code with automated tests

Typical fit

ETL

On-premises warehouses, constrained targets, strict pre-load audits

ELT

Cloud data platforms, medallion architectures, growing analytics

Choose ETL when the target system has limited compute, complex rules need a specialised engine, or regulation requires data to be filtered, masked or audited in staging before it lands. Microsoft's guide lists the same three reasons.

Choose ELT when the target is a modern warehouse or lakehouse that scales, you want to keep raw data for future questions and schema changes, and your team prefers transformations as code. Keeping the raw layer also means that a wrong transformation can be fixed and rerun without asking the source for a new export.

Many real platforms are hybrids. Personal data may be masked on the way in (a small ETL step), while business logic runs in the warehouse (ELT). The question is not which label to pick, but which rules must be applied before data lands and which can be applied after.

What kinds of ETL tools are there?

ETL tools fall into five categories, and a typical stack combines two or three of them rather than relying on one product for everything.

Category

What it does

Examples (vendor documentation)

Ingestion and replication

What it does

Copies data from sources to the platform through ready-made connectors

Examples (vendor documentation)

Airbyte, Fabric Data Factory copy jobs

Integration services

What it does

Visual pipelines covering extract, transform and load in one service

Examples (vendor documentation)

AWS Glue, Azure Data Factory, SQL Server Integration Services

In-warehouse transformation

What it does

The "T" in ELT, written as SQL models with tests

Examples (vendor documentation)

dbt, Databricks Lakeflow pipelines, Fabric notebooks

Change data capture

What it does

Streams row-level changes from databases

Examples (vendor documentation)

Debezium, built-in CDC in some replication tools

Orchestration

What it does

Schedules and sequences jobs and handles retries

Examples (vendor documentation)

Apache Airflow, Fabric Data Factory pipelines

Some services span categories. AWS describes Glue as a serverless data integration service that supports ETL, ELT and streaming workloads in one service, and Microsoft Fabric bundles ingestion, pipelines and notebooks in one product. Bundles reduce the number of vendors; separate tools give more freedom to swap one part later.

Moving data between operational systems, such as order data from a web shop to the ERP, is a different job. Our comparison of iPaaS and custom integrations covers that side.

How do you choose ETL tools?

You choose ETL tools by testing them against your own sources, target platform and team, not against a feature list. The nine questions below separate the tools that fit from the ones that only demo well.

  1. Which sources must be supported, and how? List every source system and check for a maintained connector, an API or a database connection. Niche ERP and industry systems are where gaps appear.
  2. Which target platform have you chosen? The warehouse or lakehouse decides much of the rest. Choose the platform first, then tools that work natively with it.
  3. How much data changes, and how often? Full reloads are simple but get slower every year. Check support for incremental loads and CDC. Our article on real-time or batch data helps decide how fresh data really needs to be.
  4. Who will build and maintain pipelines? Visual tools suit teams without developers; code-based tools suit teams that already use Git, reviews and tests.
  5. Can changes be version-controlled and tested before production? If a pipeline can only be edited live in a browser, every change is a risk.
  6. How are failures detected and rerun? Look for retries, alerts, logs and safe reruns. Our guide to data pipelines that do not break lists the practices to look for.
  7. Where does data travel and who can see it? Check hosting region, encryption, access control on raw data and whether a SaaS tool stores copies of your data.
  8. How is cost calculated? Pricing models differ: per row, per connector, per compute hour or per user. Model your own volumes over three years rather than comparing list prices.
  9. How hard is it to leave? Prefer tools that write to open formats and keep transformation logic in readable code you own.

Run a short proof of concept with two of your real sources, including the messiest one, and measure load time, failure handling and how long a change takes.

Devies saw what pipeline work can mean for an established platform at Stena Line. According to the case, the collaboration integrated several new data sources into the data warehouse, cut pipeline runtime by approximately 35% and moved the platform towards a medallion architecture in Azure Data Lake, which made new analytical use cases faster to onboard.

Devies

Want a second opinion?

Devies, a Gothenburg-based product and engineering consultancy, works on questions like this with Nordic product and IT teams. Tell us where you are and we will suggest a sensible first step.

Talk to Devies

Frequently asked questions

Is ELT better than ETL?

Neither is better in general. ELT is usually the better fit when you load into a cloud warehouse or lakehouse with elastic compute, because raw data is kept and transformations can be rerun as code. ETL is the better fit when the target has limited compute, when complex rules need a specialised engine, or when data must be masked or audited before it lands. Many platforms combine both.

What is the difference between ETL and a data pipeline?

A data pipeline is any automated chain of steps that moves data from one place to another and processes it on the way. ETL is one specific pattern for such a pipeline: extract, transform, then load. ELT, streaming and reverse ETL are other patterns. So every ETL process is a data pipeline, but not every data pipeline is ETL.

Is dbt an ETL tool?

dbt is a transformation tool, not a full ETL tool. It handles the transform step inside the warehouse or lakehouse, using SQL models with tests and documentation, which makes it a typical ELT component. It does not extract data from source systems, so it is used together with an ingestion or replication tool and often an orchestrator that schedules the runs.

Do we need a separate ETL tool if we use Microsoft Fabric?

Often not for the basics. Fabric includes Data Factory pipelines, copy jobs and notebooks for ingestion and transformation, so many teams start with what is in the platform. Separate tools can still make sense for sources without a suitable connector, for change data capture from operational databases, or when a team prefers transformations as version-controlled SQL with a tool such as dbt.

Sources

Here’s more

Join Our Newsletter

We handle your data according to our privacy policy.

Shopping Cart

We use cookies

We use cookies to improve your experience on this website. You may choose which types of cookies to allow and change your preferences at any time. Disabling cookies may impact your experience on this website. You can learn more by viewing our Cookie Policy.

Check out

Contact Form text

We will contact you text

We handle your data according to our privacy policy.