Snowflake Dynamic Tables vs Streams and Tasks for CDC
Declarative tables handle orchestration; engineers own the pipeline with streams and tasks.
Dynamic Tables and Streams + Tasks both keep a derived Snowflake table current with its sources, and both run natively inside the warehouse, so you need no external orchestration layer on top. The confusion is fair: from a distance, a Stream + Task that runs a single INSERT INTO … SELECT looks almost identical to a Dynamic Table, because both watch a source table and update a target on a schedule. The resemblance stops at the surface. Dynamic Tables make a declarative promise about freshness and then manage their own execution, deciding when and how to refresh. Streams + Tasks hand an engineer the raw primitives, change tracking on one side and scheduled execution on the other, and leave the job of assembling and running the pipeline entirely to the team that built it. That's a difference in who holds operational responsibility once the pipeline is live, and it's the only question worth asking before choosing between them.
How Dynamic Tables work: the declarative contract
A Dynamic Table materializes the result of a SELECT query and keeps that result current, and you don't have to intervene again. An engineer writes the query once, sets a TARGET_LAG, and Snowflake takes over dependency tracking, scheduling, and the refresh itself. A table named dt_orders might be defined with a TARGET_LAG of five minutes against a join of raw order and customer tables; from that point forward, Snowflake decides when to refresh it and how, not the engineer who wrote the query.
Refresh behavior comes in three modes. AUTO is the default: Snowflake picks between incremental and full refresh based on the shape of the query. INCREMENTAL forces incremental refresh and fails at creation time if the query can't support it, which gives a team a way to confirm, before anything runs in production, that a large table won't silently fall back to a full rebuild every cycle. FULL forces a complete rebuild every cycle; this fits when the transformation has constructs that can't be tracked incrementally, or when the table is small enough that rebuilding it is cheaper than maintaining change state.
The real payoff appears when Dynamic Tables are chained, each one reading from the table beneath it in a bronze-to-silver-to-gold style stack. Snowflake refreshes the whole chain in dependency order against one consistent snapshot, so if you query the top of the chain, you never see a partial result from a refresh still running partway down. If you set TARGET_LAG = DOWNSTREAM on the intermediate tables in that chain, they refresh only when something downstream actually needs them, so compute isn't wasted on a layer nobody queries directly. Iceberg support, covering both reads from Snowflake-managed Iceberg tables and the creation of dynamic Iceberg tables, reached general availability on November 12, 2024.
Underneath the CREATE DYNAMIC TABLE statement, Snowflake attaches lightweight change tracking to the base tables the query depends on, builds a dependency graph across any chained tables, and uses that graph to decide the order and scope of each refresh, falling back to incremental merge logic wherever the query permits it. None of that machinery is something an engineer configures directly. It's the trade a team makes for writing one SELECT instead of a pipeline.
How Streams + Tasks work: the imperative contract
Streams + Tasks reached general availability on April 6, 2020, well before Dynamic Tables existed as an option, and the pattern it established still defines how most procedural CDC pipelines in Snowflake get built. A Stream is a change-tracking object layered on top of a table: it records which rows have been inserted, updated, or deleted since the Stream was last read. A Task is a unit of scheduled or triggered SQL execution. Neither does anything on its own; you wire them together and decide what the Task does with whatever the Stream reports.
Because of that triggered-task approach, a Task never wakes up to find an empty Stream and burn a warehouse-resume cycle for nothing. Inside the Task body, an engineer has access to stored procedures written in JavaScript, Python, Java, Scala, or Snowflake Scripting, along with MERGE statements and external function calls, none of which can be written inside a Dynamic Table's query definition. That procedural range is the entire reason Streams + Tasks still exist as a pattern rather than being fully subsumed by a declarative alternative.
The SCD Type 2 pattern is the canonical Streams + Tasks use case: a Stream tracks row-level changes in the source; a Task runs on a schedule or event trigger; the Task executes a MERGE that expires existing active records, inserts new versions, and preserves history. A Stream watches row-level changes on the source table. A Task runs on a schedule or fires when the Stream has data. The Task executes a MERGE that expires the active version of a changed record, inserts the new version, and leaves the full history of prior versions intact. That's a structural skeleton, not a finished script, and every joint in it, the Stream's scope, the Task's trigger condition, the MERGE's key logic, is something the engineer has to decide and maintain.
That ownership comes with a built-in discipline. A Stream's offset only advances once the consuming Task commits successfully; if the Task fails partway through, the transaction rolls back and the offset stays where it was, so the next successful run picks up the same changes again. That guarantee keeps the pipeline from silently losing data on a failure, but it also means every MERGE written against a Stream has to be idempotent, because reprocessing the same change twice is not a hypothetical: it's the designed behavior.
The 60-second floor
Dynamic Tables cannot refresh faster than a 60-second TARGET_LAG, and that floor is a hard architectural limit, not a setting that can be tuned down with enough compute. Any pipeline with a freshness requirement tighter than a minute has already ruled out Dynamic Tables, independent of every other factor in the decision.
Streams + Tasks look like they escape that floor, since a Task can technically be scheduled every minute, but a scheduled interval is not the same thing as delivered freshness. A Task has to start, a suspended warehouse has to resume, and the MERGE has to finish executing, so the real end-to-end lag is the scheduled interval plus however long the run actually takes. Triggered tasks fire when a Stream receives data instead of waiting on a fixed clock, so they close most of that gap, and when responsiveness actually matters, they're usually the better call than a tight cron schedule.
Genuine sub-minute latency inside Snowflake requires a different mechanism entirely: Snowpipe Streaming, which can deliver ingest-to-queryable latency as low as 5 seconds, with Named Channels providing ordered, exactly-once ingestion through offset tokens for sources that demand strict ordering, like Kafka partitions or CDC feeds. Dynamic Tables and minute-level Tasks both settle near the same lower bound on freshness, and Snowpipe Streaming is built to go below it. They aren't competing options so much as different bands on the same latency spectrum, and a pipeline's freshness requirement usually picks the band before any other factor gets a vote.
For pipelines that genuinely need sub-minute freshness, such as real-time dashboards or AI agents trained on live operational data, neither Dynamic Tables nor minute-level Tasks will get there, and the gap has to be closed before data ever reaches Snowflake's refresh cycle at all. A managed CDC platform like Artie streams row-level changes from the source database to the warehouse at sub-60-second latency, so it closes that gap upstream instead of asking Snowflake's internal mechanisms to do something they're not built to do.
Where Dynamic Tables break down in production
The limits on Dynamic Tables are documented, and a team that deploys the feature without reading them will eventually hit one in production with no configuration path around it.
The output of a Dynamic Table is read-only: it cannot accept INSERT, UPDATE, DELETE, or TRUNCATE. If a pipeline needs to modify the output table directly, a GDPR right-to-erasure request being the clearest example, it has no option inside a Dynamic Table, so it has to use Streams + Tasks against a standard table instead. The query definition itself can't contain a MERGE, so if your pipeline is built around upsert logic against compound keys, you can't express it as a Dynamic Table. SEQ1, SEQ2, UUID_STRING, and RANDOM() are all unsupported in incremental refresh mode, which blocks the surrogate-key generation patterns that occur constantly in warehouse dimensional modeling.
Operational costs compound these constraints. Adding a column to a Dynamic Table's definition forces Snowflake to fully reinitialize the table, reprocessing every row from scratch, so pipelines whose schemas change often are better served by Streams + Tasks against a table that accepts a plain ALTER TABLE. Incremental refresh degrades or fails outright once more than roughly 5% of the underlying data changes between refresh cycles, and that limit matters directly if your table has high write volume. When a refresh can't run incrementally, it fails silently, and the only way to catch that is to have built monitoring and alerting against Snowflake's refresh history ahead of time. Dynamic Tables also can't read from directory tables, external tables, other Streams, or materialized views, which rules certain source architectures out before the question of freshness even comes up.
Two further gaps matter beyond raw mechanics. Snowflake's ACCESS_HISTORY auditing view doesn't capture operations on Dynamic Tables, which is a structural compliance gap if your team operates under HIPAA, SOC 2, or a similar audit regime. And the most surprising failure for teams that assume these tools compose cleanly: a Dynamic Table sitting downstream of a dbt-managed table model can serve stale data indefinitely, because dbt's CREATE OR REPLACE statement on the upstream model destroys the change tracking history Snowflake relies on, after which incremental refresh can no longer run (full-refresh mode tables are unaffected) while the dbt run itself keeps reporting success.
Where Streams + Tasks break down
Streams + Tasks fail too, and none of those failures are bugs. They follow directly from the imperative contract: the team that owns the pipeline owns whatever breaks in it.
The offset behavior that makes Streams + Tasks safe against data loss is the same mechanism that makes idempotency mandatory rather than optional. When a Task fails, its transaction rolls back and the Stream's offset doesn't move, so the next successful run reprocesses the same set of changes. That guarantees nothing gets lost, but it also means duplicate processing is a real risk unless every operation downstream is written to tolerate reprocessing. MERGE is the standard fix, and it has to be applied consistently across every Task in the pipeline, not just the ones an engineer happens to remember. A Stream left unconsumed for longer than the table's data retention window, 14 days by default on permanent tables, goes stale and has to be recreated from scratch, which turns into a quiet production incident for any team without monitoring watching for it.
The operational load is broader than any single failure mode. Where Snowflake manages the entire scheduler for a Dynamic Table, a Streams + Tasks pipeline leaves the team responsible for monitoring task history, accounting for warehouse resume latency, managing dependencies between tasks, and responding when something in that chain breaks. None of that is optional overhead; it's the ongoing cost of the control the pattern provides.
None of this makes Streams + Tasks the riskier option. The failure contract is explicit instead of managed by Snowflake on the team's behalf, and that's the right trade when a pipeline genuinely needs procedural control, output tables that accept direct writes, or MERGE logic against compound keys. It's the wrong trade when none of those requirements apply and a team is carrying operational weight for no reason.
The hybrid that most production pipelines use
A narrative has taken hold that Dynamic Tables are the modern default and will eventually replace Streams + Tasks across the board. What production pipelines actually look like contradicts that story: most teams end up running both, in combination, rather than picking one and retiring the other.
Snowflake's own guidance cuts straight to the deciding test: does the transformation logic fit inside a single SELECT? If so, it's a candidate for a Dynamic Table. If it doesn't, it isn't, and no amount of clever SQL restructuring changes that. In a realistic architecture, CDC-based ingestion from operational databases lands raw data into Snowflake tables first, a layer that sits outside both tools entirely.
From there, Dynamic Tables take over the multi-table SQL transforms across the bronze-to-silver-to-gold stack, the joins, aggregations, cleaning, and denormalization that used to be written by hand as Streams + Tasks pipelines before a declarative option existed. Streams + Tasks remain in place for the steps that resist a single SELECT: stored procedures, external function calls, MERGE statements against compound keys, and any output table that has to accept direct writes. The SCD Type 2 pattern splits cleanly along this line. Where the logic can be derived with SQL window functions, it belongs in a Dynamic Table; where it requires procedural control or exact CDC precision, it belongs in Streams + Tasks.
Dynamic Tables haven't made Streams + Tasks obsolete. They've absorbed the declarative majority of the transformation work that used to require hand-built pipelines, while the procedural minority, the steps that need direct writes, compound-key MERGE logic, or external function calls, stays exactly where it's always been.
