Free Practice Questions for Snowflake ADA-C01 Exam

According to the Snowflake documentation, when a database or schema is cloned, the clone inherits all granted privileges on the clones of all child objects contained in the source object, such as tables and views. However, the clone of the container itself does not inherit the privileges granted on the source container. Therefore, the ANALYST role will have SELECT privilege on all tables and views in DatabaseA_clone.Schema1, but not USAGE privilege on the database or schema. The type of view (secure or non-secure) does not affect the cloning of privileges.

According to the Snowflake documentation1, breaking changes are changes that affect the schema or structure of the shared data, such as dropping or renaming a column or a table. These changes may cause errors or unexpected results for the consumers who query the shared data. Deleting data from a table, unpublishing the data listing, or adding region availability to the listing are not breaking changes, as they do not alter the schema or structure of the shared data.

1: Managing Data Listings in Snowflake Data Marketplace | Snowflake Documentation

The EXPLAIN command returns the logical execution plan for a query, which shows the upper bound estimates for the number of partitions and bytes that might be scanned by the query1. However, these estimates do not account for the runtime optimizations that Snowflake performs to improve the query performance and reduce the resource consumption2. One of these optimizations is join pruning, which eliminates unnecessary partitions from the join inputs based on the join predicates2. This can result in fewer partitions and bytes scanned than the estimates from the EXPLAIN output3. Therefore, the actual partitions scanned value in the Query Profile can be lower than the partitionsAssigned value in the EXPLAIN output4.

The inclusion of the CURRENT_TIMESTAMP() function in the second query prevents it from re-using the first query’s persisted result cache because this function makes each execution unique due to the constantly changing timestamp. According to the Snowflake documentation, “The query does not include non-reusable functions, which return different results for successive runs of the same query. UUID_STRING, RANDOM, and RANDSTR are good examples of non-reusable functions.” The CURRENT_TIMESTAMP() function is another example of a non-reusable function, as it returns the current date and time at the start of query execution, which varies for each run. Therefore, the second query is not identical to the first query, and the result cache is not reused. The other options are either incorrect or irrelevant to the question. Option B is incorrect, as the CURRENT_DATE() function is a reusable function, as it returns the same value for all queries executed within the same day. Option C is irrelevant, as the virtual warehouse used to execute the query does not affect the result cache reuse. Option D is also irrelevant, as the role used to execute the query does not affect the result cache reuse, as long as the role has the necessary access privileges for all the tables used in the query.

Network policies are used to restrict access to the Snowflake service and internal stages based on user IP address1. To create network policies, a role must have the global permission of CREATE NETWORK POLICY2. By default, the system-defined roles of SECURITYADMIN and ACCOUNTADMIN have this permission3. However, any other role can be granted this permission by an administrator4. Therefore, the answer is B, C, and E. The other options are incorrect because SYSADMIN and ORGADMIN do not have the CREATE NETWORK POLICY permission by default3, and network policies are not tied to specific databases5.

According to the Snowflake documentation1, the ORGADMIN role is a special system role that is responsible for managing operations at the organization level, such as creating and viewingaccounts, enabling database replication, and setting global account parameters. The ACCOUNTADMIN role is a system role that is responsible for managing operations at the account level, such as creating and managing users, roles, warehouses, databases, and shares. Therefore, the commands that can be performed by the ORGADMIN role but not the ACCOUNTADMIN role are:

•SHOW ORGANIZATION ACCOUNTS: This command lists all the accounts in the organization and their properties, such as region, edition, and status2. The ACCOUNTADMIN role can only show the current account and its properties using the SHOW ACCOUNTS command3.

•SELECT SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER: This function sets a global account parameter for an account in the organization, such as enabling account database replication4. The ACCOUNTADMIN role can only set local account parameters using the ALTER ACCOUNT command.

Option A is incorrect because the SHOW REGIONS command can be executed by any role, not just the ORGADMIN role. Option B is incorrect because the SHOW USERS command can be executed by the ACCOUNTADMIN role, as well as any role that has been granted the MONITOR privilege on the account. Option D is incorrect because the GRANT ROLE ORGADMIN TO USER command can be executed by the ACCOUNTADMIN role, as well as any role that has been granted the ORGADMIN role1.

The SIMULATED_DATA_SHARING_CONSUMER session parameter allows a data provider to test the integrity of secure views based on the account that is accessing that view2. By setting this parameter to the name of the consumer account, the data provider can query the secure view and see the results that a user in the consumer account will see2. This helps to ensure that sensitive data in a shared database is not exposed to unauthorized users1. The other options are not valid session parameters in Snowflake3

Dropping role A does not affect the SELECT privileges granted to roles B and C on the table DB1.S1.T1. According to the Snowflake documentation, dropping a role revokes all privileges granted to the role, but does not revoke any privileges granted by the role. Therefore, the OWNERSHIP privilege on the table DB1.S1.T1 will be revoked from role A, but the SELECT privileges granted by role A to role B and by role B to role C will remain. The GRANTED_BY column will still show the original grantor of the privilege, not the ACCOUNTADMIN role.

According to the Multi-cluster Warehouses documentation, auto-suspend is a feature that allows a warehouse to automatically suspend itself after a specified period of inactivity. For a multi-cluster warehouse, auto-suspend applies to the entire warehouse, not to individual clusters. Therefore, auto-suspend occurs when the minimum number of clusters is running and there is no activity for the specified period of time. The other options are incorrect because:

•A. Auto-suspend does not occur when there has been no activity on any cluster for the specified period of time. This would imply that each cluster has its own auto-suspend timer, which is not the case. The warehouse has a single auto-suspend timer that is reset by any activity on any cluster.

•B. Auto-suspend does not occur after a specified period of time when an additional cluster has started on the maximum number of clusters specified for a warehouse. This would imply that the auto-suspend timer is affected by the number of clusters running, which is not the case. The auto-suspend timer is only affected by the activity on the warehouse, regardless of the number of clusters running.

•D. Auto-suspend does apply for multi-cluster warehouses, as explained above. It is a feature that can be enabled or disabled for any warehouse, regardless of the number of clusters.