Snowflake Dynamic Tables vs Continuous Replication

The comparison sounds reasonable on its face. Both promise fresh data in Snowflake, both get described with CDC-like language, and Snowflake's own marketing touches both topics. But the framing falls apart once you ask what layer of the stack each one actually occupies. Continuous replication moves operational database changes across a system boundary, from a production database into Snowflake. Dynamic Tables never leave Snowflake. They take data that has already landed and orchestrate what happens to it next.

That's a layer distinction. One is an ingestion mechanism, the other is a compute and transformation mechanism, and confusing the two isn't a semantic quibble. Teams that expect a Dynamic Table to somehow reach into an operational database end up with a pipeline that has no source. Teams that build a replication pipeline without planning for what transforms the data once it lands end up with a warehouse full of raw, unusable change logs. Both mistakes come from the same root: treating two adjacent layers as if they were interchangeable options on a menu.

Dynamic Tables and the Problem They Solve Inside Snowflake

Say a retail analyst needs quarter-to-date order totals broken out by country, pulled from a handful of joined tables that update constantly throughout the day. Before Dynamic Tables, that meant hand-building Streams to track row changes, Tasks to run on a schedule, and MERGE statements to reconcile the results, all wired together and maintained by someone on the data team.

That shift, declaring the outcome instead of orchestrating the steps, is the entire value proposition. Two settings decide almost everything about how a given table behaves once it's created. TARGET_LAG controls how stale the data is allowed to get, expressed in seconds, minutes, hours, or days, or set to DOWNSTREAM so the table simply inherits its refresh cadence from whatever feeds it.

Under the hood, Snowflake attaches a hidden Stream to each base table the query touches, capturing every row-level insert, update, and delete as it happens. That's what makes incremental refresh possible: instead of rescanning the whole table every time, Snowflake processes only what changed. And because Dynamic Tables can reference other Dynamic Tables, you can chain them into a full pipeline with a bronze layer that lands raw data, a silver layer that cleans and conforms it, and a gold layer that aggregates it for reporting, all expressed as plain declarative SQL rather than a tangle of orchestration code.

What continuous replication solves that Dynamic Tables cannot

Everything in the section above assumes the data is already sitting in Snowflake. Continuous replication is what gets it there. Rather than repeatedly querying a source database and comparing results (the slow, brittle way teams used to detect change), continuous replication reads the database's own transaction log directly, using the WAL in PostgreSQL, the binlog in MySQL, or the redo log in Oracle, and turns every row-level change into an event the moment it's committed.

The reason this has to exist at all comes down to a basic fact about how systems are built: a production Postgres database has no native way to talk to Snowflake. Nothing in Postgres knows Snowflake exists. Something external has to sit in the middle, watch the source for changes, and carry them across that boundary, and that something is the replication layer. It functions as a precondition sitting alongside Dynamic Tables. It's the precondition for Dynamic Tables to have anything meaningful to work with.

Snowflake's role in this exchange is strictly as a destination. Only once that data has physically arrived inside Snowflake can a Dynamic Table see it, touch it, or transform it. Dynamic Tables have no reach beyond Snowflake's own storage, so if the replication feed stops from a dropped connection, a misconfigured slot, or a downed connector, the Dynamic Table pipeline doesn't route around the gap. It just stalls, refreshing against data that's stopped moving, because nothing substitutes for the missing feed.

Dynamic Tables in practice: refresh modes, performance, and 2026 capabilities

The single most consequential decision a practitioner makes with a Dynamic Table is its refresh mode, and it needs to be right before anything else. INCREMENTAL processes only the rows that changed since the last refresh, and it's the mode you want for the overwhelming majority of pipelines because it keeps compute costs down. FULL recomputes the entire result set on every refresh, which is necessary for some aggregations Snowflake cannot process incrementally. AUTO lets Snowflake pick the mode for you, which is fine while you're still building and testing something, but risky once it's running in production, because Snowflake's choice of mode can shift between platform releases without warning. Snowflake's own guidance is blunt on this point: never leave REFRESH_MODE set to AUTO in a production table. Build the table, watch what mode Snowflake actually picks in development, and then set that mode explicitly.

A newer mode, ADAPTIVE, entered public preview per Snowflake's blog post. It defaults to incremental refresh but will automatically fall back to a full recompute when its internal heuristics decide incremental would cost meaningfully more, which matters most for tables where you can't declare a primary key but still want to avoid paying for full refresh every single cycle.

For base tables that receive append-only CDC records, the QUALIFY ROW_NUMBER() = 1 pattern picks the latest row per business key regardless of ingestion order. Pair that with SELECT * EXCLUDE, and adding a column to the base table or dropping one flows the change downstream without anyone having to edit the Dynamic Table's definition.

2026 also brought a set of compute changes aimed squarely at cost. A dual warehouse strategy lets you assign a larger INITIALIZATION_WAREHOUSE to handle full scans and backfills, while a smaller warehouse handles the steady drip of incremental refreshes, cutting cost without slowing anything down. Adaptive Virtual Warehouses go a step further, collapsing that whole decision: one warehouse that scales itself to handle both the heavy initialization load and the lighter incremental cycles, so you're no longer choosing between multi-clustering and a manually split dual-warehouse setup. Immutable regions, announced in 2025, let you mark part of a Dynamic Table with IMMUTABLE WHERE (condition), locking that partition against any update, insert, or delete no matter what happens upstream, a natural fit for something like a closed accounting period that should never move again. And for anyone changing a table definition on data that's already been processed, Zero-Copy Clone lets you backfill a Dynamic Table with historical output directly, instead of burning compute reprocessing months or years of source history.

PRIMARY KEY RELY constraints are another piece to know about. Declare a primary key on a base table, and any downstream Dynamic Table created or replaced after that point, provided it passes the key column through without transforming it, will use that key for change detection instead of relying on change-tracking metadata columns. That matters operationally because an INSERT OVERWRITE on the base table resets those change-tracking columns, which otherwise cascades into a full reinitialization of everything downstream. Snowflake benchmarked the most popular Dynamic Table patterns from May 2025 to May 2026 and measured up to 2.8× faster refresh performance on Gen2 warehouses, covering top-level aggregate functions, QUALIFY row/rank = 1 (SCD-1), cluster-by operations, and joins.

The limits of Dynamic Tables

None of this makes Dynamic Tables a universal tool, and the boundaries sit in specific places. They operate entirely inside Snowflake. There's no version of a Dynamic Table that reaches out to a Postgres instance, a MySQL database, MongoDB, or DynamoDB and pulls data in on its own.

They also don't do append-only processing.

Certain patterns just don't express cleanly in a standard SELECT-based Dynamic Table. Complex MERGE logic that can't be flattened into a single SELECT statement is one. Stream-static joins, soft deletes represented as a flag rather than a physical row removal, and running accumulators that need state carried over from the previous refresh cycle all sit awkwardly, or don't fit at all, inside the declarative model Dynamic Tables are built around.

And latency has a hard floor set by whatever Target Lag you configure. Dynamic Tables are near-real-time by design, not streaming, so a use case that genuinely needs sub-second delivery has to solve that problem somewhere else, further up in the ingestion architecture, because no Target Lag setting turns a Dynamic Table into a streaming system.

Continuous Replication via Log-Based CDC from Source Database to Snowflake

PostgreSQL's logical replication is the clearest illustration of how this machinery actually runs. Postgres decodes its write-ahead log into a stream of logical, row-level events, inserts, updates, deletes, rather than exposing the raw physical page writes the database engine uses internally. A replication slot tracks how far a given connector has read through that stream, so if the connector crashes or gets restarted, it resumes from the slot's last position instead of missing changes or replaying ones already delivered.

Changes arrive in the order they were actually committed. Deletes and updates get captured as first-class events, not just inserts, which polling-based approaches routinely miss or approximate. And reading a log file imposes essentially no extra query load on the production database, which matters enormously on a system that's already handling live transaction traffic.

MySQL follows a comparable path through its binary log, the binlog, using a replication protocol native to the database engine itself. MongoDB works differently, since it's document-oriented rather than relational: Change Streams expose a document-level feed of changes, and the real engineering work sits in flattening deeply nested documents into something a warehouse table can represent, along with handling the schema-on-read variability that's normal in MongoDB collections but foreign to a fixed-schema warehouse table. DynamoDB exposes item-level changes through DynamoDB Streams, and while the change data itself is straightforward to read, landing it reliably in an analytics target takes deliberate architectural handling around ordering and delivery guarantees.

Whatever the source, the data that eventually reaches Snowflake typically arrives through Snowpipe Streaming into a staging or change table first, with a Snowflake task then merging those staged changes into the actual queryable target table. Only at that point is the data available as an input to a Dynamic Table.

Snowflake has started building its own presence directly in this layer. Openflow connectors tap into the native replication protocols of each source system, PostgreSQL's WAL, MySQL's binlog, SQL Server's Change Tracking API, with Oracle XStream support on the way, and deliver the resulting changes into Snowflake through Snowpipe Streaming. Snowflake Openflow connectors are deployable in Snowflake's managed infrastructure or in customer VPCs via BYOC. Separately, Snowflake announced Postgres Mirroring in June 2026, a push-based CDC mechanism the company describes as delivering transactional replication to the data lake with zero lag. Required configuration: wal_level = logical, max_replication_slots = 4, max_wal_senders = 4.

The replication tool landscape, how the main options differ in architecture and tradeoffs

Choosing a replication tool is less about picking a winner than about matching an architecture to what an organization is actually set up to operate. The options spread across a real spectrum: fully managed platforms where connector upkeep is somebody else's job, cloud-native tools tightly bound to one provider's ecosystem, and everything in between. Latency across these tools spans a wide range too, from sub-second on native log-based platforms out to fifteen minutes or longer on batch-oriented tools, and that gap isn't academic. Latency ranges from sub-second for native log-based platforms to 15+ minutes for batch-oriented tools, which has real consequences for use cases like fraud detection, inventory management, or AI-agent inputs.

AWS DMS is at the cloud-native end of that spectrum. It's a solid, well-worn choice for a short-lived, AWS-native database migration, moving a database from one place to another as a bounded project with a defined end. It's a weaker fit for an always-on pipeline feeding Snowflake continuously, especially one spanning more than one cloud provider, since that's simply not the problem DMS was built to solve.

Open-core and self-hosted options built on Debezium under the hood, wrapped in their own connector platforms and management interfaces, bring a broad connector catalog and treat formats like Iceberg as first-class destinations rather than afterthoughts. The tradeoff comes in what running that infrastructure demands: self-hosted directly or paid for as a managed cloud offering, it adds real operational surface area that somebody on the team has to own.

Debezium wraps each change event in a before/after envelope with source metadata and runs on Kafka Connect. That approach fits engineering organizations already committed to an open-source, event-streaming architecture and comfortable owning it end to end. It comes at a real staffing cost, though: running this kind of setup reliably at scale needs roughly 4-6 FTEs, and it adds Kafka infrastructure that many teams targeting only Snowflake do not otherwise need.

Broader ingestion-and-transformation platforms have consolidated hard in the last year. One notable example completed its merger with dbt Labs on June 1, 2026, after acquiring Census and Tobiko Data in 2025, and now sells ingestion, transformation, and a managed lake as one product. That kind of platform offers log-based CDC across the major database engines, along with schema drift handling and historical sync built in, with the vendor carrying the burden of connector maintenance. It suits organizations already juggling dozens of SaaS data sources alongside their operational databases, where the appeal of one vendor covering the entire ingestion layer outweighs the benefits of a more specialized, single-purpose tool.