Free Snowflake ARA-C01 Practice Exams - Page 9 of 22

Question #81

A company’s daily Snowflake workload consists of a huge number of concurrent queries triggered between 9pm and 11pm. At the individual level, these queries are smaller statements that get completed within a short time period.

What configuration can the company’s Architect implement to enhance the performance of this workload? (Choose two.)

A . Enable a multi-clustered virtual warehouse in maximized mode during the workload duration.
B . Set the MAX_CONCURRENCY_LEVEL to a higher value than its default value of 8 at the virtual warehouse level.
C . Increase the size of the virtual warehouse to size X-Large.
D . Reduce the amount of data that is being processed through this workload.
E . Set the connection timeout to a higher value than its default.

Reveal Solution Hide Solution

Correct Answer: A, B
A, B

Explanation:

These two configuration options can enhance the performance of the workload that consists of a huge number of concurrent queries that are smaller and faster.

Enabling a multi-clustered virtual warehouse in maximized mode allows the warehouse to scale out automatically by adding more clusters as soon as the current cluster is fully loaded, regardless of the number of queries in the queue. This can improve the concurrency and throughput of the workload by minimizing or preventing queuing. The maximized mode is suitable for workloads that require high performance and low latency, and are less sensitive to credit consumption1.

Setting the MAX_CONCURRENCY_LEVEL to a higher value than its default value of 8 at the virtual warehouse level allows the warehouse to run more queries concurrently on each cluster. This can improve the utilization and efficiency of the warehouse resources, especially for smaller and faster queries that do not require a lot of processing power. The MAX_CONCURRENCY_LEVEL parameter can be set when creating or modifying a warehouse, and it can be changed at any time2.

Reference: Snowflake Documentation: Scaling Policy for Multi-cluster Warehouses

Snowflake Documentation: MAX_CONCURRENCY_LEVEL

Question #82

A Snowflake Architect created a new data share and would like to verify that only specific records in

secure views are visible within the data share by the consumers.

What is the recommended way to validate data accessibility by the consumers?

A . Create reader accounts as shown below and impersonate the consumers by logging in with their credentials.
create managed account reader_acctl admin_name = userl , adroin_password ■ ‘Sdfed43da!44T , type = reader;
B . Create a row access policy as shown below and assign it to the data share.
create or replace row access policy rap_acct as (acct_id varchar) returns boolean -> case when ‘acctl_role’ = current_role() then true else false end;
C . Set the session parameter called SIMULATED_DATA_SHARING_C0NSUMER as shown below in order to impersonate the consumer accounts.
alter session set simulated_data_sharing_consumer – ‘Consumer Acctl*
D . Alter the share settings as shown below, in order to impersonate a specific consumer account. alter share sales share set accounts = ‘Consumerl’ share restrictions = true

Reveal Solution Hide Solution

Correct Answer: C
C

Explanation:

The SIMULATED_DATA_SHARING_CONSUMER session parameter allows a data provider to simulate the data access of a consumer account without creating a reader account or logging in with the consumer credentials. This parameter can be used to validate the data accessibility by the consumers in a data share, especially when using secure views or secure UDFs that filter data based on the current account or role. By setting this parameter to the name of a consumer account, the data provider can see the same data as the consumer would see when querying the shared database. This is a convenient and efficient way to test the data sharing functionality and ensure that only the intended data is visible to the consumers.

Reference: Using the SIMULATED_DATA_SHARING_CONSUMER Session Parameter SnowPro Advanced: Architect Exam Study Guide

Question #82

A Snowflake Architect created a new data share and would like to verify that only specific records in

secure views are visible within the data share by the consumers.

What is the recommended way to validate data accessibility by the consumers?

A . Create reader accounts as shown below and impersonate the consumers by logging in with their credentials.
create managed account reader_acctl admin_name = userl , adroin_password ■ ‘Sdfed43da!44T , type = reader;
B . Create a row access policy as shown below and assign it to the data share.
create or replace row access policy rap_acct as (acct_id varchar) returns boolean -> case when ‘acctl_role’ = current_role() then true else false end;
C . Set the session parameter called SIMULATED_DATA_SHARING_C0NSUMER as shown below in order to impersonate the consumer accounts.
alter session set simulated_data_sharing_consumer – ‘Consumer Acctl*
D . Alter the share settings as shown below, in order to impersonate a specific consumer account. alter share sales share set accounts = ‘Consumerl’ share restrictions = true

Reveal Solution Hide Solution

Correct Answer: C
C

Explanation:

The SIMULATED_DATA_SHARING_CONSUMER session parameter allows a data provider to simulate the data access of a consumer account without creating a reader account or logging in with the consumer credentials. This parameter can be used to validate the data accessibility by the consumers in a data share, especially when using secure views or secure UDFs that filter data based on the current account or role. By setting this parameter to the name of a consumer account, the data provider can see the same data as the consumer would see when querying the shared database. This is a convenient and efficient way to test the data sharing functionality and ensure that only the intended data is visible to the consumers.

Reference: Using the SIMULATED_DATA_SHARING_CONSUMER Session Parameter SnowPro Advanced: Architect Exam Study Guide

Question #84

How is the change of local time due to daylight savings time handled in Snowflake tasks? (Choose two.)

A . A task scheduled in a UTC-based schedule will have no issues with the time changes.
B . Task schedules can be designed to follow specified or local time zones to accommodate the time changes.
C . A task will move to a suspended state during the daylight savings time change.
D . A frequent task execution schedule like minutes may not cause a problem, but will affect the task history.
E . A task schedule will follow only the specified time and will fail to handle lost or duplicated hours.

Reveal Solution Hide Solution

Correct Answer: A, B
A, B

Explanation:

According to the Snowflake documentation1 and the web search results2, these two statements are true about how the change of local time due to daylight savings time is handled in Snowflake tasks. A task is a feature that allows scheduling and executing SQL statements or stored procedures in Snowflake. A task can be scheduled using a cron expression that specifies the frequency and time zone of the task execution.

A task scheduled in a UTC-based schedule will have no issues with the time changes. UTC is a universal time standard that does not observe daylight savings time. Therefore, a task that uses UTC as the time zone will run at the same time throughout the year, regardless of the local time changes1.

Task schedules can be designed to follow specified or local time zones to accommodate the time changes. Snowflake supports using any valid IANA time zone identifier in the cron expression for a task. This allows the task to run according to the local time of the specified time zone, which may include daylight savings time adjustments. For example, a task that uses Europe/London as the time zone will run one hour earlier or later when the local time switches between GMT and BST12.

Reference: Snowflake Documentation: Scheduling Tasks

Snowflake Community: Do the timezones used in scheduling tasks in Snowflake adhere to daylight savings?

Question #84

How is the change of local time due to daylight savings time handled in Snowflake tasks? (Choose two.)

A . A task scheduled in a UTC-based schedule will have no issues with the time changes.
B . Task schedules can be designed to follow specified or local time zones to accommodate the time changes.
C . A task will move to a suspended state during the daylight savings time change.
D . A frequent task execution schedule like minutes may not cause a problem, but will affect the task history.
E . A task schedule will follow only the specified time and will fail to handle lost or duplicated hours.

Reveal Solution Hide Solution

Correct Answer: A, B
A, B

Explanation:

According to the Snowflake documentation1 and the web search results2, these two statements are true about how the change of local time due to daylight savings time is handled in Snowflake tasks. A task is a feature that allows scheduling and executing SQL statements or stored procedures in Snowflake. A task can be scheduled using a cron expression that specifies the frequency and time zone of the task execution.

A task scheduled in a UTC-based schedule will have no issues with the time changes. UTC is a universal time standard that does not observe daylight savings time. Therefore, a task that uses UTC as the time zone will run at the same time throughout the year, regardless of the local time changes1.

Task schedules can be designed to follow specified or local time zones to accommodate the time changes. Snowflake supports using any valid IANA time zone identifier in the cron expression for a task. This allows the task to run according to the local time of the specified time zone, which may include daylight savings time adjustments. For example, a task that uses Europe/London as the time zone will run one hour earlier or later when the local time switches between GMT and BST12.

Reference: Snowflake Documentation: Scheduling Tasks

Snowflake Community: Do the timezones used in scheduling tasks in Snowflake adhere to daylight savings?

Question #86

How can the Snowflake context functions be used to help determine whether a user is authorized to see data that has column-level security enforced? (Select TWO).

A . Set masking policy conditions using current_role targeting the role in use for the current session.
B . Set masking policy conditions using is_role_in_session targeting the role in use for the current account.
C . Set masking policy conditions using invoker_role targeting the executing role in a SQL statement.
D . Determine if there are ownership privileges on the masking policy that would allow the use of any function.
E . Assign the accountadmin role to the user who is executing the object.

Reveal Solution Hide Solution

Correct Answer: A, C
A, C

Explanation:

Snowflake context functions are functions that return information about the current session, user, role, warehouse, database, schema, or object. They can be used to help determine whether a user is authorized to see data that has column-level security enforced by setting masking policy conditions based on the context functions. The following context functions are relevant for column-level security:

current_role: This function returns the name of the role in use for the current session. It can be used

to set masking policy conditions that target the current session and are not affected by the execution

context of the SQL statement. For example, a masking policy condition using current_role can allow

or deny access to a column based on the role that the user activated in the session.

invoker_role: This function returns the name of the executing role in a SQL statement. It can be used

to set masking policy conditions that target the executing role and are affected by the execution

context of the SQL statement. For example, a masking policy condition using invoker_role can allow

or deny access to a column based on the role that the user specified in the SQL statement, such as

using the AS ROLE clause or a stored procedure.

is_role_in_session: This function returns TRUE if the user’s current role in the session (i.e. the role returned by current_role) inherits the privileges of the specified role. It can be used to set masking policy conditions that involve role hierarchy and privilege inheritance. For example, a masking policy condition using is_role_in_session can allow or deny access to a column based on whether the user’s current role is a lower privilege role in the specified role hierarchy.

The other options are not valid ways to use the Snowflake context functions for column-level security:

Set masking policy conditions using is_role_in_session targeting the role in use for the current account. This option is incorrect because is_role_in_session does not target the role in use for the current account, but rather the role in use for the current session. Also, the current account is not a role, but rather a logical entity that contains users, roles, warehouses, databases, and other objects. Determine if there are ownership privileges on the masking policy that would allow the use of any function. This option is incorrect because ownership privileges on the masking policy do not affect the use of any function, but rather the ability to create, alter, or drop the masking policy. Also, this is not a way to use the Snowflake context functions, but rather a way to check the privileges on the masking policy object.

Assign the accountadmin role to the user who is executing the object. This option is incorrect because assigning the accountadmin role to the user who is executing the object does not involve using the Snowflake context functions, but rather granting the highest-level role to the user. Also, this is not a recommended practice for column-level security, as it would give the user full access to all objects and data in the account, which could compromise data security and governance.

Reference: Context Functions

Advanced Column-level Security topics

Snowflake Data Governance: Column Level Security Overview Data Security Snowflake Part 2 – Column Level Security

Question #87

A Snowflake Architect Is working with Data Modelers and Table Designers to draft an ELT framework specifically for data loading using Snowpipe. The Table Designers will add a timestamp column that Inserts the current tlmestamp as the default value as records are loaded into a table. The Intent is to capture the time when each record gets loaded into the table; however, when tested the timestamps are earlier than the loae_take column values returned by the copy_history function or the Copy_HISTORY view (Account Usage).

Why Is this occurring?

A . The timestamps are different because there are parameter setup mismatches. The parameters need to be realigned
B . The Snowflake timezone parameter Is different from the cloud provider’s parameters causing the mismatch.
C . The Table Designer team has not used the localtimestamp or systimestamp functions in the Snowflake copy statement.
D . The CURRENT_TIMEis evaluated when the load operation is compiled in cloud services rather than when the record is inserted into the table.

Reveal Solution Hide Solution

Correct Answer: D
D

Explanation:

The correct answer is D because the CURRENT_TIME function returns the current timestamp at the start of the statement execution, not at the time of the record insertion. Therefore, if the load operation takes some time to complete, the CURRENT_TIME value may be earlier than the actual load time.

Option A is incorrect because the parameter setup mismatches do not affect the timestamp values. The parameters are used to control the behavior and performance of the load operation, such as the file format, the error handling, the purge option, etc.

Option B is incorrect because the Snowflake timezone parameter and the cloud provider’s parameters are independent of each other. The Snowflake timezone parameter determines the session timezone for displaying and converting timestamp values, while the cloud provider’s parameters determine the physical location and configuration of the storage and compute resources. Option C is incorrect because the localtimestamp and systimestamp functions are not relevant for the Snowpipe load operation. The localtimestamp function returns the current timestamp in the session timezone, while the systimestamp function returns the current timestamp in the system timezone. Neither of them reflect the actual load time of the records.

Reference: Snowflake Documentation: Loading Data Using Snowpipe: This document explains how to use Snowpipe to continuously load data from external sources into Snowflake tables. It also describes the syntax and usage of the COPY INTO command, which supports various options and parameters to control the loading behavior.

Snowflake Documentation: Date and Time Data Types and Functions: This document explains the different data types and functions for working with date and time values in Snowflake. It also describes how to set and change the session timezone and the system timezone.

Snowflake Documentation: Querying Metadata: This document explains how to query the metadata of the objects and operations in Snowflake using various functions, views, and tables. It also describes how to access the copy history information using the COPY_HISTORY function or the COPY_HISTORY view.

Question #88

Two queries are run on the customer_address table:

create or replace TABLE CUSTOMER_ADDRESS ( CA_ADDRESS_SK NUMBER(38,0), CA_ADDRESS_ID

VARCHAR(16), CA_STREET_NUMBER VARCHAR(IO) CA_STREET_NAME VARCHAR(60),

CA_STREET_TYPE VARCHAR(15), CA_SUITE_NUMBER VARCHAR(10), CA_CITY VARCHAR(60),

CA_COUNTY

VARCHAR(30), CA_STATE VARCHAR(2), CA_ZIP VARCHAR(10), CA_COUNTRY VARCHAR(20), CA_GMT_OFFSET NUMBER(5,2), CA_LOCATION_TYPE VARCHAR(20) );

ALTER TABLE DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS ADD SEARCH OPTIMIZATION ON SUBSTRING(CA_ADDRESS_ID);

Which queries will benefit from the use of the search optimization service? (Select TWO).

A . select * from DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS Where
substring(CA_ADDRESS_ID,1,8)= substring(‘AAAAAAAAPHPPLBAAASKDJHASLKDJHASKJD’,1,8);
B . select * from DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS Where CA_ADDRESS_ID= substring(‘AAAAAAAAPHPPLBAAASKDJHASLKDJHASKJD’,1,16);

Reveal Solution Hide Solution

Correct Answer: AB
AB

Explanation:

The use of the search optimization service in Snowflake is particularly effective when queries involve operations that match exact substrings or start from the beginning of a string. The ALTER TABLE command adding search optimization specifically for substrings on the CA_ADDRESS_ID field allows the service to create an optimized search path for queries using substring matches.

Option A benefits because it directly matches a substring from the start of the CA_ADDRESS_ID, aligning with the optimization’s capability to quickly locate records based on the beginning segments of strings.

Option B also benefits, despite performing a full equality check, because it essentially compares the full length of CA_ADDRESS_ID to a substring, which can leverage the substring index for efficient retrieval.

Options C, D, and E involve patterns that do not start from the beginning of the string or use negations, which are not optimized by the search optimization service configured for starting substring matches.

Reference: Snowflake’s documentation on the use of search optimization for substring matching in SQL queries.

Question #88

Two queries are run on the customer_address table:

create or replace TABLE CUSTOMER_ADDRESS ( CA_ADDRESS_SK NUMBER(38,0), CA_ADDRESS_ID

VARCHAR(16), CA_STREET_NUMBER VARCHAR(IO) CA_STREET_NAME VARCHAR(60),

CA_STREET_TYPE VARCHAR(15), CA_SUITE_NUMBER VARCHAR(10), CA_CITY VARCHAR(60),

CA_COUNTY

VARCHAR(30), CA_STATE VARCHAR(2), CA_ZIP VARCHAR(10), CA_COUNTRY VARCHAR(20), CA_GMT_OFFSET NUMBER(5,2), CA_LOCATION_TYPE VARCHAR(20) );

ALTER TABLE DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS ADD SEARCH OPTIMIZATION ON SUBSTRING(CA_ADDRESS_ID);

Which queries will benefit from the use of the search optimization service? (Select TWO).

A . select * from DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS Where
substring(CA_ADDRESS_ID,1,8)= substring(‘AAAAAAAAPHPPLBAAASKDJHASLKDJHASKJD’,1,8);
B . select * from DEMO_DB.DEMO_SCH.CUSTOMER_ADDRESS Where CA_ADDRESS_ID= substring(‘AAAAAAAAPHPPLBAAASKDJHASLKDJHASKJD’,1,16);

Reveal Solution Hide Solution

Correct Answer: AB
AB

Explanation:

The use of the search optimization service in Snowflake is particularly effective when queries involve operations that match exact substrings or start from the beginning of a string. The ALTER TABLE command adding search optimization specifically for substrings on the CA_ADDRESS_ID field allows the service to create an optimized search path for queries using substring matches.

Option A benefits because it directly matches a substring from the start of the CA_ADDRESS_ID, aligning with the optimization’s capability to quickly locate records based on the beginning segments of strings.

Option B also benefits, despite performing a full equality check, because it essentially compares the full length of CA_ADDRESS_ID to a substring, which can leverage the substring index for efficient retrieval.

Options C, D, and E involve patterns that do not start from the beginning of the string or use negations, which are not optimized by the search optimization service configured for starting substring matches.

Reference: Snowflake’s documentation on the use of search optimization for substring matching in SQL queries.

Question #90

An Architect is integrating an application that needs to read and write data to Snowflake without installing any additional software on the application server.

How can this requirement be met?

A . Use SnowSQL.
B . Use the Snowpipe REST API.
C . Use the Snowflake SQL REST API.
D . Use the Snowflake ODBC driver.

Reveal Solution Hide Solution

Correct Answer: C
C

Explanation:

The Snowflake SQL REST API is a REST API that you can use to access and update data in a Snowflake database. You can use this API to execute standard queries and most DDL and DML statements. This API can be used to develop custom applications and integrations that can read and write data to Snowflake without installing any additional software on the application server. Option A is not correct because SnowSQL is a command-line client that requires installation and configuration on the application server. Option B is not correct because the Snowpipe REST API is used to load data from cloud storage into Snowflake tables, not to read or write data to Snowflake. Option D is not correct because the Snowflake ODBC driver is a software component that enables applications to connect to Snowflake using the ODBC protocol, which also requires installation and configuration on the application server.

Reference: The answer can be verified from Snowflake’s official documentation on the Snowflake SQL REST API available on their website. Here are some relevant links:

Snowflake SQL REST API | Snowflake Documentation

Introduction to the SQL API | Snowflake Documentation

Submitting a Request to Execute SQL Statements | Snowflake Documentation