Overnight Batch Migration to Streaming Replication in Production

Batch ETL is no longer a defensible default. The business tolerance for stale data has collapsed, and the gap between yesterday's numbers and what is happening right now has become a competitive and operational risk. Marketing teams need campaign performance within the hour. Fraud detection needs anomalies flagged in seconds. Supply chain systems need inventory reflected the moment it changes, and none of these demands can wait for a nightly job to finish running. Shrinking the batch window to fifteen-minute micro-batches does not solve the underlying problem; it only delays the reckoning, because the architecture still produces a discrete, stale snapshot rather than a continuous, current one. The consequences compound when AI agents and dashboards are trained on that stale data: they do not simply underperform, they produce confidently wrong outputs, which turns data freshness from a latency preference into a correctness problem. Batch carries hidden costs beyond staleness as well. Nightly jobs fail silently, schema changes break the next morning's load before anyone notices, and there is no lag metric to watch until downstream consumers start complaining about numbers that do not add up.

Why migration order matters more than migration speed

The most consequential decision in any batch-to-streaming migration is which pipelines to migrate first and in what order, because getting that sequencing wrong wastes months on low-value pipelines while the high-risk ones that actually need streaming sit untouched. Not every pipeline benefits equally from the move, and the right framework scores each one on two axes: how much business value freshness actually adds, and how complex the migration will be, accounting for the number of source systems involved, join complexity, the count of downstream consumers, and SLA sensitivity. Pipelines that score high on freshness value and low on complexity are the correct starting point. Mission-critical pipelines with many downstream consumers should come later, once the team has built operational confidence through smaller wins. Some sources complicate this picture further: SaaS systems that do not expose a transaction log cannot use log-based CDC at all, and teams must fall back to API-based incremental sync or timestamp-based CDC instead. Identifying these SaaS sources early prevents wasted scoping effort on an approach that was never going to work. Before any pipeline enters the migration queue, a short readiness checklist should confirm five things: that the source system supports CDC or event streaming, that downstream consumers can tolerate a format change, that lag monitoring is in place and not just job health monitoring, that a rollback plan exists, and that a data retention policy for the streaming layer has been defined. Teams that skip prioritization and migrate in order of engineering enthusiasm rather than business impact tend to abandon the migration halfway through, with half the estate on streaming and half still on batch, and no clear criteria for finishing the job.

The initial load problem: the step that causes the most production failures

CDC only captures changes from the moment it starts, so every migration carries a bootstrap problem: historical state has to be established before the stream can take over, and underestimating this step creates gaps or duplicates that are difficult to detect and expensive to repair later. The standard connector approach handles this with a full table scan that emits every existing row as an INSERT event, then switches over to reading the transaction log, with the consumer treating those snapshot records the same way it treats genuinely new inserts. The danger sits at the seam between the snapshot and the stream. The batch export timestamp and the CDC start position in the transaction log must overlap, because a gap between them means some changes exist in neither the snapshot nor the retained log and are lost permanently, with no way to recover them after the fact. If the snapshot is older than the earliest retained event, changes that fall in that gap have no replay path and could be omitted permanently. That condition has to be checked explicitly before cutover, not assumed to be fine because the connector didn't throw an error. The consumer-side mitigation for the seam is idempotent upsert, using a last-write-wins rule keyed on event timestamp and implemented as a MERGE on primary key, so that a record emitted twice, once from the snapshot and once from the stream, resolves to the same final state rather than producing a duplicate or an overwrite in the wrong direction.

None of this works if the source database itself is not configured correctly before the snapshot begins, and the prerequisites differ by engine. PostgreSQL requires wal_level=logical, a replication slot, a publication, and a replica identity on every replicated table, which by default is the primary key but can also be a unique non-null index or REPLICA IDENTITY FULL where no such key exists. MySQL requires binlog_format=ROW, binlog_row_image=FULL, a unique server_id, and a configured retention window for the binary log. MongoDB change streams require a replica set; a standalone server cannot support them. DynamoDB provides native CDC through DynamoDB Streams or Kinesis Data Streams, without the same kind of manual log configuration the relational engines demand. Across all of these, write-ahead log retention has to be configured explicitly so the log is not purged before the connector has consumed it. Losing the log during the initial load window recreates the exact gap that the snapshot handoff was designed to prevent.

Running batch and streaming in parallel: the safety net most teams dismantle too early

Keeping both the batch pipeline and the streaming pipeline active at the same time is the single most important risk control available during a production migration, and teams that collapse that overlap under schedule pressure expose themselves to failures that stay invisible until downstream consumers break. The practical minimum for running both in parallel is two to four weeks per pipeline. Teams consistently underestimate how long validation actually takes, and the pressure to decommission batch early is the single most common cause of cutover rollbacks. That distinction matters because the two pipelines offer genuinely different guarantees, not just different latencies. The batch path produces a transactionally consistent point-in-time view, where related rows all come from the same snapshot moment, while the CDC path produces lower-latency updates that may not represent exactly the same moment across joined tables. That gap appears in outer joins, watermarks, and incremental models, and is only visible under close inspection. Monitoring during this phase has to track pipeline lag explicitly, because job health metrics alone will not surface a streaming pipeline that is technically running but steadily falling behind. Rollback readiness has to be maintained for the full duration as well: the batch pipeline's schedule should stay untouched until cutover is confirmed, and the team should be able to revert to batch-only within a defined time window if the streaming path fails a check. Dead letter queues belong in this phase as required infrastructure, since CDC connectors can fail on schema incompatibilities, serialization errors, network issues, or invalid data, and events that cannot be processed need to be captured with retry and backoff policies.

Validating output equivalence between batch and streaming

Proving that streaming output matches batch output takes a structured, ongoing validation practice, and the team needs to agree on what "equivalent" means before parallel running starts. The comparison has to happen at the row level: for any given primary key at any given point in time, the streaming table and the batch table should reflect the same state. Aggregate comparisons, like matching row counts or summed totals, can mask offsetting errors where one record is wrong in a way that another record happens to cancel out. MySQL's handling of cascading deletes illustrates exactly this risk. Through MySQL 5.7, cascading deletes triggered by foreign key constraints were handled internally by the InnoDB storage engine, and none of the resulting changes were written to the binary log. A CDC pipeline reading that log would never see the child-row deletes at all, even though the batch export, built from a direct table read, would reflect them correctly. A validation approach built only on row counts or sums would likely miss this entirely, since the totals might still reconcile while individual child records silently diverge between the two pipelines. Schema drift creates a similar silent failure mode: if the source database adds a column and the streaming pipeline handles it differently than the batch pipeline does, equivalence checks can keep passing on every existing column while the new field gets dropped or misrouted without triggering any alert. Equivalence validation needs to run continuously and automatically during parallel operation, since manual spot-checks cannot keep up with pipelines that carry high write volume or complex delete patterns. A streaming pipeline should not be considered ready for cutover until it has passed equivalence validation across the full range of operation types it will encounter in production: inserts, updates, deletes, schema changes, and cascading deletes where the source database supports them.

Schema evolution as an ongoing operational concern, not a one-time migration task

Schema evolution does not end when the migration does. It continues for as long as the pipeline runs, because applications keep changing their underlying tables long after the streaming system has gone live. The failure mode is specific and recurring: an application adds a column, CDC events begin including that new field, and the destination schema does not have anywhere to put it, so the pipeline either crashes with a "column not found" error or silently drops the field. Either outcome corrupts downstream data without producing an obvious alert, which is what makes this failure mode so dangerous in practice. Automatic schema evolution, where the pipeline detects a new column and adds it to the destination table without a human stepping in, is the production-grade expectation for any pipeline operating across many source databases. Pipelines that require a manual schema update for every application change cannot scale once the number of source systems grows past a handful. Type changes are harder to handle than simple column additions: changing a column from integer to string can require coordinated changes at the source, at the CDC connector, and at the destination schema all at once, and this case should be tested explicitly during parallel running rather than discovered for the first time in production. The write-ahead log retention settings introduced during the initial load carry forward here too: if a connector falls behind while a schema change is being rolled out, the log has to remain available until the connector has processed through the change, and the max_slot_wal_keep_size and wal_keep_size configurations are what make recovery possible once that happens.

Delivery guarantees: what exactly-once requires in a CDC pipeline

Exactly-once delivery is the stated goal of a production CDC pipeline, but the realistic default under failure conditions is at-least-once, and whether a system achieves one or the other comes down to design decisions made before the first event is ever processed, not the choice of connector. Duplicate events arise from well-understood, entirely normal failure conditions: network retries after a connector crash, producer retries, and consumer group rebalances will all generate them at some point in a pipeline's operating life. The same idempotent processing pattern used at the snapshot-to-stream seam mitigates this on the consumer side: a MERGE on primary key using last-write-wins by event timestamp, which handles both one-time deduplication at migration and ongoing at-least-once delivery in steady state. Dead letter queues stay required infrastructure here as well, since events that fail due to schema incompatibility, serialization errors, or invalid data need to be captured with their full context rather than dropped, giving the team an operational record that makes root-cause analysis possible after an incident. Connector offset management is what makes at-least-once recoverable in the first place: when a connector restarts, it resumes from its last committed offset in the transaction log rather than starting over from the beginning, and that is why log retention and offset durability have to be treated as operational requirements rather than configuration suggestions left at their defaults. Downstream consumers need to be told explicitly what delivery guarantee the streaming pipeline actually provides. Consumers built for a batch world, where every record in a file is present exactly once, often have no idempotent processing logic at all, and they will produce incorrect results the moment a duplicate event arrives from the new pipeline.

Cutover mechanics: how to switch downstream consumers without destabilizing them

Cutover is the riskiest moment in the migration, and the teams that get through it safely treat it as its own project, with its own preparation, sequencing, and rollback criteria, rather than as a final step tacked onto the migration they already finished. Downstream consumers need explicit preparation for the format change involved, because streaming pipelines produce continuous events rather than batch files, and any consumer that polls for a daily file or queries a staging table on a fixed schedule will break unless it is migrated to subscribe to the streaming output or query an always-current destination table. Sequencing cutover by consumer risk, rather than by pipeline order, determines which failures stay contained to a single report and which ones cascade into systems that act on replicated data. Read-only analytics consumers, such as dashboards and reports, can move first, since a failure there is visible and recoverable without affecting anything beyond the report itself. Write-path consumers, meaning systems that act on replicated data to update other systems downstream, carry a far larger blast radius if something goes wrong, and they belong at the end of the sequence, once every read-only consumer has already proven the pipeline stable. Each of these decisions, migration order, initial load handling, parallel running, equivalence validation, schema evolution, delivery guarantees, and cutover sequencing, builds on the one before it. A production migration from overnight batch to streaming replication succeeds or fails less on the strength of any single connector and more on the discipline with which these stages are sequenced and verified.