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

The user_level policy will override the account_level policy.

The account_level policy will override the user_level policy.

The user_level policy will override the account_level policy.

The user_level network policies will not be supported.

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

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

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

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

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

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

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

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

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

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

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

What are characteristics of Dynamic Data Masking? (Select TWO).

A single masking policy can be applied to columns in different tables.

A single masking policy can be applied to columns with different data types.

A masking policy that is currently set on a table can be dropped.

A single masking policy can be applied to columns in different tables.

A masking policy can be applied to the VALUE column of an external table.

The role that creates the masking policy will always see unmasked data in query results.

A single masking policy can be applied to columns with different data types.

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

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

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.

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

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

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

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

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.

A Snowflake Administrator needs to persist all virtual warehouse configurations for auditing and backups. Given a table already exists with the following schema:Table Name : VWH_METAColumn 1 : SNAPSHOT_TIME TIMESTAMP_NTZColumn 2 : CONFIG VARIANTWhich commands should be executed to persist the warehouse data at the time of execution in JSON format in the table VWH META?

1. SHOW WAREHOUSES; 2. INSERT INTO VWH_META SELECT CURRENT_TIMESTAMP (), OBJECT CONSTRUCT (*) FROM TABLE (RESULT_SCAN (LAST_QUERY_ID ()));

1. SHOW WAREHOUSES; 2. INSERT INTO VWH META SELECT CURRENT TIMESTAMP (), FROM TABLE (RESULT_SCAN (LAST_QUERY_ID(1) ) ) ;

1. SHOW WAREHOUSES; 2. INSERT INTO VWH META SELECT CURRENT TIMESTAMP (), * FROM TABLE (RESULT_SCAN (LAST_QUERY_ID ())) ;

1. SHOW WAREHOUSES; 2. INSERT INTO VWH_META SELECT CURRENT_TIMESTAMP (), OBJECT CONSTRUCT (*) FROM TABLE (RESULT_SCAN (LAST_QUERY_ID ()));

1. SHOW WAREHOUSES; 2. INSERT INTO VWH META SELECT CURRENT TIMESTAMP (), * FROM TABLE (RESULT_SCAN (SELECT LAST QUERY ID(-1)));

What are the requirements when creating a new account within an organization in Snowflake? (Select TWO).

The account name must be specified when the account is created.

The account name must be unique within the organization.

The account requires at least one ORGADMIN role within one of the organization's accounts.

The account name is immutable and cannot be changed.

The account name must be specified when the account is created.

The account name must be unique among all Snowflake customers.

The account name must be unique within the organization.

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?

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

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

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

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

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

What roles or security privileges will allow a consumer account to request and get data from the Data Exchange? (Select TWO).

IMPORT SHARE and CREATE DATABASE

IMPORT PRIVILEGES and SHARED DATABASE

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?

Grant modify on data exchange EXCHANGE_NAME to ROLE_NAME;

Grant imported privileges on data exchange EXCHANGE_NAME to ROLE_NAME;

Grant modify on data exchange EXCHANGE_NAME to ROLE_NAME;

Grant ownership on data exchange EXCHANGE_NAME to ROLE NAME;

Grant usage on data exchange EXCHANGE_NAME to ROLE_NAME;

A company has implemented Snowflake replication between two Snowflake accounts, both of which are running on a Snowflake Enterprise edition. The replication is for the database APP_DB containing only one schema, APP_SCHEMA. The company's Time Travel retention policy is currently set for 30 days for both accounts. An Administrator has been asked to extend the Time Travel retention policy to 60 days on the secondary database only. How can this requirement be met?

Set the data retention policy on the secondary database to 60 days.

Set the data retention policy on the schemas in the secondary database to 60 days.

Set the data retention policy on the primary database to 30 days and the schemas to 60 days.

Set the data retention policy on the primary database to 60 days.

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

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

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

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

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

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

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?

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

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

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

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

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

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

Resource monitors add no additional load to virtual warehouse compute.

Multiple triggers can be configured across various virtual warehouse thresholds.

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

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

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

Resource monitors add no additional load to virtual warehouse compute.

Multiple triggers can be configured across various virtual warehouse thresholds.

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

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

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

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

SELECT on all tables and views in DatabaseA_clone. Schema1

USAGE on the schema DatabaseA clone

USAGE on the database DatabaseA_clone. Schemal

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

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

SELECT on all tables and views in DatabaseA_clone. Schema1

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?

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;

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;

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;

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;

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;

A data provider wants to share data from multiple databases with a data consumer account.How can this be accomplished?

The data provider needs to create a secure view and grant the REFERENCE_USAGE privilege on each database referenced by the secure view.

The data provider needs to create a secure view and grant the USAGE privilege on each database referenced by the secure view.

The data provider needs to create a secure view and grant the REFERENCE_USAGE privilege on each database referenced by the secure view.

The data provider needs to create a secure view and grant the REFERENCE_USAGE privilege to a database role to include objects from multiple databases in a share

The data provider needs to create a secure view and must grant the REFERENCE_USAGE privilege on the database where the secure view is created.

A requirement has been identified to allow members of a corporate Data Product team to bring in data sets from the Snowflake Marketplace. The members of this team use the role DP_TEAM.What grant statements must the ACCOUNTADMIN execute in order for the DP TEAM role to import and work with data from the Marketplace?

grant import share on account to role dp_team; grant create database on account to role dp_team;

grant marketplace in account to role dp_team; grant create database from share to role dp_team;

grant usage on snowflake_marketplace to role dp_team; grant create database on account to role dp_team;

grant imported privileges on account to role dp_team; grant create database on account to role dp_team;

grant import share on account to role dp_team; grant create database on account to role dp_team;

The following commands were executed:Grant usage on database PROD to role PROD_ANALYST;Grant usage on database PROD to role PROD_SUPERVISOR;Grant ALL PRIVILEGES on schema PROD. WORKING to role PROD_ANALYST;Grant ALL PRIVILEGES on schema PROD. WORKING to role PROD_SUPERVISOR;Grant role PROD ANALYST to user A;Grant role PROD SUPERVISOR to user B;What authority does each user have on the WORKING schema?

Both user A and user B can create tables in the PROD. WORKING schema.

Only user B can create tables, because all privileges were transferred to PROD_SUPERVISOR.

Tables created by either user A or user B will be visible to both users.

All existing tables in schema PROD. WORKING will be visible to both users.

Both user A and user B can create tables in the PROD. WORKING schema.

An Administrator receives data from a Snowflake partner. The partner is sharing a dataset that contains multiple secure views. The Administrator would like to configure the data so that only certain roles can see certain secure views.How can this be accomplished?

Create views over the incoming shared database and apply the desired RBAC onto these views.

Apply RBAC directly onto the partner's shared secure views.

Individually grant imported privileges onto the schema in the share.

Clone the data and insert it into a company-owned share and apply the desired RBAC on the new tables.

Create views over the incoming shared database and apply the desired RBAC onto these views.

What are characteristics of data replication in Snowflake? (Select THREE).

The ALTER DATABASE ... ENABLE REPLICATION TO ACCOUNTS command must be issued from the primary account.

Databases created from shares can be replicated.

Users can have unlimited primary databases and they can be replicated to an unlimited number of accounts if all accounts are within the same organization.

The ALTER DATABASE ... ENABLE REPLICATION TO ACCOUNTS command must be issued from the primary account.

Users must be granted REPLICATIONADMIN privileges in order to enable replication.

To start replication run the ALTER DATABASE ... REFRESH command on the account where the secondary database resides.

Replication can only occur within the same cloud provider.

Databases created from shares can be replicated.

Users can have unlimited primary databases and they can be replicated to an unlimited number of accounts if all accounts are within the same organization.

An Administrator has a user who needs to be able to suspend and resume a task based on the current virtual warehouse load, but this user should not be able to modify the task or start a new run.What privileges should be granted to the user to meet these requirements? (Select TWO).

USAGE on the database and schema containing the task

OWNERSHIP on the database and schema containing the task

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?

Use bi-directional data sharing among offices in the same region and replication among offices across the continents.

Use data replication everywhere to reduce costs associated with same-region sharing.

Use the PUT command to move files to an Amazon S3 bucket and Azure Blobs, and use an external file management application to move files within the corporate VPC.

Move all the Snowflake accounts to a single region, and implement data sharing.

Use bi-directional data sharing among offices in the same region and replication among offices across the continents.

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

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

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

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

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

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

User administration is simplified across all accounts within the organization.

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

What access control policy will be put into place when future grants are assigned to both database and schema objects?

Schema privileges will take precedence over database privileges.

Database privileges will take precedence over schema privileges.

Schema privileges will take precedence over database privileges.

An access policy combining both the database object and the schema object will be used, with the most permissive policy taking precedence.

An access policy combining both the database object and the schema object will be used, with the most restrictive policy taking precedence.

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?

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

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

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

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

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

Review the output of the SHOW statement below which displays the current grants on the table DB1. S1. T1: This statement is executed:USE ROLE ACCOUNTADMIN;DROP ROLE A;What will occur?

The SELECT privileges for roles B and C will remain.

The table object DB1. S1. T1 will be dropped.

The OWNERSHIP privilege on table DB1. S1. T1 will be transferred to the ACCOUNTADMIN role.

The SELECT privilege on table DB1. S1. T1 to role B will be shown as GRANTED_BY the role ACCOUNTADMIN.

The SELECT privileges for roles B and C will remain.

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?

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 )

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 )

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 )

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 )

create or replace row access policy region policy as (region_value varchar) returns boolean -> 'ALL ACCESS ROLE' = current_role () )

An Administrator needs to create a sample of the table LINEITEM. The sample should not be repeatable and the sampling function should take the data by blocks of rows.What select command will generate a sample of 20% of the table?

select * from LINEITEM sample system (20);

select * from LINEITEM sample bernoulli (20);

select * from LINEITEM sample system (20);

select * from LINEITEM tablesample block (20 rows);

select * from LINEITEM tablesample system (20) seed (1);

How should an Administrator configure a Snowflake account to use AWS PrivateLink?

Create CNAME records in the DNS.

Block public access to Snowflake.

Use SnowCD to evaluate the network connection.

A user with the proper role issues the following commands when setting up and activating network policies:CREATE OR REPLACE NETWORK POLICY foo_policyALLOWED_IP_LIST = ( '1.1.1.0/24', '2.2.2.0/24' , '3.3. 3. 0/24' )BLOCKED IP LIST = ( '1.1.1.1')COMMENT = 'Account level policy';ALTER ACCOUNT SET NETWORK_POLICY=FOO_POLICY;CREATE OR REPLACE NETWORK POLICY bar_policyALLOWED_IP_LIST = ('3.3.3.0/24')BLOCKED IP LIST = ('3.3.3.10')COMMENT = 'user level policy';ALTER USER userl SET NETWORK_POLICY=BAR_POLICY;Afterwards, user1 attempts to log in to Snowflake from IP address 3.3.3.10.Will the login be successful?

No, because 3.3.3.10 is found in the BLOCKED_IP_LIST of bar_policy.

Yes, because 3.3.3.10 is found in the ALLOWED_IP_LIST of bar_policy.

No, because 3.3.3.10 is found in the BLOCKED_IP_LIST of bar_policy.

Yes, because 3.3.3.10 is found in the ALLOWED_IP_LIST of foo_policy.

No, because 3.3.3.10 is not found in the ALLOWED_IP_LIST of foo_policy.

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?

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

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

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

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

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

Snowflake ADA-C01 Practice Test 2 - Free Online Exam Questions 41-72

A Snowflake user runs a complex SQL query on a dedicated virtual warehouse that reads a large amount of data from micro-partitions. The same user wants to run another query that uses the same data set.

Which action would provide optimal performance for the second SQL query?

Become a Premium Member for full access

Unlock Premium Member

Snowflake ADA-C01 Practice Test 2

Question

Snowflake ADA-C01 Practice Tests

Practice Tests