COF-C02 Question 124

Community Discussion

Written by other learners. Comments can disagree with the marked answer — check the official documentation when in doubt.

• Chose C1

  Answer: C. LATERAL JOIN. Snowflake’s documentation is explicit here: lateral joins are not supported with incremental refresh, and the one exception is using LATERAL with FLATTEN(). When a dynamic table’s definition includes a construct that isn’t supported for incremental refresh, Snowflake chooses a full refresh instead, which reprocesses all the data on every refresh. sql -- Supported incrementally: LATERAL FLATTEN SELECT p.id, f.value FROM products p, LATERAL FLATTEN(input => p.attributes) f; -- Not supported incrementally: lateral join to a subquery SELECT o.id, x.total FROM orders o, LATERAL (SELECT SUM(amount) AS total FROM items i WHERE i.order_id = o.id) x; The other options are wrong because they’re all supported for incremental refresh: A. DISTINCT is supported. The docs only note that it costs resources on every refresh, because duplicates have to be checked each time, so they advise against using it more than necessary. B. GROUP BY is supported. D. UNION ALL is supported. Snowflake processes it incrementally by combining the changes from each side of the union. Once again, the “Most Voted” answer on that site (A) is wrong. DISTINCT makes incremental refresh more expensive, but it doesn’t force a full refresh. I’d keep checking the community votes against the documentation on dynamic table questions in particular, since dynamic table support changes often. For example, Snowflake added incremental support for UNION in August 2025.

• Chose A1

  When DISTINCT is used, Snowflake cannot track incremental changes because removing duplicates requires seeing the full dataset to know whether a value is truly distinct after a change.