DEA-C02 practice questions 51–60

1. Question 51 · 126 in bank

   While running an external function, the following error message is received: Error: Function received the wrong number of rows What is causing this to occur?

   • AExternal functions do not support multiple rows.
   • BNested arrays are not supported in the JSON response.
   • CThe JSON returned by the remote service is not constructed correctly.
   • DThe return message did not produce the same number of rows that it received.

   Show answer

   Answer: D

   Next question

2. Question 52 · 126 in bank

   A Data Engineer enables a result cache at the session level with the following command: ALTER SESSION SET USE_CACHED_RESULT = TRUE; The Engineer then runs the following SELECT query twice without delay: SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER SAMPLE(10) SEED (99); The underlying table does not change between executions. What are the results of both runs?

   • AThe first and second run returned the same results, because SAMPLE is deterministic.
   • BThe first and second run returned the same results, because the specific SEED value was provided.
   • CThe first and second run returned different results, because the query is evaluated each time it is run.
   • DThe first and second run returned different results, because the query uses * instead of an explicit column list.

   Show answer

   Answer: B

   Next question

3. Question 53 · 126 in bank

   A company built a sales reporting system with Python, connecting to Snowflake using the Python Connector. Based on the user's selections, the system generates the SQL queries needed to fetch the data for the report. First it gets the customers that meet the given query parameters (on average 1000 customer records for each report run), and then it loops the customer records sequentially. Inside that loop it runs the generated SQL clause for the current customer to get the detailed data for that customer number from the sales data table. When the Data Engineer tested the individual SQL clauses, they were fast enough (1 second to get the customers, 0.5 second to get the sales data for one customer), but the total runtime of the report is too long. How can this situation be improved?

   • AIncrease the size of the virtual warehouse.
   • BIncrease the number of maximum clusters of the virtual warehouse.
   • CDefine a clustering key for the sales data table.
   • DRewrite the report to eliminate the use of the loop construct.

   Show answer

   Answer: D

   Next question

4. Question 54 · 126 in bank

   A company is using Snowpipe to bring in millions of rows every day of Change Data Capture (CDC) into a Snowflake staging table on a real-time basis. The CDC needs to get processed and combined with other data in Snowflake and land in a final table as part of the full data pipeline. How can a Data Engineer MOST efficiently process the incoming CDC on an ongoing basis?

   • ACreate a stream on the staging table and schedule a task that transforms data from the stream, only when the stream has data.
   • BTransform the data during the data load with Snowpipe by modifying the related COPY INTO statement to include transformation steps such as CASE statements and JOINS.
   • CSchedule a task that dynamically retrieves the last time the task was run from information_schema.task_history and use that timestamp to process the delta of the new rows since the last time the task was run.
   • DUse a CREATE OR REPLACE TABLE AS statement that references the staging table and includes all the transformation SQL. Use a task to run the full CREATE OR REPLACE TABLE AS statement on a scheduled basis.

   Show answer

   Answer: A

   Next question

5. Question 55 · 126 in bank

   A Data Engineer is building a pipeline to transform a 1 TB table by joining it with supplemental tables. The Engineer is applying filters and several aggregations leveraging Common Table Expressions (CTEs) using a size Medium virtual warehouse in a single query in Snowflake. After checking the Query Profile, what is the recommended approach to MAXIMIZE performance of this query if the Profile shows data spillage?

   • AEnable clustering on the table.
   • BIncrease the warehouse size.
   • CRewrite the query to remove the CTEs.
   • DSwitch to a multi-cluster virtual warehouse.

   Show answer

   Answer: B

   Next question

6. Question 56 · 126 in bank

   Which system role is recommended for a custom role hierarchy to be ultimately assigned to?

   • AACCOUNTADMIN
   • BSECURITYADMIN
   • CSYSADMIN
   • DUSERADMIN

   Show answer

   Answer: C

   Next question

7. Question 57 · 126 in bank

   Which callback function is required within a JavaScript User-Defined Function (UDF) for it to execute successfully?

   • Ainitialize()
   • BprocessRow()
   • Chandler()
   • Dfinalize()

   Show answer

   Answer: B

   Next question

8. Question 58 · 126 in bank

   Which Snowflake feature facilitates access to external API services such as geocoders, data transformation, machine learning models, and other custom code?

   • ASecurity integration
   • BExternal tables
   • CExternal functions
   • DJava User-Defined Functions (UDFs)

   Show answer

   Answer: C

   Next question

9. Question 59 · 126 in bank

   A Data Engineer needs to know the details regarding the micro-partition layout for a table named Invoice using a built-in function. Which query will provide this information?

   • ASELECT SYSTEM$CLUSTERING_INFORMATION('Invoice');
   • BSELECT $CLUSTERING_INFORMATION('Invoice');
   • CCALL SYSTEM$CLUSTERING_INFORMATION('Invoice');
   • DCALL $CLUSTERING_INFORMATION('Invoice');

   Show answer

   Answer: A

   Next question

10. Question 60 · 126 in bank

    Select 3

    A Data Engineer would like to define a file structure for loading and unloading data. Where can the file structure be defined? (Choose three.)

    • ACOPY command
    • BMERGE command
    • CFILE FORMAT object
    • DPIPE object
    • ESTAGE object
    • FINSERT command

    Show answer

    Answer: ACE

    Next question

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

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