SnowPro Advanced Data Engineer
Free trial
Verified
Question 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?
- A: Add additional virtual warehouses.
- B: Increase the size of the virtual warehouse.
- C: Rewrite the query using Common Table Expressions (CTEs).
- D: Change the order of the joins and start with smaller tables first.
Question 2
Which methods can be used to create a DataFrame object in Snowpark? (Choose three.)
- A: session.jdbc_connection()
- B: session.read.json()
- C: session.table()
- D: DataFrame.write()
- E: session.builder()
- F: session.sql()
Question 3
A Data Engineer has created a scalar User-Defined Function (UDF) that contains a SELECT statement based on a projection constraint column. The Engineer notices that the UDF behaves differently depending on how it is being used.
What are the expected behaviors of the UDF for the named context? (Choose two.)
- A: Scalar SQL UDF - Snowflake allows the query to execute and returns NULL.
- B: Scalar SQL UDF - Snowflake blocks the query.
- C: Logging & Event Tables - Snowflake allows the UDF to execute and captures log and event details.
- D: Logging & Event Tables - Snowflake allows the UDF to execute but does not capture log and event details.
- E: Logging & Event Tables - Snowflake blocks the UDF but allows the statement calling the UDF to run to support logging requirements.
Question 4
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.)
- A: The role that owns the schema
- B: The role that owns the database
- C: The role that owns the CUSTOMER table
- D: The SYSADMIN role
- E: The SECURITYADMIN role
- F: The USERADMIN role with the MANAGE GRANTS privilege
Question 5
What is the purpose of the BUILD_STAGE_FILE_URL function in Snowflake?
- A: It generates an encrypted URL for accessing a file in a stage.
- B: It generates a staged URL for accessing a file in a stage.
- C: It generates a permanent URL for accessing files in a stage.
- D: It generates a temporary URL for accessing a file in a stage.
Question 6
The JSON below is stored in a VARIANT column named V in a table named jCustRaw:
Which query will return one row per team member (stored in the teamMembers array) along with all of the attributes of each team member?
- A:
- B:
- C:
- D:
Question 7
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.)
- A: It is possible to join multiple tables using DataFrames.
- B: Snowpark operations are executed lazily on the server.
- C: User-Defined Functions (UDFs) are not pushed down to Snowflake.
- D: Snowpark requires a separate cluster outside of Snowflake for computations.
- E: Columns in different DataFrames with the same name should be referred to with squared brackets.
Question 8
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?
- A: The table is clustered very well on O_ORDERDATE, as there are 493 micro-partitions that could not be significantly improved by reclustering.
- B: 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.
- C: 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.
- D: 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.
Question 9
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?
- A: Assign queries to a multi-cluster virtual warehouse with economy auto-scaling. Allow the system to automatically start and stop clusters according to demand.
- B: Assign all queries to a multi-cluster virtual warehouse set to maximized mode. Monitor to determine the smallest suitable number of clusters.
- C: Create a virtual warehouse for every 250 Analysts. Monitor to determine how many of these virtual warehouses are being utilized at capacity.
- D: 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 10
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?
- A: 12 hours
- B: 24 hours
- C: 14 days
- D: 31 days
Question 11
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?
- A: Call the system function SYSTEM$ABORT_TRANSACTION.
- B: Call the system function SYSTEM$CANCEL_TRANSACTION.
- C: Set the LOCK_TIMEOUT to FALSE in the stored procedure.
- D: Set the TRANSACTION_ABORT_ON_ERROR to TRUE in the stored procedure.
Question 12
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?
- A: The log_table contains zero records and the stored procedure returned 1 as a return value.
- B: The log_table contains one record and the stored procedure returned 1 as a return value.
- C: The log_table contains one record and the stored procedure returned NULL as a return value.
- D: The log_table contains zero records and the stored procedure returned NULL as a return value.
Question 13
How can the following relational data be transformed into semi-structured data using the LEAST amount of operational overhead?
- A: Use the TO_JSON function.
- B: Use the PARSE_JSON function to produce a VARIANT value.
- C: Use the OBJECT_CONSTRUCT function to return a Snowflake object.
- D: Use the TO_VARIANT function to convert each of the relational columns to VARIANT.
Question 14
When would a Data Engineer use TABLE with the FLATTEN function instead of the LATERAL FLATTEN combination?
- A: When TABLE with FLATTEN requires another source in the FROM clause to refer to.
- B: When TABLE with FLATTEN requires no additional source in the FROM clause to refer to.
- C: When the LATERAL FLATTEN combination requires no other source in the FROM clause to refer to.
- D: When TABLE with FLATTEN is acting like a sub-query executed for each returned row.
That’s the end of your free questions
You’ve reached the preview limit for SnowPro Advanced Data EngineerConsider upgrading to gain full access!
Free preview mode
Enjoy the free questions and consider upgrading to gain full access!