Understanding delta replication

Delta replication copies only what changed instead of copying everything. That sounds simple, but the devil is in the details.

What people mean by "delta replication"

Delta replication is any method that moves only the changed portion of a dataset from a source to a target, rather than copying the entire table or file each time. The word "delta" refers to the difference between two states: what the target already holds, and what the source holds now.

The term is used for at least four mechanisms, which is why search results for it scatter across unrelated fields. 

• In data pipelines, delta replication means sending changed rows from a source database to a target table. 

• In file synchronization, it means transmitting only changed bytes, which lowers bandwidth use across a slow WAN. 

• Inside a distributed database, the same principle applies to replica writes between nodes, where the receiving node already holds the prior version of a record and needs only the bytes that changed. 

Those four definitions are the same basic idea: transmit the difference, not the whole, but they solve different issues and have different problems. 

Operational risks in data replication happen because they’re not as easily seen. A file-sync delta that drops bytes produces a corrupt file, which is visible. A data pipeline delta that drops a deletion produces a target table that looks complete and reads cleanly but is wrong.

Delta load vs. incremental load vs. CDC vs. CDF vs. SCD

These five terms operate at different layers, and conflating them is the most common source of design mistakes in replication projects. 

• Delta load and incremental load are ingestion strategies. 

• Change data capture is a detection method.

• Change data feed is a platform feature.

• Slowly changing dimensions are a modeling choice. 

Here is how they are different:

Two limitations should be pointed out. 

• Delta sharing is not replication at all: it shares tables in a Unity Catalog metastore in real time without copying them, so recipients read the provider's data rather than maintaining their own replica.

Not all platform vendors use the same terminology. SAP Datasphere calls the mechanism a replication flow and has load types named Initial Only, Initial and Delta, and Delta Only. But the point is the same: Do you want the entire table once, the changes, or both?

Separating these layers changes what you evaluate. If you choose a tool without first deciding whether source deletions need to be reflected in the destination, you may focus on connector count and latency and discover later that the data is retaining records that should have been deleted. 

How delta replication works

Delta replication identifies changed rows in one of two ways: 

• Querying the source

• Reading its log 

Query-based detection records where the previous run stopped and asks the source for rows that changed after that point. A pipeline records that it last saw updated_at = 2026-09-01T04:00:00Z, then selects rows newer than that on the next run. This requires no special database privileges and puts a normal read query against the source, which is why it is the default in most tools.

Log-based detection reads the database's own write-ahead log or binary log, where the engine already records every committed change in order. Because the log records what the database did rather than what a query sees afterward, log-based capture sees deletions and every intermediate update, not just the latest state of each row.

Delivery is different from detection. 

• A log-based stream feeds a target continuously

• A job batches the changes and applies them every few minutes

Query-based detection is almost always batch, because the cursor query has to run on a schedule. It is also easier and cheaper to set up, but it cannot detect some types of changes, such as deletions. 

Log-based detection captures all changes, including deletions, but it requires more setup and maintenance than query-based detection. 

Why delta replication pipelines fail 

The issue with delta replication is that its common problems do not raise errors. A pipeline can work properly for weeks while the target table drifts away from the source, because most of the ways delta replication breaks produce a successful run with incomplete data.

Four problems account for most of this:

• Missed deletions. A cursor query returns rows that exist. Rows that were deleted cannot appear in a result set, so the target keeps them.

• Cutover gaps and duplicates. The handoff from initial snapshot to ongoing change stream either skips changes made during the snapshot or reprocesses them.

• Schema drift. A source column is renamed or its type narrowed, and the pipeline writes miscast values into the target rather than failing.

• Replication slot growth. A log-based consumer stops consuming, and the source database retains write-ahead log data indefinitely while the pipeline reports nothing unusual.

None of these problems causes an error. Three of them produce no log line at all until something downstream is wrong, such as a reconciliation that will not balance, a customer record that should have been removed, or a source database that runs out of disk.

Monitoring pipeline execution tells you almost nothing about pipeline correctness. Execution monitoring determines whether the job ran. Correctness monitoring determines whether the target matches the source, and it requires reconciliation checks that aren’t always included. 

Here is more detail on each of the four problems.

How to handle deletes 

Standard incremental replication cannot transmit deletions, and there’s no way to fix it because of the way it works. Because a query cannot return deleted records, standard incremental replication does not transmit deletions from the source to the destination. Moreover, records transmitted in each sync represent the state of the source when the sync ran, so intermediate changes such as multiple updates to one row are not captured.

That means a deleted row lives on in the target forever. A customer who exercised a deletion request is removed from the source database but remains in the analytics warehouse. An order cancelled and physically deleted still counts toward revenue.

Here’s how to deal with this, in rough order of cost:

Soft deletes at the source. Rather than issuing DELETE, the application sets a deleted_at timestamp and updates the row. The deletion becomes an update, which a cursor query sees. This is the cheapest fix and requires changing application behavior. 

Log-based CDC. Reading the transaction log captures deletion events, because the log records the deletion the engine performed. 

Reconciliation by primary-key set difference. For sources where CDC is unavailable, the target infers deletions by comparing key sets. Periodically extract the full set of primary keys from the source, compare against the set in the target table, and treat keys present in the target but absent from the source as deletions:

-- Rows in the target that no longer exist in the source
SELECT t.id
FROM   target_table t
LEFT JOIN source_keys s ON t.id = s.id
WHERE  s.id IS NULL
  AND  t.is_deleted = FALSE;

This reads only the key column from the source rather than the entire table, so it is cheaper than a full reload while still catching every deletion. Running it nightly against a daily incremental load limits deletion staleness to twenty-four hours.

Periodic full reconciliation. Reloading the entire table on a schedule resolves deletions along with every other form of drift. It is the most expensive option but the most reliable, and it remains a reasonable choice for small dimension tables.

Deletion handling is a design decision made at the start of a project. Choosing query-based incremental replication means solving deletions separately. 

How to handle cutover without gaps or duplicates

The cutover from initial load to ongoing change capture risks making a mistake. Snapshot the table first and then start the stream, and every change made during the snapshot is lost. Start the stream first and then snapshot, and rows changed during the window arrive twice.

The correct sequence records a log position before reading any data:

1. Record the current log position. Capture the source's current log sequence number or equivalent marker before touching the table.

2. Take the snapshot. Read the existing data into the target.

3. Start streaming from the recorded position. Replay changes from the marker recorded in step one, which means changes made during the snapshot are replayed rather than lost.

4. Apply changes idempotently. Because step three replays overlapping changes, the target must handle repeated application of the same change without corrupting state. Keyed upserts do this naturally.

5. Reconcile. Compare row counts and a checksum on a sample of keys before declaring the pipeline authoritative.

The overlap in step three is intentional. Duplicates are recoverable through idempotent application; gaps are not recoverable at all without another full load.

Platform behavior can vary, so check the source object's capabilities before committing to a replication approach. In SAP Datasphere, a replication flow falls back to “Initial Only” when a CDS view lacks the required extraction annotations. In that case, the initial load works, but ongoing delta capture does not. 

When planning a migration, that means write down the log position before the snapshot starts, and build the reconciliation query before the cutover rather than after, because it’s effectively impossible to do afterward.

How to handle schema drift

Many pipelines don’t notice schema changes at the source. Three source changes are the most common, and each fails differently.

• A column rename is usually implemented as a drop plus an add. The pipeline sees one column disappear and another appear, so the historical values stop accumulating under the old name and start over under the new one. A type narrowing, such as a bigint becoming an int, or a varchar(255) becoming a varchar(50), truncates values on write. A dropped column removes a field that downstream transformations still reference, and depending on the target's write mode, may simply write nulls.

Teams handle this in one of two ways:

• Schema contracts. The pipeline validates the source schema against a registered expectation and fails the run when they diverge.

• Automatic evolution. The pipeline propagates the change into the target, adding columns and widening types as it encounters them. Data keeps moving, and the target's schema is the most recent source schema

Neither is universally right. Contracts suit pipelines feeding regulated reporting, where a wrong number is worse than a late one. Automatic evolution suits exploratory and event data, where continuity matters more than precision and the schema is expected to move.

A practical middle position works for most teams: Automatically evolve additive changes, which are safe by construction, and fail on destructive ones. Adding a column cannot corrupt existing data. Narrowing a type or dropping a column can. 

Decide this, because the default in most tools is automatic evolution. 

How to keep replication slots from filling the disk

A PostgreSQL replication slot means that the database keeps write-ahead log data until its consumer reads it, and by default there is no upper bound on how much it keeps. When max_slot_wal_keep_size is set to -1, which has been the default since PostgreSQL 13, replication slots may retain an unlimited amount of WAL files.

When a CDC connector is paused, decommissioned, or crashes without its slot being dropped, the slot remains. Consequently, the database keeps every WAL segment the slot has not consumed. WAL accumulates in pg_wal until the volume is full, at which point the primary database stops accepting writes. 

Three practices prevent it:

Set a bound. Configure max_slot_wal_keep_size to a value your disk can handle. Once the bound is exceeded, the slot is invalidated rather than the database being protected indefinitely. That means the slot reaches wal_status = lost with a null restart_lsn, the sync fails, and recovery requires dropping and recreating the slot followed by a full re-sync. While that is inconvenient, it’s better than the alternative. 

Monitor retained WAL per slot. Slot lag is a leading indicator and is queryable from pg_replication_slots. Alerting on retained bytes per slot catches the problem earlier.

Drop slots when you decommission a pipeline. Turning off the consumer does not remove the slot. Make slot removal part of the decommissioning checklist for any CDC connection, because an orphaned slot has no owner watching it.

This is the problem that causes the most damage if you don’t take steps to address it ahead of time.

Why MERGE and upsert performance degrades over time

Applying deltas to a Delta Lake table with MERGE does not update rows in place. Instead, it rewrites whole files, which degrades performance as replication frequency increases. 

Two issues make it worse.

• Write amplification means the cost of a MERGE scales with the size of the files accessed rather than the number of rows changed; updating one row in a large Parquet file rewrites the whole file.

• Small-file proliferation means each frequent, small MERGE produces new small files, and query planning slows as file counts grow.

Three mechanisms address it:

• Deletion vectors. With deletion vectors enabled, DELETE, UPDATE, and MERGE mark rows as removed or changed without rewriting the Parquet file, and reads resolve current table state by applying those markers. Databricks recommends Databricks Runtime 14.3 LTS and above for writing tables with deletion vectors. The soft deletions show up when the files are rewritten, either by OPTIMIZE, by auto-compaction, or by REORG TABLE ... APPLY (PURGE).

• Compaction. Running OPTIMIZE on a schedule consolidates small files into larger ones. Microsoft's Fabric documentation notes that OPTIMIZE automatically purges files where more than 5% of records are referenced by deletion vectors, so routine compaction usually handles cleanup without a separate purge step.

• Clustering on the merge key. Physically co-locating rows by the column used in the MERGE predicate reduces the number of files each merge accesses, which reduces rewrite volume at the source.

Ironically,  increasing replication frequency makes a pipeline slower and more expensive per row, because more frequent merges mean more file rewrites for the same total change volume. Batching changes into fewer, larger merges is often faster than applying them continuously.

Should you build CDC yourself or buy a managed platform?

The build-versus-buy decision in delta replication is mostly determined by who runs the distributed systems underneath. Self-hosted log-based CDC gives you control over latency, cost structure, and behavior. It also means running the capture layer, the message transport, and the connector framework as production infrastructure. 

Here’s a summary of the two models. 

The decision usually turns on two questions rather than a feature comparison. 

1. Does any workload require sub-second latency? Teams that pick self-hosted CDC for latency they do not need do more work without any benefit.

2. Does the team have the capacity to run three distributed systems, including on-call for them, alongside its other work?

Teams with genuine sub-second requirements, unusual source systems, or transformation logic that has to run inside the pipeline get value that managed platforms cannot match.

How consumption-based pricing works 

Most managed replication platforms price on changed data volume rather than on connections or processing, which makes cost a function of your source systems' change rate rather than your own usage decisions. A pipeline nobody used can cost more this month than last because an upstream application started updating a table more often.

Several mechanics drive unexpected increases:

• High-churn tables. A table where most rows change daily bills close to a full reload every day, even though the pipeline is running incrementally.

• Backfills and re-syncs. Any event that forces a full historical load, such as a schema change, a broken replication slot, or a connector reset, processes the entire table volume through a pricing model designed around incremental changes.

• Wide row models. Data models that decompose entities into many small rows generate more billable change events per unit of business activity than models with fewer, wider rows.

• Deletion and metadata events. Depending on the vendor and plan, operations beyond inserts and updates may count toward billed volume.

Three practices make costs more predictable: 

1. Measure the daily change rate on your largest tables before committing to a vendor, using row counts against updated_at rather than estimates. 

2. Model the cost of a full re-sync, because it will happen at least once. 

3. Set volume alerts at the pipeline level so a runaway connector shows up within a day rather than at the end of a billing cycle.

Consumption pricing costs you money based on the upstream change rate, which you don’t control. That’s why it’s better to measure it in advance. 

How to choose a delta replication approach

Eight criteria determine which delta replication approach fits, and latency is rarely the one that should decide it. Teams tend to lead with latency because it is the easiest attribute to compare, then discover the constraints that actually mattered after the pipeline is in production.

Decision criteria practitioners weigh

1. Latency SLA: Sub-second (Debezium, Estuary) vs. minutes (Airbyte/cloud) vs. batch (Fivetran's platform-wide default is every six hours; database connectors default to 15 minutes; 1-minute syncs are Enterprise-plus only). Advice repeatedly given: "pick the tier that matches your SLA, not the fastest you can afford."

2. Deployment/ops burden: Self-hosted (own the Kafka stack) vs. fully managed.

3. Deletion fidelity: Does the method propagate deletes? (Log-based CDC yes; timestamp incremental no; key-reconciliation yes, bounded by reconciliation interval.)

4. Intermediate-state fidelity: Does the method capture every update between syncs, or only the latest state at sync time? (Log-based CDC yes; incremental/reconciliation approaches no.)

5. Primary-key / cursor availability in the source.

(Cost model and predictability, connector/source breadth, schema-evolution handling, and source impact on the production database are also real decision inputs raised in the research,  but they vary by specific tool/vendor rather than cleanly by architectural approach, so they're set aside from this approach-level comparison. See table below.)

Approach comparison

Two rules of thumb:

1. Choose log-based capture when deletions or intermediate states matter, regardless of latency requirements.

2. Choose the latency tier your workload requires rather than the fastest available, because latency below the business requirement is more work without adding value.

When batch incremental replication is enough

Real-time change capture is not useful when the source data changes only once a day. If a dimension table is refreshed nightly at the source, the destination cannot be any fresher than the source, no matter how quickly the pipeline copies the data. 

The test is whether latency changes a decision. A fraud check that must evaluate a transaction before authorizing it needs current data, and stale data produces a wrong answer. A dashboard reviewed on Monday mornings does not, and hourly batch replication provides the same response as sub-second streaming while costing less to run. 

Three conditions make batch delta replication the better choice:

• The source itself updates on a batch cadence, so streaming cannot deliver fresher data than the source produces

• Downstream consumption is periodic, such as scheduled reports, daily model training, or monthly reconciliation

• No automated decision depends on the data being current within minutes

Batch does not mean full reload. A scheduled incremental load that moves only changed rows every hour is still delta replication; it simply delivers those incremental changes on a schedule rather than continuously. Deletion, schema, and reconciliation concerns still apply.

For most teams, the useful move is to require a stated latency requirement before designing for streaming. If no decision breaks at fifteen minutes of delay, the requirement is fifteen minutes.

How does Aerospike use delta replication internally?

Aerospike applies the same delta principle to its own intra-cluster replica writes, not just to external replication pipelines. Wire compression in Aerospike Database 8.2.0 includes a delta mode built for replica writes between nodes: Rather than sending a full copy of a changed record, the receiving node, which already holds the prior version, receives only the bytes that changed.

The mechanism works with standard wire compression to reduce the amount of data sent between nodes for replica writes, partition migrations, and metadata synchronization.  Standard compression typically shrinks replica payloads by 40% to 70%; delta mode shrinks partial updates further still, because only the changed bytes cross the wire. Migration traffic compresses under the same mechanism, which shortens recovery time after a node event. Per-namespace metrics let an operator measure the effect on their own workload before turning the feature on broadly.

This matters for the same reason as consumption pricing. Cloud providers charge for every byte that crosses an availability-zone boundary, and a cluster running across multiple zones pays that cost on every replica write regardless of replication tooling. Delta-based compression at the replica-write layer reduces that cost by moving only what actually changed.

For a team already running a distributed database across multiple availability zones or regions, the next step is measuring before committing. Per-namespace metrics show what delta compression would save on your workload before you decide whether to enable it broadly.

Takeaways

• Delta replication moves only changed data, and the term covers different mechanisms, including pipeline change capture, byte-level file sync, and intra-cluster replica writes.

• Query-based incremental replication cannot see deletions or intermediate row states. Log-based change data capture can, because it reads what the database did.

• Missed deletions, cutover gaps, schema drift, and unbounded replication slot growth are silent failures. None of them produce failed pipeline runs.

• Record the log position before the initial snapshot, then replay from it and apply changes idempotently. Duplicates are recoverable; gaps are not.

• MERGE rewrites files rather than updating rows, so higher replication frequency raises cost per row. Deletion vectors, compaction, and clustering on the merge key address it.

• Choose on deletion fidelity and operational capacity before latency. Latency is the easiest criterion to compare but is rarely the one that determines whether the architecture holds.