SnowPro Advanced Data Engineer Practice Test

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

Question 21

Which output is provided by both the SYSTEM$CLUSTERING_DEPTH function and the SYSTEM$CLUSTERING_INFORMATION function?

  1. average_depth
  2. notes
  3. average_overlaps
  4. total_partition_count

Question 22

A Data Engineer needs to ingest invoice data in PDF format into Snowflake so that the data can be queried and used in a forecasting solution. What is the recommended way to ingest this data?

  1. Use Snowpipe to ingest the files that land in an external stage into a Snowflake table.
  2. Use a COPY INTO command to ingest the PDF files in an external stage into a Snowflake table with a VARIANT column.
  3. Create an external table on the PDF files that are stored in a stage and parse the data into structured data.
  4. Create a Java User-Defined Function (UDF) that leverages Java-based PDF parser libraries to parse PDF data into structured data.

Question 23

A table is loaded using Snowpipe and truncated afterwards. Later, a Data Engineer finds that the table needs to be reloaded, but the metadata of the pipe will not allow the same files to be loaded again. How can this issue be solved using the LEAST amount of operational overhead?

  1. Wait until the metadata expires and then reload the file using Snowpipe.
  2. Modify the file by adding a blank row to the bottom and re-stage the file.
  3. Set the FORCE=TRUE option in the Snowpipe COPY INTO command.
  4. Recreate the pipe by using the CREATE OR REPLACE PIPE command.

Question 24

A stream called TRANSACTIONS_STM is created on top of a TRANSACTIONS table in a continuous pipeline running in Snowflake. After a couple of months, the TRANSACTIONS table is renamed TRANSACTIONS_RAW to comply with new naming standards. What will happen to the TRANSACTIONS_STM object?

  1. TRANSACTIONS_STM will keep working as expected.
  2. TRANSACTIONS_STM will be stale and will need to be re-created.
  3. TRANSACTIONS_STM will be automatically renamed TRANSACTIONS_RAW_STM.
  4. Reading from the TRANSACTIONS_STM stream will succeed for some time after the expected STALE_TIME.

Question 25

A Data Engineer is evaluating the performance of a query in a development environment. Based on the Query Profile, what are some performance tuning options the Engineer can use? (Choose two.)

  1. Add a LIMIT to the ORDER BY if possible
  2. Use a multi-cluster virtual warehouse with the scaling policy set to standard
  3. Move the query to a larger virtual warehouse
  4. Create indexes to ensure sorted access to data
  5. Increase the MAX_CLUSTER_COUNT