Snowflake SnowPro Advanced Administrator ADA-C02 Sample Questions:

1. A company has many users in the role ANALYST who routinely query Snowflake through a reporting tool. The Administrator has noticed that the ANALYST users keep two small clusters busy all of the time, and occasionally they need three or four clusters of that size.
Based on this scenario, how should the Administrator set up a virtual warehouse to MOST efficiently support this group of users?

A) Create a multi-cluster warehouse with MIN_CLUSTERS set to 1. Give MANAGE privileges to the ANALYST role so this group can start and stop the warehouse, and increase the number of clusters as needed.
B) Create four virtual warehouses (sized Small through XL) and set them to auto-suspend and auto-resume. Have users in the ANALYST role select the appropriate warehouse based on how many queries are being run.
C) Create a standard X-Large warehouse, which is equivalent to four small clusters. Set the warehouse to auto-resume and auto-suspend, and give USAGE privileges to the ANALYST role.
D) Create a multi-cluster warehouse with MIN_CLUSTERS set to 2. Set the warehouse to auto-resume and auto-suspend, and give USAGE privileges to the ANALYST role. Allow the warehouse to auto-scale.

2. A virtual warehouse report_wh is configured with AUTO_RESUME=TRUE and AUTO_SUSPEND=300. A user has been granted the role accountant.
An application with the accountant role should use this warehouse to run financial reports, and should keep track of compute credits used by the warehouse.
What minimal privileges on the warehouse should be granted to the role to meet the requirements for the application? (Choose two.)

A) MONITOR
B) USAGE
C) OPERATE
D) MODIFY
E) OWNERSHIP

3. An Administrator needs to configure a virtual warehouse in Snowflake for a new use case with these details:
1. There will be 50-100 concurrent users running reports
2. Minimize costs based on the number of active users at a given time
3. The virtual warehouse should suspend and resume automatically based on user activity
Which configuration will meet these requirements?

A) create or replace warehouse reporting_wh
warehouse_size = xxxlarge
max_cluster_count = 10
scaling_policy = standard
auto_suspend = 600
auto_resume = false;
B) create or replace warehouse reporting_wh
warehouse_size = xxxlarge
auto_suspend = 60
auto_resume = true;
C) create or replace warehouse reporting_wh
warehouse_size = medium
max_cluster_count = 10
scaling_policy = standard
auto_suspend = 600
auto_resume = true;
D) create or replace warehouse reporting_wh
warehouse_size = medium
auto_suspend = 600
auto_resume = true;

4. What are characteristics of Dynamic Data Masking? (Choose two.)

A) A masking policy that is currently set on a table can be dropped.
B) A single masking policy can be applied to columns in different tables.
C) The role that creates the masking policy will always see unmasked data in query results.
D) A single masking policy can be applied to columns with different data types.
E) A masking policy can be applied to the VALUE column of an external table.

5. What is a characteristic of Snowflake's transaction locking and concurrency modeling?

A) A deadlock cannot occur in Snowflake, since concurrently executed queries and DML statements do not block one another.
B) Transaction locking in Snowflake is enforced exclusively at the row and table levels.
C) If two queries are concurrently executed against the same table, one of the two queries will be blocked until the other query completes.
D) Queries executed within a given transaction see that transaction's uncommitted changes.

Solutions: