---
title: "Real-time CDC Database Replication"
slug: "real-time-cdc-database-replication"
updated: 2026-07-31T06:31:41Z
published: 2026-07-31T06:31:41Z
canonical: "docs.dataddo.com/real-time-cdc-database-replication"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.dataddo.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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](/docs/high-performance-batch-database-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](/docs/google-bigquery), [Snowflake](/docs/snowflake), or [Databricks](/docs/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:

| Source engine | CDC mechanism | Position tracking |
| --- | --- | --- |
| [MySQL](/docs/mysql) | Binary log (binlog) | Binlog position |
| [PostgreSQL](/docs/postgresql-as-a-source) | WAL logical replication | Replication slot |
| [SQL Server](/docs/universal-sql-server-source) | Change Data Capture (CDC) tables | LSN |
| [Oracle](/docs/oracle-database-as-a-source) | MLOG with automatic management | SCN |
| [Mongo](/docs/mongodb-as-a-source) | Oplog | Resume token |

For non-supported engines use full loads or pointer-based incremental extraction as described in [High-Performance Full Load and Incremental Replication](/docs/high-performance-batch-database-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:

| Database | Required setup |
| --- | --- |
| MySQL | Binary logging (binlog) enabled on the server, and the connecting user allowed to read it. |
| PostgreSQL | Logical replication enabled on the server (`wal_level = logical`) and an available replication slot. |
| SQL Server | Change Data Capture (CDC) enabled on the database **and on every table** you replicate. |
| Oracle | Enabling MLOG. |

## 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](/docs/real-time-cdc-database-replication#ordered-change-events-and-operation-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](/docs/data-storages#write-modes).

## Setup Walkthrough

1. **Prepare the source database.** Complete the [engine-specific prerequisites](/docs/real-time-cdc-database-replication#prerequisites-per-engine) above and confirm each table has a primary key or unique column.
2. **Create the data source.** Follow [Creating a Data Source](/docs/creating-a-data-source), pick your database connector, and select **Log-based Replication** as the extraction method (see [Database Replication](/docs/database-replication#log-based-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](/docs/data-backfilling-for-database-replication).
5. **Connect the destination.** See [How to Connect a Data Destination](/docs/how-to-connect-a-data-destination).
6. **Create the flow.** Follow [How to Create a Data Flow](/docs/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](/docs/data-storages#automatic-table-setup)).

## Configuration Recommendations

| Scenario | Write mode | Key |
| --- | --- | --- |
| Fully materialized mirror, no post-processing | CDC (where available: operational databases, warehouses such as SQL Server or BigQuery) | Source primary key |
| Mirror on a destination without the CDC write mode | Upsert + delete | Composite key matching the source primary key |
| Change history / audit trail (keep every event) | Insert | None; order by the sequence metadata |
| Event system or data lake target | Not applicable; the ordered operation log is delivered as is | Materialize downstream using `op` + sequence metadata |

## Monitoring

The [CDC Supervisor](/docs/real-time-cdc-database-replication#continuous-replication-and-supervision) 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](/docs/data-quality-watcher) to alert on anomalies in delivered data.

## Troubleshooting

Only CDC-specific issues are listed here; for general problems see [Troubleshooting](/docs/troubleshooting).

- **Source creation fails or no changes arrive.** Re-check the [engine prerequisites](/docs/real-time-cdc-database-replication#prerequisites-per-engine): 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](/docs/real-time-cdc-database-replication#continuous-replication-and-supervision) 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](/docs/data-storages#delete)).
- **Historical rows are missing.** CDC only replicates changes after the starting position. Run a [backfill](/docs/data-backfilling-for-database-replication) to load history.