ETL Guide

TL;DR

• ETL means extract, transform, load. It moves data from operational sources into an analytical destination such as a data warehouse, data lake, or lakehouse.
• Batch ETL processes bounded sets of records. Runs usually execute hourly or daily, although micro-batches can reduce latency to minutes.
• ELT changes the transform location. It loads raw data first and uses the destination's compute engine, often through dbt, to build governed models.
• Incremental processing needs state. A watermark, source offset, or change log identifies what the next run must read. That state is committed only after the destination is valid.
• Idempotency is the primary recovery property. Reprocessing the same input must converge on the same output without duplicate facts or damaged history.
• CDC and streaming are different operating models. Use them when per-change delivery or low latency is worth continuous infrastructure and more complex recovery.
• Production ETL is a data product, not a scheduled script. It needs ownership, contracts, observability, lineage, security controls, backfills, and tested failure behavior.

Quick Decision Guide

• Use batch ETL when consumers tolerate hourly or daily freshness, the data is naturally bounded, and pre-load transformation or masking is required.
• Use ELT when raw landing is permitted, warehouse compute is available, and analysts need SQL-based models that can evolve independently of ingestion.
• Use CDC when inserts, updates, and deletes must be replicated accurately from a transactional database.
• Use streaming when applications react to events within seconds and event-time semantics, ordering, and continuous availability matter.

Who this guide is for

This guide is for data engineers, analytics engineers, platform engineers, software engineers who own source systems, and architects choosing data movement patterns. It assumes basic SQL and database concepts but does not require prior pipeline experience.

Learning Path

Start with the extract-transform-load contract and watermark discipline. Continue through architecture, workflow, and the production example. Then use the comparison and operations sections to choose and run a pattern. Follow with data lakes, data warehouses, and lakehouse architecture.

On this page

• Why This Matters
• The Problem ETL Solves
• How We Got Here
• What Is ETL?
• How ETL Works
• Architecture
• Step-by-Step Flow
• Real Production Example
• Design Decisions
• Comparisons
• Common Mistakes
• Where It Breaks Down
• When NOT to Use Batch ETL
• Running in Production
• Production Checklist
• Related Guides
• Interview Questions
• Key Takeaways
• FAQs
• References
• Further Reading

Why This Matters

Operational data is useful for analytics only when it arrives with known meaning, freshness, and quality. A dashboard can execute perfectly and still report the wrong revenue if a pipeline omitted late invoices, duplicated retries, or applied inconsistent currency conversion. ML features can be statistically valid but operationally stale. The pipeline establishes the boundary between source behavior and downstream trust.

ETL provides a repeatable place to enforce that boundary. Extraction isolates source access. Transformation applies data types, business definitions, privacy controls, and historical modeling. Loading makes publication atomic or otherwise observable. Separating these responsibilities lets teams reason about failures: whether records were unavailable at the source, rejected by a contract, transformed incorrectly, or not committed to the destination.

The acronym is older than cloud warehouses, but the engineering concerns remain. Airflow may orchestrate a Spark job; a managed connector may land files; dbt may implement warehouse transformations. Each component still participates in extraction, transformation, loading, or control-plane duties. Understanding those duties prevents tool-specific assumptions from becoming data-loss incidents.

Engineering Insight

An ETL pipeline is a distributed transaction without a single transaction manager. Durable staging, idempotent writes, validation, and delayed watermark commits collectively provide the recovery guarantees that one database transaction would otherwise provide.

The Problem ETL Solves

Systems that create data optimize for their own workloads. PostgreSQL schemas normalize entities for transactional integrity. SaaS APIs paginate and rate-limit records. Kafka topics preserve event streams. Object storage holds files with weak or evolving schemas. None of these interfaces automatically supplies a stable customer dimension, daily revenue fact, or policy-compliant training dataset.

Analytical consumers need a different representation: integrated across sources, typed consistently, historical, documented, and efficient to scan. ETL bridges the impedance mismatch without forcing the operational system to serve large analytical queries.

The problem is not merely moving bytes. A reliable pipeline must preserve semantic correctness across partial failures, retries, late data, source mutations, and schema changes. Copying a table once is export. Repeating the copy safely for years is data engineering.

How We Got Here

ETL evolved with storage economics and analytical compute. Early warehouse systems required data to be transformed before load because destination capacity was expensive and schemas were rigid. Distributed processing moved large transforms onto commodity clusters. Cloud object storage and elastic warehouses then made raw-first ELT practical. Log-based CDC and event streaming reduced latency further, but did not remove the need for governed transformation.

Diagram: Evolution of analytical data movement

timeline
    title ETL evolution
    1980s : File exports
          : Batch loaders
    1990s : Warehouses
          : ETL suites
    2000s : Hadoop
          : Distributed jobs
    2010s : Cloud ELT
          : Object storage
    2020s : Lakehouses
          : CDC and streaming

The dominant execution engine changed over time, while extraction state, transformation semantics, and reliable publication remained necessary.

Modern platforms therefore mix patterns. A connector may use CDC, object storage may retain raw events, Spark may normalize large files, and dbt may build marts. Calling the whole system ETL or ELT is less useful than documenting where each transformation runs, what state it owns, and what guarantees connect the stages.

What Is ETL?

ETL is a data integration pattern with three logical phases:

1. Extract reads records from source systems. Sources include databases through JDBC or transaction logs, REST and GraphQL APIs, SFTP files, object storage, SaaS connectors, and event archives. Extraction must account for pagination, snapshots, rate limits, source consistency, and incremental state.
2. Transform converts source-shaped data into destination-shaped data. Typical work includes validation, type conversion, deduplication, joins, aggregation, key resolution, privacy enforcement, and slowly changing dimensions. A transformation should be deterministic for a defined input and configuration.
3. Load publishes results to the destination. Common strategies include append, MERGE/upsert, copy-on-write partition replacement, and atomic table or view swaps. Loading defines duplicate handling and visibility to readers.

These phases are logical, not necessarily separate processes. One Spark application can extract files, transform rows, and write an Iceberg table. Conversely, extraction, raw landing, staging, intermediate models, and final publication may be distinct jobs coordinated by an orchestrator.

ETL commonly uses a staging area. Raw or lightly parsed input is persisted before production tables are changed. Staging decouples source availability from transformation retries, supports forensic inspection, and makes backfills possible. The retention period should match replay and audit requirements.

How ETL Works

Most ETL systems process bounded batches on a schedule. A batch can mean a source snapshot, a set of files, a timestamp interval, or a range of ordered offsets. The boundary must be reproducible. “Everything new right now” is ambiguous unless the pipeline records what “now” meant.

Batch pipeline stages

1. Allocate a run ID. Record the logical interval, code version, environment, and prior committed state.
2. Read the previous watermark. The watermark might be a timestamp plus tie-breaker key, monotonically increasing ID, source log position, or file manifest.
3. Extract a closed range. Capture an upper bound before reading so a long-running query does not chase newly arriving rows.
4. Land immutable input. Persist source records and extraction metadata to staging.
5. Transform deterministically. Apply contracts, normalization, deduplication, business logic, and historical rules.
6. Load idempotently. Merge by stable key or replace the exact destination partition associated with the run.
7. Validate output. Check technical constraints and business invariants before publication.
8. Publish and commit state. Make output visible, then advance the watermark in a compare-and-set operation.

Watermark discipline prevents gaps. Suppose a run reads rows through 10:00 and fails during load. If the watermark was already advanced to 10:00, the retry starts after those rows and silently loses them. If state remains at 09:00, the retry reads the interval again; idempotent loading removes the risk of duplicates.

A timestamp alone can be unsafe when many rows share the same value or clocks have insufficient precision. Use a compound cursor such as (updated_at, primary_key), querying lexicographically. Apply a lookback window when sources can commit late updates, and rely on destination keys to deduplicate overlap.

Source: dbt Labs

Incremental versus full loads

Slowly changing dimensions

Dimensions such as customer region, subscription tier, and product category change over time. Slowly changing dimension strategies define whether historical facts should resolve to current or past attributes.

For Type 2, the business key identifies the entity and a surrogate key identifies each version. The transform closes the prior open interval and inserts the new version in one atomic operation. Overlapping validity windows and multiple current rows are data-quality failures. Late-arriving changes require rebuilding the affected intervals, not simply appending at load time.

Architecture

A production platform separates data-plane work from control-plane state. Data-plane components read, transform, and write records. The control plane schedules runs, stores checkpoints, distributes credentials, records lineage, and alerts operators.

Diagram: ETL platform layers

flowchart LR
    S[Sources] --> I[Ingest]
    I --> R[Raw zone]
    R --> P[Transform]
    P --> Q[Quality]
    Q --> W[Serving]
    O[Orchestrator] -. controls .-> I
    O -. controls .-> P
    M[Metadata] -. tracks .-> O

Sources pass through durable raw storage, transformation, and quality gates while orchestration and metadata coordinate the work.

Query engines such as Trino can serve lake tables across storage systems, while DuckDB is useful for local validation and development against Parquet. These tools change execution placement; they do not replace contracts, checkpoints, or publication controls.

Step-by-Step Flow

The following sequence shows a successful incremental run. The important ordering is that validation and publication precede checkpoint advancement.

Diagram: Incremental batch execution

sequenceDiagram
    participant O as Orchestrator
    participant C as Checkpoint
    participant S as Source
    participant R as Raw
    participant W as Warehouse
    O->>C: Read watermark
    O->>S: Extract range
    S-->>O: Records
    O->>R: Write batch
    O->>W: Merge rows
    O->>W: Run checks
    W-->>O: Checks pass
    O->>C: Commit watermark

The run can safely retry because input is durable, the merge is idempotent, and the checkpoint moves only after checks pass.

Before extraction, the orchestrator should acquire a logical lock or enforce maximum active runs for the dataset. Overlapping runs that share one checkpoint can race and regress state. The source query should use an upper and lower bound recorded in run metadata.

After raw landing, write a manifest that includes object names, byte counts, row counts when available, and checksums. A completion marker must be created only after every object is durable. Transform jobs consume the manifest rather than listing a mutable prefix.

Loading should isolate work from readers. Use a transaction, temporary table followed by a swap, or a table-format snapshot commit. Validation can include unique-key checks, accepted values, referential integrity, volume ranges, and reconciliation against source totals. A “non-empty” check alone is insufficient because a partial batch is also non-empty.

Finally, record the output version, validation results, duration, and committed watermark. Emit lineage from the source range through output tables. Notify downstream jobs only after publication, ideally by dataset completion rather than by assumed wall-clock time.

Real Production Example

Consider a SaaS company that archives product usage events from Kafka into object storage and loads them into a warehouse. Dashboards tolerate daily freshness, but billing requires every event to be represented once. Files can arrive late, and operators must replay a date after correcting transformation logic.

The simplified Python below illustrates the transaction boundary. Production code would use parameterized APIs for object paths, structured logging, explicit time zones, retries around transient operations, and a real quality framework. The core behavior is preserved: a fixed interval, staged input, deterministic deduplication, idempotent MERGE, validation, and a final checkpoint commit.

from dataclasses import dataclass
from datetime import datetime, timezone


@dataclass(frozen=True)
class BatchWindow:
    start: datetime
    end: datetime


def run_usage_etl(checkpoints, warehouse, object_store, run_id: str) -> None:
    start = checkpoints.get("usage_events")
    end = datetime.now(timezone.utc)
    window = BatchWindow(start=start, end=end)

    # Extract a fixed interval. The lookback captures late file publication;
    # event_id makes overlapping input safe.
    files = object_store.list_event_files(
        dataset="usage",
        from_time=window.start,
        to_time=window.end,
        lookback_hours=24,
    )
    if not files:
        raise RuntimeError("No usage files found; source may be delayed")

    warehouse.execute(
        """
        COPY INTO staging.usage_events_raw
        FROM @usage_stage
        FILES = %(files)s
        FILE_FORMAT = (TYPE = PARQUET)
        MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
        """,
        {"files": files},
    )

    # Rank duplicate deliveries deterministically before the idempotent merge.
    warehouse.execute(
        """
        MERGE INTO analytics.usage_events AS target
        USING (
            SELECT
                event_id,
                account_id,
                event_type,
                TRY_CAST(properties:duration AS INTEGER) AS duration_sec,
                event_timestamp,
                received_at
            FROM staging.usage_events_raw
            WHERE event_timestamp >= %(start)s
              AND event_timestamp < %(end)s
              AND event_id IS NOT NULL
            QUALIFY ROW_NUMBER() OVER (
                PARTITION BY event_id
                ORDER BY received_at DESC
            ) = 1
        ) AS source
        ON target.event_id = source.event_id
        WHEN MATCHED THEN UPDATE SET
            account_id = source.account_id,
            event_type = source.event_type,
            duration_sec = source.duration_sec,
            event_timestamp = source.event_timestamp,
            loaded_at = CURRENT_TIMESTAMP()
        WHEN NOT MATCHED THEN INSERT (
            event_id, account_id, event_type, duration_sec,
            event_timestamp, loaded_at
        ) VALUES (
            source.event_id, source.account_id, source.event_type,
            source.duration_sec, source.event_timestamp, CURRENT_TIMESTAMP()
        )
        """,
        {"start": window.start, "end": window.end},
    )

    result = warehouse.fetch_one(
        """
        SELECT
            COUNT(*) AS rows_in_window,
            COUNT_IF(account_id IS NULL) AS missing_accounts,
            COUNT(DISTINCT event_id) AS unique_events
        FROM analytics.usage_events
        WHERE event_timestamp >= %(start)s
          AND event_timestamp < %(end)s
        """,
        {"start": window.start, "end": window.end},
    )
    if result["rows_in_window"] == 0:
        raise RuntimeError("Loaded window is empty")
    if result["rows_in_window"] != result["unique_events"]:
        raise RuntimeError("Duplicate event IDs detected")
    if result["missing_accounts"] > 0:
        raise RuntimeError("Required account IDs are missing")

    # Compare-and-set prevents a concurrent run from overwriting newer state.
    checkpoints.commit(
        dataset="usage_events",
        expected=window.start,
        new_value=window.end,
        run_id=run_id,
    )

The code intentionally does not advance the checkpoint in a finally block. Any extract, load, or validation exception leaves state unchanged. A retry may stage files again, but the MERGE converges by event_id. A real implementation would also track loaded file identities so repeated COPY operations do not inflate staging, retain rejected rows, and reconcile event counts against the archive manifest.

Design Decisions

Pipeline design begins with consumer requirements and source capabilities, not a preferred tool.

Idempotency strategy

Choose idempotency before choosing retry settings. Facts with immutable event IDs fit MERGE or insert-on-conflict. Partitioned snapshots fit overwrite-by-partition. Append-only loads need a uniqueness mechanism or a ledger of committed input files. A task retry that only “usually” avoids duplicates is not idempotent.

Contract and schema evolution

Raw landing can accept additive fields while curated tables remain strict. Removing or changing a field type should be versioned and coordinated with consumers. Store the observed source schema for every run. Avoid silently coercing malformed values to null unless null-rate checks and a quarantine path make the loss visible.

Late and corrected data

Define an allowed-lateness window based on evidence. Reprocess that overlap on each run and deduplicate. For corrections outside the window, provide a targeted backfill interface. Event time should drive business windows; ingestion time should support operational debugging.

Build versus buy

Managed connectors reduce protocol and API maintenance for standard sources. Custom ingestion is justified for proprietary APIs, unusual consistency requirements, or economics at high volume. Even managed connectors require destination contracts, monitoring, ownership, and recovery testing.

Comparisons

ETL, ELT, CDC, and streaming solve overlapping but different parts of data movement. A platform often combines them.

ETL versus ELT

ELT does not mean “no transformation during ingestion.” Basic parsing, encryption, and policy filtering may still occur before landing. Likewise, ETL often retains raw inputs for replay. Use the terms to communicate the dominant transformation location, then document the actual stages.

Batch ETL versus CDC and streaming

Diagram: Choosing a data movement pattern

flowchart TD
    A[Freshness need?] -->|Hours| B[Batch ETL]
    A -->|Minutes| C{Row changes?}
    A -->|Seconds| D{Event action?}
    C -->|Yes| E[CDC]
    C -->|No| F[Micro-batch]
    D -->|Yes| G[Streaming]
    D -->|No| E

Choose by consumer latency and change semantics, then verify that the source can support the selected pattern.

Common Mistakes

1. Using non-idempotent loads. Blind append makes task retries duplicate rows. Use stable keys with MERGE, deterministic partition replacement, or a committed-file ledger.
2. Advancing the watermark before validation. A failed load then creates a permanent gap. Commit extraction state only after output is published and checked.
3. Using only updated_at with an exclusive predicate. Equal timestamps or late transactions can be missed. Add a key tie-breaker or overlap window.
4. Running heavy transforms on the application database. Large joins and scans consume resources needed for user transactions. Extract bounded data to staging first.
5. Treating schema drift as a parser problem. Permissive parsing can turn failures into silent nulls. Record schemas, test contracts, and quarantine incompatible records.
6. Ignoring hard deletes. Timestamp polling sees updates but not absent rows. Use CDC tombstones, soft-delete fields, or periodic key reconciliation.
7. Building monolithic jobs. One script that extracts twelve sources and publishes every mart has a large blast radius. Separate replayable datasets with explicit dependencies.
8. Assuming orchestration retries provide correctness. Retries amplify a bad write semantic. First make the operation safe to repeat, then configure bounded retries with backoff.
9. Ignoring late-arriving records. Event delivery and source commits are not perfectly ordered. Define lateness and correction policies explicitly.
10. Alerting only on task failure. A successful task can load zero rows or stale data. Monitor data outcomes as well as process status.

Where It Breaks Down

Low-latency serving. A nightly pipeline cannot support fraud decisions or operational features that require seconds. Increasing frequency eventually creates overlapping runs and scheduler overhead. CDC or streaming is usually the correct transport, with batch retained for reconciliation.

Unstable source APIs. Rate limits, expiring cursors, inconsistent pagination, and retroactive edits make incremental extraction unreliable. The connector needs durable page state, retry budgets, and periodic reconciliation. If the source provides neither stable snapshots nor change tracking, complete correctness may be impossible.

Large mutable tables without keys. Snapshot diff can become prohibitively expensive, while merges cannot identify logical records. The source contract must provide a stable key, change sequence, or immutable files.

Cross-source temporal joins. Joining many systems before load couples unrelated availability and watermark semantics. Land each source separately, preserve event and ingestion times, and perform governed joins in a shared processing layer.

Backfill pressure. A design sized only for daily increments may take weeks to rebuild five years of history. Backfill concurrency can also starve current production runs. Capacity planning must include historical throughput and isolated execution pools.

Unbounded quarantine. Continuing past malformed rows protects freshness but can normalize data loss. Quarantine requires ownership, retention, replay tooling, and thresholds that fail a batch when rejection becomes material.

Warehouse contention and cost. Frequent full scans, unconstrained merges, and poorly partitioned tables consume serving resources. Incremental predicates must prune data, and pipeline compute should be isolated or scheduled around interactive workloads.

When NOT to Use Batch ETL

Do not use batch ETL as the primary path when a consumer must react within seconds, such as online fraud checks, inventory reservation, or operational alerting. The schedule itself creates a minimum latency, and reducing the interval does not provide continuous event-time processing.

Do not create an analytical ETL copy merely to synchronize two operational services. Prefer an explicit service API, transactional outbox, or event contract. A warehouse-oriented batch pipeline does not provide the consistency semantics expected between operational systems.

Do not add a transformation layer when the requirement is a one-time, verifiable export. A versioned query and manifest may be sufficient. Operationalizing a scheduler, checkpoint store, alerting, and backfill process would add maintenance without recurring value.

Do not pull complete source tables repeatedly when a supported log-based CDC interface exists and changes are sparse. Use CDC for transport and batch transforms for consolidation where appropriate.

Do not load raw regulated fields into a destination that is not approved to hold them. If policy requires irreversible masking before data crosses the boundary, use ETL with that control upstream rather than raw-first ELT.

Finally, avoid building custom ETL for standard SaaS sources unless requirements justify owning authentication changes, pagination edge cases, API deprecations, and historical resync. A managed connector can be the lower-risk implementation, but it still needs monitoring and contracts.

Running in Production

Production operation requires both process reliability and data correctness. The table converts common controls into explicit operating requirements.

Best Practice

Treat the watermark as committed data, not scheduler metadata. Store it durably, update it with compare-and-set semantics, and link every value to the input manifest, output version, code version, and validation result.

Measure source lag separately from pipeline duration. A run can finish quickly while reading data that the source published six hours late. Track input freshness, output freshness, volume, rejection rate, duplicate rate, null distribution, and reconciliation differences. Alert on symptoms consumers care about, not only scheduler state.

Backfills should accept explicit intervals or manifests, write through the same transformation code, and avoid changing the live watermark unless intended. Isolate their compute and annotate output lineage. Test recovery by deliberately failing after staging and after loading; confirm that reruns converge and checkpoints remain correct.

Protect credentials in a secret manager and grant separate read and write roles. Avoid embedding source credentials in transformation code or logs. Encrypt staging, define retention, and apply masking before data enters a zone that cannot store sensitive attributes.

Production Checklist

• Every source and output dataset has an owner, freshness objective, and escalation path.
• Extracts use a reproducible closed interval, offset range, snapshot, or immutable manifest.
• Rerunning identical input produces the same logical output without duplicates.
• The watermark advances only after load, publication, and validation succeed.
• Required keys, uniqueness, null rates, row counts, and business totals are tested.
• Additive and breaking schema changes have documented handling and CI coverage.
• Raw staging is encrypted, access-controlled, and retained long enough for replay.
• Metrics cover source lag, freshness, duration, volume, rejects, failures, and cost.
• Hard deletes, late data, and source corrections have explicit reconciliation rules.
• Backfills are parameterized, resumable, isolated from current runs, and exercised.
• Lineage records connect source ranges, code versions, run IDs, and output versions.
• Runbooks cover partial loads, checkpoint recovery, credential failure, and rollback.

Related Guides

Prerequisites

ETL is a foundational topic and has no required guide prerequisite. Familiarity with SQL transactions, primary keys, and object storage helps with the production sections.

Core Concepts

• Data Lakes explains raw and curated object-storage zones used for staging and replay.
• Data Warehouses covers analytical schemas, serving, and warehouse-native ELT.
• Lakehouse combines open lake storage with table transactions and warehouse behavior.

Tools

• dbt implements versioned SQL transformations and tests in ELT workflows.
• Spark executes distributed transformations and large backfills.
• Kafka transports ordered event streams and often feeds raw archives.
• Databricks provides managed lakehouse processing and orchestration.
• Trino, DuckDB, and Iceberg support query, local validation, and transactional lake tables.

Diagram: ETL learning path

flowchart LR
    E[ETL] --> L[Data lakes]
    E --> W[Warehouses]
    L --> H[Lakehouse]
    W --> H
    H --> T[Data tools]

Learn the movement contract first, then study storage destinations, combined lakehouse architecture, and implementation tools.

Interview Questions

1. Why must a watermark be committed after validation?

Committing it earlier can skip an interval when loading or validation fails. Leaving the prior value in place causes a retry to reread data; an idempotent destination write makes that overlap safe.

2. What makes an ETL load idempotent?

Given the same logical input, one or many executions produce the same logical destination state. Common implementations are MERGE on a stable key, exact partition replacement, and input-file ledgers with uniqueness constraints.

3. How would you extract rows when updated_at is not unique?

Use a compound cursor such as (updated_at, primary_key) and a deterministic ordering. Query rows lexicographically after the previous cursor, or reread an overlap window and deduplicate by key.

4. What is the difference between ETL and ELT?

ETL performs the main transformation before curated data enters the destination. ELT lands source-aligned data first and transforms it with destination compute. Real systems often combine both.

5. When is CDC preferable to timestamp polling?

CDC is preferable when deletes matter, timestamps are unreliable, change volume is much smaller than table size, or consumers need low-latency replication. It requires transaction-log access and durable offset management.

6. How do you handle late-arriving data?

Define an evidence-based lateness window, reread overlapping input, and deduplicate idempotently. Provide targeted backfills for corrections beyond the normal window and use event time for business attribution.

7. What checks belong before publication?

Check primary-key uniqueness, required fields, accepted values, referential integrity, source-to-target counts or totals, freshness, rejection thresholds, and domain invariants. The exact set depends on consumer risk.

8. How would you model customer history?

Use SCD Type 2 when reports must resolve facts against attributes valid at event time. Maintain non-overlapping validity intervals, one current row per business key, and surrogate keys for versions.

9. How should backfills differ from scheduled runs?

They should use the same transformations but accept explicit historical boundaries, run on isolated capacity, record separate lineage, and avoid moving the live checkpoint by default. They must be resumable and observable.

10. What would you monitor beyond task success?

Monitor source lag, output freshness, row volume, duplicates, rejected records, null distributions, reconciliation totals, duration, retry count, and cost. A successful scheduler task does not prove correct data.

Key Takeaways

• ETL converts source-specific operational data into governed analytical datasets; byte movement alone is not sufficient.
• Reliable incremental processing uses reproducible boundaries, durable staging, idempotent loads, validation, and delayed watermark commits.
• ETL and ELT primarily differ in transformation placement. Hybrid systems are normal.
• CDC captures database changes and streaming supports continuous reactions; both still require transformation, quality, and recovery.
• Stable keys, explicit delete handling, schema contracts, and late-data policy determine long-term correctness.
• Production readiness includes ownership, observability, lineage, security, backfills, cost controls, and tested failure recovery.

FAQs

Is ETL always batch processing?

No. The phases can be implemented continuously, but “ETL” commonly describes bounded batch pipelines. Streaming systems use analogous extraction, transformation, and sink operations with different state and time semantics.

What is a staging area?

It is intermediate durable storage for raw or partially parsed input before publication. Staging supports replay, audit, isolation from source outages, and debugging of transformations.

Should raw data be retained?

Retain it when replay, audit, or future transformations justify the storage and policy permits it. Apply encryption, access controls, retention limits, and deletion obligations. Raw retention is not permission to copy sensitive data indiscriminately.

How often should an ETL pipeline run?

Derive frequency from the consumer freshness objective, source capacity, and cost. Daily reporting does not need a one-minute schedule. If the required interval becomes shorter than normal batch duration, evaluate CDC or streaming.

How are source deletes handled?

Use CDC tombstones, an explicit soft-delete field, or periodic reconciliation between source and destination keys. Timestamp incremental extraction alone cannot discover a row that no longer exists.

Can dbt perform extraction?

dbt primarily transforms data already accessible to its target engine. A connector, replication tool, or custom job generally performs extraction and landing; dbt then builds tested models.

When should Spark be used instead of warehouse SQL?

Use Spark when transformation volume, file processing, custom code, or cross-storage workloads exceed practical warehouse SQL patterns. Use SQL when data already resides in the warehouse and relational transformations dominate.

How do I validate a backfill?

Compare counts and business totals by historical partition, check uniqueness and referential integrity, inspect rejection rates, and compare selected periods with trusted reports. Publish only after checks pass, and preserve the prior output version for rollback.

References

• dbt: What is ELT?
• Apache Airflow: Core Concepts
• Debezium Documentation
• Kimball Group: Slowly Changing Dimensions
• Apache Spark SQL, DataFrames and Datasets
• Apache Iceberg: Reliability

Further Reading

• dbt: Incremental Models
• Apache Airflow: Best Practices
• Debezium: Architecture
• Confluent: Event Time and Processing Time
• Kimball Group: ETL Architecture
• Apache Iceberg: Evolution