ARA-C01 Snowflake Exam Questions and Free Practice Test

Question 37

What Snowflake system functions are used to view and or monitor the clustering metadata for a table? (Select TWO).

A. SYSTEMSCLUSTERING

B. SYSTEMSTABLE_CLUSTERING

C. SYSTEMSCLUSTERING_DEPTH

D. SYSTEMSCLUSTERING_RATIO

E. SYSTEMSCLUSTERING_INFORMATION

Correct Answer:CE
✑ The SYSTEM$CLUSTERING_INFORMATION function in Snowflake returns a variety of clustering information for a specified table. This information includes the average clustering depth, total number of micro-partitions, total constant partition count, average overlaps, average depth, and a partition depth histogram. This function allows you to specify either one or multiple columns for which the clustering information is returned, and it returns this data in JSON format.
✑ The SYSTEM$CLUSTERING_DEPTH function computes the average depth of a table based on specified columns or the clustering key defined for the table. A lower average depth indicates that the table is better clustered with respect to the specified columns. This function also allows specifying columns to calculate the depth, and the values need to be enclosed in single quotes.
References:
✑ SYSTEM$CLUSTERING_INFORMATION: Snowflake Documentation
✑ SYSTEM$CLUSTERING_DEPTH: Snowflake Documentation

Question 38

An Architect is troubleshooting a query with poor performance using the QUERY_HIST0RY function. The Architect observes that the COMPILATIONJHME is greater than the EXECUTIONJTIME.
What is the reason for this?

A. The query is processing a very large dataset.

B. The query has overly complex logic.

C. The query is queued for execution.

D. The query is reading from remote storage.

Correct Answer:B
Compilation time is the time it takes for the optimizer to create an optimal query plan for the efficient execution of the query. It also involves some pruning of partition files, making the query execution efficient2
If the compilation time is greater than the execution time, it means that the optimizer spent more time analyzing the query than actually running it. This could indicate that the query has overly complex logic, such as multiple joins, subqueries, aggregations, or
expressions. The complexity of the query could also affect the size and quality of the query plan, which could impact the performance of the query3
To reduce the compilation time, the Architect can try to simplify the query logic, use views or common table expressions (CTEs) to break down the query into smaller parts, or use
hints to guide the optimizer. The Architect can also use the EXPLAIN command to examine the query plan and identify potential bottlenecks or inefficiencies4 References:
✑ 1: SnowPro Advanced: Architect | Study Guide 5
✑ 2: Snowflake Documentation | Query Profile Overview 6
✑ 3: Understanding Why Compilation Time in Snowflake Can Be Higher than Execution Time 7
✑ 4: Snowflake Documentation | Optimizing Query Performance 8
✑ : SnowPro Advanced: Architect | Study Guide
✑ : Query Profile Overview
✑ : Understanding Why Compilation Time in Snowflake Can Be Higher than Execution Time
✑ : Optimizing Query Performance

Question 39

A table, EMP_ TBL has three records as shown:
ARA-C01 dumps exhibit
The following variables are set for the session:

Which SELECT statements will retrieve all three records? (Select TWO).

A. Select * FROM Stbl_ref WHERE Scol_ref IN ('Name1','Nam2','Name3');

B. SELECT * FROM EMP_TBL WHERE identifier(Scol_ref) IN ('Namel','Name2', 'Name3');

C. SELECT * FROM identifier<Stbl_ref> WHERE NAME IN ($var1, $var2, $var3);

D. SELECT * FROM identifier($tbl_ref) WHERE ID IN Cvarl','var2','var3');

E. SELECT * FROM $tb1_ref WHERE $col_ref IN ($var1, Svar2, Svar3);

Correct Answer:BE
✑ The correct answer is B and E because they use the correct syntax and values for the identifier function and the session variables.
✑ The identifier function allows you to use a variable or expression as an identifier (such as a table name or column name) in a SQL statement. It takes a single argument and returns it as an identifier. For example, identifier($tbl_ref) returns
EMP_TBL as an identifier.
✑ The session variables are set using the SET command and can be referenced using the $ sign. For example, $var1 returns Name1 as a value.
✑ Option A is incorrect because it uses Stbl_ref and Scol_ref, which are not valid session variables or identifiers. They should be $tbl_ref and $col_ref instead.
✑ Option C is incorrect because it uses identifier<Stbl_ref>, which is not a valid syntax for the identifier function. It should be identifier($tbl_ref) instead.
✑ Option D is incorrect because it uses Cvarl, var2, and var3, which are not valid session variables or values. They should be $var1, $var2, and $var3
instead. References:
✑ Snowflake Documentation: Identifier Function
✑ Snowflake Documentation: Session Variables
✑ Snowflake Learning: SnowPro Advanced: Architect Exam Study Guide

Question 40

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

C. select*fromDEMO_DB.DEMO_SCH.CUSTOMER_ADDRESSWhereCA_ADDRESS_IDLIKE ??%BAAASKD%';

D. select*fromDEMO_DB.DEMO_SCH.CUSTOMER_ADDRESSWhereCA_ADDRESS_IDLIK E '%PHPP%';

E. select*fromDEMO_DB.DEMO_SCH.CUSTOMER_ADDRESSWhereCA_ADDRESS_IDNO T LIKE '%AAAAAAAAPHPPL%';

Correct Answer:AB
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.References: Snowflake's documentation on the use of search optimization for substring matching in SQL queries.

Question 41

A company is designing a process for importing a large amount of loT JSON data from cloud storage into Snowflake. New sets of loT data get generated and uploaded approximately every 5 minutes.
Once the loT data is in Snowflake, the company needs up-to-date information from an external vendor to join to the data. This data is then presented to users through a dashboard that shows different levels of aggregation. The external vendor is a Snowflake customer.
What solution will MINIMIZE complexity and MAXIMIZE performance?

A. * 1. Create an external table over the JSON data in cloud storage.* 2. Create a task that runs every 5 minutes to run a transformation procedure on new data, based on a saved timestamp.* 3. Ask the vendor to expose an API so an external function can be used to generate a call to join the data back to the loT data in the transformation procedure.* 4. Give the transformed table access to the dashboard tool.* 5. Perform the aggregations on the dashboard tool.

B. * 1. Create an external table over the JSON data in cloud storage.* 2. Create a task that runs every 5 minutes to run a transformation procedure on new data based on a saved timestamp.* 3. Ask the vendor to create a data share with the required data that can be imported into the company's Snowflake account.* 4. Join the vendor's data back to the loT data using a transformation procedure.* 5. Create views over the larger dataset to perform the aggregations required by the dashboard.* 6. Give the views access to the dashboard tool.

C. * 1. Create a Snowpipe to bring the JSON data into Snowflake.* 2. Use streams and tasks to trigger a transformation procedure when new JSON data arrives.* 3. Ask the vendor to expose an API so an external function call can be made to join the vendor's data back to the loT data in a transformation procedure.* 4. Create materialized views over the larger dataset to perform the aggregations required by the dashboard.* 5. Give the materialized views access to the dashboard tool.

D. * 1. Create a Snowpipe to bring the JSON data into Snowflake.* 2. Use streams and tasks to trigger a transformation procedure when new JSON data arrives.* 3. Ask the vendor to create a data share with the required data that is then imported into the Snowflake account.* 4. Join the vendor's data back to the loT data in a transformation procedure* 5. Create materialized views over the larger dataset to perform the aggregations required by the dashboard.* 6. Give the materialized views access to the dashboard tool.

Correct Answer:D
Using Snowpipe for continuous, automated data ingestion minimizes the need for manual intervention and ensures that data is available in Snowflake promptly after it is generated. Leveraging Snowflake??s data sharing capabilities allows for efficient and secure access to the vendor??s data without the need for complex API integrations. Materialized views provide pre-aggregated data for fast access, which is ideal for dashboards that require high performance1234.
References =
•Snowflake Documentation on Snowpipe4
•Snowflake Documentation on Secure Data Sharing2
•Best Practices for Data Ingestion with Snowflake1

Question 42

An Architect has been asked to clone schema STAGING as it looked one week ago, Tuesday June 1st at 8:00 AM, to recover some objects.
The STAGING schema has 50 days of retention. The Architect runs the following statement:
CREATE SCHEMA STAGING_CLONE CLONE STAGING at (timestamp => '2021-06-01 08:00:00');
The Architect receives the following error: Time travel data is not available for schema STAGING. The requested time is either beyond the allowed time travel period or before the object creation time.
The Architect then checks the schema history and sees the following: CREATED_ON|NAME|DROPPED_ON
2021-06-02 23:00:00 | STAGING | NULL
2021-05-01 10:00:00 | STAGING | 2021-06-02 23:00:00
How can cloning the STAGING schema be achieved?

A. Undrop the STAGING schema and then rerun the CLONE statement.

B. Modify the statement: CREATE SCHEMA STAGING_CLONE CLONE STAGING at (timestamp => '2021-05-01 10:00:00');

C. Rename the STAGING schema and perform an UNDROP to retrieve the previous STAGING schema version, then run the CLONE statement.

D. Cloning cannot be accomplished because the STAGING schema version was not active during the proposed Time Travel time period.

Correct Answer:D
The error encountered during the cloning attempt arises because the schema STAGING as it existed on June 1st, 2021, is not within the Time Travel retention period. According to the schema history, STAGING was recreated on June 2nd, 2021, after being dropped on the same day. The requested timestamp of '2021-06-01 08:00:00' is prior to this recreation, hence not available. The STAGING schema from before June 2nd was dropped and exceeded the Time Travel period for retrieval by the time of the cloning attempt. Therefore, cloning STAGING as it looked on June 1st, 2021, cannot be achieved because the data from that time is no longer available within the allowed Time Travel window.References: Snowflake documentation on Time Travel and data cloning, which is covered under the SnowPro Advanced: Architect certification resources.

START ARA-C01 EXAM