Snowflake Dynamic Table support for`UNION ALL`

Question

While trying to modify an existing table to be a Dynamic table, I noticed that it was not incrementally refreshing like I had hoped. refresh_mode_reason is given as below.

Change tracking is not supported on queries with UNION ALLs or outer joins that would produce conflicting ROW_IDs.

However, within the table in Understanding dynamic table refresh: Types of queries that support incremental refreshes it says:

Dynamic tables support UNION ALL.

theories

1. Behind-the-scenes Snowflake determines whether the UNION ALL results in "conflicting ROW_IDs, if so, only full refresh is supported
2. the docs are misleading and should really say: "Dynamic tables support UNION ALL, but not incremental refresh"
3. Or is this a bug with dynamic tables?

related questions

Dynamic tables with UNION -- change tracking not enabled

Answer 1

I have found the root cause. There might be a discrepancy between your understanding of the query and its actual structure. So you really need to pay attention to the underlying structure of the query that is being executed.

Please carefully review your dynamic table query for any hidden joins or UNION ALL statements. These can be present even if you don't explicitly write them.

Some common scenarios:

• Views used within the query might contain joins or UNION ALLs.
• Subqueries might unknowingly introduce these operations.

Answer 2

It indeed looks like a bug or bad documentation. If you read up the documentation you shared, this is what you can find:

It is indeed misleading and even counterintuitive: Why would UNION ALL be supported if using that operation does NOT guarantee row uniqueness? After all, that is what incremental refreshes requires.

Answer 3

It looks like incremental updates do not support UNION, but they do support UNION ALL as mentioned in the documentation that you added. The difference is that UNION doesn't allow for duplicates.