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.
On this page
ETL pipeline stages
Each stage needs an observable input, output and failure boundary.
| Stage | Main responsibility | Useful evidence | Failure example |
|---|---|---|---|
| Extract | Retrieve the intended source population | Source position and bounded batch | Missing page of API results |
| Validate | Check required identity, types and fields | Accepted and rejected records | Missing business key |
| Transform | Apply the agreed business and format rules | Reproducible mapped values | Currency units changed incorrectly |
| Load | Write the intended destination state | Keys, versions and transaction result | Duplicate rows after a retry |
| Reconcile | Compare the result with expected records | Counts, totals and selected identities | Correct count with wrong values |
| Commit progress | Record the accepted source boundary | Durable checkpoint | Cursor 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.
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.
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
| Symptom | Likely investigation | Required acceptance check |
|---|---|---|
| Destination stops changing | Source cursor, permissions or extraction scope | Known new and changed records arrive |
| Rows multiply after retry | Missing key or non-idempotent append | Repeated batch preserves expected records |
| Earlier update disappears | Cursor advanced before accepted load | Failed batch leaves recoverable progress |
| Revenue changes unexpectedly | Units, currency, joins or stale overwrite | Known records and independent total agree |
| Deleted customers remain active | Missing deletion mechanism | Accepted source deletion reaches intended state |
| Historical report changes silently | Untracked backfill or definition change | Run 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
- Define the source object, business key and accepted destination model.
- Choose the extraction boundary and required update/delete mechanism.
- Write transformation rules with explicit invalid-data handling.
- Implement repeatable loading and recoverable checkpoint behavior.
- Reconcile a small known batch, its retry and a changed record.
- Introduce a controlled failure and verify that progress remains recoverable.
- Document freshness, monitoring and the operating owner.
- 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
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
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
Data WarehouseSchema Drift in Marketing Pipelines: 7 Changes to Detect
Detect seven forms of schema drift in marketing pipelines. Compare contracts and alerts, catch new CRM values and repair reports without losing records.
Jafrin Akter AloLast updated 12 min read
Data WarehouseHow to Set a Useful Marketing Data Freshness SLA
Set a marketing data freshness agreement around real decisions. Define cutoffs, coverage, deadlines, alerts and recovery, with a runnable readiness fixture.
Jafrin Akter AloLast updated 14 min read
Data WarehouseData Incident Runbook: 8 Steps to Recover Marketing and Product Data
Resolve marketing data incidents with eight practical steps, severity rules, replay limits, reconciliation checks and copyable stakeholder messages.
Jafrin Akter AloLast updated 14 min read
Data warehouse & CDP
See the implementation scope, deliverables and acceptance checks.
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

Written by
Jafrin Akter AloData 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.