Free SnowPro Advanced: Data Engineer Certification Exam DEA-C01 Exam Practice Test

Mark the Incorrect Statements with respect to types of streams supported by Snowflake?

• Standard streams cannot retrieve update data for geospatial data.
• An append-only stream returns the appended rows only and therefore can be much more performant than a standard stream for extract, load, transform (ELT).
• Insert-only Stream supported on external tables only.
• An insert-only stream tracks row inserts & Delete ops only

David, a Lead Data engineer with XYZ company looking out to improve query performance & oth-er benefits while working with Tables, Regular Views, MVs and Cached Results.Which one of the following does not shows key similarities and differences between tables, regular views, cached query results, and materialized views while choosing any of them by David?

• Regular views do not cache data, and therefore cannot improve performance by cach-ing.
• As with non-materialized views, a materialized view automatically inherits the privileges of its base table.
• Cached Query Results: Used only if data has not changed and if query only uses de-terministic functions (e.g. not CURRENT_DATE).
• Materialized views are faster than tables because of their “cache” (i.e. the query results for theview); in addition, if data has changed, they can use their “cache” for data that hasn’t changed and use the base table for any data that has changed.
• Both materialized views and regular views enhance data security by allowing data to be exposed or hidden at the row level or column level.

In Which Data Modelling Technique, Data Engineer generally refer the terms Hubs & Satellites?

• Data Hub
• Data Vault
• Star Schema
• Snowflake Schema

To support Time Travel, Which of the following SQL extensions/parameters/commands have been implemented?

• AT | BEFORE clause which can be specified in the CREATE … CLONE commands.
• ONSET (time difference in seconds from the present time)
• OFFSET (time difference in seconds from the present time)
• STATEMENT_ID (identifier for statement, e.g. query ID)
• STATEMENT (identifier for statement, e.g. query ID)
• UNDROP command for tables, schemas, and databases.

Data Engineer is using existing pipe that automates data loads using event notifications, later he figured out the needs to modify pipe properties. For the same, He decided to recreate the pipe as best practice. He followed the below steps for the same.

• Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is RUN-NING.
• Recreate the pipe (using CREATE OR REPLACE PIPE).
• Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is RUN-NING. Which are the Missing recommended steps while Recreating Pipes for Automated Data Loads?
• CREATE OR REPLACE PIPE command will recreate the PIPE successfully.
• Terminate the existing pipe (using ALTER PIPE … SET PIPE_EXECUTION_TERMINATE = true) before recreation.
• Pause the pipe (using ALTER PIPE … SET PIPE_EXECUTION_PAUSED = true) Pre & Post recreation & Resume after recreation (using ALTER PIPE … SET PIPE_EXECUTION_PAUSED = false).
• Force the pipe to resume (using SYSTEM$PIPE_FORCE_RESUME).

Which UDF programming language is not supported with Snowflake Secure Data Sharing feature?

• SQL
• JAVA
• JAVASCRIPT
• PYTHON

A SQL UDF evaluates an arbitrary SQL expression and returns the result(s) of the expression. Which value type it can returns?

• Single Value
• A Set of Rows
• Scaler or Tabular depend on input SQL expression
• Regex

Select the Incorrect statement about External Functions in SnowFlake?

• An external function is a type of UDF.
• An external function does not contain its own code; instead, the external function calls code that is stored and executed outside Snowflake.
• Inside Snowflake, the external function is stored as a API Integration object.
• Inside Snowflake, the external function is stored as a database object that contains in-formation that Snowflake uses to call the remote service.

Data Engineer, ran the below clustering depth analysis function:select system$clustering_depth('TPCH_CUSTOMERS', '(C1, C6)', 'C9 = 30'); on TPCH_CUSTOMERS table, will return which of the following?

• An error: this function does not accept lists of columns as a third parameter.
• An error: this function does not accept predicates ('C9 = 30') as parameter.
• Calculate the clustering depth for a table using mentioned columns in the table.
• Calculate the clustering depth for a table using the clustering key defined for the table.