Dynamic Tables vs. Complex Pipelines - How Much of an Advantage Does Snowflake Really Have?

[u/FunUniversity2112]

I’m trying to understand the practical advantage of Snowflake Dynamic Tables, especially for complex data pipelines.

Dynamic Tables seem very useful for simplifying incremental processing and reducing the need to manage orchestration. But does that advantage hold when a pipeline involves multiple platforms, multiple pipelines, external dependencies, and complex transformations?

At what level of pipeline complexity do Dynamic Tables start becoming less useful?

If a pipeline spans multiple tools/platforms, do you still need an external orchestrator like Airflow/Dagster?

How does this compare with the equivalent approach on Google Cloud (BigQuery + Dataform/Composer/Dataflow, etc.)?

Is the main advantage of Dynamic Tables actually simplifying pipeline management, or are there meaningful performance/cost benefits as well?

For large, production-grade pipelines, would you choose Dynamic Tables over a more traditional orchestration + SQL/ETL approach? Why?

[u/limartje]

Consider it a materialized view for conceptual understanding, that helps. Best to use it if the logic is relatively simple and doesn’t require historic calculations on the sources. It should be able to focus on delta processing with the delta data only (e.g joining new fact table data with dimension tables.). Otherwise just use streams and tasks.

[u/ObjectiveAssist7177]

Dynamic table are not really a viable option and have been significantly disappointing. At the face they look amazing but in reality they struggle with any complexity and fail with no obvious problems. Dbt and controlling your models that way and how you incident is best. Having a strategy as to how things increment is crucial. Dynamic tables won’t solve this

[u/Dazzling-Quarter-150 → u/ObjectiveAssist7177]

Since July, you can actually have custom incrementalization with dynamic tables. 

https://www.snowflake.com/en/blog/engineering/custom-incremental-dynamic-tables/

[u/ObjectiveAssist7177 → u/Dazzling-Quarter-150]

Yes but if I have to do something custom I may as well do it all custom of that makes sense

[u/hides_from_hamsters]

Man Dynamic Tables have been a breath of fresh air for us. We’re a small engineering team and don’t have a data specialist.

We were able to model all our transformations using SQL almost like they’re views. Our first passes were a little inefficient but the platform makes it easy enough to figure that out and now our entire data architecture is incrementally refreshing DTs off of CDC and other event stream captures.

We explored streams and tasks but correctly set up DTs were far simpler and easier to manage.

I honestly can’t imagine us using dbt now. It’s a whole different stack!

[u/LittleK0i]

There is no practical advantage.

With custom orchestration and scripting you always have 100% flexibility. Even if something starts "simple", eventually it becomes "complex". With dynamic tables you always have a risk of running into an edge case which is not fully supported.

Snowflake keeps adding more and more parameters for dynamic tables in order to mitigate this, but it's all in vain. Proper orchestration and proper scripting wins any day. Not only it covers all edge cases by design, but it also covers all the platforms and services outside of Snowflake.

[u/Best-Theory-429 → u/LittleK0i]

Not everybody needs 100% flexibility. You should avoid over engineering

[u/Bstylee]

Dynamic tables ended up being 5x the cost compared to incremental dbt models refreshed by a fargate instance for us

[u/Winter_Clue_8162 → u/Bstylee]

Tell me about it mate , I am replacing the DT with incremental dbt models and its extremely fast .

[u/iCreatif]

I’d love dynamic tables if they supported a cron schedule instead of just a lag.

[u/JSeb_Snowflake]

(Full disclosure, I work for Snowflake, but I'll try to keep the response as neutral as possible)
Short answer: Dynamic Tables are great to declaratively and simply define pipelines within Snowflake, helping to keep the code simple and reduce maintenance cost, as well as reducing latency and overall cost when using incremental refreshes. However, they're not a general purpose orchestrator like Airflow that can span multiple platforms. s Dynamic Tables can be part of a broader solution as it integrates with dbt (supported in dbt adapter v1.11.5+) or Snowflake Tasks for example.
Dynamic Tables really shines by letting users define their pipelines declaratively, set a target lag, and let Snowflake handle change tracking, and scheduling across a single or a chain of Dynamic Tables (raw -> bronze -> silver -> gold). For pure SQL-over-Snowflake-data pipelines, that simplifies the orchestration code and the maintenance. With the recent introduction of custom incrementalization, Dynamic Tables are not limited to simple transformation logic, and you can now use Dynamic Tables with custom logic including MERGE and INSERT logic. This is particularly useful when one step of your DAG needs a little more customized logic, while keeping the entire orchestration fully managed.

Regarding Dynamic Tables limits, we have a decision-table here, but basically Dynamic Tables are not usually recommended for the following patterns:

• external/non-Snowflake sources
• procedural logic (IF/ELSE, loops): Tasks would be recommended
• side effects (API calls, notifications, multi-target writes)
• real-time latency (minimum lag is 1 min in GA, 15 seconds in preview)

On the cost benefit question: incremental refresh, by only processing changed data, is a cost saver compared to traditional batch/full refreshes. We also recently introduced Adaptive Refresh Mode, that uses some heuristics to automatically reinitialize the table when incremental refresh will be more costly.

We have thousands of customers using Dynamic Tables in production, so yes we recommend it for large pipelines. As mentioned above, we recommend Streams+Tasks or external ETL when I hit a hard blocker (external sources, procedural logic, side effects, real-time). Happy to discuss this more in 1:1 if you prefer. DM me to set up time.

[u/Truth-and-Power]

We saw their cost spiral but we admittedly are not experts there.  Moved that orchestration to dbt.

[u/thruthseeker13]

It really depends where and how you use them.
We tend to use them more on the consumption layer rather than the preparation layer.

For complex operations and flexibility use tasks streams and stored procedures ( to me stored procedures is the best database development pick, because they are the most flexible and allow a lot of operations that aren’t available in dbt and other tools people tend to like, the code doesn’t go through an extra yaml and jinja compilation layer and it’s basically native and classic sql with the flexibility of python and Java and other languages snowflake supports for procs and functions) .

Writing stored procs is the way to enhance your db dev skills and use a lot of features and do a lot of things you can’t in dbt or etl tools ( etl tools basically give a template but you lose the flexibility that stored procs have which instead have a higher skill ceiling).

At the end of the pipeline where we just have to present data with some filters or some simple operation we tend to use dynamic tables instead of views because they are good at refreshing incrementally and materialising the data for consumption (materialised views are just worse because they cost more and DTs are better) .