ETL vs ELT: Differences, SQL Example and When to Use Each

Q: What SQL statements are used for ELT transformations?

Mostly CREATE TABLE AS SELECT to build tables from queries, INSERT ... SELECT to append, and MERGE to upsert changed rows into reporting tables, plus views. Tools such as dbt generate these statements from SELECT models.

Quick verdict

Short answer

Choose ELT when the target is a cloud warehouse or lakehouse that can scale compute, you want to keep raw data for reprocessing, and your team can express transformations in SQL (often managed with a tool such as dbt). Choose ETL when the target is small or expensive to compute on, when transformations need a specialised engine, or when rules require data to be cleaned, masked or audited before it is loaded. Most real pipelines mix both: sensitive fields are blocked or hashed in flight, and the rest of the modelling happens in the warehouse.

How we know: This page is conceptual and research-based: definitions and guidance were checked against Microsoft Learn, AWS, dbt, Snowflake, Fivetran and Airbyte documentation in October 2026. Products are named only as examples, and the SQL example was checked against Snowflake's documentation rather than run; we have not run performance tests.

ETL is, in Microsoft's Azure Architecture Center definition, a data integration process that consolidates data from diverse sources into a unified store, where data is modified according to business rules using a specialised engine, often through staging tables, before being loaded into its destination. Typical transformations are filtering, sorting, aggregating, joining, cleaning, deduplicating and validating. SQL Server Integration Services (SSIS) and Data Factory in Microsoft Fabric are examples Microsoft gives of ETL tools.

ELT, in the same guide, differs from ETL solely in where the transformation takes place: data is loaded first and the processing capabilities of the target data store transform it, with no separate transformation engine in the pipeline. Ingestion tools such as Fivetran and Airbyte load raw data; SQL, often organised by a transformation tool such as dbt, then builds clean, modelled tables inside the warehouse.

Same three steps, different order. Both approaches extract, transform and load. The decision is which system does the transforming and whether raw data is kept in the target. For the wider picture of pipelines, see ETL vs data pipeline; for why warehouses exist at all, see data warehouse vs database.

Side by side

Aspect	ETL	ELT
Order of steps	Extract, transform, then load	Extract, load, then transform
Where transformation runs	A separate engine or server, often with staging tables	Inside the target warehouse or lakehouse, using its compute
What the target holds	Only cleaned, conformed data	Raw data plus the transformed models built from it
Typical transformation language	The ETL tool's designer, Python, Spark or vendor-specific logic	Mostly SQL (for example CREATE TABLE AS SELECT and MERGE), often managed by dbt
Reprocessing after a logic change	Usually needs re-extracting from sources	Rebuild models from the raw tables already loaded
Sensitive data	Can be removed or masked before it reaches the target	Lands raw unless blocked or hashed in flight; then controlled by warehouse access policies
Fits best with	Constrained targets, complex non-SQL rules, audited staging	Cloud warehouses and lakehouses with elastic compute
Main trade-off	Less load on the target and cleaner data at rest, but another engine to run and less flexibility to reprocess	Simpler pipeline and full raw history, but warehouse compute and storage costs grow and raw data needs governing

Key differences

Where the transformation runs

In ETL, data passes through a transformation engine between source and target. Microsoft notes that the three phases often run in parallel, so transformation can start on data already extracted while extraction continues. The target receives only finished data, which keeps its storage and compute needs down and means analysts never see raw source records.

In ELT, extraction and loading are kept as simple as possible, often a near-copy of each source table, and all reshaping happens afterwards in the target. Microsoft lists two advantages: the transformation engine disappears from the architecture, and scaling the target also scales the pipeline. It also states the condition: ELT only works well when the target system is powerful enough to transform the data efficiently.

Why cloud warehouses shifted practice towards ELT

ETL grew up when warehouse capacity was fixed and expensive, so it made sense to transform data elsewhere and load only what was needed. AWS describes the change: with cloud technologies, companies could store large volumes of raw data and analyse it later as required, and ELT became the common modern integration method. Cloud warehouses separate storage from compute; Snowflake, for example, documents virtual warehouses as compute clusters that can be resized, started and suspended independently of the stored data.

That changes the economics. Storing raw copies is comparatively cheap, transformation compute can be scaled up for a large rebuild and suspended afterwards, and keeping raw data means a changed business rule can be applied to the whole history without going back to the source systems. Managed ingestion services that copy sources into the warehouse with little configuration, and SQL-based transformation tools, made the pattern practical for small teams. Microsoft's guidance reflects this: choose ELT when the target is a modern warehouse or lakehouse with elastic compute, when you need to preserve raw data, and when transformation benefits from the target's native capabilities.

An in-warehouse transformation in SQL

A typical ELT step: an ingestion tool has loaded raw.shop_orders as text columns, possibly with several versions of the same order. The first statement builds a typed, deduplicated staging table; the second merges it into a reporting table. Written for Snowflake:

-- Snowflake SQL: step 1, typed and deduplicated staging table (CTAS)
CREATE OR REPLACE TABLE staging.orders AS
SELECT
    order_id,
    customer_id,
    TRY_TO_DECIMAL(amount_raw, 12, 2)    AS amount,       -- NULL if not numeric
    TRY_TO_TIMESTAMP_NTZ(ordered_at_raw) AS ordered_at,
    UPPER(TRIM(status))                  AS status,
    _loaded_at
FROM raw.shop_orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id
                           ORDER BY _loaded_at DESC) = 1;  -- latest version only

-- Snowflake SQL: step 2, upsert into the reporting table
MERGE INTO analytics.fct_orders AS t
USING staging.orders AS s
  ON t.order_id = s.order_id
WHEN MATCHED AND s._loaded_at > t._loaded_at THEN UPDATE SET
  t.amount = s.amount,
  t.ordered_at = s.ordered_at,
  t.status = s.status,
  t._loaded_at = s._loaded_at
WHEN NOT MATCHED THEN INSERT
  (order_id, customer_id, amount, ordered_at, status, _loaded_at)
  VALUES (s.order_id, s.customer_id, s.amount, s.ordered_at, s.status, s._loaded_at);

The deduplication matters: Snowflake documents that when several source rows match one target row, MERGE returns an error by default (the ERROR_ON_NONDETERMINISTIC_MERGE parameter), so the source should have one row per key. The same pattern works on other warehouses with dialect changes; SQL Server, for example, has MERGE but no QUALIFY, so the deduplication would use a subquery or CTE with ROW_NUMBER(). In an ETL design, the cleaning in step 1 would happen in the ETL tool and only the final rows would be loaded.

dbt's role in ELT

dbt is one widely used example of an ELT transformation tool. Its documentation describes it as the T in ELT: you write SELECT statements as models, and dbt compiles them, works out the dependency order, and runs them inside your data platform. A model materialised as a table is rebuilt on each run with a CREATE TABLE AS statement; a view with CREATE VIEW AS; an incremental model inserts or updates only new records, using a strategy such as merge where the adapter supports it. The MERGE above could be written as a dbt incremental model:

-- dbt model models/fct_orders.sql (SQL with Jinja), Snowflake adapter
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge'
) }}
select order_id, customer_id, amount, ordered_at, status, _loaded_at
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where _loaded_at > (select max(_loaded_at) from {{ this }})
{% endif %}

dbt Core is open source under Apache-2.0, and the dbt platform (formerly dbt Cloud) adds hosted development, scheduling and CI. Fivetran and dbt Labs completed a merger on 1 June 2026 and operate as "Fivetran + dbt Labs"; the announcement included dbt Core v2.0 (alpha), based on the dbt Fusion engine, under Apache-2.0. dbt is not required for ELT: stored procedures, scheduled SQL, or transformation features in warehouse platforms and orchestrators do the same job.

Data privacy: when to transform before loading

Loading raw data means personal or regulated data lands in the warehouse unless you prevent it. Microsoft's guidance names regulatory or compliance requirements for curated, audited staging before loading as a reason to choose ETL. Data minimisation principles in privacy law also argue against copying fields you do not need. Typical cases for transforming first are payment card data, health records, national identifiers, and data that must not leave a region in identifiable form.

Modern ELT tools address part of this in flight, which is a small ETL step inside an ELT pipeline. Fivetran documents data blocking, which excludes tables and columns from syncs, and column hashing, which hashes values with a per-destination salt before they are written so they stay joinable but unreadable. Airbyte documents mappings (on its Plus, Pro and Enterprise Flex plans) that hash, encrypt, rename or filter data during the sync. Inside the warehouse, access controls and masking policies govern what remains. In our view the safe default is to block or hash anything you are not sure you need before it lands, and transform the rest in the warehouse.

Pricing and licensing

ETL and ELT are approaches, not products, so there is no price to compare. What differs is where the money goes. ETL pays for a transformation engine (a server, an ETL tool licence or a managed integration service) and the people to build and run it; the warehouse can be smaller because it stores and computes only finished data.

ELT moves transformation cost into the warehouse: every model rebuild uses warehouse compute (credits, slots, DBUs or capacity units, depending on the platform), and keeping raw history uses storage. Ingestion services are usually billed by usage, such as rows changed per month or credits. AWS describes ELT as having a simpler stack and lower setup costs; whether it is cheaper to run depends on data volume, how often models are rebuilt and whether incremental models avoid full rebuilds. Measure warehouse compute spent on transformations rather than assuming either approach is cheaper. For warehouse billing units, see Snowflake vs Databricks.

Pricing checked on the vendors' official pages on 7 October 2026. Prices change; confirm before buying.

Where each one leads

ETL strengths

Only cleaned, approved data reaches the target
Sensitive fields can be removed or masked before loading
Keeps transformation load off a constrained or expensive target
Suits complex logic that needs a non-SQL engine

ELT strengths

Simpler pipeline: no separate transformation engine
Raw data is kept, so models can be rebuilt with new rules without re-extracting
Transformations scale with the warehouse's elastic compute
Transformations are SQL that can be version-controlled and tested, for example with dbt
Loading is quick to set up with managed ingestion tools

Limitations

ETL limitations

Another engine or tool to build, run and pay for
Changing a rule often means re-extracting from sources
Raw detail discarded during transformation cannot be recovered later
Transformation logic may be locked into a proprietary designer format

ELT limitations

Warehouse compute and storage costs grow with rebuilds and raw history
Raw personal data lands in the target unless blocked or hashed in flight
Needs a target powerful enough to transform the data efficiently
Raw and staging tables multiply, so naming, ownership and access need governing

When to choose each

Choose ETL if

The target is a small or on-premises database with limited spare capacity
Compliance requires data to be cleaned, masked or audited before it is stored
Transformations need a specialised engine or non-SQL processing
Only a small, curated subset of source data is needed downstream

Choose ELT if

The target is a cloud warehouse or lakehouse with elastic compute
You want raw history kept so models can be rebuilt as rules change
Your team works in SQL and wants transformations in version control
You use a managed ingestion tool and want loading to stay simple

When neither is right

You need data processed continuously as events arrive, such as fraud detection or live dashboards: a streaming architecture (a message broker plus a stream processor) fits better than batch ETL or ELT; see ETL vs data pipeline.
You only need to report on one operational database: a read replica or reporting views may be enough without any pipeline; see data warehouse vs database.
You need to push warehouse data back into business applications: that is reverse ETL, a separate step after either approach.

Final recommendation

Bottom line

For most new analytics stacks built on a cloud warehouse or lakehouse, ELT is the sensible default: load sources with a managed or open source ingestion tool, keep the raw data, and build models in SQL with dbt or a similar tool. Keep ETL, or at least an ETL step, where the target cannot carry the transformation load, where logic needs a specialised engine, and above all where sensitive data should be removed or hashed before it lands. To choose tools for each side, see Airbyte vs Fivetran for ingestion and Apache Airflow vs Airbyte for orchestration.

Frequently asked questions

What is the main difference between ETL and ELT?

Where the transformation happens. ETL transforms data in a separate engine before loading it into the target; ELT loads raw data first and transforms it inside the target warehouse using its compute.

Is ELT replacing ETL?

ELT has become the common default for cloud warehouses and lakehouses, as AWS and Microsoft both describe, but ETL remains appropriate for constrained targets, specialised transformations and compliance cases where data must be cleaned or masked before loading. Many pipelines combine both.

Is dbt an ETL or ELT tool?

dbt describes itself as the T in ELT: it does not extract or load data, but compiles SQL models and runs them inside your warehouse. It is usually paired with an ingestion tool that loads the raw data.

Is ELT less secure than ETL?

Not inherently, but raw data, including personal data, lands in the warehouse unless you block or hash it during ingestion. Use the ingestion tool's blocking or hashing features for fields you do not need, and warehouse access controls and masking for the rest.

Is ELT cheaper than ETL?

It often has a simpler stack and lower setup cost, but transformation compute moves into the warehouse bill, and raw history adds storage. Which costs less depends on data volume and how often models are rebuilt; incremental models reduce rebuild cost.

What SQL statements are used for ELT transformations?

Mostly CREATE TABLE AS SELECT to build tables from queries, INSERT ... SELECT to append, and MERGE to upsert changed rows into reporting tables, plus views. Tools such as dbt generate these statements from SELECT models.

Sources

Checked October 2026.

How we research comparisons: our editorial method.