SnowPro Advanced Data Engineer Practice Test

SnowPro Advanced Data Engineer Practice Test practice questions, page 4 of 7, with answer review and explanations.

Question 16

A company is building a dashboard for thousands of Analysts. The dashboard presents the results of a few summary queries on tables that are regularly updated. The query conditions vary by topic according to what data each Analyst needs. Responsiveness of the dashboard queries is a top priority, and the data cache should be preserved. How should the Data Engineer configure the compute resources to support this dashboard?

  1. Assign queries to a multi-cluster virtual warehouse with economy auto-scaling. Allow the system to automatically start and stop clusters according to demand.
  2. Assign all queries to a multi-cluster virtual warehouse set to maximized mode. Monitor to determine the smallest suitable number of clusters.
  3. Create a virtual warehouse for every 250 Analysts. Monitor to determine how many of these virtual warehouses are being utilized at capacity.
  4. Create a size XL virtual warehouse to support all the dashboard queries. Monitor query runtimes to determine whether the virtual warehouse should be resized.

Question 17

A Data Engineer has developed a dashboard that will issue the same SQL select clause to Snowflake every 12 hours. How long will Snowflake use the persisted query results from the result cache, provided that the underlying data has not changed?

  1. 12 hours
  2. 24 hours
  3. 14 days
  4. 31 days

Question 18

A Data Engineer ran a stored procedure containing various transactions. During the execution, the session abruptly disconnected, preventing one transaction from committing or rolling back. The transaction was left in a detached state and created a lock on resources. What step must the Engineer take to immediately run a new transaction?

  1. Call the system function SYSTEM$ABORT_TRANSACTION.
  2. Call the system function SYSTEM$CANCEL_TRANSACTION.
  3. Set the LOCK_TIMEOUT to FALSE in the stored procedure.
  4. Set the TRANSACTION_ABORT_ON_ERROR to TRUE in the stored procedure.

Question 19

A database contains a table and a stored procedure defined as: The log_table is initially empty and a Data Engineer issues the following command: CALL insert_log(NULL::VARCHAR); No other operations are affecting the log_table. What will be the outcome of the procedure call?

  1. The log_table contains zero records and the stored procedure returned 1 as a return value.
  2. The log_table contains one record and the stored procedure returned 1 as a return value.
  3. The log_table contains one record and the stored procedure returned NULL as a return value.
  4. The log_table contains zero records and the stored procedure returned NULL as a return value.

Question 20

When would a Data Engineer use TABLE with the FLATTEN function instead of the LATERAL FLATTEN combination?

  1. When TABLE with FLATTEN requires another source in the FROM clause to refer to.
  2. When TABLE with FLATTEN requires no additional source in the FROM clause to refer to.
  3. When the LATERAL FLATTEN combination requires no other source in the FROM clause to refer to.
  4. When TABLE with FLATTEN is acting like a sub-query executed for each returned row.