ETL vs ELT in Azure: Which Approach to Choose

ETL: transform before loading

In the traditional ETL approach, a dedicated transformation engine extracts data from the source, applies all transformation logic (cleaning, filtering, joining, aggregating, type conversion), and loads only the final, clean, transformed data into the warehouse.

Source System          ETL Engine              Data Warehouse
─────────────    ──────────────────────    ────────────────────
SQL Server     → ADF Mapping Data Flow → Synapse dedicated pool
Salesforce     → (join, clean, validate)   (final clean data)
SAP ERP        → (aggregate, type-cast)

In Azure, the ETL engine is typically Azure Data Factory Mapping Data Flows (a visual, code-free transformation canvas that runs Spark under the hood) or a custom Python transformation in an ADF Notebook Activity.

ETL advantages:

• The warehouse only ever contains clean, validated data — no intermediate raw state
• Transformation logic is encapsulated in the ETL tool, separate from warehouse SQL
• Sensitive raw data (PII, passwords, internal cost prices) can be masked or excluded before it ever lands in the warehouse

ETL disadvantages:

• If the transformation logic is wrong, you have no raw data to reprocess from — you must re-extract from the source
• Transformation happens outside the warehouse, so you cannot use warehouse-specific optimisations (indexes, materialised views) during transformation
• Complex transformations in ADF Data Flows require the visual designer to be fast enough to work with — large flows become hard to manage

ELT: load raw, transform inside

In the ELT approach, raw data is landed in the data lake (ADLS bronze layer) or loaded into a staging table in the warehouse with minimal or no transformation. The actual transformation work happens inside the destination platform — using Spark in a Synapse/Databricks notebook, T-SQL in a Synapse dedicated pool, or dbt SQL models.

Source System       Fast Load              Data Lake / Warehouse
─────────────    ──────────────────    ─────────────────────────────────
SQL Server     → ADF Copy Activity → ADLS bronze (raw)
Salesforce       (no transformation)    → Spark job → silver Delta tables
SAP ERP          (just move data fast)  → dbt models → gold layer / DW

ELT advantages:

• Raw data is preserved in the lake — if transformation logic is wrong, reprocess from bronze without re-extracting from the source
• Transformation runs on the destination platform’s compute, which is often cheaper and faster than an intermediate ETL engine for large datasets
• SQL engineers can write and test transformation logic directly in the warehouse using familiar tools
• Data is available immediately after load even before transformation completes — useful for exploratory analysis of fresh raw data

ELT disadvantages:

• Raw (possibly sensitive) data lands in the lake — requires careful access control on the bronze layer
• The destination system (warehouse or lake) must have enough compute capacity for transformation workloads in addition to query workloads
• Poor-quality raw data pollutes the warehouse until the transformation job runs and cleans it

ELT with Azure Data Factory: fast extract and load

In a modern Azure ELT pipeline, ADF (or Synapse Pipelines) is responsible only for extraction and raw loading. The Copy Activity moves data at maximum speed from source to bronze ADLS. All transformation happens downstream in Spark or SQL.

# ADF Copy Activity settings for ELT (Python SDK for ADF management)
# This is the ADF Copy Activity configuration for extracting from SQL Server
# Note: transformation options are minimal — we just want fast data movement

copy_activity_config = {
    "name": "CopyOrdersToLakeBronze",
    "type": "Copy",
    "source": {
        "type": "SqlServerSource",
        "sqlReaderQuery": (
            "SELECT * FROM dbo.Orders "
            "WHERE modified_date > '@{activity('GetWatermark').output.firstRow.last_load_time}'"
        ),
        "queryTimeout": "02:00:00",
        "partitionOption": "None"
    },
    "sink": {
        "type": "ParquetSink",
        "storeSettings": {
            "type": "AzureBlobFSWriteSettings",
            "copyBehavior": "PreserveHierarchy"
        },
        "formatSettings": {
            "type": "ParquetWriteSettings"
        }
    },
    "translator": {
        "type": "TabularTranslator",
        "typeConversion": True,
        "typeConversionSettings": {
            "allowDataTruncation": True,
            "treatBooleanAsNumber": False
        }
    },
    "enableStaging": False,
    "dataIntegrationUnits": 32  # Higher DIUs = faster parallel copy
}
# Result: raw Parquet files land in bronze ADLS as fast as possible
# All cleaning and transformation happens in the subsequent Spark notebook activity

ELT with dbt: SQL transformations as version-controlled code

dbt (data build tool) is a transformation framework that turns SELECT statements into managed data assets. You write a dbt model as a SQL SELECT — dbt wraps it in CREATE TABLE AS SELECT or CREATE VIEW and runs it in your warehouse. dbt handles dependency ordering, testing, documentation, and incremental materialisation.

-- dbt model: models/silver/orders.sql
-- This dbt model reads from the raw staging table and produces a clean silver orders table
-- dbt runs this as: CREATE TABLE silver.orders AS SELECT ...

{{
    config(
        materialized='incremental',
        unique_key='order_id',
        on_schema_change='fail'
    )
}}

SELECT
    CAST(order_id AS BIGINT)            AS order_id,
    CAST(customer_id AS INT)            AS customer_id,
    CAST(order_date AS DATE)            AS order_date,
    CAST(order_total AS DECIMAL(18,2))  AS order_total,
    UPPER(TRIM(status))                 AS status,
    CASE
        WHEN UPPER(TRIM(status)) IN ('COMPLETED', 'SHIPPED') THEN TRUE
        ELSE FALSE
    END                                 AS is_fulfilled,
    GETDATE()                           AS dbt_loaded_at
FROM
    {{ source('bronze', 'raw_orders') }}
WHERE
    order_id IS NOT NULL
    AND order_total >= 0
    AND CAST(order_date AS DATE) <= GETDATE()

{% if is_incremental() %}
    AND modified_date > (SELECT MAX(dbt_loaded_at) FROM {{ this }})
{% endif %}

-- dbt model: models/gold/monthly_revenue.sql
-- References the silver orders model; dbt resolves dependencies automatically

SELECT
    YEAR(order_date)    AS order_year,
    MONTH(order_date)   AS order_month,
    COUNT(order_id)     AS order_count,
    SUM(order_total)    AS total_revenue,
    AVG(order_total)    AS avg_order_value
FROM
    {{ ref('orders') }}  -- references the silver orders model
WHERE
    is_fulfilled = TRUE
GROUP BY
    YEAR(order_date),
    MONTH(order_date)

dbt connects to Synapse, Databricks SQL, or other warehouses via adapters. Run all models with dbt run; run tests with dbt test. Because dbt models are SQL files checked into Git, they get all the benefits of version control, code review, and CI/CD.

Choosing between ETL and ELT

In practice, most modern Azure data architectures use a hybrid: ADF Copy Activity does fast, minimal-transformation load to the bronze lake (ELT-style), and then Spark notebooks or dbt models handle complex transformations inside the lake/warehouse (also ELT). Pure ETL (heavy transformation in ADF Data Flows before landing) is reserved for compliance use cases where raw data genuinely cannot be stored.