ARA-C01 practice questions 101–110

1. Question 101 · 193 in bank

   The Business Intelligence team reports that when some team members run queries for their dashboards in parallel with others, the query response time is getting significantly slower. What can a Snowflake Architect do to identify what is occurring and troubleshoot this issue?

   • AUse larger warehouses to speed up the queries running in parallel. Identify the queries running in parallel using this query:
   • BIncrease the size of the warehouse cache to speed up concurrent queries. Identify the concurrent queries using this query:
   • CIntroduce multi-cluster warehouses to help with concurrent queries. Identify the concurrent queries by running this query:
   • DIdentify which queries are spilled to remote storage and change the warehouse parameters to address this issue. Identify the issue by running this query:

   Show answer

   Answer: C

   Next question

2. Question 102 · 193 in bank

   What is a key consideration when setting up search optimization service for a table?

   • ASearch optimization service works best with a column that has a minimum of 100 K distinct values.
   • BSearch optimization service can significantly improve query performance on partitioned external tables.
   • CSearch optimization service can help to optimize storage usage by compressing the data into a GZIP format.
   • DThe table must be clustered with a key having multiple columns for effective search optimization.

   Show answer

   Answer: A

   Explanation

   Correct Answer should be A "At least one of the columns accessed by the query filter operation has on the order of 100,000 distinct values or more." https://docs.snowflake.com/en/user-guide/search-optimization/queries-that-benefit

   Source: Adapted from community discussion — cui_li

   References

   • Snowflake documentation

   Next question

3. Question 103 · 193 in bank

   Select 2

   A retail company has 2000+ stores spread across the country. Store Managers report that they are having trouble running key reports related to inventory management, sales targets, payroll, and staffing during business hours. The Managers report that performance is poor and time-outs occur frequently. Currently all reports share the same Snowflake virtual warehouse. How should this situation be addressed? (Choose two.)

   • AUse a Business Intelligence tool for in-memory computation to improve performance.
   • BConfigure a dedicated virtual warehouse for the Store Manager team.
   • CConfigure the virtual warehouse to be multi-clustered.
   • DConfigure the virtual warehouse to size 4-XL.
   • EAdvise the Store Manager team to defer report execution to off-business hours.

   Show answer

   Answer: BC

   Next question

4. Question 104 · 193 in bank

   A company needs to have the following features available in its Snowflake account: 1. Support for Multi-Factor Authentication (MFA) 2. A minimum of 2 months of Time Travel availability 3. Database replication in between different regions 4. Native support for JDBC and ODBC 5. Customer-managed encryption keys using Tri-Secret Secure 6. Support for Payment Card Industry Data Security Standards (PCI DSS) In order to provide all the listed services, what is the MINIMUM Snowflake edition that should be selected during account creation?

   • AStandard
   • BEnterprise
   • CBusiness Critical
   • DVirtual Private Snowflake (VPS)

   Show answer

   Answer: C

   Next question

5. Question 105 · 193 in bank

   When using the COPY INTO [table] command with the CSV file format, how does the MATCH_BY_COLUMN_NAME parameter behave?

   • AIt expects a header to be present in the CSV file, which is matched to a case-sensitive table column name.
   • BThe parameter will be ignored.
   • CThe command will return an error.
   • DThe command will return a warning stating that the file has unmatched columns.

   Show answer

   Answer: A

   Explanation

   Since option A isn't true unless the parameter is set to CASE_SENSITIVE and CSV column name detection is in Open Private Preview only , the correct answer here should be C

   Source: Adapted from community discussion — Atomic_Gecko

   Next question

6. Question 106 · 193 in bank

   Select 2

   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? (Choose two.)

   • ASet masking policy conditions using CURRENT_ROLE targeting the role in use for the current session.
   • BSet masking policy conditions using IS_ROLE_IN_SESSION targeting the role in use for the current account.
   • CSet masking policy conditions using INVOKER_ROLE targeting the executing role in a SQL statement.
   • DDetermine if there are OWNERSHIP privileges on the masking policy that would allow the use of any function.
   • EAssign the ACCOUNTADMIN role to the user who is executing the object.

   Show answer

   Answer: AC

   Explanation

   A: The CURRENT_ROLE() context function returns the role currently active for the session. Masking policies can be configured to apply based on the current role, allowing different views of data based on the user's role. C: The INVOKER_ROLE() context function returns the role that invoked the function or SQL statement. This allows masking policies to dynamically apply based on the role executing the query, ensuring data security based on the user's permissions.

   Source: Adapted from community discussion — MSIDDIQUI18

   Next question

7. Question 107 · 193 in bank

   A table, T1, has a table stream defined, named S1. The data retention time on the table is set for 10 days. Stream S1has not been consumed for 10 consecutive days. What consideration needs to be made if an Architect wants to implement an ELT process on the stream?

   • ABecause of the data retention time setting on table T1, from the 11th day forward if any ETL process tries to access stream S1 the records from the stream will be inaccessible.
   • BAn ETL process can access stream S1 after the 11th day because Snowflake automatically puts a temporary extension on the stream offset for 7 additional days.
   • CAn ETL process can access stream S1 after the 11th day because Snowflake automatically puts a temporary extension on the stream offset for 14 additional days.
   • DAn ETL process can access the stream S1 after the 11th day because streams have no dependency on the table DATA_RETENTION_TIME_IN_DAYS parameter.

   Show answer

   Answer: A

   Next question

8. Question 108 · 193 in bank

   Instead of updating a particular row, a user forgot to put in the where clause and accidentally updated the entire table, named customer. The variable error_update_query_id is set to query_id for the mistaken update statement. How can the current version of the table customer be preserved into the table CUSTOMER_ERROR, and the table CUSTOMER be restored to the version that was in place prior to the update?

   • ACREATE TABLE CUSTOMER_ERROR AS CUSTOMER BEFORE(STATEMENT => $error_update_query_id) ALTER TABLE CUSTOMER SWAP WITH CUSTOMER ERROR; __________________________________________________________
   • BCREATE TABLE CUSTOMER_ERROR CLONE CUSTOMER BEFORE(STATEMENT => $error_update_query_id) ALTER TABLE CUSTOMER SWAP WITH CUSTOMER_ERROR; __________________________________________________________
   • CCREATE TABLE CUSTOMER_ERROR LIKE CUSTOMER BEFORE(STATEMENT => $error_update_query_id); ALTER TABLE CUSTOMER SWAP WITH CUSTOMER ERROR; __________________________________________________________
   • DCREATE TABLE CUSTOMER_ERROR AS SELECT * FROM CUSTOMER BEFORE(STATEMENT => $error_update_query_id); __________________________________________________________

   Show answer

   Answer: D

   Next question

9. Question 109 · 193 in bank

   A Snowflake Architect anticipates that a table will contain several terabytes of data, and so defines a custom cluster key of three columns to optimize query performance. However, the table still takes a long time to query. Why is this occurring?

   • AA multi-cluster virtual warehouse is not being used.
   • BThe cluster key should not exceed two columns.
   • CThe custom cluster key cardinality is too high.
   • DThe table needs to be manually reclustered to partition the key.

   Show answer

   Answer: C

   Next question

10. Question 110 · 193 in bank

    A company is implementing a machine learning use case on a very large data set which will require high volumes of memory and compute resources. How should this Snowflake architecture be designed to meet these requirements?

    • AUse a Snowpark-optimized virtual warehouse.
    • BUse a standard multi-cluster virtual warehouse in maximized mode.
    • CUse a standard virtual warehouse with the search optimization service implemented.
    • DUse a standard virtual warehouse with the query acceleration service implemented.

    Show answer

    Answer: A

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

ARA-C01 practice, question 101. 193 questions available.