ETL vs ELT — Why ELT Won

This lesson on ETL vs ELT — Why ELT Won is hands-on and example-driven. You will evaluate architectural trade-offs between ETL and ELT to select the optimal pipeline pattern for your data stack. You will implement raw data ingestion into cloud warehouses and structure downstream in-warehouse transformations while accounting for compliance, cost, and compute constraints.

What You'll Be Able To Do

• Differentiate the execution order and compute placement between ETL and ELT pipelines.
• Identify workload characteristics that necessitate pre-load ETL transformations for regulatory compliance.
• Configure cloud warehouse ingestion patterns that decouple raw loading from SQL-based transformation.
• Evaluate compute and storage costs when migrating legacy on-premise ETL workflows to cloud ELT architectures.
• Design a hybrid ingestion architecture combining rapid raw data loading with governed analytical modeling.

Detailed Concept Walkthrough

1. Pipeline Sequencing and Compute Placement

The defining technical distinction between ETL and ELT is the placement of the compute engine and where transformation logic executes relative to warehouse loading.

• Mechanism: In legacy ETL, extraction reads from source databases and ships data to an intermediate transformation server that cleans, aggregates, and reformats rows before final insertion into target storage.
• Execution Flow: In modern ELT, extraction scripts dump raw, untransformed payloads directly into the cloud warehouse landing tables, executing all joins, deduplication, and schema formatting in-place using declarative SQL.
• Best Practice: Use ELT when downstream analytical requirements are fluid, as preserving raw ingested records prevents the need to re-extract source operational data when business logic changes.

-- ELT in-warehouse transformation: raw staging to dimensional table
CREATE OR REPLACE TABLE analytics.fct_daily_sales AS
SELECT
    DATE(s.transaction_timestamp) AS sales_date,
    s.customer_id,
    SUM(s.amount) AS total_revenue,
    COUNT(s.order_id) AS total_orders
FROM raw_staging.sales_transactions AS s
GROUP BY 1, 2;

Key Takeaway: ETL transforms data on an external intermediate server before storage, whereas ELT transforms data inside the target warehouse engine using native compute.

2. Storage and Compute Decoupling

The shift toward ELT was driven by cloud data warehouse architectures that physically isolate scalable object storage from on-demand compute clusters.

• Under the Hood: On-premise systems tightly coupled fixed CPU capacity with disk volumes, requiring ETL to filter and aggregate records upfront to avoid exhausting warehouse disk space and CPU cycles.
• Mechanism: Modern cloud data warehouses (e.g., Snowflake, BigQuery) store petabytes of raw semi-structured and structured data for minimal cost in underlying object storage, allocating compute dynamically only when transformations run.
• Best Practice: Ingest raw payloads into schema-on-read landing zones and utilize elastic compute warehouses that automatically suspend execution when transformation queries complete.

-- Loading raw JSON directly into Snowflake variant column without prior server-side transformation
COPY INTO raw_staging.customer_events (payload, ingested_at)
FROM @external_s3_stage/events/
FILE_FORMAT = (TYPE = 'JSON')
ON_ERROR = 'CONTINUE';

Key Takeaway: Decoupled storage and compute makes storing massive raw data cheap, turning transformation into an on-demand query process.

3. Security, Compliance, and ETL Use Cases

ETL remains mandatory when privacy regulations or data sensitivity require sanitization before records are written to shared analytical storage.

• Mechanism: Pre-load transformation scrubs, anonymizes, or masks Personally Identifiable Information (PII) on a secure intermediary layer before persisting records to analytical warehouses.
• Under the Hood: Highly regulated environments (such as healthcare under HIPAA or finance under GDPR) often restrict unauthorized raw PII from landing on multi-tenant cloud storage or cross-departmental staging zones.
• Syntax Rule: Anonymization functions, cryptographic hashing, or tokenization must execute in-memory or on ephemeral compute instances prior to issuing write commands to warehouse targets.

# Python ETL step: Hashing PII on an intermediate server prior to warehouse load
import hashlib

def sanitize_record(raw_row: dict) -> dict:
    # Scrub raw email with SHA-256 before downstream insertion
    raw_email = raw_row.get('email', '').strip().lower()
    hashed_email = hashlib.sha256(raw_email.encode('utf-8')).hexdigest()
    return {
        'customer_id': raw_row['customer_id'],
        'email_hash': hashed_email,
        'signup_date': raw_row['signup_date']
    }

Key Takeaway: ETL is the required pattern when regulatory constraints mandate that unmasked sensitive data must never persist in the data warehouse.

4. Hybrid Modern Data Stack and Zero-ETL

Production architectures blend ELT ingestion with modular in-warehouse ETL modeling stages, while emerging zero-ETL paradigms virtualize queries directly across storage engines.

• Mechanism: Modern data teams implement hybrid architectures where automated extract-load connectors ingest raw data into staging tables, after which orchestrators trigger modular transformation models (e.g., dbt or Spark).
• Under the Hood: In-warehouse data marts represent controlled subsets of transformed data tailored for BI dashboards, avoiding the rigid vendor lock-in of traditional monolithic ETL servers.
• Best Practice: Monitor emerging zero-ETL integrations that allow native cloud warehouses to query transactional transactional databases directly via shared memory or storage without explicit replication jobs.

# dbt model (SQL-based in-warehouse ELT layer)
{{ config(materialized='incremental', unique_key='order_id') }}

SELECT
    order_id,
    customer_id,
    order_total,
    updated_at
FROM {{ source('raw_store', 'orders') }}
{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

Key Takeaway: Modern data architectures treat ELT as the ingestion vehicle for raw staging, followed by structured in-warehouse modeling.

Topics Covered in ETL vs ELT — Why ELT Won

1. Introduction to Data Ingestion Paradigms (0:00 - 0:45) — The instructor introduces ETL and ELT as the two fundamental architectural patterns for moving and processing data.
2. Defining ETL and ELT Mechanics (0:45 - 1:35) — A breakdown of pipeline sequencing details how swapping transformation and loading changes compute placement.
3. Retail Sales Workflow Comparison (1:35 - 2:20) — A concrete retail batch example illustrates the operational difference between dedicated ETL servers and in-warehouse SQL.
4. The Cloud Shift and Decoupled Compute (2:20 - 3:10) — The lesson explains how decoupled cloud storage and elastic compute shifted data engineering economics from ETL to ELT.
5. Strategic Decision Matrix for ETL vs ELT (3:10 - 4:00) — The instructor outlines specific scenarios favoring ETL for compliance and ELT for speed and analytical flexibility.
6. Modern Data Stack and Hybrid Architectures (4:00 - 4:50) — The lesson shows how modern teams combine ELT raw ingestion with in-warehouse modeling and highlights future zero-ETL trends.

Data Engineering Cheat Sheet

• ETL Pipeline — Transforms data on separate server before target load

  data = extract(); clean_data = transform(data); load(clean_data)
  

• ELT Pipeline — Loads raw data directly then transforms via SQL

  load(extract()); run_sql_transformations()
  

• COPY INTO — Bulk loads raw files directly into warehouse tables

  COPY INTO raw_table FROM @stage/path FILE_FORMAT=(TYPE='CSV');
  

• CREATE TABLE AS SELECT (CTAS) — Performs in-warehouse transformation and persists results to new table

  CREATE TABLE mart_sales AS SELECT id, SUM(val) FROM raw_sales GROUP BY 1;
  

• PII Masking at Ingest — Hashes sensitive data on intermediate node before loading

  df['ssn'] = df['ssn'].apply(lambda x: hashlib.sha256(x.encode()).hexdigest())
  

• Zero-ETL Query — Queries remote transactional storage directly without moving data

  SELECT * FROM federated_mysql_db.production_orders WHERE status = 'COMPLETED';
  

Comparison Table

Common Pitfalls

• Mistake: Loading unmasked PII into raw warehouse staging layers under strict compliance rules. Avoid: Use pre-load ETL transformation scripts to sanitize or hash sensitive attributes before warehouse ingestion.
• Mistake: Over-filtering raw data during ELT extraction steps. Avoid: Ingest entire source schemas into raw tables so future analytical requests do not require source backfills.
• Mistake: Running uncontrolled compute-heavy transformations during peak querying hours. Avoid: Isolate analytical query compute clusters from scheduled transformation compute clusters.

FAQs

• Why was ETL the standard for decades before ELT became popular? Early on-premise data warehouses had coupled, expensive compute and limited storage, making it cost-prohibitive to load and transform raw unaggregated data directly on the warehouse.
• Does choosing ELT mean we never transform data outside the warehouse? No, data teams frequently use pre-load ETL steps for compliance, tokenization, or high-throughput stream processing before running bulk ELT modeling in the warehouse.
• How does ELT benefit data science and machine learning workflows? ELT preserves unmodified raw data in the warehouse, allowing data scientists to extract unaggregated historical features without requesting new pipeline builds.