ETL Pipelines: Stages, Incremental Loads and a Reliable Working Example

An ETL pipeline extracts source data, transforms it into an accepted structure and loads it into a destination. A useful pipeline also preserves progress, handles retries and verifies the resulting records. Start with the source contract and expected output, then test an update and a failed run before scheduling the process. A successful first load is only the beginning of reliable operation.

Jafrin Akter AloLast updated 10 min read
On this page

ETL pipeline stages

Each stage needs an observable input, output and failure boundary.

ETL pipeline stages
StageMain responsibilityUseful evidenceFailure example
ExtractRetrieve the intended source populationSource position and bounded batchMissing page of API results
ValidateCheck required identity, types and fieldsAccepted and rejected recordsMissing business key
TransformApply the agreed business and format rulesReproducible mapped valuesCurrency units changed incorrectly
LoadWrite the intended destination stateKeys, versions and transaction resultDuplicate rows after a retry
ReconcileCompare the result with expected recordsCounts, totals and selected identitiesCorrect count with wrong values
Commit progressRecord the accepted source boundaryDurable checkpointCursor advances before the load succeeds

AWS's ETL overview describes the extract, transform and load sequence. The additional operating boundaries in the table make that sequence testable: they identify what must succeed before a run is accepted.

We would choose one source and one useful destination model first. Define the business key, required history, update behavior and freshness target. A broad connector catalog does not answer those questions for a particular source object.

At Martechdevs, data warehouse and pipeline implementation connects the source contract to a maintained reporting result. We retain a small reconciliation sample and a recovery path so the next failed run can be investigated without rebuilding the pipeline from memory.

A white crank-operated mill turns lime blocks into a pink strip beside the ETL Pipeline headline.

ETL, ELT and orchestration

In ETL, the transformation occurs before the data is loaded into the intended analytical destination. In ELT, data is loaded first and then transformed within the destination environment. A real architecture can combine both approaches for different operations.

The ETL versus ELT guide covers that design choice. The important point here is to identify the actual transformation boundary instead of describing every ingestion connector as a complete ETL system.

Orchestration coordinates tasks and their dependencies. Apache Airflow, for example, represents a workflow as a DAG of tasks and supports scheduled or triggered runs. A task graph does not automatically establish correct business data; the tasks still need their own validation and recovery behavior. See Airflow DAG concepts.

Keep the processing interval separate from the time the job runs. A backfill executed today can process an earlier period. Airflow documents a data interval for each run, which helps make that distinction explicit. The destination should reflect the intended business period rather than whichever day the operator pressed Run.

Source contracts and extraction boundaries

The source contract should identify the object, business key, fields, types, update marker, deletion mechanism and historical range. It should also state which account or integration identity can retrieve the required data.

For an API source, include pagination and rate-limit behavior. For a database source, identify a consistent extraction boundary and the mechanism that reveals changed records. A query that reads only new primary keys can miss updates to older records.

Microsoft documents incremental loading with a watermark such as a last-updated timestamp or incrementing key. The changed population lies between the old and new watermark. It separately describes change tracking that can identify inserts, updates and deletes. These mechanisms have different coverage. See incremental-load approaches.

Timestamp watermarks require attention to equal timestamps, precision, delayed updates and clock behavior. An incrementing primary key is not an update log. Choose the source mechanism that actually represents the changes the destination must preserve.

Retain the extraction boundary with the run. If a job reads an open-ended source while it is changing, the team may struggle to explain what belongs in that run. A bounded batch or source-supported snapshot makes reconciliation and replay easier to reason about.

Transformation rules and rejected records

Transformations can normalize types, map names, calculate derived values and apply approved exclusions. Each operation should have a clear reason and expected result. Dropping an unfamiliar field or converting an invalid value to zero can conceal a source change.

For a monetary amount, define currency and units. A source string of 12.00 USD can become 1,200 integer cents in an accepted destination model. The rule should reject unsupported precision or currency unless a documented conversion policy handles it.

Keep invalid input visible. A rejected row needs the source identifier, relevant failure reason and an owner. Whether the pipeline stops the entire batch or quarantines selected records depends on the business requirement, but the report must not present incomplete data as complete without a stated policy.

The schema drift guide covers unexpected source changes. Treat a new field, changed type and missing required property as separate cases rather than one generic connector failure.

A reproducible incremental ETL example

The following synthetic example uses Python's standard library and an in-memory SQLite database. It models a source with stable order IDs and monotonically increasing change versions. Transformation occurs before each destination write, and the destination checkpoint advances only after the accepted batch reconciles.

The initial source has order A for USD 12 and order B for USD 8. A later batch changes A to USD 15 and introduces order C with an invalid amount. That batch must leave both the earlier destination state and checkpoint unchanged. After correcting C to USD 5, the accepted destination total becomes USD 28.

Python
import sqlite3
from decimal import Decimal, InvalidOperation

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE orders (
  order_id TEXT PRIMARY KEY,
  source_version INTEGER NOT NULL,
  amount_cents INTEGER NOT NULL
);
CREATE TABLE checkpoint (version INTEGER NOT NULL);
INSERT INTO checkpoint VALUES (0);
""")
db.commit()

def load_batch(rows, upper_version, expected_cents):
    with db:
        old = db.execute("SELECT version FROM checkpoint").fetchone()[0]
        if upper_version < old:
            raise ValueError("Checkpoint cannot move backward")
        batch = sorted(
            (r for r in rows if old < r["version"] <= upper_version),
            key=lambda r: r["version"],
        )
        for row in batch:
            cents = Decimal(row["amount"]) * 100
            if (row["currency"] != "USD" or not cents.is_finite()
                    or cents < 0 or cents != cents.to_integral_value()):
                raise ValueError("Unsupported monetary value")
            db.execute("""
                INSERT INTO orders VALUES (?, ?, ?)
                ON CONFLICT(order_id) DO UPDATE SET
                  source_version = excluded.source_version,
                  amount_cents = excluded.amount_cents
                WHERE excluded.source_version > orders.source_version
            """, (row["id"], row["version"], int(cents)))
        total = db.execute(
            "SELECT COALESCE(SUM(amount_cents), 0) FROM orders"
        ).fetchone()[0]
        if total != expected_cents:
            raise ValueError("Reconciliation failed")
        db.execute("UPDATE checkpoint SET version = ?", (upper_version,))

initial = [
    {"id": "A", "version": 1, "amount": "12.00", "currency": "USD"},
    {"id": "B", "version": 2, "amount": "8.00", "currency": "USD"},
]
load_batch(initial, 2, 2000)
load_batch(initial, 2, 2000)

changed = [
    {"id": "A", "version": 3, "amount": "15.00", "currency": "USD"},
    {"id": "C", "version": 4, "amount": "invalid", "currency": "USD"},
]
try:
    load_batch(changed, 4, 2800)
except (ValueError, InvalidOperation):
    pass

assert db.execute("SELECT version FROM checkpoint").fetchone()[0] == 2
assert db.execute("SELECT SUM(amount_cents) FROM orders").fetchone()[0] == 2000
changed[1]["amount"] = "5.00"
load_batch(changed, 4, 2800)
print(db.execute("SELECT * FROM orders ORDER BY order_id").fetchall())
print(db.execute("SELECT version FROM checkpoint").fetchone()[0])

The final rows are A at version 3 with 1,500 cents, B at version 2 with 800 cents, and C at version 4 with 500 cents. The checkpoint is 4. Repeating the initial accepted interval does not add another copy of either order.

SQLite's UPSERT documentation explains conflict handling on a unique key. Python's decimal documentation supports the explicit decimal-to-integer transformation. The example demonstrates a bounded local pattern, not a ready-made distributed pipeline or a guarantee about a particular vendor connector.

Transaction boundaries and accepted progress

The example keeps destination writes and the checkpoint in the same SQLite transaction. An invalid amount rolls back the preceding update in that transaction, so source version 3 is not silently skipped when the corrected batch is retried.

Production systems often separate the checkpoint store from the destination. That creates additional failure cases: data may commit before the checkpoint is saved, or a checkpoint may be saved before the destination result is accepted. Design the replay and acknowledgment behavior explicitly for those systems.

Idempotent loading means that repeating the same accepted work produces the intended state rather than another copy of the business record. It does not mean the pipeline ran only once. A retryable design can tolerate repeated processing while preserving the correct keys and values.

Do not infer complete correctness from one total. The example's expected total is an independently specified fixture value. A production reconciliation should also inspect record keys, versions, selected values, exclusions and completeness. Two wrong amounts can accidentally add to the right total.

Updates, deletions and historical repair

An update replaces or adds to history according to the destination's design. A current-state table and an event-history table serve different questions. Decide which one the business needs before choosing an append or upsert strategy.

The sample above maintains current order state and does not implement deletion capture. A real pipeline that must reflect deletion needs an explicit tombstone, change-tracking mechanism or qualified reconciliation process. A record disappearing from a source query is not always enough to tell the destination what happened.

Microsoft's incremental-loading overview distinguishes watermark loading from change tracking that includes deletes. Match the source capability to the required behavior instead of treating incremental as a complete specification.

A backfill should state its source interval, target population and interaction with current data. Reprocessing an older record must not overwrite a newer accepted state unintentionally. The example's version condition illustrates that protection within its stated monotonic-version model; a timestamp-based source needs its own equivalent rule.

Keep the reason for historical correction with the run. A changed business definition and a recovered missing source page can both revise a report, but they should not be explained as the same incident.

Scheduling, freshness and monitoring

A schedule describes when work is attempted. Freshness describes the age of the business information available to a consumer. Source delay, extraction, transformation, loading and reporting can all contribute to the difference.

Use the marketing data freshness guide to define the expected age and the timestamps that demonstrate it. A successful scheduled task with no new source data may be operating correctly, while a successful task that skipped a source page may be misleadingly green.

Monitor the accepted source boundary, row volume, rejected records, output totals and end-to-end delay. Alerts should identify the affected source and business output, not simply report that a generic job failed.

The data incident runbook assigns recovery responsibility. The operator should know whether the next safe action is a retry, credential repair, mapping correction or deliberate historical replay.

ETL pipeline failure diagnostics

ETL pipeline failure diagnostics
SymptomLikely investigationRequired acceptance check
Destination stops changingSource cursor, permissions or extraction scopeKnown new and changed records arrive
Rows multiply after retryMissing key or non-idempotent appendRepeated batch preserves expected records
Earlier update disappearsCursor advanced before accepted loadFailed batch leaves recoverable progress
Revenue changes unexpectedlyUnits, currency, joins or stale overwriteKnown records and independent total agree
Deleted customers remain activeMissing deletion mechanismAccepted source deletion reaches intended state
Historical report changes silentlyUntracked backfill or definition changeRun reason and affected period documented

Use the same ordinary and failure cases after a repair. A corrected field mapping can affect the total, while a changed cursor can alter which records are processed. Approving only the stage that visibly failed can miss those downstream consequences.

Pipeline implementation sequence

  1. Define the source object, business key and accepted destination model.
  2. Choose the extraction boundary and required update/delete mechanism.
  3. Write transformation rules with explicit invalid-data handling.
  4. Implement repeatable loading and recoverable checkpoint behavior.
  5. Reconcile a small known batch, its retry and a changed record.
  6. Introduce a controlled failure and verify that progress remains recoverable.
  7. Document freshness, monitoring and the operating owner.
  8. Schedule the bounded workflow and expand only after acceptance.

The ETL tools guide compares software options after those requirements are clear. Use the software pricing tool to include source usage, processing, storage and support in the operating budget. The connector subscription alone rarely describes the complete pipeline.

ETL acceptance and repair workflow

Selection workflow
A bounded extract must pass source validation before transformation and keyed loading. Source/schema failures and rejected destination reconciliation return to the extract through repair; only accepted output commits progress and proceeds to the next interval.
Read diagram as text
  • Source contract and bounded extract to Valid required records.
  • Valid required records to Source or schema repair: No.
  • Source or schema repair to Source contract and bounded extract.
  • Valid required records to Transformation and keyed load: Yes.
  • Transformation and keyed load to Record and total reconciliation.
  • Record and total reconciliation to Accepted destination state.
  • Accepted destination state to Mapping or replay repair: No.
  • Mapping or replay repair to Source contract and bounded extract.
  • Accepted destination state to Committed progress and monitoring: Yes.
  • Committed progress and monitoring to Next bounded interval
Open full diagram

ETL pipeline checklist

  • A defined source object, business key and historical range.
  • A bounded extract with a documented change mechanism.
  • Required fields, units, currency and rejected-record treatment.
  • An explicit current-state or historical destination design.
  • Repeatable loading without unintended duplicate business records.
  • Recoverable checkpoints tied to accepted destination output.
  • Known inserts, updates, retries and failures in the fixture.
  • An explicit deletion and backfill policy where required.
  • Freshness evidence and a named incident owner.
  • Revalidation after cursor, schema or mapping changes.

The marketing data audit skill helps retain source and reconciliation evidence. A dependable pipeline is one the team can explain and repair after the original implementation is complete.

Keep reading

Browse all Data Warehouse guides

Questions people ask

Does an ETL pipeline finish when the extraction request succeeds?

An ETL pipeline does not finish when the extraction request succeeds. The batch still needs valid input, the intended transformation, accepted destination writes and reconciliation. Progress should remain recoverable if a later stage fails. A successful source response can therefore coexist with an incomplete business result, especially when pagination, rejected records or destination errors are not checked separately.

Can an increasing source primary key capture updates to older records?

An increasing source primary key cannot automatically capture updates to older records. It identifies new keys, while an existing record can change without receiving a new primary key. Use a suitable update watermark, change log or other qualified mechanism for the required behavior, and verify deletion handling separately. See Microsoft's incremental-loading approaches.

Does idempotent loading mean that an ETL task executes exactly once?

Idempotent loading does not mean the task executes exactly once. It means repeating the same intended work preserves the accepted destination result rather than creating unintended duplicates or changes. A pipeline can retry several times and still be correct. Test the stable business key, update version and checkpoint behavior together, because a repeatable write alone does not prevent skipped source data.

Can a matching destination total prove that every ETL record is correct?

A matching destination total cannot prove that every record is correct. Different errors can offset one another, and a correct sum can hide missing identities, duplicated relationships or stale values. Reconcile keys, versions and selected records alongside the total and expected exclusions. Keep a small fixture with independently known results so the acceptance check does not merely repeat the pipeline's own calculation.

Does the local Python example implement deletion capture and distributed recovery?

The local Python example does not implement deletion capture or distributed recovery. It demonstrates decimal transformation, keyed updates and a checkpoint in one SQLite transaction using synthetic change versions. Production sources and destinations may have separate commits, deletion mechanisms and consistency boundaries. Extend the design only after identifying those actual behaviors and testing the required failure and replay cases.

Sources

  1. AWS ETL overview
  2. Microsoft incremental-loading approaches
  3. Apache Airflow DAGs and data intervals
  4. SQLite UPSERT behavior
  5. Python decimal arithmetic
Portrait of Jafrin Akter Alo

Written by

Jafrin Akter Alo

Data Engineer and Co-founder at Martechdevs and Growth and Analytics Partners

Jafrin writes practical guides to marketing technology, customer data, analytics and AI workflows. Martechdevs is a Growth and Analytics Partners company.

View LinkedIn profile

Book a Free Call

Thirty minutes on your stack and what it needs. Free, and no pitch.

Thirty minutes, and you leave with a plan

We look at what you have, what it is costing you and what to do about it first.

  • 30 minutes
  • Video call, link on booking
  • No obligation

What we'll cover

  • Your current stack and where it hurts
  • Quick wins we can ship first
  • An integration roadmap
  • Timeline, scope and next steps

Calendar not loading? Open it in a new tab or email hello@martechdevs.com.