Dataddo offers multiple data replication (extraction) methods across our supported database connectors, including PostgreSQL, MySQL, Microsoft SQL Server, Oracle, and MariaDB. Choosing the right method ensures efficient data syncing, maintains high data integrity, and minimizes performance overhead on your production database.
When configuring your database dataset, you can choose from four extraction strategies based on your table structure and business requirements.
How to Choose a Method
Answer these three questions about the table you want to replicate:
- Do you need to capture deleted rows? If yes, use Log-based Replication. It is the only method that detects deletes.
- Are existing rows ever updated after insert? If yes, use Table Replication by Timestamp (requires a reliably maintained
updated_at-style column) or Log-based Replication. If rows are only ever inserted, Table Replication by Row Sequence is the simplest and cheapest option. - Do you need joins, filters, or aggregation before extraction? Use a Custom SQL Query.
Comparison Overview
| Replication Method | Captures New Rows? | Captures Updated Rows? | Captures Deleted Rows? | Performance Impact | Best For |
|---|---|---|---|---|---|
| Table Replication by Timestamp | Yes | Yes | No | Low to Medium | Frequently edited/updated tables |
| Table Replication by Row Sequence | Yes | No | No | Low | Append-only logs and transactions |
| Log-based Replication | Yes | Yes | Yes | Very Low | High-volume, real-time syncs |
| Custom SQL Query | Depends on query | Depends on query | Depends on query | Variable | Complex filters and table joins |
Method Availability by Database
Table Replication by Timestamp, Table Replication by Row Sequence, and Custom SQL Query are available for all supported database connectors. Log-based Replication requires engine-level Change Data Capture support.
Table Replication by Timestamp
This method tracks changes based on a date, time, or timestamp column in your table, called the Change Tracking Column in the source configuration. During each sync, Dataddo extracts only the rows whose tracking column value falls inside the source's relative date range (for example, "last 24 hours"), and that window moves forward with the current date.
- How it works: Because a row re-enters the extraction window whenever its timestamp is refreshed, this method captures both new and updated rows.
- Best for: Tables where existing records are regularly updated, modified, or cycled over time (e.g., user profiles, active order statuses, or inventory levels).
Configuration fields: Schema, Table, Columns, Change Tracking Column, and an optional WHERE Clause.
To use this method effectively, your table must feature a column (such as last_updated, modified_at, or timestamp) that is set on insert and automatically refreshed on every update via database triggers or application logic. If the column is only set once at insert time, updates will not be captured.
The extraction window must be at least as wide as the gap between two scheduled runs. For example, a source that syncs once a day must use a date range of at least one day; otherwise, rows changed between runs are silently missed. When in doubt, make the window wider than the schedule interval; overlapping rows can be deduplicated in the destination using the upsert write mode.
Table Replication by Row Sequence
This method tracks changes using a sequential, continuously increasing numerical value, configured as the Sequence Tracking Column(s) (typically an auto-increment primary key). Dataddo stores the highest value extracted so far (the high-watermark) and starts the next extraction from that exact point.
- How it works: Each run extracts only rows where the tracking column value is greater than the last stored watermark (
WHERE id > last_stored_id), ensuring seamless data continuity. The first run starts from the beginning of the table. - Best for: Append-only tables where new records are constantly inserted but historical records are never changed or deleted (e.g., transaction histories, immutable system logs, or event streams).
Configuration fields: Schema, Table, Columns, Sequence Tracking Column(s), an optional WHERE Clause, and an optional Row limit per run.
Use the Row limit per run field to cap how many rows a single run extracts. This lets you split the initial load of a very large table across several scheduled runs instead of extracting the whole history at once.
This method cannot capture updates made to historical rows. If an existing row with an older ID is modified, Dataddo will not sync the change. If your data is subject to post-insert modifications, use Table Replication by Timestamp or Log-based Replication instead.
Log-based Replication
Log-based replication uses Change Data Capture (CDC) to read committed changes directly from the database's internal transaction log rather than querying the active storage tables.
- How it works: It acts as an event-driven system, reading the transaction log to capture every
INSERT,UPDATE, andDELETEaction. It is the only method that detects deleted rows. - Best for: High-volume, mission-critical tables where near-real-time syncing is required, or where running heavy analytical queries would cause too much performance degradation on the production instance.
Prerequisites
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:
- Each replicated table must have a primary key or unique column so that 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 |
|---|---|
| PostgreSQL | Logical replication enabled on the server (wal_level = logical) and an available replication slot. |
| Microsoft SQL Server | Change Data Capture (CDC) enabled on the database and on every table you replicate. |
| Oracle | Supplemental logging enabled, and the connecting user able to read the redo logs (e.g., via Oracle LogMiner). |
Custom SQL Query
For advanced scenarios, Dataddo lets you bypass the automated replication logic entirely and write your own SQL statement to control exactly how data is extracted from the source engine.
- How it works: Dataddo executes your SQL Statement as-is during each scheduled extraction. No tracking filter is added automatically; if you need incremental behavior, implement it yourself using the date range functions below.
- Best for: Complex requirements such as joining multiple tables, applying multi-conditional
WHEREclauses, or pre-aggregating metrics before they reach your destination.
Date Range Functions
To implement incremental logic, use the dateRangeStart() and dateRangeEnd() functions in your statement. Both take a relative date range expression and an optional date format:
SELECT id, status, total, updated_at FROM orders WHERE updated_at >= '{{dateRangeStart}}' AND updated_at < '{{dateRangeEnd}}'
- The first argument is the range expression in the format
<start><unit><stop>, where the unit isi(minute),h(hour),d(day),w(week),m(month), ory(year). For example,1d0means "from yesterday until today" and1d1means "yesterday only". - The second argument is the output date format. The default is
Y-m-d; useY-m-d H:i:swhen your column stores a full timestamp.
Ensure your custom query is highly optimized. Poorly indexed queries or heavy, unindexed joins can result in slow extraction times or timeout errors on large datasets, affecting overall pipeline performance.
Additional Notes
- WHERE Clause and tracking filters. In the two Table Replication methods, the optional WHERE Clause is combined with the automatic tracking filter. Do not repeat the time or sequence condition in it.
- Loading historical data. The replication methods sync changes going forward. To load a table's full history into your destination, see Data Backfilling for Database Replication.
- Handling duplicates in the destination. Timestamp-based replication with an overlapping window re-extracts some rows by design. Use the upsert write mode in your data flow so re-extracted rows update existing records instead of creating duplicates.