Snowflake Updated ADA-C02 Exam Questions and Answers by peggy

Snowflake administrators can capture virtual warehouse metadata by combining SHOW WAREHOUSES with RESULT_SCAN and OBJECT_CONSTRUCT.

The SHOW WAREHOUSES command returns information about accessible warehouses, including names, sizes, operational states, configuration settings, and other metadata.

RESULT_SCAN(LAST_QUERY_ID()) exposes the result of the preceding SHOW statement as a queryable result set.

The OBJECT_CONSTRUCT(*) function transforms each result row into a structured OBJECT containing column names as keys and corresponding values as attributes. Snowflake can store this object directly in a VARIANT column.

Option C correctly produces two values per inserted row: CURRENT_TIMESTAMP() for the snapshot time and OBJECT_CONSTRUCT(*) for the configuration data.

Option A does not provide the required configuration object and uses LAST_QUERY_ID(1), which references the first session query rather than necessarily the preceding SHOW statement.

Option B returns multiple metadata columns instead of constructing the single VARIANT value required by VWH_META.

Option D has an invalid RESULT_SCAN expression and likewise fails to construct the configuration object.

Technical qualification: OBJECT_CONSTRUCT omits attributes whose values are SQL NULL. Thus, the solution records available non-null metadata rather than necessarily preserving every possible configuration attribute. The result also depends on which warehouses the executing role can access.

Official Snowflake references: SHOW WAREHOUSES; RESULT_SCAN; LAST_QUERY_ID; OBJECT_CONSTRUCT.

Snowflake provides the MINS_TO_BYPASS_MFA user property to support temporary authentication recovery when an enrolled user cannot complete multi-factor authentication.

This property specifies the number of minutes during which the affected user may authenticate without providing the normally required second authentication factor.

The requested duration is 24 hours, equivalent to 1,440 minutes:

ALTER USER user1

SET MINS_TO_BYPASS_MFA = 1440;

An authorized administrator can execute this command to establish a temporary MFA bypass for user1. The bypass expires after the configured duration, allowing normal MFA enforcement to resume without permanently removing the user's enrollment.

Option B is incorrect because DISABLE_MFA is a Boolean user property, not a numeric duration setting. Assigning 1440 does not express the intended temporary bypass. Options C and D reference properties that are not supported by Snowflake's ALTER USER syntax.

The administrator should use the minimum bypass duration necessary to restore access because authentication without a second factor increases exposure to compromised credentials.

Snowflake also supports ALTER USER ... ENROLL MFA to help locked-out users register a replacement authentication method. That approach may be preferable when the original device has been lost.

Official Snowflake references: ALTER USER; CREATE USER; Multi-factor Authentication: Recovering a User Who Is Locked Out.

Snowflake persisted query results enable the query engine to reuse previously computed results when eligible identical queries are executed again.

Option D is the intended optimization because CURRENT_DATE expresses the business requirement using date-level precision rather than calculating an execution-time timestamp and subsequently converting it to a date.

This is more appropriate for reports whose date boundaries change daily rather than with every query execution.

Option A is incorrect because wrapping SQL inside a UDF does not automatically make queries eligible for persisted-result reuse.

Option B is incorrect because persisted query results are not tied to the local data cache of a particular virtual warehouse.

Option C is unnecessary because Snowflake can reuse eligible persisted SELECT results across roles when the executing roles have the required access privileges.

Snowflake also requires compatible query text, unchanged underlying data, an available cached result, and appropriate configuration settings. Persisted results generally expire after 24 hours, although successful reuse can extend their availability.

Critical source-query warning: The exhibit reverses its BETWEEN boundaries: the current date appears before the date seven days earlier. This must be corrected before deployment. Furthermore, CURRENT_DATE is session-time-zone-dependent, and Snowflake does not guarantee cache reuse merely because CURRENT_TIMESTAMP is replaced. The answer is the best of the available choices, not a guaranteed cache optimization.

Official Snowflake references: Using Persisted Query Results; CURRENT_DATE; Context Functions.

Snowflake network policies control inbound authentication requests by evaluating their originating network locations against configured allowed and blocked entries.

In this scenario, two network policies are activated. The FOO_POLICY is assigned at the account level, while BAR_POLICY is assigned specifically to user1.

Snowflake applies user-level network policies in preference to account-level policies. Therefore, BAR_POLICY determines whether user1 can authenticate.

Within BAR_POLICY, the ALLOWED_IP_LIST contains the 3.3.3.0/24 subnet, which includes the attempted source address 3.3.3.10.

However, the BLOCKED_IP_LIST explicitly identifies 3.3.3.10. Snowflake documents that blocked entries take precedence over allowed entries when an address matches both.

Consequently, user1's authentication request is rejected, making option B correct.

Option A ignores blocked-list precedence. Options C and D incorrectly evaluate the account-level policy rather than the applicable user-level policy.

Current documentation qualification: Although the original SQL remains representative of the legacy IP-list configuration, Snowflake recommends using network rules with ALLOWED_NETWORK_RULE_LIST and BLOCKED_NETWORK_RULE_LIST for newly configured network policies. This improves administrative consistency and supports additional network identifier types.

Official Snowflake references: Controlling Network Traffic with Network Policies; ALTER NETWORK POLICY.