SnowPro Advanced: Data Engineer free question 7 / 10 · Domain 4: Data Governance
You attached a row access policy to the table EVENTS. Before attaching it, SELECT COUNT() FROM EVENTS returned in a few milliseconds, and you were considering the search optimization service for queries heavy on point lookups. Which statement is correct after the policy is attached?
- COUNT(*) answers instantly using statistics, and search optimization cannot be used on tables with policies
- COUNT(*) counts without using statistics, and search optimization can be used on tables with policies
- COUNT(*) counts without using statistics, and search optimization cannot be used on tables with policies
- COUNT(*) answers instantly using statistics, and search optimization can be used on tables with policies
Show the answer and explanation
Answer: B
Why this is correct
Snowflake keeps statistics for tables and columns and answers simple queries such as COUNT() or MAX in milliseconds. But on a table with a row access policy, the query must first decide the subset of rows it is allowed to read, so these statistics and optimizations generally cannot be used and the query may take longer than expected. The value returned is also based only on the rows permitted to that role. On the other hand, the documentation says the search optimization service can improve query performance on tables that use masking policies or row access policies.
Why the other options are wrong
"COUNT() counts without using statistics, and search optimization cannot be used on tables with policies" … It is right that statistics are not used, but the search optimization service can be used on tables with policies. The documentation states explicitly that it can improve performance on tables that use masking policies or row access policies.
"COUNT() answers instantly using statistics, and search optimization can be used on tables with policies" … The explanation of search optimization is right, but on a table with a row access policy the count has to be made after deciding the permitted rows, so an instant answer from statistics is generally not used.
"COUNT() answers instantly using statistics, and search optimization cannot be used on tables with policies" … Both are reversed. Instant answers from statistics are no longer used, and the search optimization service can improve performance even on tables with policies.
Common pitfall
If counts become slow after attaching a policy, or the counts differ by role, that is by design and not a failure. The count returned is not the true value without the policy but the number of permitted rows. For processing that reconciles ingestion counts, run it with a role that is not narrowed by the policy, or review the premise of the reconciliation. Reducing subqueries and joins in the policy body and keeping the WHERE conditions simple is also the documentation's performance guidance.