DEA-C02 practice questions 11–20

1. Question 11 · 126 in bank

   Select 3

   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.)

   • AThe role that owns the schema
   • BThe role that owns the database
   • CThe role that owns the CUSTOMER table
   • DThe SYSADMIN role
   • EThe SECURITYADMIN role
   • FThe USERADMIN role with the MANAGE GRANTS privilege

   Show answer

   Answer: AEF

   Next question

2. Question 12 · 126 in bank

   What is the purpose of the BUILD_STAGE_FILE_URL function in Snowflake?

   • AIt generates an encrypted URL for accessing a file in a stage.
   • BIt generates a staged URL for accessing a file in a stage.
   • CIt generates a permanent URL for accessing files in a stage.
   • DIt generates a temporary URL for accessing a file in a stage.

   Show answer

   Answer: C

   Next question

3. Question 13 · 126 in bank

   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//IMG//
   • B//IMG//
   • C//IMG//
   • D//IMG//

   Show answer

   Answer: B

   Next question

4. Question 14 · 126 in bank

   Select 2

   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.)

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

   Show answer

   Answer: AB

   Next question

5. Question 15 · 126 in bank

   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?

   • AThe table is clustered very well on O_ORDERDATE, as there are 493 micro-partitions that could not be significantly improved by reclustering.
   • BThe 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.
   • CThe 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.
   • DThe 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.

   Show answer

   Answer: A

   Next question

6. Question 16 · 126 in bank

   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?

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

   Show answer

   Answer: B

   Next question

7. Question 17 · 126 in bank

   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?

   • A12 hours
   • B24 hours
   • C14 days
   • D31 days

   Show answer

   Answer: D

   Next question

8. Question 18 · 126 in bank

   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?

   • ACall the system function SYSTEM$ABORT_TRANSACTION.
   • BCall the system function SYSTEM$CANCEL_TRANSACTION.
   • CSet the LOCK_TIMEOUT to FALSE in the stored procedure.
   • DSet the TRANSACTION_ABORT_ON_ERROR to TRUE in the stored procedure.

   Show answer

   Answer: A

   Next question

9. Question 19 · 126 in bank

   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?

   • AThe log_table contains zero records and the stored procedure returned 1 as a return value.
   • BThe log_table contains one record and the stored procedure returned 1 as a return value.
   • CThe log_table contains one record and the stored procedure returned NULL as a return value.
   • DThe log_table contains zero records and the stored procedure returned NULL as a return value.

   Show answer

   Answer: D

   Next question

10. Question 20 · 126 in bank

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

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

    Show answer

    Answer: B

    Next question

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

DEA-C02 practice, question 11. 126 questions available.