At Ulpia, we are eager to try out new technologies. When a client of ours asked us to help improve the implementation of dynamic tables, we immediately identified several use cases where we wanted to see how they would perform.
However, the more we tried to make them work, the more we felt the frustration of using them, which ultimately led to focusing our efforts on leveraging old school ETL approach, based on tasks and procedures.
If you are in the process of deciding whether you should or shouldn’t use dynamic tables, I hope the article will save you several months of work.
1. Limited available capabilities
Even when fully committed to using only dynamic tables, we were forced to fall back on procedures for tasks like pivoting/unpivoting, reading from external tables, accessing shared secure views, or handling results from other procedures. If you think these limitations won’t affect you—think again. What I’ve listed is just the tip of the iceberg.
To achieve a “performant and efficient” (we’ll cover this partr later) incremental mode, you need to eliminate non-deterministic functions (e.g., CURRENT_TIME, CURRENT_USER, etc.) as well as any user-defined functions (UDFs) you’ve written. The list of constraints is extensive, and you can read more about them here: Snowflake Dynamic Tables Limitations.
2. Lack of granular control of refresh time
Dynamic tables give you control over data freshness (so-called target lag) — but not as precise as we hoped for. While you specify how fresh the data should be, the actual refresh timing can still vary.
A refresh 5 minutes early? Sure, why not? A delay of 4 minutes? It happens.
Want to compute data that depends on the current day? You’ll need to refresh it explicitly if you want to be sure it updates correctly.
Another key point: if no DML operations have occurred on the underlying tables, the dynamic table won’t refresh at all. That might work in your favor, but it could also be an unwanted behavior—especially since it will refuse to refresh until a change occurs (even a manual refresh).
3. Performance issues
Snowflake gives you two refresh strategies for dynamic tables: Full and Incremental. If you’re unsure which to pick, you can simply leave it unspecified—choosing Auto, which means handing over control to Snowflake.
It is time to choose, Neo
A Full refresh treats every refresh as if it were the first, reprocessing all data—including records from years ago. An Incremental refresh, on the other hand, attempts to update only the records that have changed since the last refresh.
So why would anyone choose a Full refresh? Because Incremental refresh relies on streams (or a similar mechanism) under the hood. If you’re working with multiple tables and an outer join, things can get messy. Handling desynchronized streams is already tricky for humans—I wouldn’t expect a generic algorithm to handle it efficiently either.
In my experience, if your query includes:
- A CTE (Common Table Expression)
- More than one outer join
- Several GROUP BY operations
…you should brace yourself for poor performance. That’s exactly why we switched back to Full refresh.
4. Missing community and Snowflake help
Dynamic tables are a relatively new tool, and finding solid opinions or guides on how to use them wasn’t easy. Another challenge? They’re a unique concept across databases, meaning we’re the explorers—paving the way for future users, one rake-filled step at a time.
After losing hope in the community, you might turn to Snowflake support and… well, flip a coin. Dynamic tables are a learning curve for them too. Sometimes, I received well-reasoned, helpful responses. Other times? Half-correct answers after a long wait.
We learn together. We struggle together.
5. Serverless computing is not supported
Snowflake forces you to specify which warehouse you will use. For people liking the scalability of tasks and pricing of serverless computations moving back to a hardcoded warehouse will be the same as moving to the Stone Age (or the time before inventing Snowflake more or less the same)
How I feel about specifying the warehouse for my DT
6. Benefits of DTs
After all of the things I said… I like dynamic tables. They were not the silver bullet that I wanted but the garlic that I deserved. There are no bad or good programming languages, frameworks, or instruments; there are only poorly chosen ones. Dynamic tables are great for staging when you have several revisions of specific row and you want the newest one. They are great when you have complicated transformations that require one row. I hope the issues that I described will be resolved sooner or later and one day I will publish “Top 5 Reasons why you need dynamic tables in Snowflake”
Subscribe to our newsletter for new articles or contact us with your business problem and wait for your business solution!
