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
- Behind-the-scenes Snowflake determines whether the
UNION ALL
results in "conflictingROW_ID
s, if so, only full refresh is supported - the docs are misleading and should really say: "Dynamic tables support UNION ALL, but not incremental refresh"
- Or is this a bug with dynamic tables?