Snowflake SnowPro Advanced: Administrator Certification ADA-C01 Exam Questions

Page: 1 / 14
Total 78 questions
Question 1

A large international company with many operating regions requires data to be shared bi-directionally among all offices (head office to regional offices and regional offices among themselves). This company is a Snowflake account holder with European operations deployed in Microsoft Azure (single region) while North American regional offices are using AWS (single region) as their deployment cloud. This setup is required to comply with Personal Identifiable Information (PII) regulations in some of the European countries. The corporate head office is in Europe.

How can this data be shared bi-directionally, while MINIMIZING costs?



Answer : D

According to the Snowflake documentation1, data sharing is a feature that allows sharing selected objects in a database in one account with other accounts in the same organization, without copying or transferring any data. Data sharing is supported across regions and across cloud platforms, but it requires enabling account database replication for both the source and target accounts2. Data replication is a feature that allows replicating objects from a source account to one or more target accounts in the same organization, providing read-only access for the replicated objects. Data replication is also supported across regions and across cloud platforms, but it incurs additional storage costs for the replicated data2. Therefore, the best way to share data bi-directionally among all offices, while minimizing costs, is to use data sharing among offices in the same region, which does not require replication or additional storage, and use replication among offices across the continents, which provides near real-time access to the shared data. Option A is incorrect because using data replication everywhere would increase the costs associated with additional storage and compute resources for the replicated data. Option B is incorrect because using the PUT command to move files to an Amazon S3 bucket and Azure Blobs, and using an external file management application to move files within the corporate VPC, would not leverage the benefits of Snowflake's data sharing and replication features, and would also incur additional costs and complexity for data transfer and synchronization. Option C is incorrect because moving all the Snowflake accounts to a single region would violate the PII regulations in some of the European countries, and would also incur additional costs and complexity for data migration and consolidation.


Question 2

Which function is the role SECURITYADMIN responsible for that is not granted to role USERADMIN?



Answer : B

According to the Snowflake documentation1, the SECURITYADMIN role is responsible for managing all grants on objects in the account, including system grants. The USERADMIN role can only create and manage users and roles, but not grant privileges on other objects. Therefore, the function that is unique to the SECURITYADMIN role is to manage system grants. Option A is incorrect because both roles can reset a user's password. Option C is incorrect because both roles can create new users. Option D is incorrect because both roles can create new roles.


Question 3

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?



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 4

What information is required from the Identity Provider (IdP) to enable federated authentication in Snowflake? (Select TWO).



Answer : B, D

To enable federated authentication (aka SSO via SAML 2.0) in Snowflake, the integration with an Identity Provider (IdP) must be configured. This setup involves configuring external authentication via SAML, and Snowflake needs specific information from the IdP.

Required Information from IdP:

URL Endpoint for SAML Requests (B)

This is often referred to as the SSO URL or SAML 2.0 Endpoint (HTTP).

It's the URL that Snowflake redirects users to for authentication.

In Snowflake's SAML configuration, this is required as the SAML2_ISSUER or SAML2_SSO_URL.

Authentication Certificate (D)

This is the X.509 certificate issued by the IdP.

It's used by Snowflake to validate the digital signature of the SAML assertions sent by the IdP.

It ensures that the SAML response is authentic and not tampered with.

Why Other Options Are Incorrect:

A . IdP account details

Not needed. Snowflake doesn't require credentials or internal details from the IdP. It relies on assertions sent via SAML, not stored accounts.

C . SAML response format

Snowflake adheres to SAML 2.0 standard, and expects a compliant format. There's no need to specify format explicitly --- it's part of the standard protocol.

E . IdP encryption key

Not required by Snowflake. Snowflake verifies SAML assertions via signature validation, not encryption using the IdP's private key.

SnowPro Administrator Reference:

Snowflake Documentation --- Federated Authentication Setup

https://docs.snowflake.com/en/user-guide/security-fed-auth-use

https://docs.snowflake.com/en/user-guide/security-fed-auth-config

Required IdP Metadata for Snowflake SAML Configuration:

SAML2_SSO_URL: SAML 2.0 POST binding endpoint

SAML2_X509_CERT: Public cert used to validate IdP signatures


Question 5

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).



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.


Question 6

An organization's sales team leverages this Snowflake query a few times a day:

SELECT CUSTOMER ID, CUSTOMER_NAME, ADDRESS, PHONE NO

FROM CUSTOMERS

WHERE LAST UPDATED BETWEEN TO_DATE (CURRENT_TIMESTAMP) AND (TO_DATE (CURRENT_TIMESTAMP) -7);

What can the Snowflake Administrator do to optimize the use of persisted query results whenever possible?



Answer : D

According to the web search results from my predefined tool search_web, one of the factors that affects the reuse of persisted query results is the exact match of the query syntax1. If the query contains functions that return different values for successive runs, such as CURRENT_TIMESTAMP, then the query will not match the previous query and will not benefit from the cache. To avoid this, the query should use functions that return consistent values for the same day, such as CURRENT_DATE, which returns the current date without the time component2. Option A is incorrect because wrapping the query in a UDF does not guarantee the syntax match, as the UDF may also contain dynamic functions. Option B is incorrect because the virtual warehouse does not affect the persisted query results, which are stored at the account level1. Option C is incorrect because the security role does not affect the persisted query results, as long as the role has the necessary privileges to access the tables and views used in the query1.

1: Using Persisted Query Results | Snowflake Documentation 2: Date and Time Functions | Snowflake Documentation


Question 7

A team of developers created a new schema for a new project. The developers are assigned the role DEV_TEAM which was set up using the following statements:

USE ROLE SECURITYADMIN;

CREATE ROLE DEV TEAM;

GRANT USAGE, CREATE SCHEMA ON DATABASE DEV_DB01 TO ROLE DEV_TEAM;

GRANT USAGE ON WAREHOUSE DEV_WH TO ROLE DEV_TEAM;

Each team member's access is set up using the following statements:

USE ROLE SECURITYADMIN;

CREATE ROLE JDOE_PROFILE;

CREATE USER JDOE LOGIN NAME = 'JDOE' DEFAULT_ROLE='JDOE_PROFILE';

GRANT ROLE JDOE_PROFILE TO USER JDOE;

GRANT ROLE DEV_TEAM TO ROLE JDOE_PROFILE;

New tables created by any of the developers are not accessible by the team as a whole.

How can an Administrator address this problem?



Answer : C

According to the Snowflake documentation1, future grants are a way to automatically grant privileges on future objects of a specific type that are created in a database or schema. By setting up future grants on the newly-created schemas, the administrator can ensure that any tables created by the developers in those schemas will be accessible by the DEV_TEAM role, without having to grant privileges on each table individually. Option A is incorrect because assigning ownership privilege to DEV_TEAM on the newly-created schema does not grant privileges on the tables in the schema, only on the schema itself. Option B is incorrect because assigning usage privilege on the virtual warehouse DEV_WH to the role JDOE_PROFILE does not affect the access to the tables in the schemas, only the ability to use the warehouse. Option D is incorrect because setting up the new schema as a managed-access schema does not grant privileges on the tables in the schema, but rather requires explicit grants for each table.


Page:    1 / 14   
Total 78 questions