A developer expects Snowflake to refresh a dynamic table incrementally, but Snowflake selects full refresh mode instead.
Which query operator can prevent incremental refresh and cause Snowflake to use full refresh mode when REFRESH_MODE = AUTO?
Correct Answer: C
Snowflake supports incremental refresh for many common query constructs, including supported uses of DISTINCT, GROUP BY, and UNION ALL.
General lateral joins are not supported for incremental dynamic-table refresh. A documented exception exists for supported uses of LATERAL FLATTEN.
When a dynamic table is created with REFRESH_MODE = AUTO, Snowflake analyzes the defining query.
When the query cannot be refreshed incrementally, Snowflake can choose full refresh mode.
When REFRESH_MODE = INCREMENTAL is explicitly requested and the query contains an unsupported construct, dynamic-table creation can fail instead of silently changing the requested refresh mode.
References:
Snowflake Documentation - Supported queries for incremental refresh: https://docs.snowflake.com/en/user- guide/dynamic-tables-supported-queries Snowflake Documentation - Dynamic-table refresh modes: https://docs.snowflake.com/en/user-guide
/dynamic-tables-refresh
==