Real-time CDC Database Replication

At the end of this guide you will have a log-based CDC (change data capture) pipeline that streams every committed INSERT, UPDATE, and DELETE from your production database into any supported destination, in near real time and with minimal load on the source. Choose this pattern when latency matters or when you must capture deleted rows; if you are moving large tables on a schedule and can tolerate batch freshness, see High-Performance Full Load and Incremental Replication instead.

Architecture: Why Log-Based CDC

Log-based replication reads committed changes directly from the database engine's transaction log instead of querying the active storage tables. It acts as an event-driven system: every insert, update, and delete recorded in the log is captured and forwarded, so nothing is missed between runs and no polling queries compete with your production workload.

This is why log-based CDC beats query-based replication for latency-sensitive workloads:

• Lower latency. Changes are picked up from the change stream as they are committed, rather than waiting for the next scheduled extraction query.
• Lower source load. Reading the log has a very low performance impact; query-based methods repeatedly scan tables and depend on well-indexed tracking columns.
• Deletes are captured. Log-based replication is the only Dataddo replication method that detects deleted rows. Query-based methods cannot see a row that is no longer there.

The pipeline path is: source database transaction log > Dataddo data source (log reader) > data flow > destination. Replication is cross-technology: any of the supported source engines can feed any supported destination, for example Google BigQuery, Snowflake, or Databricks.

Continuous Replication and Supervision

Unlike scheduled batch extractions, log-based replication is a continuous process: Dataddo permanently listens to the database change stream and picks up committed changes as they happen, rather than polling on an interval.

A long-running process must stay healthy, so a built-in CDC Supervisor regularly wakes up, checks the status of each running replication process, and automatically restarts it if it has stalled or failed. Because the current log position (binlog position, replication slot, LSN, or SCN) is tracked persistently, a restarted process resumes from where it left off and no committed changes are lost. The pipeline recovers without manual intervention.

Ordered Change Events and Operation Metadata

The extracted data is a sequence of operations: each committed insert, update, and delete becomes one change event. Dataddo takes care of sorting, so the sequence of operations is maintained as they were committed on the source.

Each event also carries technical metadata so you can verify or re-establish ordering downstream if you need to:

• op identifies the operation (insert, update, or delete).
• A sequence identifier from the engine's change stream (such as the scn for Oracle or the lsn for SQL Server) identifies each operation's position in the sequence. Check the configuration of each engine specifically.

Supported Source Engines

Log-based CDC is supported for multiple source engines. Each uses the engine's native change stream:

For non-supported engines use full loads or pointer-based incremental extraction as described in High-Performance Full Load and Incremental Replication.

Prerequisites per Engine

Log-based replication reads the change stream of the database engine, so it needs one-time configuration on your database before the source can be created:

• Every replicated table must have a primary key or unique column, so updates and deletes can be matched to specific rows.
• The connecting database user must have permission to read the change stream.
• Engine-specific setup:

Decision Guide: Write Strategy

How the operation sequence lands in your destination depends on the destination type:

• CDC write mode: automatic materialization. For destinations that support it, typically operational databases and data warehouses (e.g. SQL Server or Google BigQuery), a dedicated CDC write mode is available. Based on the primary key you define, it automatically squashes the sequence of operations into a fully materialized table: inserts create rows, updates modify them, deletes remove them. You get an always-current mirror with no post-processing to build.
• Operation log delivery. For event-based engines and data lakes, the CDC write mode is not available; the delivered product is the ordered list of operations itself. Materialize it downstream if you need table semantics, using the sequence metadata to apply operations in order.
• Manual materialization with upsert + delete. Where the CDC write mode is not offered but table write modes are, combine upsert (composite key matching the source primary key) with the delete write mode to mirror all three change types yourself. See Write Modes.

Setup Walkthrough

1. Prepare the source database. Complete the engine-specific prerequisites above and confirm each table has a primary key or unique column.
2. Create the data source. Follow Creating a Data Source, pick your database connector, and select Log-based Replication as the extraction method (see Database Replication).
3. Seed the starting position (optional, first run only). You can set an initial log position so replication starts from a known point, for example an initial LSN for SQL Server or a binlog position for MySQL. This applies to the first run only.
4. Load history if you need it. CDC captures changes going forward from the starting position. To bring the table's existing history into the destination, run a one-time backfill as described in Data Backfilling for Database Replication.
5. Connect the destination. See How to Connect a Data Destination.
6. Create the flow. Follow How to Create a Data Flow and set the write modes from the decision guide above. Let automatic table setup create the target table so upsert indexing is in place from the first load (see Data Storages).

Configuration Recommendations

Monitoring

The CDC Supervisor health-checks the replication process and restarts it automatically when needed. Beyond that, watch flow runs and logs as with any flow, and consider the Data Quality Watcher to alert on anomalies in delivered data.

Troubleshooting

Only CDC-specific issues are listed here; for general problems see Troubleshooting.

• Source creation fails or no changes arrive. Re-check the engine prerequisites: the change stream must be enabled (logical replication, CDC tables, supplemental logging) and the connecting user must be allowed to read it.
• Replication was interrupted. The CDC Supervisor restarts interrupted processes automatically, resuming from the tracked log position. If restarts recur, re-check the prerequisites and make sure the source database retains its logs long enough to cover any downtime (e.g. binlog or WAL retention).
• Updates or deletes are not matched in the destination. The replicated table lacks a primary key or unique column, or the flow's composite key does not match it.
• Deleted rows remain in the destination. The flow uses upsert only. Switch to the CDC write mode if your destination offers it, or configure the delete write mode with a delete composite key (see Write Modes).
• Historical rows are missing. CDC only replicates changes after the starting position. Run a backfill to load history.