Five ETL Pipeline Patterns Every Team Ends Up Needing

Every ETL pipeline starts as a full copy of a table on a schedule. Every ETL pipeline that survives contact with production ends up using some combination of the five patterns below. Knowing them in advance saves the rewrite.

For each pattern: what it is, when to use it, the failure it prevents, and the detail that makes it safe to rerun.

1. Full load

What it is. Read the whole source table and replace the whole destination table.

When to use it. Small reference tables such as countries, product categories or exchange rates; any source without a reliable way to detect changes; the first run of any other pattern.

What it prevents. Drift. Because the destination is replaced, it cannot accumulate rows that were deleted upstream.

Making it safe. Load into a staging table and swap, so readers never see a half-written table. Replace, do not append: an appended full load is the most common source of duplicate rows in a warehouse.

2. Incremental load

What it is. Read only the rows that changed since the last run, using a cursor: an updated-at timestamp, an increasing ID, or a version number.

When to use it. Large tables where a full load would take too long or cost too much against the source. This is the default for transactional data.

What it prevents. Reading millions of unchanged rows every hour.

Making it safe. Three details decide whether incremental loads are reliable:

• Overlap the cursor. Read from a little before the last cursor, not exactly from it, so rows committed late with an earlier timestamp are not missed. The overlap costs a few duplicate reads, which the next point absorbs.
• Merge on a key. Upsert into the destination on the primary key, so a row read twice is written once. This is what makes the load idempotent: running it twice gives the same result as running it once.
• Handle deletes. An updated-at cursor never sees a deleted row. Either the source uses soft deletes, or you periodically reconcile keys, or you move to change data capture.

3. Change data capture

What it is. Read the database's own change log, such as the write-ahead log in PostgreSQL, and stream every insert, update and delete as it happens.

When to use it. When deletes matter, when latency has to be minutes rather than hours, or when the source cannot afford repeated queries. CDC is how a warehouse tracks reality instead of last night's snapshot.

What it prevents. Missed deletes, stale data, and query load on production databases.

Making it safe. CDC is sensitive to schema changes and to log retention: if the pipeline falls behind further than the database keeps its log, you need a fresh snapshot. Treat the snapshot plus the stream as one pipeline with a recorded position, not two scripts. CDC via Debezium is part of Revoluzen Flow Enterprise; the open-source build covers incremental loads with cursors. See the PostgreSQL CDC guide.

4. Backfill

What it is. Reprocess a window of history, from a day to several years, through the normal pipeline.

When to use it. After a bug in the transformation, after a source outage, when a new column has to be populated historically, or when onboarding a source that has years of history.

What it prevents. History that was produced by a different code path than the daily run, which is the usual cause of "the numbers changed when we reran".

Making it safe. A backfill must be the same pipeline with a date range, not a one-off script, and it must be idempotent for the same reason incremental loads are: a window reprocessed twice must give one result. Partition the destination by the date the pipeline runs on, and replace partitions rather than appending to them. Revoluzen Flow treats backfills as a first-class run type for this reason.

5. Slowly changing dimensions

What it is. Keeping history for attributes that change over time, such as a customer's segment or a product's category, so that a report for March uses the values that were true in March.

When to use it. Whenever a report has to be reproducible as of a date, which is most finance, regulatory and commission reporting.

What it prevents. Silent restatement of history. Without it, a customer who moves segments today changes last year's revenue by segment.

Making it safe. The usual approach, called type 2, adds valid-from and valid-to dates to each row and closes the old row when a new version arrives. The hard part is getting the close date right under reruns: closing a row twice or leaving two rows open corrupts the history. Model the dimension once, in one place, and let every pipeline write through that model rather than reimplementing the logic per pipeline. In the Revoluzen platform that place is Revoluzen Catalog.

The pattern behind the patterns

All five come down to the same rule: the destination must be a pure function of the source and the run window. Replace partitions, merge on keys, record the cursor, keep history explicit. A pipeline built that way can be rerun, backfilled and audited; a pipeline that appends can only be rerun once.

The tool matters less than the discipline, but a tool that makes idempotent loads and per-step resume the default removes most of the ways to get it wrong. That is the design center of Revoluzen Flow.

Frequently asked questions

What is the difference between incremental load and change data capture?

Incremental load queries the source for rows whose cursor is newer than the last run; it misses deletes and runs on a schedule. Change data capture reads the database's change log, sees every insert, update and delete, and runs continuously. CDC is more complete and lower latency; incremental load is simpler and works on sources without an accessible log.

What does idempotent mean for an ETL pipeline?

Running the pipeline twice over the same window produces the same destination as running it once. It is achieved by merging on keys or replacing partitions instead of appending rows.

How do I backfill without duplicating data?

Run the same pipeline over the historical window with partition replacement or key merging in place, never a separate script that appends. Verify by comparing row counts and totals per partition before and after.

When should I use a full load instead of an incremental load?

For small tables, for sources without a reliable change cursor, and as the initial run that seeds an incremental or CDC pipeline.

What is a slowly changing dimension type 2?

A dimension table that keeps every historical version of a row, each with a valid-from and valid-to date, so that facts can be joined to the version that was current when they occurred.