ETL, ELT, and Data-Pipeline Design Patterns

In plain terms

A pipeline does three things to data: takes it out of a source (extract), changes its shape (transform), and puts it somewhere useful (load). The order of the last two gives the two acronyms. ETL transforms the data on the way, in a separate processing system, and loads the finished result. ELT loads the raw data first and transforms it inside the destination, usually with SQL.

The load boundary determines where restrictions and transformations apply. Across either design, a pipeline also needs to extract without missing changes, load safely on retries, process increments, and rebuild history after a logic fix. The following patterns address those concerns.

Choosing where transformation runs

ETL transforms extracted data before loading the chosen analytical destination. It can reduce the volume sent there, enforce pre-load data restrictions, or use a processing runtime the destination does not provide. Keeping a raw copy elsewhere is a separate decision; ETL does not forbid it.

ELT loads extracted data into the destination before the main analytical transformations. A capable analytical engine can run those transformations near the data, often in SQL. Storage, scans, transfer, and access management still cost money. Choose the location for each operation from data restrictions, engine capability, replay needs, and measured operating cost.

	ETL	ELT
Where transformation runs	a separate processing system between source and destination	inside the destination, after loading
What the destination holds	transformed output; raw evidence may be retained separately	raw copies plus the tables derived from them
Language of the logic	the tool’s mappings, or Python/Spark code	mostly SQL
Recomputing after a logic fix	reprocess retained inputs or re-extract if still available	re-run the SQL over the raw copy
Where it still fits	stripping personal data before it lands; sources too small or too odd to load raw; streaming enrichment; heavy compute that the destination cannot do	when the destination can safely store and transform the inputs

Recomputing depends on retained inputs, code, reference data, and permissions, not the acronym. A June fix can be applied to January only if the necessary January evidence remains available. An ELT design that has deleted its inputs cannot reconstruct them merely by rerunning SQL.

Some transformations must precede loading, such as removing fields prohibited in the destination. Hashing an identifier is not automatically equivalent to removing personal information. Other operations can follow loading. Streaming describes how input is processed over time, so it can participate in either ETL or ELT depending on the chosen load boundary. A platform can combine both.

Extraction patterns

Extraction determines which changes downstream processing can observe. Compare the four approaches by coverage, ordering, replay, and source load; they need not be mutually exclusive.

Pattern	What is read	Sees deletes?	Cost on the source	Use when
Full snapshot	the whole table, every run	absence can be detected from complete snapshots of the same scope; not the exact delete time	a full scan	small tables; reference data; when nothing else is possible
Incremental by watermark	rows with updated_at later than the last run’s high-water mark	no	an index range scan, if the column is indexed	large append-mostly tables with a reliable modification timestamp
Log-based change data capture	the database’s own transaction log	within capture scope and source ordering guarantees	log retention, decoding, CPU and network overhead	large tables; deletes matter; history of changes matters
File or API delivery	whatever the provider sends	only if the provider says so	provider load, API limits, pagination and transfer costs	partners, SaaS vendors, exports

Record what a batch represents: source snapshot or cursor, lower and upper extraction boundaries, delivery identifier, row count, and applicable schema/code version. Retain input evidence needed for replay under the data policy. If fields must be removed before storage, record that transformation; do not claim the stored representation is the unchanged source. Metadata alone cannot reconstruct data that was not retained.

The watermark’s weak spot

An updated_at cursor requires every relevant change to advance that value. Tied timestamps, concurrent commits, pagination, and updates arriving behind the cursor can cause misses. A (timestamp, key) cursor can resolve pagination ties but not every late commit; use a consistent upper boundary, overlap with idempotent loads, or a log-based capture protocol as appropriate. Hard-deleted rows cannot appear in a query of current rows. Reconciliation requires a complete comparable snapshot, and CDC requires reliable capture and recovery. This extraction cursor is distinct from an event-time watermark used to manage stream progress.

Staging: a place to be wrong safely

A staging area isolates input or intermediate results from the version consumers use. It may be a temporary table, a persistent table keyed by run ID, or a storage prefix. Access and publication rules create the separation; naming a table staging does not do so. A temporary table also disappears with its session, so retain permitted input elsewhere when investigation or replay must survive failure.

extract  ──►  staging (this batch only)  ──►  validate input  ──►  transform  ──►  validate output  ──►  publish into the target table
                                                 │
                                                 └─ fail here: target untouched, batch kept for inspection

The validation step is the data-assertion layer from the engineering-habits article: row counts against the source, required columns present, keys unique, values in range. A batch that fails stays in staging; the target table keeps yesterday’s good state; the job exits non-zero and the failure is investigated with the offending batch still available. Direct target writes can also be held in a transaction, checked, and rolled back before commit. Staging makes candidate inspection and isolation explicit; neither approach is safe without effective checks and a controlled publication boundary.

Load patterns

How a batch enters the target table decides whether the pipeline can be rerun. Each pattern below has a defined answer to the question “what happens if this runs twice for the same input?”.

Append

INSERT INTO fact_events SELECT * FROM staging_events;

Plain append duplicates the staged rows on a retry. Use it for data whose intended model adds records, with a stable batch or event identity and a replay policy. A batch ledger only prevents the same batch ID from loading twice; it does not deduplicate overlapping batches. The ledger entry and insert must commit together.

Partition overwrite

BEGIN;
DELETE FROM fact_orders WHERE order_date = '2026-03-14';
INSERT INTO fact_orders SELECT * FROM staging_orders WHERE order_date = '2026-03-14';
COMMIT;

Partition overwrite requires the complete desired contents of every replacement partition, not just changed rows from it. Specify the replacement dates separately from the rows: a partition emptied by source deletions must still be deleted. Replace those dates and insert the snapshot in one supported transaction. A mutable derived partition is different from an append-only raw archive. For object storage, use a supported table commit or publication manifest; a multi-object rename is not a portable atomic swap. S3’s general-purpose-bucket rename workflow uses copy and delete.

Merge (upsert)

MERGE INTO dim_customer_current t
USING staging_customers s
ON    t.customer_id = s.customer_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET city = s.city, segment = s.segment, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (customer_id, city, segment, updated_at) VALUES (s.customer_id, s.city, s.segment, s.updated_at);

With a unique target key and one unambiguous source version per key, merge inserts new identities and applies newer versions. A strict updated_at guard ignores older and equal versions; equal timestamps with different payloads need a defined conflict rule rather than arbitrary last-writer wins. Update every mutable attribute, and reject or deterministically resolve duplicate staged keys. PostgreSQL added MERGE in version 15. SQLite uses INSERT ... ON CONFLICT DO UPDATE; syntax and concurrency guarantees are engine-specific.

Merge with history (type 2)

For type 2 history, detect changes to tracked attributes, close the relevant version, and insert its replacement atomically. Use the business-effective change time, not automatically the load date. With half-open intervals, the old valid_to equals the new valid_from; an inclusive whole-date model instead ends the old row the day before. Late or out-of-order changes can require splitting historical intervals. This article describes the pattern rather than a complete type 2 loader.

Full refresh

BEGIN;   -- requires transactional DDL support
CREATE TABLE mart_daily_revenue_new AS SELECT ... ;   -- build and validate candidate
ALTER TABLE mart_daily_revenue RENAME TO mart_daily_revenue_old;
ALTER TABLE mart_daily_revenue_new RENAME TO mart_daily_revenue;   -- switch is published on COMMIT
DROP TABLE mart_daily_revenue_old;
COMMIT;

Build a complete candidate table, validate it, then publish with a mechanism supported by the database. Separate autocommitted renames are not an atomic swap. For simple SQLite tables without dependent views, foreign keys, or special grants, construction and renames can be wrapped in one transaction. In a production schema, also preserve constraints, indexes, grants, and dependency behavior; a native swap or stable view may be more appropriate. Readers may wait on locks rather than see partial output.

Pattern	Rerun twice gives	Fits	Watch for
Append	duplicates, unless a batch ledger prevents the second load	immutable events with a load ledger	retries; re-delivered files
Partition overwrite	same partition, same content	derived date partitions; replaceable staging tables	batches that span partitions; late rows for old partitions
Merge	same rows, same values	state tables; incremental loads with possible overlap	duplicate keys in the batch; out-of-order batches
Merge with history	no new versions	type-2 dimensions	comparing on load time instead of attributes
Full refresh with swap	identical table	marts; small or medium tables	cost at scale; downstream views bound to the old object

Idempotency as a pipeline property

Idempotency means that applying the same logical load again has no additional effect on its target state. Reproducing a whole pipeline result also requires the same input snapshot, code, reference data, and deterministic transformations. The same date parameter is not enough when the live source has changed. A corrected source can legitimately produce a different result for that date.

Retain a reproducible input version and use a replay-safe write pattern. External side effects need their own delivery protocol: an email sent before its ledger entry is committed can be sent again after failure. A local ledger alone cannot atomically cover another service; use receiver-supported idempotency keys or an outbox with deduplicated delivery where appropriate.

Test the same frozen input and transformation version twice, then compare target rows in a stable order. Counts and checksums are useful summaries, but a matching count does not prove content equality. A truncated checksum is not a collision-free proof, and the comparison requires a defined serialization format. Also test failures between writes and replay after an uncertain outcome.

Incremental processing

Processing changes can reduce repeated scanning, but runtime still depends on change volume, joins, lookback, indexes, and destination writes. It does not guarantee a fixed twenty-minute run. Three design elements make the retained progress usable.

The watermark

A stored position that identifies successfully processed input: a validated modification-time cursor, an ordered file sequence, a set of offsets per Kafka partition, or a date under an explicit source protocol. The next run reads from there. Store it in a control table with the run that set it, so that it can be moved backwards deliberately when a reprocess is needed, and so that a failed run does not advance it.

pipeline_state
pipeline_name      | watermark_value           | set_by_run | set_at
load_orders        | 2026-03-14T02:00:00Z      | run-8812   | 2026-03-14T02:19:07Z

The lookback window

Choose the clock for the lookback. A reliable modification-time cursor may capture a newly recorded order even if its order_date is old; filtering only on order_date may miss it. Rebuild the event-date partitions actually affected by updates, including both dates when a row moves between partitions, or apply a bounded lookback with a route for older corrections. Choose the horizon from observed delays and the accepted residual risk, not a universal three-day rule.

-- Example replacement: four dates, including the run date; complete snapshots required
BEGIN;
DELETE FROM fact_orders WHERE order_date BETWEEN '2026-03-11' AND '2026-03-14';
INSERT INTO fact_orders
SELECT * FROM staging_orders WHERE order_date BETWEEN '2026-03-11' AND '2026-03-14';
COMMIT;

Label provisional results with the source cutoff and last successful publication time. A refresh timestamp alone is not evidence that all source data through that time is complete.

The full-refresh escape hatch

Plan a recovery path for incremental tables: a full rebuild, a bounded rebuild, or restoration plus replay. It requires retained inputs, compatible versions, permissions, and enough capacity to meet the recovery objective. A full refresh is useful when feasible, but neither safe nor available merely because a command exists.

Backfill

A backfill recomputes history: a range of past partitions, run through an explicitly selected code and reference-data version. It happens after a logic change, after a source correction, and when a new table is added that needs history. Three habits make it routine rather than frightening.

• It is the same job with different dates. Reuse versioned transformation logic with explicit historical input boundaries. Independent daily partitions can be rebuilt in a loop; rolling features and cross-date dependencies may need extra input history and a dependency-aware execution order. Bulk scheduling can differ from the daily run without duplicating business logic.
• It is bounded and announced. “Recompute 2026-01-01 to 2026-03-14 of fact_orders and everything downstream, starting 22:00” tells consumers which numbers will move and when. Orchestrators run this as a first-class operation with limited parallelism so the warehouse is not saturated.
• Downstream goes too. Rebuilding a fact table without rebuilding the marts derived from it leaves the platform inconsistent. Lineage is what tells you what “downstream” contains.

Coordinate a backfill with ordinary loads. An older snapshot published after a fresh daily run can overwrite newer data even if both writes are individually atomic. Serialize overlapping partitions or publish versioned candidates with a check that the expected target version is still current. A scheduler timeout alone does not stop a remote query or revoke an old worker’s ability to publish.

Publishing atomically

Readers should see a table either before a load or after it, never during. Several mechanisms provide this, and which one applies depends on the storage:

• A transaction around delete-and-insert in a database that supports it.
• A database-supported atomic swap or transactional publication of a validated table; separate renames are insufficient.
• A table format commit on a lake (Iceberg, Delta), which replaces the list of files a table points to in one step.
• Versioned files and a publication manifest that readers follow only after validation. A _SUCCESS marker alone does not make replacement of existing files atomic.

Atomic publication prevents readers from seeing an intermediate version within that publication boundary. It does not make failures in other tables or external services harmless, nor does it prove the input is correct. Test both rollback and reader behavior for the actual storage engine.

Handling bad records

A batch can contain malformed rows: a missing key, a date in the wrong format, a currency code that does not exist. Two responses are common and both have a place.

Fail the batch when its input cannot support the promised output, such as a schema change that nulls required keys. Quarantine rows with their input, reason, and batch ID for investigation. Publish the remaining rows only if the consumer contract permits incomplete output; a small rejection rate alone does not justify it. One missing high-value order can invalidate a financial total. Set rate and impact thresholds, name an owner, and define how corrected rows re-enter processing.

quarantine_orders
batch_id   | order_id | reason                         | raw_row
run-8812   | A4471    | amount not numeric: 'N/A'      | {...}
run-8812   | NULL     | order_id missing               | {...}

Failure classes and what to do with each

Failure	Example	Response
Transient	source timeout; warehouse busy; network drop	retry with backoff; the load pattern makes the retry safe
Input or access failure	schema changed; credentials revoked; expected file still missing after its deadline	stop, alert, keep the batch in staging; a human decides
Permanent, in the code	a join now fans out; a type error on a new value	stop; fix; rerun the affected dates
Partial	three of five source tables extracted	fail the required coverage check and hold publication; staging alone cannot detect missing sources
Poison record	one row crashes the parser	retain the row and reason; publish the rest only under the agreed completeness policy
Slow	a run that used to take 20 minutes takes 3 hours	bound runtime and concurrency; confirm cancellation or prevent a stale worker from publishing before retrying

The orders pipeline, assembled

The subscription-commerce company from the architecture article, its orders table in Postgres, and the daily job that maintains fact_orders. In ELT form, as the platform would run it:

1. extract   choose a consistent input boundary; retain permitted input and batch metadata
2. stage     load the retained input into an isolated staging area
3. validate  check source coverage, schema, required keys and source-version conflicts
4. transform build candidate core rows and marts; identify every affected partition
5. validate  check candidate outputs and reconcile with the agreed reference
6. publish   commit validated outputs using supported transactions or a versioned publication protocol
7. state     record successful input progress; make replay safe if this is a separate commit
8. monitor   retain run metadata and handle later corrections, deletes and rejected rows

Build and validate candidate outputs before publication; a failed output check must not follow an irreversible publish. Advance extraction progress only after the required outputs are committed. If progress and outputs cannot share a transaction, design recovery so replay is safe and a failed run cannot skip input. Repeating a retained input version should have no additional logical effect; re-reading a changed live source is a new input.

Keep extraction, transformation, validation, and publication as reviewable units. An orchestrator orders them and reports failures; the storage operations themselves must still implement the claimed transaction and replay guarantees.

Choosing between the patterns

• Small table, or cheap to recompute: full refresh with swap. Prefer the simpler rebuild when its runtime, source load and publication behavior meet the requirement.
• Date-partitioned facts: partition overwrite with a lookback window.
• Keyed state, or batches that may overlap: merge with an updated-at guard.
• History of attribute changes required: merge with history at the required resolution; daily snapshots cannot capture every intra-day transition.
• Immutable events at high volume: append, with a batch ledger, into date partitions.
• Deletes matter, or the watermark column is unreliable: capture changes with CDC and apply ordered inserts, updates, and explicit deletes or tombstones. An upsert alone does not remove deleted keys.
• Personal data that must not land: a transformation before the load, on the way in.

References

• Amazon S3 User Guide, Copying, moving, and renaming objects
• PostgreSQL Documentation, Release 15
• SQLite Documentation, Atomic Commit In SQLite