Skip to main content

ETL vs. ELT

A pipeline moves data across boundaries. Extract, transform, load (ETL) applies important transformations before data enters the destination. Extract, load, transform (ELT) lands data first and transforms it with the destination's compute. Neither ordering is inherently more reliable: reliability comes from contracts, durable inputs, repeatable transformations, controlled publication, and evidence that every expected record was handled.

Prerequisites​

Complete Choosing a Data Processing Tool and Schemas, Types, and Evolution. This batch reuses one repository-owned event feed throughout.

Learning goals​

  • Separate transport, validation, transformation, and publication.
  • Choose ETL or ELT from trust, governance, cost, and recovery constraints.
  • Preserve raw evidence without giving unvalidated data production meaning.
  • State the grain and contract of each pipeline stage.

Worked example​

The source grain is one payment event per event_id. Strings in JSON are transport values; the transform validates required fields and parses amount as Decimal. This small ETL-style pass does not publish rows until all three validate.

import json
from decimal import Decimal
from pathlib import Path

source = Path("fixtures/data-engineering/pipelines/events-batch-1.jsonl")
raw_events = [json.loads(line) for line in source.read_text().splitlines()]
typed_events = [
    {**event, "amount": Decimal(event["amount"])}
    for event in raw_events
]
print(len(raw_events), len(typed_events), sum(e["amount"] for e in typed_events))

The result is 3 3 40.00. An ELT design could first land the unchanged JSON lines in a restricted raw zone, then run the same typed validation in a warehouse. The raw copy improves replay and audit options, but it must not be mistaken for a trusted analytical table.

Use ETL when data must be rejected, redacted, normalized, or minimized before it crosses the destination boundary. ELT is attractive when the destination can retain immutable raw inputs, isolate access, and execute versioned transformations economically. Many systems are hybrids: validate identity and security constraints before loading, then perform business modeling after loading.

For every stage, record its input grain, output grain, schema, rejected-record policy, ownership, retention, and atomic publication boundary. A row count alone cannot prove semantic correctness; reconcile keys, amounts, nulls, and business invariants too.

Limitations and common mistakes​

  • ETL and ELT describe ordering, not at-most-once, at-least-once, or exactly-once delivery.
  • Keeping raw data may conflict with privacy, retention, or data-minimization obligations.
  • Loading before validation can expose malformed data if consumers can query the landing area.
  • Transforming before loading can lose replay evidence unless the source is durably retained.
  • A successful job can still publish partial, duplicated, stale, or semantically wrong data.

Practice​

Classify the example's JSON file, typed rows, and final analytical table as raw, validated, or published. Then write one rule that must run before loading and one that may safely run afterward.

Next steps​

Continue with Batch vs. Streaming to choose when the same logical stages run. The idempotency lesson will make publication safe to repeat.

Primary sources​

Checked 2026-10-15: Google Cloud's BigQuery ETL/ELT integration guide, Python json, and Python decimal.