ETL vs ELT: Same Pipeline, Two Patterns

ETL and ELT use the same sources and serve the same dashboards. The difference is where the transformation runs, and that one decision changes your compute costs, your tooling, your team's skills and how you handle sensitive data.

Side by side

                   ETL                           ELT
Transform runs in  Spark cluster                 Warehouse compute
Warehouse holds    Clean, modeled data only      Raw + staging + modeled
Logic written in   PySpark / Scala               SQL + dbt (Jinja)
Reprocess from     Data lake raw zone            Raw tables in the warehouse
PII handling       Masked before it lands        Masking policies in warehouse
Best for           Heavy or non-SQL transforms   SQL-friendly analytics

ETL in practice: Spark cleans, then loads

Spark reads from the lake, applies every transformation, including masking PII, and writes only the finished table to the warehouse.

from pyspark.sql import functions as F

clean = (
    spark.read.parquet("/lake/bronze/orders/")
    .filter(F.col("order_status") != "TEST")
    .withColumn("email", F.sha2(F.col("email"), 256))
    .withColumn("amount", F.col("amount").cast("decimal(18,2)"))
    .dropDuplicates(["order_id"])
)

# Snowflake: write with the Spark connector
(
    clean.write.format("net.snowflake.spark.snowflake")
    .options(**sf_options)
    .option("dbtable", "ANALYTICS.FACT_ORDERS")
    .mode("append")
    .save()
)

# Microsoft Fabric: write a Delta table to the Lakehouse
clean.write.format("delta").mode("overwrite").saveAsTable("fact_orders")

• Hashing email with sha2 before loading means raw PII never reaches the warehouse.
• Spark handles work SQL struggles with: parsing nested JSON, ML feature engineering, very large joins.
• The warehouse stays small and cheap to query because it only stores finished tables.

ELT in practice: land raw, transform with dbt

Step 1: load the raw data into the warehouse unchanged.

COPY INTO raw.orders
FROM @raw_stage/orders/
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '"')
ON_ERROR = 'ABORT_STATEMENT';

Step 2: a dbt staging model cleans and types the raw data.

-- models/staging/stg_orders.sql
select
    order_id,
    customer_id,
    cast(amount as number(18, 2))   as amount,
    try_to_timestamp(order_ts)      as order_ts,
    lower(trim(order_status))       as order_status
from {{ source('raw', 'orders') }}
where order_status <> 'TEST'

Step 3: an incremental mart model processes only new rows on each run.

-- models/marts/fct_orders.sql
{{ config(materialized='incremental', unique_key='order_id') }}

select *
from {{ ref('stg_orders') }}

{% if is_incremental() %}
where order_ts > (select max(order_ts) from {{ this }})
{% endif %}

Step 4: tests are declared in YAML and run as part of dbt build.

# models/marts/schema.yml
version: 2
models:
  - name: fct_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: amount
        tests: [not_null]

• dbt build runs models and tests in dependency order. A failed test stops bad data before it reaches Power BI.
• ref() builds the lineage graph automatically, so dbt always runs models in the right order.
• Incremental models keep warehouse costs down on large tables.
• Raw tables are never modified, so any model can be rebuilt with dbt build --full-refresh.

Orchestrating both patterns with Airflow

The DAG structure is identical. Only the operators change.

from airflow.operators.bash import BashOperator
from airflow.providers.apache.spark.operators.spark_submit import SparkSubmitOperator
from airflow.providers.common.sql.operators.sql import SQLExecuteQueryOperator

# ETL DAG
spark_transform = SparkSubmitOperator(
    task_id="spark_transform",
    application="/jobs/orders_clean.py",
    conn_id="spark_default",
)
spark_transform >> load_warehouse >> refresh_pbi

# ELT DAG
load_raw = SQLExecuteQueryOperator(
    task_id="load_raw",
    conn_id="snowflake_default",
    sql="sql/copy_raw_orders.sql",
)
dbt_build = BashOperator(
    task_id="dbt_build",
    bash_command="cd /opt/dbt/analytics && dbt build --target prod",
)
load_raw >> dbt_build >> refresh_pbi

• A single dbt build task is simple, but if one model fails the whole task retries.
• Astronomer Cosmos renders each dbt model as its own Airflow task, which gives per-model retries, logs and visibility.
• In both patterns, Airflow owns scheduling, dependencies, retries and alerting. The compute stays in Spark or the warehouse.

Cost and performance trade-offs

• ETL: you pay for Spark cluster time, but warehouse compute and storage stay small.
• ELT: the warehouse does the heavy lifting. Right-size compute, auto-suspend idle warehouses and use incremental models.
• ELT stores raw copies in the warehouse, so storage grows. Set retention rules on raw schemas.
• PII: ETL masks before data lands. In ELT, lock down raw schemas and use masking policies on sensitive columns.

Microsoft Fabric supports both

• ETL style: a Spark notebook reads raw files in the Lakehouse and writes clean Delta tables.
• ELT style: a Data Factory copy activity lands raw data in the Warehouse, then T-SQL procedures or dbt (with the dbt-fabric adapter) transform it in place.
• Either way, tables live in OneLake as Delta, and Power BI can read them through Direct Lake without an import refresh.

Decision guide

• Sensitive data that must be masked before landing: ETL
• Heavy, non-SQL transformations such as nested JSON or ML features: ETL
• Cloud warehouse with a SQL-first analytics team: ELT
• Fast iteration, version-controlled models and built-in testing: ELT with dbt
• Most real platforms: Spark for heavy raw processing, dbt for business modeling

From my own work

My Python ETL jobs load incrementally into Oracle before Power BI ever sees the data. Classic ETL, and still the right call when the database is the source of truth and the transformations need procedural control.