Consolidating Five Operational Databases Into Snowflake Without a Custom Orchestration Layer

Consolidating five operational databases into Snowflake is a fan-in CDC problem spanning five connections to a warehouse. The hidden cost is the custom orchestration needed to coordinate schema changes, ordering, checkpoints, and recovery across independent streams. A managed replication service with native fan-in support takes on that work so the team no longer needs the custom layer, and Snowflake staging and state tables hold the data that analysis can rely on.

Why consolidating multiple operational databases into Snowflake is harder than it looks

Consider a team with five operational databases spanning Postgres, another relational database system, MongoDB, and DynamoDB, all feeding the same Snowflake destination. Each source has its own replication protocol, schema lifecycle, and failure modes. Those differences become operational dependencies when their data serves a shared analytical layer. One stream can be current while another is recovering. A connection that works therefore says little about whether the data in the warehouse is ready for analysis.

Batch reloads move entire tables to find a handful of changed rows, while CDC moves only the rows that changed. That distinction makes incremental replication attractive. However, capture efficiency alone does not resolve deduplication, schema state, or the consequences of independent retries across five sources.

How each source database surfaces its changes, and why the mechanisms differ

Postgres exposes changes through its write-ahead log, or WAL, using logical replication rather than repeated queries against production tables. Snowflake’s Openflow engineering post describes capture through native APIs such as CaptureChangePostgreSQL. This requires wal_level = logical. On RDS, setting rds.logical_replication = 1 requires a reboot because the parameter is static. A publication sets which tables take part, and a replication slot keeps WAL segments until the consumer reads them.

A paused consumer can leave WAL accumulating on disk, so slot lag needs operational monitoring. Event completeness also depends on configuration. By default, the before-image includes only key columns unless REPLICA IDENTITY FULL is set. The downstream apply layer must use the images that capture gives it.

MongoDB instead exposes Change Streams over its internal oplog. A replica set or sharded cluster is required because a standalone instance cannot stream through this mechanism. Every event has a resume token that the pipeline must save in durable storage. Recovery depends on a finite retention window. If the required oplog entries have been overwritten before the token is used, the collection needs a full re-snapshot.

DynamoDB Streams captures changes for each table and keeps a durably stored record of each item-level write in time order. Its NEW_AND_OLD_IMAGES view includes both images, which supports auditing and change comparison, but it is not a universal requirement for replication. The stream has a short retention window. These protocols therefore differ in metadata, recovery state, and ordering semantics before any event reaches Snowflake, and their outputs need normalization before a common apply pattern can work.

How do you consolidate CDC streams from five databases into one Snowflake warehouse?

Snowflake’s Openflow post describes a dual-table architecture for applying captured changes. Each source table has an append-only staging table that gets events through Snowpipe Streaming and a separate state table that holds the latest materialized data. The append is only the first step. Snowpipe Streaming inserts events without scanning the data already there, but it does not run the MERGE that turns those events into current state. That work needs separate apply logic.

A scheduled MERGE uses WHEN MATCHED for updates and WHEN NOT MATCHED for inserts, and it also needs logic for deletes. A direct MERGE from an incoming stream, however, is not equivalent to a deduplicated apply operation. Commit-timestamp guards prevent an older replay from replacing newer data, while within-batch deduplication resolves repeated changes before they reach the target.

Snowflake Streams and Tasks can supply internal change tracking and scheduled execution for this pattern. They do not capture changes from external databases. Across five sources, staging pipelines and MERGE jobs still have separate compute demands, retries, and lag monitors. Without a shared control plane, those jobs have no automatic awareness of one another. The code that coordinates them is, in practice, a custom orchestration layer.

How schema drift across five sources turns the orchestration layer into the product

Schema evolution makes that layer cost much more to maintain. A column addition may be safe if the pipeline propagates nulls, while a rename breaks hardcoded field references and a type change can silently corrupt data. Table drops require a guard against unintended propagation into the warehouse. Each can change what staging, apply logic, and downstream consumers need to do.

The frequency is easy to underestimate when sources are considered separately. A schema change may be rare for an individual table, yet changes across five tables create a much busier aggregate workload. Their timing is independent as well. A Postgres migration can coincide with a MongoDB backfill while a DynamoDB table is under high write load. The orchestration layer must track schema state per source rather than treating the entire pipeline as having one schema version.

Ad hoc handling turns those differences into detection scripts, manual rename workflows, and alerts outside the replication tooling. A rename can break a staging table, a MERGE reference, and a dashboard join before a consumer identifies the problem. The maintenance work then competes with the analytical project itself. Snowflake’s Openflow post identifies automatic adaptation to additions, renames, and drops as a connector responsibility, without manual configuration or additional scripts. Consolidation should proceed with tooling that owns schema evolution.

Ordering guarantees and exactly-once delivery across parallel streams

Most CDC pipelines deliver events at least once, so a restart can deliver an event that the warehouse has already processed. Duplicate delivery is not automatically duplicate state. The distinction depends on an idempotent apply operation that can run again without replacing a newer value with an older one. Versioning based on transaction log position supplies an ordering guard for upserts. Commit-timestamp conditions such as WHEN MATCHED AND s.commit_ts >= t.updated_at THEN UPDATE provide the corresponding protection in the MERGE.

Snowflake’s Openflow post describes at-least-once delivery with buffering and streaming, leaving deduplication and ordering enforcement to the MERGE step. For consolidation, the useful requirement is that repeated delivery cannot corrupt the materialized state. A service claim about delivery semantics therefore needs to be read together with its apply behavior. An idempotent target is the practical safeguard after a restart.

Recovery also has different deadlines for different sources. MongoDB depends on durable resume tokens and retained oplog entries, while DynamoDB has a 24-hour stream retention window. An outage beyond that window requires a re-snapshot from DynamoDB. In the meantime, other consumers can keep moving forward, so Snowflake can be further along with some sources than with others. Custom orchestration must track those checkpoints and retry boundaries, detect inconsistent progress, and decide whether downstream queries should wait. Schema changes that arrive during recovery add more state for custom orchestration to track.

What AWS DMS handles and where it stops

AWS Database Migration Service, or AWS DMS, supports full-load and ongoing CDC replication, including cross-engine cases. It runs through a managed replication instance in the AWS account and is a legitimate choice for migration work. Its strengths fit that purpose. AWS-centric teams performing point-in-time migrations or cross-engine replication within an AWS ecosystem can use DMS without treating it as a general-purpose continuous integration platform.

AWS DMS documentation states that it “does not provide real-time replication” and offers no latency SLA for CDC tasks. Typical latency can range from seconds to several minutes depending on configuration and load. The absence of an SLA means a team cannot treat a particular observed latency as a guaranteed service property.

The more decisive limitation for this project is task coordination. DMS requires a separate replication task per source pair, and its documentation states that changes are not coordinated across tasks because each task has its own capture and log-reading process. Schema changes can be replicated, but keeping downstream tables consistent usually takes manual configuration and extra scripts. Ordering, deduplication, and schema state across five sources therefore remain outside DMS.

Why native fan-in support in a managed replication service eliminates the orchestration layer

Checkpoint management, schema-change detection, buffering, MERGE scheduling, and recovery become service responsibilities rather than separate systems maintained by the warehouse team. The coordination still exists beneath the interface. What disappears is the custom layer that the team would otherwise build to connect independently operated pipelines.

Snowflake’s Openflow post describes the relevant connector architecture, including CDC capture, buffering, Snowpipe Streaming delivery, and MERGE orchestration with schema evolution and failure recovery. A managed service can apply the same staging-to-state pattern across all five sources through one control plane for scheduling, retries, and monitoring. When column additions are supported, both staging and state tables can update on their own. These routine changes then need no repair script or manual work, though schema events still help the team see what is happening.

For this workload, the recommended choice is a managed CDC service with native fan-in, automatic DDL handling, per-source recovery, and idempotent apply. DMS fits the migration-oriented case described earlier, while Openflow provides a concrete reference for connector-owned Snowflake delivery and apply orchestration. A service must support the mix of sources in use and take care of the whole workflow before it can free the team from custom coordination work. Uniform replay protection prevents a restarted consumer from corrupting state, without turning independent source progress into a claim of simultaneous recovery.

How the apply layer in Snowflake should be structured once the orchestration burden is removed

Once the service takes care of coordination, the Snowflake layer should have the same staging-and-state structure for every source table. The append-only staging table receives events through Snowpipe Streaming, while the state table provides the analytics-ready mirror. Their roles should remain distinct. Staging supports audits, local replays, and reconstruction of a table’s state at a particular time. The state table gives consumers the latest materialized values they need.

The apply contract should require deduplication with primary keys and source log positions before the MERGE runs. The illustrated QUALIFY ROW_NUMBER() OVER (PARTITION BY key ORDER BY commit_ts DESC) = 1 pattern selects the latest timestamped event per key within a batch. Every matched update should skip events that are older than the target state. Deletes need their own logic, such as WHEN MATCHED AND s.op = 'D' AND s.commit_ts >= t.updated_at THEN DELETE, or soft-delete flags used the same way each time where audits are a large part of the work.

Snowflake Streams can track internal DML through metadata including METADATA$ACTION and METADATA$ISUPDATE, while Tasks schedule the MERGE. Those streams need to be consumed regularly to keep up. Managed scheduling removes that maintenance obligation from bespoke jobs. Observability should still expose lag independently for each source, including Postgres slot lag, MongoDB oplog retention margin, and the DynamoDB retention window. Schema notifications should identify DDL events before the first failed dashboard query.

This architecture leaves the team responsible for analytical correctness without making it the operator of five separate recovery systems. The warehouse has a consistent apply contract, and the replication service owns the source-specific coordination. That is the practical payoff of native fan-in. A correctly structured five-source consolidation becomes operationally equivalent in complexity to a single-source pipeline because the fan-in problem is solved at the service layer, keeping that complexity out of the warehouse.