SnowPro Advanced Data Engineer practice test

1. Question 1 · 1

   A Data Engineer is investigating a query that is taking a long time to return. The Query Profile shows the following: What step should the Engineer take to increase the query performance?

   Choose one answer.

   

   • Add additional virtual warehouses.
   • Increase the size of the virtual warehouse.
   • Rewrite the query using Common Table Expressions (CTEs).
   • Change the order of the joins and start with smaller tables first.

2. Question 2 · 1

   Which methods can be used to create a DataFrame object in Snowpark? (Choose three.)

   Choose all answers that apply.

   • session.jdbc_connection()
   • session.read.json()
   • session.table()
   • DataFrame.write()
   • session.builder()
   • session.sql()

3. Question 3 · 1

   A new CUSTOMER table is created by a data pipeline in a Snowflake schema where MANAGED ACCESS is enabled. Which roles can grant access to the CUSTOMER table? (Choose three.)

   Choose all answers that apply.

   • The role that owns the schema
   • The role that owns the database
   • The role that owns the CUSTOMER table
   • The SYSADMIN role
   • The SECURITYADMIN role
   • The USERADMIN role with the MANAGE GRANTS privilege

4. Question 4 · 1

   What is the purpose of the BUILD_STAGE_FILE_URL function in Snowflake?

   Choose one answer.

   • It generates an encrypted URL for accessing a file in a stage.
   • It generates a staged URL for accessing a file in a stage.
   • It generates a permanent URL for accessing files in a stage.
   • It generates a temporary URL for accessing a file in a stage.

5. Question 5 · 1

   A company has an extensive script in Scala that transforms data by leveraging DataFrames. A Data Engineer needs to move these transformations to Snowpark. What characteristics of data transformations in Snowpark should be considered to meet this requirement? (Choose two.)

   Choose all answers that apply.

   • It is possible to join multiple tables using DataFrames.
   • Snowpark operations are executed lazily on the server.
   • User-Defined Functions (UDFs) are not pushed down to Snowflake.
   • Snowpark requires a separate cluster outside of Snowflake for computations.
   • Columns in different DataFrames with the same name should be referred to with squared brackets.

6. Question 6 · 1

   The following is returned from SYSTEM$CLUSTERING_INFORMATION() for a table named ORDERS with a DATE column named O_ORDERDATE: What does the total_constant_partition_count value indicate about this table?

   Choose one answer.

   

   • The table is clustered very well on O_ORDERDATE, as there are 493 micro-partitions that could not be significantly improved by reclustering.
   • The table is not clustered well on O_ORDERDATE, as there are 493 micro-partitions where the range of values in that column overlap with every other micro-partition in the table.
   • The data in O_ORDERDATE does not change very often, as there are 493 micro-partitions containing rows where that column has not been modified since the row was created.
   • The data in O_ORDERDATE has a very low cardinality, as there are 493 micro-partitions where there is only a single distinct value in that column for all rows in the micro-partition.

7. Question 7 · 1

   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?

   Choose one answer.

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

8. Question 8 · 1

   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?

   Choose one answer.

   • 12 hours
   • 24 hours
   • 14 days
   • 31 days

9. Question 9 · 1

   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?

   Choose one answer.

   • Call the system function SYSTEM$ABORT_TRANSACTION.
   • Call the system function SYSTEM$CANCEL_TRANSACTION.
   • Set the LOCK_TIMEOUT to FALSE in the stored procedure.
   • Set the TRANSACTION_ABORT_ON_ERROR to TRUE in the stored procedure.

10. Question 10 · 1

    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?

    Choose one answer.

    

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

11. Question 11 · 1

    How can the following relational data be transformed into semi-structured data using the LEAST amount of operational overhead?

    Choose one answer.

    

    • Use the TO_JSON function.
    • Use the PARSE_JSON function to produce a VARIANT value.
    • Use the OBJECT_CONSTRUCT function to return a Snowflake object.
    • Use the TO_VARIANT function to convert each of the relational columns to VARIANT.

12. Question 12 · 1

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

    Choose one answer.

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

13. Question 13 · 1

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

    Choose one answer.

    • average_depth
    • notes
    • average_overlaps
    • total_partition_count

14. Question 14 · 1

    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?

    Choose one answer.

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

15. Question 15 · 1

    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?

    Choose one answer.

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

Paid access to all 57 questions

Each purchase applies to this certification. Prices are one-time payments, not monthly subscriptions.