At the end of this guide you will have a scheduled, batch-based replication pipeline that moves whole tables (full load) or only new rows (pointer-based incremental replication) from your database into any supported destination, tuned for throughput on large tables - including parallel Mesh extraction that moves billions of rows at a fraction of the time of conventional bulk tools (see Measured Performance below). Choose this pattern when you optimize for volume per run and simplicity and can tolerate batch freshness; if you need near-real-time syncs or must capture deletes, use Real-Time Database Replication with CDC instead.
Architecture
The pipeline path is: source database > scheduled extraction (snapshot) > data flow > destination. Each extraction creates a snapshot of the selected data; the flow then writes the full content of the most recent extraction to the destination (see Data Storages). Throughput scales with Dataddo's adaptive data ingestion: smaller volumes are written with batch insert queries, growing volumes switch to loading through files in object storage, and very large volumes use streaming (see Adaptive Data Ingestion). For large volumes you can also use a Streaming Data Flow, which is usually considerably faster and guarantees no caching, at the cost of flow-level transformations being unavailable (see Flows overview).
Two extraction strategies cover this use case:
- Full load / snapshot extracts the whole table on every run. Available for all relational sources, including batch-only engines such as MongoDB, which has no log-based option. For large tables, use Mesh ingestion to run the extraction in parallel threads.
- Pointer-based incremental replication extracts only rows beyond a stored high-watermark, using one or more monotonically increasing columns such as a timestamp or an auto-increment id. In Dataddo this is Table Replication by Timestamp (captures new and updated rows) and Table Replication by Row Sequence (captures new rows only, the simplest and cheapest option for append-only tables). It is available for a much wider set of engines than CDC, including MySQL, PostgreSQL, SQL Server, Oracle, Snowflake, Amazon Redshift, Databricks, ClickHouse, CockroachDB, Azure SQL, IBM Informix, Firebird, Vertica, and more.
For very large tables, Row Sequence replication supports a configurable Row limit per run, which caps how many rows a single run extracts. This lets you split the initial load of a huge table across several scheduled runs instead of pulling the whole history at once.
Neither batch method captures deleted rows, and Row Sequence cannot capture updates to historical rows. If you need either, switch to log-based CDC.
Parallel Extraction with Mesh Ingestion
For tables where a single reader becomes the bottleneck, Mesh ingestion parallelizes the extraction itself: instead of reading the table in one stream, the table is split into chunks along a primary-key column and extracted by multiple parallel threads (workers), each reading its own key range concurrently. This is what makes full loads of billion-row tables practical on a schedule.
Configuring Mesh Ingestion
- Workers (default 4, up to 64). The number of parallel extraction threads. Each worker reads its own primary-key range, so total throughput scales with the worker count until the source database's I/O, CPU, or the network becomes the limit. Start with the default and increase gradually while watching the load on the source; extraction parallelism is also read pressure on the database.
- Chunk size (default 100,000 rows). How many rows make up one work unit handed to a worker. Larger chunks reduce coordination overhead on huge uniform tables; smaller chunks distribute work more evenly and keep individual retries cheap when a chunk fails.
- Fetch size (default 1,000 rows). How many rows a worker fetches from the database per round trip within a chunk. Raising it reduces the number of round trips at the cost of memory per worker; lowering it helps with very wide rows.
- Primary-key column. The column used to split the table into chunks. It should be indexed and reasonably evenly distributed; a clustered primary key is ideal.
- Worker balancing (off by default). Enable when primary-key values are unevenly distributed (e.g. large gaps or skewed ranges), so chunks are rebalanced and all workers finish at roughly the same time instead of one worker dragging the run.
- Filter (optional). Restricts the extraction to a subset of rows, letting you parallel-load only the segment you need instead of the whole table.
- Isolation level (engine default). The transaction isolation used by the parallel readers. Keep the engine default unless you need to trade read consistency against locking behavior on a busy production database.
Measured Performance
Real-world full-load measurements from SQL Server to Google BigQuery using parallel Mesh extraction, expressed as the average time to transfer 1 million records. As a baseline, the same tables were exported with bcp, SQL Server's native bulk copy utility:
| Table shape | Dataddo (Mesh) | bcp baseline |
Speedup |
|---|---|---|---|
| 10 columns, 3B+ records | 1.8 s per 1M records | 40.2 s per 1M records | ~22x |
| 250 columns (very wide), 1B records | 28.9 s per 1M records | 150.8 s per 1M records | ~5x |
How to read these numbers. The metric is wall-clock time over the whole run divided by rows transferred, expressed per 1 million records; rows/s is derived from it. The bcp baseline exported the same table from the same server.
What moves the numbers in your environment:
- Row width. The wide 250-column table moves roughly 16x more slowly per record than the narrow one, so plan throughput around data volume, not row counts.
- Write mode. Truncate insert and plain insert are the fastest paths; upsert trades throughput for a deduplicated mirror (see Write Modes).
- Ingestion path. Adaptive data ingestion switches between batch inserts, object-storage file loads, and streaming as volume grows, so throughput is not linear across dataset sizes.
- Network. Region placement and connectivity between source, Dataddo, and destination set the floor for any transfer.
Decision Guide: Write Strategy
Match the write mode to the extraction strategy: full loads that should mirror the source pair naturally with truncate insert (the destination table is cleared and rewritten each run), incremental replication of updated rows pairs with upsert on the table's primary key so re-extracted rows update instead of duplicate, and pure append-only streams can use plain insert. If a table lacks a clean unique column, the Dataddo Hash metadata field generates a hash of selected columns that serves as a ready-made natural key for the upsert composite key. Include the Dataddo Extraction Timestamp when you need to track Slowly Changing Dimensions, since each extraction becomes a distinguishable version of the row. Write mode availability varies per destination; see Write Modes for behavior and composite key setup.
Setup Walkthrough
- Create the data source. Follow Creating a Data Source and pick the replication method for your table shape using the decision questions in Database Replication.
- Configure the tracking columns. For Timestamp replication, set the Change Tracking Column and make the extraction window at least as wide as the gap between two scheduled runs. For Row Sequence replication, set the Sequence Tracking Column(s) and, for large tables, a Row limit per run.
- Set the schedule. Choose the extraction frequency during source setup or later on the source's Snapshotting tab; Dataddo supports intervals down to 1 minute and custom expressions (see Extraction Frequency).
- Connect the destination. See How to Connect a Data Destination.
- Create the flow. Follow How to Create a Data Flow, set the write mode from the decision guide, and let automatic table setup create the target table so upsert indexing is in place from the first load (see Data Storages).
Configuration Recommendations
| Scenario | Extraction method | Write mode | Notes |
|---|---|---|---|
| Billion-row table, maximum throughput | Full load via Mesh ingestion | Truncate insert | Tune workers and chunk size; enable worker balancing for skewed keys |
| Append-only log or event table | Row Sequence | Insert | Cheapest option; first run starts from the beginning of the table |
| Very large append-only table, staged initial load | Row Sequence + Row limit per run | Insert | Spread the initial load across several runs |
| Frequently updated table (orders, profiles) | Timestamp | Upsert | Window at least as wide as the schedule; overlap is deduplicated by upsert |
| Small table, exact mirror each run | Full load | Truncate insert | Destination is cleared and rewritten every run |
Partitioning full loads by period. Date placeholders in the destination table name, re-evaluated at every run, split repeated full loads into per-period tables instead of overwriting one table: for example orders_{{1d1|Ym}} writes each month's load into its own table (see Partitioning Files and Tables by Date).
Operations
- Full Data Re-Sync as recovery and refresh. Full Data Re-Sync reloads all historical data for a flow, which is the standard mechanism for recovering lost data, fixing past sync issues, or refreshing a drifted target table. It requires a source with date-range support and a destination that supports upsert, runs separately from regular syncs, and is limited to one re-sync job per flow per day (plan-dependent feature). Track progress on the Full Data Re-Sync jobs overview page.
- Data quality checks. For storage destinations, the Data Quality Firewall validates each load between extraction and write; run it in blocking mode to stop non-compliant loads or in non-blocking mode to deliver the data and alert instead.
- Monitoring. Watch flow runs and logs, and consider the Data Quality Watcher for anomaly alerts on delivered data.
Troubleshooting
Only issues specific to batch replication are listed here; for general problems see Troubleshooting.
- Updates missing from the destination. Row Sequence replication cannot capture changes to historical rows, and Timestamp replication misses rows whose tracking column is not refreshed on update. Fix the tracking column, or switch to Timestamp or CDC.
- Rows changed between runs are missing. The Timestamp extraction window is narrower than the schedule interval; widen it and deduplicate the overlap with upsert.
- Duplicates in the destination. Usually an inappropriate write mode; switch from insert to upsert or truncate insert (see Handling Data Duplicates).
- Cannot switch an existing flow to upsert. Some destinations restrict this because indexes are created at first load; see the workarounds in Data Backfilling for Database Replication.