Fan-In CDC From Five Production Databases Into One Snowflake Warehouse

Fan-in CDC replication from five production databases into one Snowflake warehouse requires database-native capture, source-aware versioning, and a destination layer that reconciles changes without losing their order or schema context. Preventing schema changes, retries, backfills, and competing writes from interfering at the shared destination is the central challenge.

What fan-in CDC actually means and why it compounds standard problems

Picture five database icons funneling into one Snowflake icon. The diagram hides the difficult part of fan-in CDC, which is that heterogeneous sources converge on a shared target without sharing replication guarantees. Standard CDC already requires log capture, schema handling, deduplication, and recovery. A schema conflict in one database can reach the destination while another accumulates replication lag and a third needs a backfill.

Snowflake’s Openflow engineering post describes production OLTP databases behind firewalls inside virtual private clouds, so capture must first work across five security perimeters. Beyond those perimeters, Postgres WAL, MongoDB change streams, and DynamoDB’s streaming models impose different recovery constraints. Supporting one does not establish readiness for the others.

How each source database exposes changes, and what that means for a shared pipeline

Postgres exposes row changes through WAL-based logical decoding. Committed changes go into the WAL before they are applied to data files, and logical decoding turns that record into insert, update, and delete events. A replication slot provides a named, crash-safe cursor into the WAL. Postgres retains the WAL that the slot still needs, allowing a disconnected consumer to resume from its saved position.

A lagging slot can retain enough WAL to fill the disk and block application writes. Setting max_slot_wal_keep_size, available from PostgreSQL 13+, limits how much disk space is at risk, and checking pg_replication_slots gives teams the data they need for slot-lag alerts. Retention alone does not establish downstream correctness. Log-based capture provides at-least-once delivery, so application at the destination needs primary-key UPSERTs and idempotency based on the transaction commit LSN or an intra-transaction sequence number. DDL requires a separate path because logical replication does not propagate it by default.

Snowflake’s data mirroring work introduces a different capture boundary. Its snowflake_cdc Postgres extension pushes changes into per-table change logs written to Iceberg through pg_lake, coordinating schema changes and DML/DDL transactions inside Postgres. Object storage separates the producer from the consumer. This differs from an external consumer pulling directly from a replication slot, and it changes which failures can propagate back toward the source.

MongoDB exposes change streams over its oplog. These require a replica set or sharded cluster because a standalone instance has no oplog. Each event includes a unique resume token in _id, which a recovering consumer supplies to continue from its saved position. Recovery depends on that position remaining available. Because the oplog is capped, falling behind until it wraps past the resume point invalidates the stream and requires a full re-snapshot. MongoDB 6.0 added fullDocumentBeforeChange pre-image support to capture documents before and after a change.

DynamoDB offers DynamoDB Streams and Kinesis Data Streams for DynamoDB, with different retention, consumer limits, and delivery semantics. AWS’s documentation distinguishes these as separate streaming models. Both isolate stream reads from table throughput. That keeps replication reads from consuming the application’s table read capacity, but does not remove the need to choose an adequate replay window. The destination schema also needs to handle new attributes, even though the source needs no fixed table schema.

Why ordering guarantees break down when five sources write to the same destination

Five source clocks do not create one timeline. Postgres LSNs, MongoDB oplog timestamps and resume tokens, and DynamoDB shard sequence numbers identify positions within their own replication systems. Events arriving at Snowflake in the same second therefore have no common sequencing coordinate, and arrival time cannot resolve conflicting updates from independent sources.

An older event delivered after a restart can replace newer state if the destination applies arrivals without checking their source position. Source-aware versioning prevents that regression within the relevant source stream, but it does not decide which database owns a conflicting value. A MongoDB backfill running beside four live streams adds another distinction, because snapshot and incremental positions cannot be conflated. The destination’s merge architecture is where those distinctions are either preserved or lost.

How changes actually land in Snowflake and why the merge layer is where fan-in pressure concentrates

Snowpipe Streaming can land events in an append-only staging table. Landing is only the first destination-side step. Snowflake’s Openflow uses a dual-table architecture where a journal records operations as they arrive and MERGE operations run at intervals to build a separate current-state table. The journal has an audit trail and allows replay, while queries use the state table. This separation allows ingestion and materialization to serve different purposes.

Snowflake’s streams documentation describes a stream as an offset into a table’s version history. Query results depend on that offset and the table’s change-tracking metadata. The offset advances only when the stream is consumed in a DML transaction, a distinction that affects how journal consumption and destination updates are coordinated.

Explicit MERGE suits workloads that need tight control over deduplication, version checks, and primary keys from different sources that conflict. That control comes with maintenance work. Dynamic Tables instead express materialization in SQL and compute incremental or full refreshes against a declared TARGET_LAG, including row-versioned Slowly Changing Dimensions. They suit declared transformations whose update and insert logic needs no upkeep by hand, though schema changes can still call for changes to queries. Neither choice removes fan-in write amplification, as concurrent pipelines can compete for destination tables, making merge frequency a per-source cost and latency decision.

Schema evolution as the most common break point in a multi-source pipeline

Schema evolution is routine over months of production operation. It becomes especially disruptive when sources expose structural changes differently but share destination tables. Postgres presents the least obvious gap here. Its logical replication stream does not propagate DDL by default, so a consumer needs event triggers, pg_catalog, or schema history tables to recognize changes. Without that separate handling, a column addition can cause the downstream MERGE to fail or leave the new column unrepresented.

DynamoDB attributes can become destination columns automatically only when the pipeline detects new fields and updates the target. MongoDB can change a field from a string to a nested object, requiring type handling as well as column discovery. These are not simply variations of relational DDL. The destination must reconcile both structural changes and changes in the values a field can represent.

Openflow detects schema modifications, updates destination tables, and creates new journal tables for the revised schema. Dynamic Tables support evolution without manual drops and recreation, but CREATE OR ALTER DYNAMIC TABLE query changes may be necessary, and added columns may trigger full background recomputation for standard Dynamic Tables. Backfills add a further constraint to both approaches. A snapshot must remain aligned with the schema version in effect when it was taken, rather than being interpreted unconditionally through the current schema.

Failure recovery and exactly-once delivery when five independent failure domains share one destination

Exactly-once application is a system property. Network retries, producer retries, and consumer rebalances make duplicate delivery a practical expectation. The destination therefore needs primary-key resolution and source-native position tracking so repeated events do not change already-correct state. Source recovery must also remain independent. Otherwise, bringing one stream back can disrupt materialization for the four sources that never failed.

DynamoDB Streams retains records for a short window, after which missed records are gone and affected tables require a full backfill. For replay beyond same-day, teams should route through Kinesis Data Streams for DynamoDB, which supports configurable long-term retention. MongoDB has a related retention boundary in its capped oplog. Either recovery can inject a large backfill into Snowflake while other sources continue supplying incremental changes.

Postgres failover requires explicit replication-slot preparation. On Postgres 17 and later, slot survival requires the failover option, standby settings including sync_replication_slots, hot_standby_feedback, and primary_slot_name pointing to a physical slot, plus synchronized_standby_slots on the primary. On Postgres 16 and earlier, a full re-snapshot may be needed if primary-local slots are lost without pg_failover_slots. The mirroring model moves this failure boundary by buffering pushed batches in object storage. After Snowflake-side outages, the apply process can then resume from the last committed batch in the meta log without the consumer causing WAL to build up.

How do CDC tools compare for five production databases feeding one Snowflake warehouse?

Snowflake Openflow is the strongest architectural fit among these options for Snowflake-centered heterogeneous fan-in, subject to coverage of the exact source set. It combines database-native capture, Snowpipe Streaming, automatic schema handling, a journal/current-state split, and configurable merge scheduling. Deployment is also part of that fit. Managed infrastructure and customer-VPC BYOC deployment address different network-isolation needs, while its native protocols include Postgres WAL, MySQL binlog, and SQL Server Change Tracking. These capabilities do not, by themselves, establish support for every proposed five-database combination.

AWS DMS documentation states that it offers no SLAs for CDC latency. Its limited schema evolution support and potential full-table resyncs make it unsuitable where heterogeneous fan-in requires a guaranteed sub-minute freshness commitment. PeerDB has a narrower design focus. Its Postgres-to-warehouse, queue, and storage orientation fits Postgres replication, rather than establishing suitability for a source set that also includes MongoDB and DynamoDB. Google Datastream documentation identifies unsupported DDL operations, including certain column drops and type changes, but the available details do not establish an equivalent Snowflake fan-in fit.

A self-built pipeline retains responsibility for everything beyond capture, including deduplication, schema sequencing, merges, and concurrent backfills. That requires five connectors and substantial destination engineering. Managed services can take on that work, but they make production databases depend on a vendor close to them. BYOC and passthrough designs help with concerns over where data is kept and who holds it, though the choice of where to deploy still leaves vendor lock-in. Tools therefore need to prove which sources they cover and how they recover, beyond listing connectors in a catalogue.

Security and compliance requirements that a fan-in architecture must satisfy at the pipeline level

Five production sources can bring five compliance perimeters with them. Where a pipeline handles regulated records, its infrastructure and vendor relationships fall within the relevant security assessment alongside the source databases and warehouse. The pipeline needs encryption in transit and at rest, role-based access to replication credentials, immutable record-write audit logs, and written steps for recovery. These controls need to operate continuously. A SOC 2 Type II audit assesses operation over a 3-to-12-month period, rather than inspecting only an audit-day configuration.

HHS OCR issued a Notice of Proposed Rulemaking in January 2025 proposing stronger HIPAA Security Rule obligations, including explicit BAA security terms and annual technical-control verification. Proposed requirements are not enacted requirements. For vendor assessment, the distinction still supports requesting a BAA and evidence of MFA, encryption, and incident-notification controls. Passthrough architecture narrows the custody surface because it does not persist customer data in pipeline infrastructure, but rules for handling regulated data still apply. If one source contains ePHI or PCI data, the applicable controls must follow that data through the shared pipeline.

Why fan-in data freshness is a prerequisite for AI agents and real-time analytics, not an optimization

An AI recommendation can join current customer data from Postgres with inventory from a DynamoDB stream that has fallen behind. The result may appear current while reflecting incompatible operational states. That failure is easy to miss in isolation. Each source can meet its own replication schedule while a cross-source query remains unsuitable for a real-time decision, particularly when one stream updates hourly and another daily.

Feature stores introduce a second correctness risk when replayed events replace newer feature values. Source-aware versioning stops that from happening, while freshness targets for the whole pipeline reveal lag that schedules for each source can hide. Sub-minute latency across all five sources is therefore a correctness requirement for the real-time uses described here. It does not create a global transaction order, which still requires the ordering distinctions established earlier. As agentic applications increasingly depend on current operational data from multiple systems, fan-in CDC becomes foundational infrastructure.