ETL vs ELT Pipelines: The Modern Data Stack

Historically, data engineering was constrained by the high cost of enterprise disk storage and the tight coupling of compute and storage in on-premise appliances (such as Oracle Exadata or Teradata).

Legacy ETL (Extract-Transform-Load):

1. Extract: Data was pulled from operational OLTP databases and internal CRM systems.
2. Transform (External): Dedicated transformation servers (running tools like Informatica, Talend, or monolithic Python batch scripts) parsed, sanitized, normalized, and aggregated the data in an intermediate compute layer.
3. Load: Only the final aggregated tables were loaded into the destination warehouse. Raw, un-transformed data was discarded due to storage capacity limits.

The Failure Mode of Legacy ETL: The external transformation cluster was a severe compute bottleneck. Worse, if business logic changed (e.g., how "Churn" or "Active User" was calculated), the historical raw data was gone. Teams had to reconstruct historical pipelines from scratch or lose past accuracy entirely.

Modern ELT (Extract-Load-Transform):

With the advent of cloud object storage (AWS S3, GCS) and decoupled compute warehouses (Snowflake, Google BigQuery, Databricks, ClickHouse), storage is virtually free (sim0.02 per GB/month$), and warehouse compute scales elastically on demand.

In modern ELT, raw data is extracted and loaded immediately in its raw, unmodified state (JSON, CSV, Avro) directly into the lakehouse landing tier. Transformations are expressed as modular, testable SQL models using dbt (Data Build Tool) and executed directly inside the massively parallel analytical warehouse.

ELT democratizes data engineering across software engineers, data engineers, and analytics engineers:

• Immutable Raw Data Preservation: Because raw events and source snapshots are preserved permanently in the Bronze/Raw layer, any historical metric can be recomputed retrospectively at any time simply by executing an updated dbt transformation DAG.
• Declarative Software Engineering for SQL (dbt): dbt treats SQL transformations like production code:
  • Version Control: Managed via Git with Pull Request code reviews.
  • Automated Data Quality Testing: Asserts uniqueness, non-null constraints, and referential integrity during pipeline compilation.
  • DAG Lineage: Automatically compiles dependency graphs between upstream source tables and downstream business marts.
  • Incremental Materializations: Recomputes only newly inserted or mutated records based on timestamp watermarks rather than scanning entire historical tables.

-- Example dbt Incremental Model (Transforming Bronze to Silver)
{{
  config(
    materialized = 'incremental',
    unique_key = 'order_id',
    on_schema_change = 'append_new_columns'
  )
}}

SELECT
    raw_payload:id::BIGINT AS order_id,
    raw_payload:customer_id::BIGINT AS customer_id,
    raw_payload:amount::NUMERIC(12, 2) AS order_amount,
    raw_payload:status::VARCHAR(32) AS order_status,
    raw_payload:created_at::TIMESTAMP_NTZ AS created_at,
    CURRENT_TIMESTAMP() AS transformed_at
FROM {{ source('raw_ingestion', 'orders_raw_stream') }}

{% if is_incremental() %}
  -- Only scan rows loaded after the latest record in the existing table
  WHERE raw_payload:created_at::TIMESTAMP_NTZ > (SELECT MAX(created_at) FROM {{ this }})
{% endif %}

Loading data into the modern lakehouse occurs through two dominant patterns:

1. Batch API Pulls (e.g., Fivetran, Airbyte): Ingestion workers periodically poll third-party REST APIs (Stripe, HubSpot, Salesforce) or read table watermarks (updated_at > :last_sync) on scheduled cron intervals (e.g., hourly or daily).
2. Change Data Capture (CDC) via Log Mining (e.g., Debezium, Kafka Connect): Instead of executing expensive SELECT * FROM table WHERE updated_at > ... queries that lock production OLTP tables, CDC agents tail the database Write-Ahead Log (PostgreSQL WAL, MySQL Binary Log). Every committed INSERT, UPDATE, and DELETE is converted into an Avro/JSON event stream and forwarded into Kafka/Kinesis, landing in the data lake within seconds.

Modern ELT pipelines structure analytical data into three standard architectural maturity tiers:

• Bronze Layer (Raw Landing): Exact append-only replica of raw source data with original JSON payloads, source metadata, and ingestion timestamps. No schema enforcement or filtering.
• Silver Layer (Cleaned & Conformed): Filtered, deduplicated, type-cast, and joined tables. Represents an enterprise-wide single source of truth (3NF or dimensional model).
• Gold Layer (Curated Business Marts): Highly aggregated, optimized star-schema dimensional tables (fact tables and dimension tables) powering executive dashboards, BI tools (Looker, Tableau), and ML feature stores.