Snowflake ADA-C01 Exam Questions Instant Access

Question 1

What role or roles should be used to properly create the object required to setup OAuth 2.0 integration?

AAny role with GRANT USAGE on SECURITY INTEGRATION

BACCOUNTADMIN and SYSADMIN

CACCOUNTADMIN and SECURITYADMIN

DACCOUNTADMIN only

Answer : D

According to the Using OAuth 2.0 with Snowflake - Blog, only the ACCOUNTADMIN role can create and manage integrations, so an administrator must assume that role when creating a security integration for OAuth. The other roles do not have the necessary privileges to create the object required to setup OAuth 2.0 integration.

Question 2

For Snowflake network policies, what will occur when the account_level and user_level network policies are both defined?

AThe account_level policy will override the user_level policy.

BThe user_level policy will override the account_level policy.

CThe user_level network policies will not be supported.

DA network policy error will be generated with no definitions provided.

Answer : B

According to the Network Policies documentation, a network policy can be applied to an account, a security integration, or a user. If there are network policies applied to more than one of these, the most specific network policy overrides more general network policies. The following summarizes the order of precedence:

* Account: Network policies applied to an account are the most general network policies. They are overridden by network policies applied to a security integration or user.

* Security Integration: Network policies applied to a security integration override network policies applied to the account, but are overridden by a network policy applied to a user.

* User: Network policies applied to a user are the most specific network policies. They override both accounts and security integrations.

Therefore, if both the account_level and user_level network policies are defined, the user_level policy will take effect and the account_level policy will be ignored. The other options are incorrect because:

* The account_level policy will not override the user_level policy, as explained above.

* The user_level network policies will be supported, as they are part of the network policy feature.

* A network policy error will not be generated, as there is no conflict between the account_level and user_level network policies.

Question 3

DatabaseA has a single schema called Schema1. This schema contains many tables and views. The ANALYST role has privileges to select from all objects in

DatabaseA. Schema1. The SYSADMIN role clones DatabaseA to DatabaseA_clone.

What privileges does the ANALYST role have on tables and views in DatabaseA_clone? (Select TWO).

AUSAGE on the schema DatabaseA clone

BUSAGE on the database DatabaseA_clone. Schemal

CSELECT on all tables, and only non-secure views in DatabaseA_clone. Schemal

DSELECT on all tables, and only secure views in DatabaseA_clone. Schemal

ESELECT on all tables and views in DatabaseA_clone. Schema1

Answer : C, E

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.

Question 4

A resource monitor named MONTHLY_FINANCE_LIMIT has been created and applied to two virtual warehouses (fin_wh1 and fin_wh2) using the following SQL:

Given that the combined total of credits consumed by fin_wh1 and fin_wh2 (including cloud services) has reached 800 credits and both warehouses are suspended, what should the ACCOUNTADMIN execute to allow both warehouses to be resumed? (Select TWO).

AALTER WAREHOUSE fin_wh1 RESUME;

BALTER WAREHOUSE fin_wh2 RESUME;

CALTER WAREHOUSE fin_wh1 UNSET RESOURCE_MONITOR MONTHLY_FINANCE_LIMIT;

DALTER WAREHOUSE fin_wh2 UNSET RESOURCE_MONITOR MONTHLY_FINANCE_LIMIT;

EALTER RESOURCE MONITOR MONTHLY_FINANCE_LIMIT SET CREDIT_QUOTA = 1500;

FALTER RESOURCE MONITOR MONTHLY_FINANCE_LIMIT RESET;

GALTER WAREHOUSE fin_wh1 UNSET RESOURCE_MONITORS;

Answer : E, F

Scenario:

Resource Monitor MONTHLY_FINANCE_LIMIT has a credit quota of 1000.

800 credits have been used and warehouses are already suspended.

According to monitor configuration:

At 80%, warehouses are suspended.

At 100%, warehouses would be suspended immediately.

Warehouses cannot resume until the monitor is reset or the quota is increased.

E. SET CREDIT_QUOTA = 1500

Increases the monthly credit limit to 1500.

Since current usage is 800 < 1500, this puts usage below 80%.

This allows resumption of warehouses.

F. RESET

sql

CopyEdit

ALTER RESOURCE MONITOR MONTHLY_FINANCE_LIMIT RESET;

Resets usage to zero for the current period.

Allows warehouses to resume immediately --- same effect as a fresh cycle.

Why Other Options Are Incorrect:

A . / B. ALTER WAREHOUSE ... RESUME

Won't work while the resource monitor is actively suspending the warehouses due to limits.

C . / D. UNSET RESOURCE_MONITOR

You can't remove a resource monitor from a warehouse while it is currently suspended by that same monitor.

You must first reset or adjust the monitor.

G . UNSET RESOURCE_MONITORS

Invalid syntax --- there's no RESOURCE_MONITORS plural keyword.

SnowPro Administrator Reference:

Resource Monitors Overview

ALTER RESOURCE MONITOR

Best Practices for Controlling Warehouse Credit Usage

Question 5

A Snowflake account is configured with SCIM provisioning for user accounts and has bi-directional synchronization for user identities. An Administrator with access to SECURITYADMIN uses the Snowflake UI to create a user by issuing the following commands:

use role USERADMIN;

create or replace role DEVELOPER_ROLE;

create user PTORRES PASSWORD = 'hello world!' MUST_CHANGE_PASSWORD = FALSE

default_role = DEVELOPER_ROLE;

The new user named PTORRES successfully logs in, but sees a default role of PUBLIC in the web UI. When attempted, the following command fails:

use DEVELOPER_ROLE;

Why does this command fail?

AThe DEVELOPER_ROLE needs to be granted to SYSADMIN before user PTORRES will be able to use the role.

BThe new role can only take effect after USERADMIN has logged out.

CUSERADMIN needs to explicitly grant the DEVELOPER_ROLE to the new USER.

DThe new role will only take effect once the identity provider has synchronized by way of SCIM with the Snowflake account.

Answer : C

According to the Snowflake documentation1, creating a user with a default role does not automatically grant that role to the user. The user must be explicitly granted the role by the role owner or a higher-level role. Therefore, the USERADMIN role, which created the DEVELOPER_ROLE, needs to explicitly grant the DEVELOPER_ROLE to the new user PTORRES using the GRANT ROLE command. Otherwise, the user PTORRES will not be able to use the DEVELOPER_ROLE and will see the default role of PUBLIC in the web UI. Option A is incorrect because the DEVELOPER_ROLE does not need to be granted to SYSADMIN before user PTORRES can use the role. Option B is incorrect because the new role can take effect immediately after it is created and granted to the user, and does not depend on the USERADMIN role logging out. Option D is incorrect because the new role will not be affected by the identity provider synchronization, as it is created and managed in Snowflake.

Question 6

What are benefits of using Snowflake organizations? (Select TWO).

AAdministrators can change Snowflake account editions on-demand based on need.

BAdministrators can monitor and understand usage across all accounts in the organization.

CAdministrators can simplify data movement across all accounts within the organization.

DUser administration is simplified across all accounts within the organization.

EAdministrators have the ability to create accounts in any available cloud provider or region.

Answer : B, E

According to the Snowflake documentation1, organizations are a feature that allows linking the accounts owned by a business entity, simplifying account management and billing, replication and failover, data sharing, and other account administration tasks. Some of the benefits of using organizations are:

* Administrators can monitor and understand usage across all accounts in the organization using the ORGANIZATION_USAGE schema, which provides historical usage data for all accounts in the organization via views in a shared database named SNOWFLAKE2. This can help to optimize costs and performance across the organization.

* Administrators have the ability to create accounts in any available cloud provider or region using the CREATE ACCOUNT command, which allows specifying the cloud platform and region for the new account3. This can help to meet the business needs and compliance requirements of the organization.

Option A is incorrect because administrators cannot change Snowflake account editions on-demand based on need, but rather have to contact Snowflake Support to request an edition change4. Option C is incorrect because administrators cannot simplify data movement across all accounts within the organization, but rather have to enable account database replication for both the source and target accounts, and use the ALTER DATABASE ... ENABLE REPLICATION TO ACCOUNTS command to promote a local database to serve as the primary database and enable replication to the target accounts5. Option D is incorrect because user administration is not simplified across all accounts within the organization, but rather requires creating and managing users, roles, and privileges for each account separately, unless using a federated authentication method such as SSO or SCIM.

Question 7

MY_TABLE is a table that has not been updated or modified for several days. On 01 January 2021 at 07:01, a user executed a query to update this table. The query ID is

'8e5d0ca9-005e-44e6-b858-a8f5b37c5726'. It is now 07:30 on the same day.

Which queries will allow the user to view the historical data that was in the table before this query was executed? (Select THREE).

ASELECT * FROM my table WITH TIME_TRAVEL (OFFSET => -60*30);

BSELECT * FROM my_table AT (TIMESTAMP => '2021-01-01 07:00:00' :: timestamp);

CSELECT * FROM TIME_TRAVEL ('MY_TABLE', 2021-01-01 07:00:00);

DSELECT * FROM my table PRIOR TO STATEMENT '8e5d0ca9-005e-44e6-b858-a8f5b37c5726';

ESELECT * FROM my_table AT (OFFSET => -60*30);

FSELECT * FROM my_table BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');

Answer : B, D, F

According to the AT | BEFORE documentation, the AT or BEFORE clause is used for Snowflake Time Travel, which allows you to query historical data from a table based on a specific point in the past. The clause can use one of the following parameters to pinpoint the exact historical data you wish to access:

* TIMESTAMP: Specifies an exact date and time to use for Time Travel.

* OFFSET: Specifies the difference in seconds from the current time to use for Time Travel.

* STATEMENT: Specifies the query ID of a statement to use as the reference point for Time Travel.

Therefore, the queries that will allow the user to view the historical data that was in the table before the query was executed are:

* B. SELECT * FROM my_table AT (TIMESTAMP => '2021-01-01 07:00:00' :: timestamp); This query uses the TIMESTAMP parameter to specify a point in time that is before the query execution time of 07:01.

* D. SELECT * FROM my table PRIOR TO STATEMENT '8e5d0ca9-005e-44e6-b858-a8f5b37c5726'; This query uses the PRIOR TO STATEMENT keyword and the STATEMENT parameter to specify a point in time that is immediately preceding the query execution time of 07:01.

* F. SELECT * FROM my_table BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726'); This query uses the BEFORE keyword and the STATEMENT parameter to specify a point in time that is immediately preceding the query execution time of 07:01.

The other queries are incorrect because:

* A. SELECT * FROM my table WITH TIME_TRAVEL (OFFSET => -60*30); This query uses the OFFSET parameter to specify a point in time that is 30 minutes before the current time, which is 07:30. This is after the query execution time of 07:01, so it will not show the historical data before the query was executed.

* C. SELECT * FROM TIME_TRAVEL ('MY_TABLE', 2021-01-01 07:00:00); This query is not valid syntax for Time Travel. The TIME_TRAVEL function does not exist in Snowflake. The correct syntax is to use the AT or BEFORE clause after the table name in the FROM clause.

* E. SELECT * FROM my_table AT (OFFSET => -60*30); This query uses the AT keyword and the OFFSET parameter to specify a point in time that is 30 minutes before the current time, which is 07:30. This is equal to the query execution time of 07:01, so it will not show the historical data before the query was executed. The AT keyword specifies that the request is inclusive of any changes made by a statement or transaction with timestamp equal to the specified parameter. To exclude the changes made by the query, the BEFORE keyword should be used instead.

Snowflake SnowPro Advanced: Administrator Certification ADA-C01 Exam Questions