Black Friday Special Limited Time 70% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: 70special

Snowflake ADA-C01 SnowPro Advanced Administrator Exam Practice Test

Page: 1 / 7
Total 72 questions

SnowPro Advanced Administrator Questions and Answers

Testing Engine

  • Product Type: Testing Engine
$37.5  $124.99

PDF Study Guide

  • Product Type: PDF Study Guide
$33  $109.99
Question 1

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

Options:

A.

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

B.

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

C.

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

D.

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

E.

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

F.

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

Question 2

A retailer uses a TRANSACTIONS table (100M rows, 1.2 TB) that has been clustered by the STORE_ID column (varchar(50)). The vast majority of analyses on this table are

grouped by STORE_ID to look at store performance.

There are 1000 stores operated by the retailer but most sales come from only 20 stores. The Administrator notes that most queries are currently experiencing poor pruning,

with large amounts of bytes processed by even simple queries.

Why is this occurring?

Options:

A.

The STORE_ID should be numeric.

B.

The table is not big enough to take advantage of the clustering key.

C.

Sales across stores are not uniformly distributed.

D.

The cardinality of the stores to transaction count ratio is too low to use the STORE_ID as a clustering key.

Question 3

A Snowflake customer is experiencing higher costs than anticipated while migrating their data warehouse workloads from on-premises to Snowflake. The migration

workloads have been deployed on a single warehouse and are characterized by a large number of small INSERTs rather than bulk loading of large extracts. That single

warehouse has been configured as a single cluster, 2XL because there are many parallel INSERTs that are scheduled during nightly loads.

How can the Administrator reduce the costs, while minimizing the overall load times, for migrating data warehouse history?

Options:

A.

There should be another 2XL warehouse deployed to handle a portion of the load queries.

B.

The 2XL warehouse should be changed to 4XL to increase the number of threads available for parallel load queries.

C.

The warehouse should be kept as a SMALL or XSMALL and configured as a multi-cluster warehouse to handle the parallel load queries.

D.

The INSERTS should be converted to several tables to avoid contention on large tables that slows down query processing.

Question 4

In general, the monthly billing for database replication is proportional to which variables? (Select TWO).

Options:

A.

The frequency of changes to the primary database as a result of data loading or DML operations

B.

The amount of table data in the primary database that changes as a result of data loading or DML operations

C.

The frequency of the secondary database refreshes from the primary database

D.

The number of times data moves across regions and/or cloud service providers between the primary and secondary database accounts

E.

The number and size of warehouses defined in the primary account

Question 5

What are benefits of creating and maintaining resource monitors in Snowflake? (Select THREE).

Options:

A.

The cost of running a resource monitor is only 10% of a credit, per day of operation.

B.

Multiple resource monitors can be applied to a single virtual warehouse.

C.

Resource monitors add no additional load to virtual warehouse compute.

D.

Multiple triggers can be configured across various virtual warehouse thresholds.

E.

Resource monitor governance is tightly controlled and monitors can only be created by the ACCOUNTADMIN role or users with the CREATE MONITOR privilege.

F.

Resource monitors can be applied to more than one virtual warehouse.

Question 6

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

Options:

A.

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

B.

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

C.

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

D.

User administration is simplified across all accounts within the organization.

E.

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

Question 7

An Administrator has a warehouse which is intended to have a credit quota set for 3000 for each calendar year. The Administrator needs to create a resource monitor that

will perform the following tasks:

1. At 80% usage notify the account Administrators.

2. At 100% usage suspend the warehouse and notify the account Administrators.

3. At 120% stop all running executions, suspend the warehouse, and notify the account Administrators.

Which SQL command will meet these requirements?

Options:

A.

create or replace resource monitor RM1 with credit_quota=3000

start_timestamp = '2022-01-01 00:00 CET'

triggers on 80 percent do notify

on 100 percent do suspend

on 120 percent do suspend_immediate;

alter warehouse WH1 set resource_monitor = RM1;

B.

create or replace resource monitor RM1 with credit_quota=3000

frequency = yearly

start_timestamp = '2022-01-01 00:00 CET'

triggers on 80 percent do notify

on 100 percent do suspend

on 120 percent do suspend_immediate;

alter warehouse WH1 set resource monitor = RM1;

C.

create or replace resource monitor RM1 with credit_quota=3000

start_timestamp = '2022-01-01 00:00 CET'

triggers on 80 percent do notify

on 100 percent do notify and suspend

on 120 percent do notify and suspend_immediate;

alter warehouse WH1 set resource monitor = RM1;

D.

create or replace resource monitor RM1 with credit_quota=3000

frequency = yearly

triggers on 80 percent do notify

on 100 percent do suspend

on 120 percent do suspend_immediate;

alter warehouse WH1 set resource_monitor = RM1;

Question 8

A user has enrolled in Multi-factor Authentication (MFA) for connecting to Snowflake. The user informs the Snowflake Administrator that they lost their mobile phone the previous evening.

Which step should the Administrator take to allow the user to log in to the system, without revoking their MFA enrollment?

Options:

A.

Alter the user and set MINS TO BYPASS MFA to a value that will disable MFA long enough for the user to log in.

B.

Alter the user and set DISABLE_MFA to true, which will suspend the MFA requirement for 24 hours.

C.

Instruct the user to connect to Snowflake using SnowSQL, which does not support MFA authentication.

D.

Instruct the user to append the normal URL with /?mode=mfa_bypass&code= to log on.

Question 9

A company's Snowflake account has multiple roles. Each role should have access only to data that resides in the given role's specific region.

When creating a row access policy, which code snippet below will provide privileges to the role ALL_ACCESS_ROLE to see all rows regardless of region, while the other

roles can only see rows for their own regions?

Options:

A.

create or replace row access policy region policy as (region_value varchar) returns boolean ->

'ALL ACCESS_ROLE' = current_role ()

and exists (

select 1 from entitlement_table

where role = current_role ()

and region = region_value

)

B.

create or replace row access policy region policy as (region_value varchar) returns boolean ->

exists (

select 1 from entitlement_table

where role = current_role ()

and region = region_value

)

C.

create or replace row access policy region policy as (region_value varchar) returns boolean ->

'ALL_ACCESS_ROLE' = current_role ()

or exists (

select 1 from entitlement_table

where role = current_role ()

and region = region_value

)

D.

create or replace row access policy region policy as (region_value varchar) returns boolean ->

'ALL ACCESS ROLE' = current_role ()

)

Question 10

When does auto-suspend occur for a multi-cluster virtual warehouse?

Options:

A.

When there has been no activity on any cluster for the specified period of time.

B.

After a specified period of time when an additional cluster has started on the maximum number of clusters specified for a warehouse.

C.

When the minimum number of clusters is running and there is no activity for the specified period of time.

D.

Auto-suspend does not apply for multi-cluster warehouses.

Question 11

An Administrator wants to delegate the administration of a company's data exchange to users who do not have access to the ACCOUNTADMIN role.

How can this requirement be met?

Options:

A.

Grant imported privileges on data exchange EXCHANGE_NAME to ROLE_NAME;

B.

Grant modify on data exchange EXCHANGE_NAME to ROLE_NAME;

C.

Grant ownership on data exchange EXCHANGE_NAME to ROLE NAME;

D.

Grant usage on data exchange EXCHANGE_NAME to ROLE_NAME;

Question 12

The ACCOUNTADMIN of Account 123 works with Snowflake Support to set up a Data Exchange. After the exchange is populated with listings from other Snowflake accounts,

what roles in Account 123 are allowed to request and get data?

Options:

A.

Only the ACCOUNTADMIN role, and no other roles

B.

Any role with USAGE privilege on the Data Exchange

C.

Any role with IMPORT SHARE and CREATE DATABASE privileges

D.

Any role that the listing provider has designated as authorized

Question 13

An Administrator is evaluating a complex query using the EXPLAIN command. The Globalstats operation indicates 500 partitionsAssigned.

The Administrator then runs the query to completion and opens the Query Profile. They notice that the partitions scanned value is 429.

Why might the actual partitions scanned be lower than the estimate from the EXPLAIN output?

Options:

A.

The EXPLAIN results always include a 10-15% safety factor in order to provide conservative estimates.

B.

The GlobalStats partition assignment includes the micro-partitions that will be assigned for preservation of the query results.

C.

Runtime optimizations such as join pruning can reduce the number of partitions and bytes scanned during query execution.

D.

In-flight data compression will result in fewer micro-partitions being scanned at the virtual warehouse layer than were identified at the storage layer.

Question 14

A Snowflake Administrator wants to create a virtual warehouse that supports several dashboards, issuing various queries on the same database.

For this warehouse, why should the Administrator consider setting AUTO_SUSPEND to 0 or NULL?

Options:

A.

To save costs on warehouse shutdowns and startups for different queries

B.

To save costs by running the warehouse as little as possible

C.

To keep the data cache warm to support good performance of similar queries

D.

To keep the query result cache warm for good performance on repeated queries

Question 15

If the query matches the definition, will Snowflake always dynamically rewrite the query to use a materialized view?

Options:

A.

No, because joins are not supported by materialized views.

B.

No, because the optimizer might decide against it.

C.

No, because the materialized view may not be up-to-date.

D.

Yes, because materialized views are always faster.

Question 16

A Snowflake Administrator needs to set up Time Travel for a presentation area that includes facts and dimensions tables, and receives a lot of meaningless and erroneous

loT data. Time Travel is being used as a component of the company's data quality process in which the ingestion pipeline should revert to a known quality data state if any

anomalies are detected in the latest load. Data from the past 30 days may have to be retrieved because of latencies in the data acquisition process.

According to best practices, how should these requirements be met? (Select TWO).

Options:

A.

Related data should not be placed together in the same schema. Facts and dimension tables should each have their own schemas.

B.

The fact and dimension tables should have the same DATA_RETENTION_TIME_IN_ DAYS.

C.

The DATA_RETENTION_TIME_IN_DAYS should be kept at the account level and never used for lower level containers (databases and schemas).

D.

Only TRANSIENT tables should be used to ensure referential integrity between the fact and dimension tables.

E.

The fact and dimension tables should be cloned together using the same Time Travel options to reduce potential referential integrity issues with the restored data.

Question 17

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?

Options:

A.

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

B.

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

C.

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

D.

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

Question 18

A company has set up a new Snowflake account. An Identity Provider (IdP) has been configured for both Single Sign-On (SSO) and SCIM provisioning.

What maintenance is required to ensure that the SCIM provisioning process continues to operate without errors?

Options:

A.

The IdP service account requires a new RSA key pair to be generated every six months.

B.

The Administrator must issue a POST RENEW call to the REST API at least once every six months.

C.

The OAuth Bearer Tokens have a lifespan of six months and must be regenerated prior to expiration.

D.

The IdP Administrator must issue a REFRESH transaction at least once every six months to synchronize all users and roles.

Question 19

An Administrator has been asked to support the company's application team need to build a loyalty program for its customers. The customer table contains Personal

Identifiable Information (PII), and the application team's role is DEVELOPER.

CREATE TABLE customer_data (

customer_first_name string,

customer_last_name string,

customer_address string,

customer_email string,

... some other columns,

);

The application team would like to access the customer data, but the email field must be obfuscated.

How can the Administrator protect the sensitive information, while maintaining the usability of the data?

Options:

A.

Create a view on the customer_data table to eliminate the email column by omitting it from the SELECT clause. Grant the role DEVELOPER access to the view.

B.

Create a separate table for all the non-Pll columns and grant the role DEVELOPER access to the new table.

C.

Use the CURRENT_ROLE and CURRENT_USER context functions to integrate with a secure view and filter the sensitive data.

D.

Use the CURRENT_ROLE context function to integrate with a masking policy on the fields that contain sensitive data.

Question 20

An Administrator loads data into a staging table every day. Once loaded, users from several different departments perform transformations on the data and load it into

different production tables.

How should the staging table be created and used to MINIMIZE storage costs and MAXIMIZE performance?

Options:

A.

Create it as an external table, which will not incur Time Travel costs.

B.

Create it as a transient table with a retention time of 0 days.

C.

Create it as a temporary table with a retention time of 0 days.

D.

Create it as a permanent table with a retention time of 0 days.

Question 21

.

Company A uses Snowflake to manage audio files of call recordings. Company A hired Company B, who also uses Snowflake, to transcribe the audio files for further

analysis.

Company A's Administrator created a share.

What object should be added to the share to allow Company B access to the files?

Options:

A.

A secure view with a column for file URLs.

B.

A secure view with a column for pre-signed URLs.

C.

A secure view with a column for METADATA$FILENAME.

D.

A secure view with a column for the stage name and a column for the file path.

Page: 1 / 7
Total 72 questions