ETL vs ELT in 2026: Data Pipeline Architecture and Interview Questions

ETL vs ELT defines how data moves from source systems to analytics environments. The choice between extracting, transforming, then loading (ETL) versus extracting, loading, then transforming (ELT) affects infrastructure costs, data freshness, and the skills required from engineering teams.

ETL Architecture: Transform Before Loading

ETL emerged when data warehouses had limited compute capacity and storage was expensive. The pattern made sense: filter and aggregate data outside the warehouse, load only what analysis required. Oracle Warehouse Builder, Informatica PowerCenter, and Talend built tooling around this model.

The transformation stage in ETL runs on intermediate servers. Data moves from source to a staging area, gets cleaned and reshaped, then loads into the destination. This approach reduces warehouse load but creates a bottleneck at the transformation layer.

# etl_pipeline.py
# Traditional ETL pattern with intermediate transformation

import pandas as pd
from sqlalchemy import create_engine

def extract_from_source(connection_string: str, query: str) -> pd.DataFrame:
    """Pull data from source database."""
    engine = create_engine(connection_string)
    return pd.read_sql(query, engine)

def transform_data(df: pd.DataFrame) -> pd.DataFrame:
    """Clean and reshape data before loading.
    
    This runs on the ETL server, not the warehouse.
    """
    # Remove duplicates based on business key
    df = df.drop_duplicates(subset=['customer_id', 'order_date'])
    
    # Convert date strings to proper datetime
    df['order_date'] = pd.to_datetime(df['order_date'])
    
    # Calculate derived metrics
    df['order_total'] = df['quantity'] * df['unit_price']
    df['order_month'] = df['order_date'].dt.to_period('M')
    
    # Filter to relevant records only
    df = df[df['order_status'] != 'cancelled']
    
    return df

def load_to_warehouse(df: pd.DataFrame, warehouse_conn: str, table: str):
    """Load transformed data to destination."""
    engine = create_engine(warehouse_conn)
    df.to_sql(table, engine, if_exists='append', index=False)

# Pipeline execution
raw_orders = extract_from_source(SOURCE_CONN, "SELECT * FROM orders")
clean_orders = transform_data(raw_orders)
load_to_warehouse(clean_orders, WAREHOUSE_CONN, 'fact_orders')

ETL works well when transformation logic stays stable and data volumes remain predictable. The downside shows when requirements change: modifying transforms means reprocessing historical data from scratch.

ELT Architecture: Load First, Transform in the Warehouse

ELT shifts transformation into the data warehouse. Snowflake, BigQuery, Databricks, and Redshift provide near-unlimited compute that scales with query complexity. Loading raw data first preserves the source state; transformations become SQL models that can be versioned and rerun without re-extracting.

The dbt (data build tool) project popularized ELT by treating SQL transformations as code. Instead of black-box ETL jobs, transformations live in version control as SELECT statements that reference raw tables and build derived models.

-- models/staging/stg_orders.sql
-- dbt model: first transformation layer on raw data

with source as (
    -- Reference the raw table loaded by the extraction tool
    select * from {{ source('salesforce', 'orders') }}
),

renamed as (
    select
        id as order_id,
        customer_id,
        cast(order_date as date) as order_date,
        quantity,
        unit_price,
        order_status,
        -- Calculate derived fields in SQL
        quantity * unit_price as order_total,
        date_trunc('month', cast(order_date as date)) as order_month
    from source
    where order_status != 'cancelled'
)

select * from renamed

-- models/marts/fct_monthly_revenue.sql
-- Aggregated fact table built from staging model

with orders as (
    select * from {{ ref('stg_orders') }}
),

monthly_agg as (
    select
        order_month,
        count(distinct customer_id) as unique_customers,
        count(order_id) as total_orders,
        sum(order_total) as revenue
    from orders
    group by order_month
)

select * from monthly_agg

ELT preserves raw data, which enables reprocessing when business logic changes. If a calculation was wrong six months ago, fixing the dbt model and running a full refresh corrects historical data. With ETL, that same fix requires re-extracting from sources that may no longer have the original records.

Comparison Table: ETL vs ELT Tradeoffs

Hybrid Approaches: When ETL and ELT Combine

Modern data stacks rarely use pure ETL or ELT. Apache Airflow orchestrates pipelines that mix both patterns. Sensitive data might get anonymized before loading (an ETL step), while aggregations run in the warehouse (ELT).

Fivetran and Airbyte extract and load raw data without transformation, then dbt transforms inside the warehouse. But these tools also support lightweight transformations during extraction: column selection, data type coercion, hashing PII fields. That blurs the ETL/ELT boundary.

# airflow/dags/hybrid_pipeline.py
# DAG combining extraction, lightweight ETL, and warehouse ELT

from airflow import DAG
from airflow.providers.airbyte.operators.airbyte import AirbyteTriggerSyncOperator
from airflow.providers.dbt.cloud.operators.dbt import DbtCloudRunJobOperator
from datetime import datetime

with DAG(
    dag_id='hybrid_etl_elt_pipeline',
    start_date=datetime(2026, 1, 1),
    schedule='@daily',
    catchup=False
) as dag:
    
    # Step 1: Extract and load with Airbyte
    # Minor transforms happen here: type casting, PII hashing
    sync_salesforce = AirbyteTriggerSyncOperator(
        task_id='sync_salesforce_orders',
        airbyte_conn_id='airbyte_default',
        connection_id='salesforce-to-snowflake',
        asynchronous=False
    )
    
    # Step 2: Transform in warehouse with dbt
    # Heavy aggregations, joins, business logic
    run_dbt_models = DbtCloudRunJobOperator(
        task_id='run_dbt_transformations',
        dbt_cloud_conn_id='dbt_cloud',
        job_id=12345,
        wait_for_termination=True
    )
    
    sync_salesforce >> run_dbt_models

The pipeline above extracts from Salesforce with Airbyte (which can hash email addresses during sync), loads to Snowflake, then runs dbt models for business transformations. Neither pure ETL nor pure ELT, but practical.

Interview Questions: ETL vs ELT for Data Engineers

Technical interviews for data engineering roles at companies using modern data stacks probe understanding of pipeline architecture. These questions appear frequently, based on patterns from ETL/ELT interview preparation modules.

Question 1: When Would You Choose ETL Over ELT?

Strong answers identify specific scenarios:

• Compliance requirements: GDPR or HIPAA mandates that certain data never reaches the warehouse in raw form. PII must be anonymized or removed before loading.
• Legacy warehouse constraints: On-premises systems like Teradata or older Redshift configurations with fixed compute benefit from pre-aggregated loads.
• Network costs: Loading 10TB daily to a cloud warehouse, then discarding 90% after transformation, wastes egress bandwidth. Pre-filtering makes economic sense.

Weak answers say "ETL is outdated" or fail to give concrete scenarios. Interviewers look for nuance.

Question 2: How Do You Handle Schema Changes in an ELT Pipeline?

This tests understanding of raw data landing zones. Expected topics:

• JSON or semi-structured columns that absorb new fields without schema migration
• Staging models that explicitly select columns, isolating downstream models from source changes
• dbt macros or Dataform assertions that fail builds when expected columns disappear
• Monitoring for schema drift using tools like Monte Carlo or Great Expectations

-- Schema evolution handling in dbt
-- Use VARIANT/JSON columns to absorb unknown fields

with raw_events as (
    select
        event_id,
        event_payload,  -- JSON column from source
        received_at
    from {{ source('app', 'raw_events') }}
),

parsed as (
    select
        event_id,
        event_payload:user_id::string as user_id,
        event_payload:event_type::string as event_type,
        -- New fields appear in JSON without breaking the model
        event_payload:metadata::variant as metadata,
        received_at
    from raw_events
)

select * from parsed

Question 3: Compare Orchestrating ETL with Airflow vs Running dbt for ELT

The question probes understanding that these tools solve different problems:

• Airflow orchestrates tasks: extraction, API calls, file transfers, model training. It manages dependencies across heterogeneous jobs.
• dbt transforms data inside a warehouse. It manages dependencies between SQL models, runs tests, generates documentation.

A complete pipeline often uses both: Airflow triggers Airbyte syncs, waits for completion, then triggers dbt runs. Knowing when to use which tool distinguishes senior candidates.

Question 4: Your ELT Pipeline Processes 500M Rows Daily and Analysts Report Slow Queries

This open-ended question tests diagnostic thinking:

1. Check model materialization: Are heavy models still views? Incremental models or tables might help.
2. Partition and cluster: For BigQuery, are fact tables partitioned by date? For Snowflake, is clustering optimized for common query patterns?
3. Query pushdown: Are analysts querying staging models instead of pre-aggregated marts?
4. Warehouse sizing: Is the compute scaled appropriately during query hours?
5. Freshness requirements: Could transformation run at night instead of during business hours?

No single right answer exists. Interviewers evaluate systematic troubleshooting.

Tooling Landscape in 2026

The data integration market has consolidated around a few patterns:

Extraction and loading: Fivetran, Airbyte, Stitch, and Meltano handle the EL portion. These tools connect to hundreds of sources and sync to cloud warehouses without custom code.

Transformation: dbt dominates SQL-based transformation. Alternatives include Dataform (now part of Google Cloud), SQLMesh (open source with virtual data environments), and Coalesce (visual modeling).

Orchestration: Airflow remains the default for complex pipelines. Dagster and Prefect offer alternatives with better local development and asset-centric views.

Quality: Great Expectations, dbt tests, Monte Carlo, and Soda provide data quality monitoring. These catch issues between extraction and downstream consumption.

# great_expectations checkpoint for ELT quality gates
# Runs after dbt completes, before downstream dashboards refresh

import great_expectations as gx

context = gx.get_context()

checkpoint = context.checkpoints.get("daily_orders_checkpoint")

result = checkpoint.run(
    batch_parameters={"year": 2026, "month": 9},
    expectation_suite_name="orders_suite"
)

if not result.success:
    # Block downstream refresh, alert data team
    raise ValueError(f"Data quality check failed: {result.describe()}")

Selecting Pipeline Architecture for New Projects

For most greenfield projects in 2026, ELT is the default. Cloud warehouse compute costs less than maintaining transformation servers. Raw data preservation enables retroactive fixes. SQL-based transformations are auditable and version-controlled.

ETL remains relevant for:

• Regulatory environments requiring data minimization before warehouse entry
• Real-time streaming where transformation must happen at ingestion time (Kafka Streams, Flink)
• Edge computing scenarios with limited downstream storage
• Legacy integrations where the source system controls the export format

The interview-ready answer acknowledges both patterns and explains the tradeoffs without ideological preference.

Key Takeaways for Data Pipeline Architecture

• ETL transforms data before loading, reducing warehouse load but creating reprocessing friction when logic changes
• ELT loads raw data first, enabling SQL-based transformations that can be versioned, tested, and rerun against historical data
• Modern stacks typically combine both: lightweight extraction transforms (PII hashing, type casting) with warehouse-based aggregation
• dbt has become the standard for ELT transformation, treating SQL models as testable, documented code
• Interview questions probe scenario selection, schema evolution handling, and tooling tradeoffs rather than rote definitions
• Raw data preservation in ELT pipelines enables fixes to historical calculations without re-extracting from sources