DEA-C02 practice questions 91–100

1. Question 91 · 126 in bank

   A Data Engineer needs to create a SQL script that will load and transform data in batch mode into Snowflake. The script should be run with SnowSQL, and should be scheduled to run at specific times in the company’s corporate scheduler. Based on this scenario, what is the MOST secure way to authenticate into Snowflake?

   • ASingle Sign-On (SSO)
   • BKey-pair authentication
   • CUsername and password
   • DMulti-Factor Authentication (MFA)

   Show answer

   Answer: B

   Next question

2. Question 92 · 126 in bank

   A Data Engineer is debugging a SQL stored procedure that contains a transaction with a rollback using this code: DROP TABLE A1; CREATE OR REPLACE TABLE A1(i int); BEGIN TRANSACTION; INSERT INTO A1 VALUES (1), (2); INSERT INTO A1 VALUES (3), (4); CREATE OR REPLACE TABLE table2 (i VARCHAR); INSERT INTO A1 VALUES (5), (6); ROLLBACK; How many rows will the code write into table A1?

   • A0 rows - the ROLLBACK command at the end of the query will remove all inserted data.
   • B2 rows - the CREATE statement acts as an implicit ROLLBACK, so all prior inserted rows are COMMITTED. The ROLLBACK command is outside of the transaction.
   • C4 rows - the ROLLBACK command at the end of the query will result in values 5 and 6 not being committed.
   • D6 rows - the CREATE statement acts as an implicit END to the BEGIN transaction. All values are therefore COMMITTED.

   Show answer

   Answer: D

   Next question

3. Question 93 · 126 in bank

   Select 2

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

   • AScalar SQL UDF - Snowflake allows the query to execute and returns NULL.
   • BScalar SQL UDF - Snowflake blocks the query.
   • CLogging & Event Tables - Snowflake allows the UDF to execute and captures log and event details.
   • DLogging & Event Tables - Snowflake allows the UDF to execute but does not capture log and event details.
   • ELogging & Event Tables - Snowflake blocks the UDF but allows the statement calling the UDF to run to support logging requirements.

   Show answer

   Answer: AD

   Next question

4. Question 94 · 126 in bank

   A Data Engineer needs to obtain details about data loading history for tables in a specific database and schema. The tables were loaded using Snowpipe in the last 24 hours. How should the Engineer accomplish these tasks?

   • AUse the USAGE privilege on the database and schema to use the copy history view in the information schema.
   • BUse the LOAD HISTORY view using the usage privilege on the database and schema, and any privilege on the table.
   • CUse the MONITOR privilege on the account and access the table details to use the copy history view in the information schema.
   • DUse the USAGE privilege on the database and schema, select the database and the schema, and access the table details to use the copy history tab.

   Show answer

   Answer: B

   Next question

5. Question 95 · 126 in bank

   A Data Engineer is developing a data pipeline to ingest data from an Amazon S3 bucket into a Snowflake table. Files will be loaded into the S3 bucket randomly, with most files loaded during working hours. The files are in a compressed CSV format (*.csv.gz) and average 100 MB per file. The data ingestion requirements are listed in order of priority: 1. MOST cost-effective method 2. LEAST amount of operational overhead 3. New data MUST be available within 10 minutes of the creation of a file Which ingestion method will meet these requirements?

   • ASnowpipe
   • BSnowpipe Streaming
   • CSnowflake Kafka Connector
   • DThe COPY INTO {location} command

   Show answer

   Answer: A

   Next question

6. Question 96 · 126 in bank

   How should a Data Engineer build a pipeline that executes a stored procedure to parse and load unstructured data files as the files arrive in a stage?

   • ACreate a Snowpipe with the parameter AUTO_INGEST = TRUE.
   • BCreate a Snowpipe that includes the INTEGRATION = ‘’ parameter.
   • CCreate a stage with parameter ENCODING = ‘’ to call the procedure when flies land in the stage.
   • DCreate a stage with a directory table, a stream on the directory table, and a task referencing the stream.

   Show answer

   Answer: D

   Next question

7. Question 97 · 126 in bank

   A Data Engineer needs to configure a task in Snowflake to automatically send error notifications in case of a data loading failure. The Engineer wants to use an existing task that inserts data into a table every 5 minutes. The Engineer set up a notification integration with Amazon Secure Notification Service (SNS) named my_notification_int. How should the task be updated to automatically send error notifications if it fails?

   • AAdd an ALERT clause in the task definition to link it to the notification service.
   • BAdd the command: ALTER TASK MYTASK SET ERROR_INTEGRATION = ‘my_notification_int’; to the notification.
   • CUse the NOTIFICATION INTEGRATION = ‘my_notification_int’ parameter in the task configuration.
   • DAdd NOTIFICATION_PROVIDER = ‘my_notificacion_int’ to the task schedule.

   Show answer

   Answer: B

   Next question

8. Question 98 · 126 in bank

   A Data Engineer has created two SQL User-Defined Functions (UDFs) with the same name:

   

   The following function call is made: select plus5(to_date(‘2024-01-25’)); What will be the output of this call?

   • A2024-01-30
   • B2024-01-255
   • C2024-01-25’5’
   • DThe SQL compilation error: error line 1 at position 7 Invalid argument types for function ’PLUS5': (DATE)

   Show answer

   Answer: D

   Next question

9. Question 99 · 126 in bank

   A company has loaded the following JSON data into the CAR_SALES table and needs assistance loading the data into a VEHICLE_DIM dimension.

   

   Which query will load the data into a VEHICLE_DIM?

   • A//IMG//
   • B//IMG//
   • C//IMG//
   • D//IMG//

   Show answer

   Answer: A

   Next question

10. Question 100 · 126 in bank

    How are overlapping columns handled when joining DataFrames in Snowpark?

    • ASnowpark will return a join result containing multiple columns that have the same names.
    • BSnowpark will return the error: 'Ambiguous column'.
    • CSnowpark will prepend a randomly-generated prefix to the overlapping columns in the join result.
    • DSnowpark will append the DataFrame names to new columns in the join result.

    Show answer

    Answer: B

    Next question

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

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