Data synchronization

Overview

Data synchronization is the work of keeping two or more copies of data consistent with each other. A customer address updated in an operational database must eventually appear in the warehouse, the cache, the search index and the files used to train a model. Synchronization covers the methods that make this happen and the checks that prove it did.

There is no single technique. Teams combine change data capture for freshness, scheduled batch replication for bulk movement, and reconciliation for proof. The right mix depends on how fresh the copy must be, how large the data is, and what happens when two copies disagree. For tabular foundation models, good synchronization means the rows a model sees match the real system of record at a known point in time.

What it is

Data synchronization is a goal and a family of techniques. The goal is that copies of the same facts agree. The techniques are the ways of moving, comparing and repairing data.

One-way and two-way

• One-way sync: a source is authoritative. Changes flow from source to target only. Most analytics pipelines work this way.
• Two-way (bidirectional) sync: both sides can change data. Conflicts are possible and need rules.
• Multi-way sync: several systems exchange changes, often through a hub.

Full and incremental

• Full sync: copy everything every time. Simple, but slow and heavy on large tables.
• Incremental sync: copy only what changed. Faster, but needs a way to find changes.

Real-time and scheduled

• Real-time (streaming): changes flow within seconds.
• Near real-time: micro-batches every few seconds or minutes.
• Scheduled batch: hourly, daily or weekly jobs.

What can be synchronized

Where it came from

Synchronization is as old as having more than one computer. Early systems copied tapes and files between machines overnight. Relational databases added replication so a standby could take over after a failure. Mobile devices brought offline-first sync, where a phone edits its own copy and merges later.

Several ideas shaped the field.

• Replication in databases: primary and standby servers, using logs to stay aligned.
• ETL and warehousing: nightly extract, transform and load jobs that fed reports.
• Distributed systems theory: the CAP idea, which says that during a network split a system must choose between consistency and availability. Eventual consistency became a common, accepted trade-off.
• Version vectors and similar methods: ways to tell which of two edits came first, or whether they were concurrent.
• Event streaming and log-based CDC: treating changes as ordered streams.
• Lakehouse table formats: Delta Lake and Apache Iceberg added merge operations and snapshots, making it practical to apply changes to files.

The trend is from "copy everything at night" toward "move only changes, continuously, and prove they match".

How it works

This section shows the main techniques step by step. The analogy is two shops that keep the same price list.

Technique 1: Full refresh

Delete the target, copy the source. Every night the second shop throws away its price list and photocopies the master.

Good for small tables and simple needs. Bad for big tables, because cost grows with total size and the target is empty or stale during the copy unless you swap tables atomically.

Technique 2: Incremental load with a watermark

Keep a high-water mark, such as the largest updated_at seen so far. On each run, fetch rows where updated_at is greater than the mark, apply them, and move the mark.

Analogy: the second shop asks, "what has changed since Tuesday 9:00?"

Risks:

• Rows deleted at the source are not seen.
• Rows updated without changing the timestamp are missed.
• Clocks that disagree can skip rows. A small overlap window (re-read the last few minutes) reduces this.

Technique 3: Log-based change data capture

Read the source's transaction log and stream every change. See change data capture. It finds deletes, keeps order and adds little load. It needs more setup.

Technique 4: Trigger-based capture

Database triggers write changes into an audit table, which the sync job reads. Works when the log is not available, but slows down writes.

Technique 5: Snapshot comparison (diffing)

Take a snapshot of both sides and compare keys and hashes. Rows only in the source are inserts. Rows only in the target are deletes. Rows in both with different hashes are updates.

Analogy: both shops count and compare their price lists line by line.

Technique 6: Reconciliation

Reconciliation does not move data. It checks whether copies match and reports the differences. Common checks are row counts, sums of numeric columns, hashes of key ranges and sampled row comparisons. Reconciliation catches silent errors that other methods miss.

Technique 7: Two-way sync with conflict resolution

When both sides can change the same row, the system needs a rule for conflicts.

Technique 8: Idempotent apply

Whatever method is used, applying the same change twice must give the same result. This is done with upserts keyed by primary key and by storing the position or version of the last applied change.

Putting it together

A typical enterprise design looks like this.

1. Log-based CDC moves hot tables continuously.
2. Scheduled batch jobs move large, slow-changing tables and files.
3. A nightly reconciliation job compares counts and checksums.
4. A repair job re-syncs any key ranges that differ.
5. Monitoring tracks lag, error rates and mismatch counts.

Key concepts and terms

Consistency models in plain words

• Strong consistency: like one shared notebook. Everyone reads the same page.
• Eventual consistency: like mail between offices. Letters arrive at different times, but everyone ends up with the same facts.
• Read-your-writes: after you save something, you see your own change at once, even if others do not yet.

Freshness versus cost

Fresher data costs more in compute, operations and complexity. A useful rule is to ask who will act on the data and how quickly. A weekly report does not need streaming.

A worked example

This example synchronizes a source table into a target with a watermark, handles deletes with a diff, and reconciles with hashes. Everything is in Python so it can be run as is.

The data

import hashlib

source = {
    1: {"id": 1, "name": "Ada",  "plan": "pro",   "updated": 10},
    2: {"id": 2, "name": "Ben",  "plan": "basic", "updated": 11},
    3: {"id": 3, "name": "Cara", "plan": "pro",   "updated": 15},
}
target = {
    1: {"id": 1, "name": "Ada",  "plan": "basic", "updated": 8},
    2: {"id": 2, "name": "Ben",  "plan": "basic", "updated": 11},
    4: {"id": 4, "name": "Dan",  "plan": "basic", "updated": 9},
}

The target is out of date in three ways: row 1 is stale, row 3 is missing, and row 4 no longer exists at the source.

Incremental by watermark

def incremental(source, target, watermark):
    changed = [r for r in source.values() if r["updated"] > watermark]
    for r in changed:
        target[r["id"]] = dict(r)
    new_mark = max([watermark] + [r["updated"] for r in changed])
    return new_mark

mark = incremental(source, target, watermark=9)
print(mark)            # 15
print(sorted(target))  # [1, 2, 3, 4]

The watermark run fixed rows 1 and 3 and also re-copied row 2 (updated at 11, greater than 9), which is harmless because the apply is idempotent. But row 4 is still there. A watermark cannot see deletes.

Detecting deletes with a key diff

def delete_missing(source, target):
    gone = set(target) - set(source)
    for k in gone:
        del target[k]
    return gone

print(delete_missing(source, target))   # {4}

For large tables you would not load all keys into memory. You would compare key ranges using hashes, as in the next step.

Reconciliation with hashes

def row_hash(r):
    text = "|".join(str(r[k]) for k in sorted(r))
    return hashlib.sha256(text.encode()).hexdigest()

def table_hash(table, lo, hi):
    h = hashlib.sha256()
    for k in sorted(table):
        if lo <= k <= hi:
            h.update(row_hash(table[k]).encode())
    return h.hexdigest()

same = table_hash(source, 1, 3) == table_hash(target, 1, 3)
print(same)   # True after the two repairs above

If the hashes differ, split the range in half and compare again. After a few rounds you reach the exact rows that differ, without comparing every row.

Conflict resolution example

Suppose both sides changed the plan for user 2 at nearly the same time.

a = {"id": 2, "plan": "pro",   "updated": 20, "by": "crm"}
b = {"id": 2, "plan": "team",  "updated": 21, "by": "billing"}

def last_write_wins(x, y):
    return x if x["updated"] >= y["updated"] else y

print(last_write_wins(a, b)["plan"])   # team

Last write wins is simple, but it silently drops the CRM edit. If billing is the authority for plans, a source-of-truth rule is safer: pick billing no matter the time.

A scheduled job outline

job: nightly-orders-sync
schedule: "0 2 * * *"
steps:
  - extract:
      from: db.example.internal/appdb.orders
      where: "updated_at > :last_watermark - interval '10 minutes'"
  - load:
      to: lake.orders
      mode: merge
      key: order_id
  - reconcile:
      compare: [row_count, sum(amount), key_range_hash]
      tolerance: 0
  - alert_on_mismatch: true

The ten-minute overlap protects against clock differences and late commits. The merge makes the overlap harmless. An orchestrator such as Apache Airflow can run steps like these on a schedule and retry them.

Numeric example of lag and cost

The incremental methods move about a thousandth of the data, which is why they scale.

Strengths and limitations

Strengths of a well-built sync

• Fresh, consistent data across systems.
• Separation of workloads: analytics and models run on copies, protecting operational systems.
• Resilience: copies serve as backups and enable failover.
• Auditability: logs and checks prove what moved and when.
• Flexibility: the same data can feed many targets.
• Efficiency: incremental methods avoid re-copying the world.

Limitations and risks

• Eventual consistency: for a time, copies disagree. Applications must tolerate that.
• Silent drift: small errors accumulate without alarms.
• Deletes and schema changes: the two most common causes of breakage.
• Conflict handling: two-way sync is much harder than one-way.
• Cost and complexity: more moving parts to run and monitor.
• Data protection: copies multiply the places where sensitive data lives.
• Clock problems: timestamp-based methods depend on trustworthy clocks.

How it compares with alternatives

Sync versus federation

Federation leaves the data where it is and queries it across systems. There is nothing to synchronize, but each query hits the source and performance depends on the slowest system. Synchronization pays upfront to make later queries fast and isolated.

Sync versus sharing

Some platforms let you share data without copying it, for example secure sharing features in warehouse products such as Snowflake. This removes some of the sync burden, but only among users of the same platform.

Platforms that help

• Stream platforms: Apache Kafka and Kafka Connect.
• Processing engines: Apache Flink, Apache Spark and Spark Streaming.
• Lakehouse platforms: Databricks and table formats like Delta Lake.
• Orchestration: Apache Airflow.
• Transformation: dbt.

Role in tabular foundation models

Tabular foundation models learn from tables with rows, typed columns, missing cells and often anonymous column names. Synchronization is what keeps the tables they read faithful to reality. No specific integration is claimed here. The following points explain how the general ideas relate.

Row and column structure

A synchronized table keeps the same keys, the same row meaning and the same column order as the source, or documents how they differ. A model that reads rows needs a stable definition of "one row". If synchronization silently duplicates keys or drops late rows, then the structure the model assumes is broken.

Mixed types

Each hop between systems can change types. A decimal becomes a float, a time zone is lost, a long identifier becomes a rounded number. Synchronization jobs should map types explicitly and test them. Types matter to a model because it treats numbers, categories and dates differently.

Missing values

Synchronization can both hide and create missing values. A failed load leaves nulls where data should be. A partial update overwrites a full row with nulls. A default value fills a gap and looks like a real observation. The check is simple: compare null rates per column between source and copy. See imputation networks for filling genuine gaps, and fix false gaps at their cause.

No column names

When column names are unhelpful or change between systems, a stable mapping is needed so the model sees the same column in the same position over time. Metadata sync, covering descriptions and mappings in a data catalog, gives people the context the model lacks. It also helps spot when two columns have been swapped.

Pre-training on synthetic or real tables

Pre-training for tabular foundation models often relies on generated tables; see synthetic data generation and TabPFN. Synchronization is not part of that stage. It matters at the point of use: the model is applied to a real, current copy of an organization's data. Reading the model research page gives background on how pre-training differs from this deployment stage.

In-context learning

In-context learning needs a labeled sample in the prompt. That sample should reflect the current truth. A synchronized copy with known lag lets you say, "this context was current as of 10:05". When a labeled row is corrected at the source, the next sync should refresh it, so the model is not conditioned on outdated labels.

Continual learning

Continual learning means updating over time as new data arrives. Incremental synchronization provides the stream of new and changed rows, with watermarks that mark exactly where the last training or context refresh ended. Without this bookkeeping it is hard to know whether a model has already seen a row. Drift between copies, and drift in the data itself, can be tracked using schema drift detection.

Imputation

Imputed values should live beside the originals, not replace them. Synchronization must carry both forward and not overwrite one with the other. If the source later supplies the true value, reconciliation should detect that the imputed value is now obsolete.

Anomaly detection

Mismatches between copies are anomalies in themselves, and reconciliation checks are a form of anomaly detection. Beyond that, sync metrics such as sudden spikes in row counts, drops in volume or unusual delete rates are strong signals of upstream problems. Models that detect anomalies in the data also depend on a faithful copy to avoid false alarms caused by sync errors.

Sector and semantic classification

Classification outputs, such as a predicted sector for each record, are new data that should flow back. This is a two-way flow: records go to the model, labels come back and are stored with model version and confidence. Use keys to join them to the source and a clear rule for which side owns the label.

Governance and privacy

Every copy increases exposure. Apply masking or tokenization before data leaves the source; see data tokenization. Record flows with data lineage. Agree on shapes and rules in a contract and validate it, as in data contract validation. Evaluation of model quality on synchronized data is discussed on the benchmarks page.

Practical guidance and best practices

Choose the method by need

1. Write down the required freshness, the data size and the delete behavior.
2. If deletes matter and freshness is minutes or less, use log-based capture.
3. If tables are small and slow, a scheduled full refresh is fine.
4. If tables are append-only, a watermark is often enough.
5. Always add reconciliation, whatever the method.

Make every step safe to repeat

• Use upserts keyed by primary key.
• Store the last applied position or version.
• Use an overlap window for timestamp methods.
• Write to a staging area, validate, then swap or merge.

Design for change

• Version schemas and use compatibility rules.
• Use a schema registry where messages are used.
• Plan for schema evolution: added columns, renamed columns, changed types.
• Alert when a column appears, disappears or changes type.

Reconcile on a schedule

• Compare row counts per partition or day.
• Compare sums and hashes for key ranges.
• Compare null rates and distinct counts.
• Sample rows and compare field by field.
• Store the results over time and graph them.

Handle conflicts deliberately

• Prefer one-way flows with a clear owner for each field.
• If two-way is needed, define the rule per field, not per table.
• Log every conflict that is resolved automatically.

Build model-friendly datasets

• Record a snapshot id or watermark with every training and evaluation run.
• Build features as of the label time to avoid leakage.
• Keep the original and the synchronized key together for traceability.
• Version the copies, for example using table snapshots in Delta Lake or Apache Iceberg.

Protect data

• Use least-privilege accounts and encrypted connections.
• Mask personal data early.
• Apply retention rules to every copy.
• Keep an inventory of where data is replicated.

Common mistakes

1. No reconciliation. Teams trust the pipeline and never check. Drift grows quietly.
2. Watermarks on unreliable timestamps. If updated_at is not changed by every write, rows are missed.
3. Forgetting deletes. Targets fill with rows that no longer exist.
4. Non-idempotent loads. A retry duplicates data.
5. Two-way sync without conflict rules. Edits are lost without anyone noticing.
6. Ignoring time zones and clocks. Rows are skipped or repeated at boundaries.
7. Changing a schema without telling consumers. Downstream jobs fail or, worse, run with wrong data.
8. Overwriting full rows with partial updates. Columns become null by mistake.
9. Syncing everything. Unneeded tables add cost and risk.
10. Using the target as a source later. Copies of copies lose track of who owns the truth.
11. Training on a moving table. Without a snapshot, results cannot be reproduced.
12. Letting copies of sensitive data spread. Every copy needs controls and an owner.

Frequently asked questions

What is data synchronization?

Data synchronization is the process of keeping data in two or more places consistent. It includes moving changes from a source to targets, checking that the copies match, and repairing differences. It can be one-way, where a source is authoritative, or two-way, where several systems can change the data.

What is the difference between data synchronization and data replication?

Replication is one way of copying data, often at the level of a whole database or storage volume. Synchronization is the broader goal of keeping data aligned, and it can use replication, change data capture, batch jobs and reconciliation together. Replication usually implies the same format on both sides, while synchronization often crosses different kinds of systems.

When should I use change data capture instead of batch sync?

Use change data capture when you need fresh data, need to see deletes, or have tables too large to reload. Use batch sync when tables are small, freshness in hours is enough, or the source offers no log access. Many organizations use both, and add reconciliation to check the results.

How do I keep two systems from overwriting each other's changes?

Avoid two-way writes to the same field where you can, and give each field a single owner. When two-way sync is unavoidable, choose a conflict rule for each field, such as source-of-truth wins or merge by column, and log every automatic resolution. Last-write-wins is simple but can silently discard a valid edit.

What is eventual consistency?

Eventual consistency means that copies of data may differ for a short time after a change, but will agree once all changes have been delivered and no new ones arrive. It is a common trade-off in distributed systems because it allows high availability and speed. Applications must be designed to tolerate the short disagreement.

How can I tell if my copies have drifted apart?

Run reconciliation checks. Compare row counts, sums of numeric columns, null rates and hashes of key ranges between source and target, and sample some rows for field-by-field comparison. Track the results over time. A sudden change or a slow trend in mismatches shows a problem to investigate.

Why do deleted rows cause so many sync problems?

Methods that look for rows with a newer timestamp can only find rows that still exist, so a deleted row leaves no trace and stays in the target forever. Log-based capture, triggers and key comparisons can detect deletes. If you must use timestamps, use soft deletes with a flag, so that the deletion is itself a visible update.

How does synchronization affect machine learning models?

Models are only as good as the data they see. If a copy is stale, duplicated, missing rows or has altered types, then training, evaluation and predictions all suffer. Recording the snapshot or watermark behind each run also makes results reproducible and helps avoid leakage from future data.

Is real-time synchronization always better?

No. Real-time sync costs more to build and run, and many use cases do not need it. A nightly or hourly job may be perfectly adequate and much simpler. Set the lag target from the real decision that depends on the data, then choose the least complex method that meets it.

How does data synchronization relate to tabular foundation models?

Synchronization supplies these models with faithful, current, well-typed tables and gives you the snapshot and lag information needed for fair evaluation. It does not change how the models learn. For how they read tables, see the model architecture page.

Glossary

• Backfill: loading historical data into a target.
• Bidirectional sync: changes flow both ways between systems.
• Checksum: a short fingerprint used to compare data.
• Conflict: two incompatible changes to the same data.
• Drift: gradual divergence between copies.
• Eventual consistency: copies converge over time.
• Full refresh: replacing the target with a new copy of the source.
• Idempotent: safe to repeat.
• Incremental load: moving only changes.
• Lag: delay between source change and target visibility.
• Reconciliation: comparing copies to find differences.
• Replica: a synchronized copy.
• Source of truth: the authoritative system for a piece of data.
• Tombstone: a marker for a deleted record.
• Upsert: insert or update by key.
• Watermark: a marker of how far synchronization has progressed.

Summary

Data synchronization keeps copies of data consistent through a mix of full refreshes, incremental loads, log-based capture and reconciliation. The key design questions are how fresh the copy must be, how deletes and conflicts are handled, and how mismatches are detected. Idempotent loads, stored positions, schema controls and regular reconciliation are the habits that make it reliable.

For tabular foundation models, synchronization protects the quality of the tables they read, supports reproducible snapshots, and exposes drift and anomalies in the data. Related reading includes change data capture, Snowflake, Databricks and the model research page.

Related

It belongs to Databases and sync in my stack, under Data engineering and lakehouse. Others in the same group: