Fan-In CDC Topology for Multi-Source Postgres and MySQL to Snowflake

Fan-in CDC from multiple Postgres and MySQL sources to Snowflake requires coordination across replication tasks. A topology holds in production when it keeps each source’s positions and failures separate and has clear rules for ordering, schema mapping, and replay at the merge point. Those contracts matter more than the number of connectors feeding the destination.

Why fan-in CDC is not just single-source replication done twice

Operational systems running on on-premises Postgres and MySQL need a downstream destination for real-time analytics because analytical queries against production databases degrade application performance. The apparent solution is simple. Union the change events, tag each row with its origin database, and write them to Snowflake.

For example, two sources may update the same customer_id, yet their logs provide no common sequence that tells the destination which update came first. Even where records do not overlap, a Postgres created_at timestamp and a MySQL creation_date datetime string do not form a unified column merely because they reach the same table. Running separate connectors preserves each source’s view of its changes. It does not establish a shared interpretation of those changes.

How Postgres logical replication works and where it creates fan-in risk

Postgres logical replication gives a CDC consumer a crash-safe, ordered cursor into the WAL. It requires wal_level=logical, because the default replica setting lacks the additional information needed to extract logical change sets. A replication slot keeps WAL until the consumer confirms how far it has read. So max_replication_slots needs room for one slot per CDC consumer plus headroom, while max_slot_wal_keep_size needs a finite limit in place of its default of -1.

The slot protects the consumer’s position, but it does not carry every part of the replication contract. Native logical replication does not propagate schema changes such as adding a column, so third-party DDL handling requires explicit verification. Failover also depends on the PostgreSQL version. Before PostgreSQL 17, slots were lost during failover, so plans had to cover slot recreation and re-sync. PostgreSQL 17 added sync_replication_slots to periodically copy failover slot state to the standby, addressing that gap without solving destination recovery.

If slot loss requires a new snapshot, Postgres replay can reach Snowflake while MySQL continues sending live changes. The destination must then merge the replayed changes with the live updates.

How MySQL binlog replication differs and why those differences matter at the merge point

MySQL also exposes logged changes, but its position model differs from the Postgres slot model. With gtid_mode=ON, GTID positions survive replica promotion, allowing a connector to resume from its GTID position without Postgres-style slot recreation. That makes GTIDs preferable for recovery, but it does not make them comparable with Postgres positions. The merge layer still receives two independent accounts of progress.

Retention creates a second distinction. On Amazon RDS and Aurora, binlog retention is set through mysql.rds_set_configuration('binlog retention hours', 24) and is based on time, independent of when the consumer confirms progress. A consumer that falls behind the retention window loses the log history needed to resume incrementally. Recovery then requires a snapshot reload.

At the merge point, that reload arrives beside live Postgres changes. The destination must absorb the replay without duplicating rows or treating its arrival order as the order of business changes. Thus, GTIDs improve source recovery without removing the need for replay-aware destination logic.

The three structural problems that only appear when streams are merged

Cross-source ordering is the first problem. When both databases hold records for the same customer_id, the unified stream needs that entity key to bring related events into the same partition. But Postgres LSNs and MySQL GTIDs are incommensurable clocks. Neither establishes which source committed first, and arrival order can reverse the apparent order of two updates. Partitioning provides per-key sequencing.

The practical mitigation is to attach a source-local high-resolution commit timestamp to every event and use it in the merge ordering. Even then, sub-millisecond conflicts remain unresolved without application-level last-write-wins semantics. The distinction matters because an ordered transport cannot supply a conflict rule that the application has never defined.

Different names and representations must be normalized before events enter the unified stream, and those transformations must be versioned. Otherwise, a change on one source can invalidate the shared mapping while the other source continues producing valid events under the old contract.

A failure that is not isolated can turn a local incident into a pipeline-wide outage. A shared checkpoint can make healthy sources wait for a failed source, leaving their changes queued and stale. Per-source checkpointing prevents that dependency by advancing each committed position independently.

How Snowflake receives and applies merged change streams

Snowflake’s CDC landing pattern separates the change history from the current table state. Events first enter an append-only journal with operation type and timestamp metadata. A scheduled merge then applies inserts, updates, and deletes to the target, collapsing multiple operations on a row into its latest state. Snowflake’s Openflow engineering description presents this journal-then-merge pattern as a central part of its architecture.

For fan-in, keep each source’s changes in separate journal tables before the final unified merge. This preserves failure isolation at the destination and makes the merge logic auditable. Consistent merge cadence across sources also prevents uneven processing lag from appearing as holes in the unified table.

Snowpipe Streaming is the preferred ingestion API for low-latency CDC, while file-based Snowpipe adds delay between file landing and queryable data. Snowflake Stream objects track table changes, but schemas still need to be managed. Dynamic Tables do not adapt on their own when a source-table schema changes, so refreshes can fail and the Dynamic Tables usually have to be recreated.

Schema evolution across multiple sources and why it is the sharpest operational risk

Schema evolution is especially risky because it can break the mapping contract while some sources keep running. A column added to the Postgres version of a table can produce misaligned destination rows if the unified mapping no longer matches the participating schemas. PostgreSQL 11+ captures TRUNCATE, but ALTER TABLE changes do not travel through native logical replication. Schema coordination therefore sits outside the replication slot.

External pull-based consumers must handle this gap explicitly. An added nullable column may be handled automatically in a self-built stack, while a binlog event that no longer matches the cached column list can fail the run and require a full table re-snapshot. Failing is preferable to silently assigning values to the wrong columns. However, neither behavior establishes that the other source’s mapping remains valid.

Snowflake’s Mirroring engineering description explains a different approach through the snowflake_cdc extension. Running inside Postgres lets the extension coordinate schema changes, complex DML and DDL transactions, and snapshot alignment in a way external pull-based consumers cannot replicate. For teams not using a managed service, the practical defense is a strict per-source column-mapping registry. It must have rules for COLUMN_ADDED, COLUMN_DROPPED, and COLUMN_RENAMED, and changes must be versioned and applied atomically before events enter the unified stream.

Exactly-once delivery is not achievable at the recovery frontier — and what to do instead

Log-based CDC must be designed for at-least-once delivery. After a Postgres restart, a slot may resend changes that the consumer already processed. Source-side recovery state alone cannot show whether the destination accepted every event before the interruption. Fan-in compounds the ambiguity when independent source failures replay changes for the same entity.

If the sink has no query endpoint to check what it accepted in the past and supports neither idempotency keys nor fencing, a recovery policy that checks only source-side state cannot achieve exactly-once delivery at the recovery frontier. Under those conditions, resending ambiguous events accepts duplicates, while skipping them accepts omissions. This limit depends on the sink’s capabilities.

The practical response is to make Snowflake writes idempotent through upsert or MERGE by primary key. With per-source checkpoints, healthy sources can avoid replay when another source has to recover.

How WAL disk bloat and binlog gap become correlated failures in a shared-destination topology

Postgres and MySQL respond differently when consumption stalls. A Postgres slot can keep WAL until the disk is full, while MySQL retention based on time can remove changes before the consumer reads them. These are separate source failures, but a shared destination exposes them to the same period of downstream delay. Their recovery traffic can then converge on the same merge capacity.

If both sources require snapshot reloads around the same incident window, Snowflake receives two replay floods. This can cause merge contention, use more credits, and leave the unified table inconsistent for a time. Capacity that is enough for normal incremental changes may be too small for that recovery load.

Monitor replication slot lag through pg_replication_slots and binlog consumer lag independently, with alert thresholds well before disk exhaustion or an unrecoverable retention gap. Stagger snapshot reloads across sources where possible. Size Snowflake merge capacity for peak replay throughput rather than steady-state throughput, because recovery is part of the workload the topology must support.

Choosing between a Kafka intermediate layer and direct CDC for fan-in

The architecture choice follows from the required replay and isolation boundaries. Direct CDC has lower latency, and the work to run it is of low-to-medium complexity. It is justified when there are two or three sources, schema overlap is limited, separate Snowflake journals provide sufficient isolation, and no other downstream consumers need the stream. In that case, an intermediate broker adds responsibilities without meeting a stated requirement.

Kafka becomes justified when the stream must also feed a search index, cache, or real-time dashboard. It also keeps data for replay through long consumer outages and has broker-level per-partition ordering for shared entity keys. In the unified stream, partitioning by customer_id brings that customer’s events together from all sources. It still does not make LSNs and GTIDs a common clock.

Those features add latency and make the system harder to run. Teams must own topic provisioning, consumer group offsets, the schema registry, and broker scaling. For teams that lack Kafka expertise, that work can take more effort than running the CDC pipeline itself.

Managed services versus self-built pipelines for fan-in at production scale

Build versus buy is a question of sustained engineering capacity. A self-built pipeline requires WAL and binlog consumers, independent checkpoints, schema handling, idempotent merges, and source-lag monitoring. Each component is maintainable on its own. Their interactions create the larger production burden.

AWS DMS uses native log-based CDC for Postgres and MySQL, but its fan-in behavior and schema handling require verification against the specific source and target versions. Snowflake Openflow automatically detects schema modifications, updates destination tables, and creates new journals for the changed schema before continuing replication. Snowflake Mirroring instead uses snowflake_cdc with pg_lake to push changes from Postgres into Iceberg tables. That extension-based capability is limited to Snowflake Postgres among Postgres and MySQL sources.

Cost also depends on data shape. Monthly active rows pricing penalizes churn across many distinct rows, row-based pricing penalizes high-volume append logs, and per-GB pricing penalizes wide JSON payloads. Credits and flat fees provide other models. Evaluate pricing against the expected workload.

A production checklist for a fan-in CDC topology that will hold under operational pressure

Set Postgres wal_level=logical. Size max_replication_slots with headroom. Set a finite max_slot_wal_keep_size. Check what happens to slots during failover in the PostgreSQL version, and plan for slot recreation on older versions. For MySQL, confirm binlog_format=ROW and gtid_mode=ON, and set explicit binlog retention on RDS and Aurora.

Maintain separate Snowflake journals for each source. Key the unified stream by entity across sources. Keep a versioned column-mapping registry for each source, with clear rules for how to handle add, drop, and rename changes. Test DDL propagation in staging before changing production schemas.

Keep checkpoints independent for each source. Alert separately on replication slot lag and binlog consumer lag before recovery history is lost. Size destination merge capacity for peak snapshot-reload throughput.

Accept at-least-once delivery as a design input. Use primary-key MERGE at the destination and build idempotency into every write path. Decide whether recovery at the failure frontier will be duplicate-tolerant or omission-tolerant. That decision is part of the topology’s correctness contract and must be made before an incident.