SnowPro Advanced Data Engineer Certification DEA-C01 Exam Questions

dumps2023-10-11T10:39:41+08:00

SnowPro Advanced Data Engineer Certification DEA-C01 exam questions are online for you to test. There are 65 Multiple choice and Multiple select questions in the real Snowflake DEA-C01 exam, and you have 115 minutes to finish the test. The DEA-C01 exam language is English. The registration fee is $375. Study Snowflake certification DEA-C01 exam questions to prepare for the test well. Free SnowPro Advanced Data Engineer Certification DEA-C01 exam questions are available below.

Page 1 of 3

1. Streams cannot be created to query change data on which of the following objects? [Select All that Apply]

Standard tables, including shared tables.

Views, including secure views

Directory tables

Query Log Tables

External tables

2. Tasks may optionally use table streams to provide a convenient way to continuously process new or changed data. A task can transform new or changed rows that a stream surfaces. Each time a task is scheduled to run, it can verify whether a stream contains change data for a table and either consume the change data or skip the current run if no change data exists.

Which System Function can be used by Data engineer to verify whether a stream contains changed data for a table?

SYSTEM$STREAM_HAS_CHANGE_DATA

SYSTEM$STREAM_CDC_DATA

SYSTEM$STREAM_HAS_DATA

SYSTEM$STREAM_DELTA_DATA

3. +--------------------------------------------------------------+

The Above example indicates that the SF_DATA table is not well-clustered for which of following valid reasons?

Zero (0) constant micro-partitions out of 1156 total micro-partitions.

High average of overlapping micro-partitions.

High average of overlap depth across micro-partitions.

Most of the micro-partitions are grouped at the lower-end of the histogram, with the majority of micro-partitions having an overlap depth between 64 and 128.

ALL of the above

4. Mark a Data Engineer, looking to implement streams on local views & want to use change tracking metadata for one of its Data Loading use case. Please select the incorrect understanding points of Mark with respect to usage of Streams on Views?

For streams on views, change tracking must be enabled explicitly for the view and underlying tables to add the hidden columns to these tables.

The CDC records returned when querying a stream rely on a combination of the offset stored in the stream and the change tracking metadata stored in the table.

Views with GROUP BY & LIMIT Clause are supported by Snowflake.

As an alternative to streams, Snowflake supports querying change tracking metadata for views using the CHANGES clause for SELECT statements.

Enabling change tracking adds a pair of hidden columns to the table and begins storing change tracking metadata. The values in these hidden CDC data columns provide the input for the stream metadata columns. The columns consume a small amount of storage.

5. To advance the offset of a stream to the current table version without consuming the change data in a DML operation, which of the following operations can be done by Data Engineer? [Select 2]

using the CREATE OR REPLACE STREAM syntax, Recreate the STREAM

Insert the current change data into a temporary table. In the INSERT statement, query the stream but include a WHERE clause that filters out all of the change data (e.g. WHERE 0 = 1).

A stream advances the offset only when it is used in a DML transaction, so none of the options works without consuming the change data of table.

Delete the offset using STREAM properties SYSTEM$RESET_OFFSET( <stream_id> )

6. Data Engineer is performing below steps in sequence while working on Stream s1 created on table t1.

Step 1: Begin transaction.

Step 2: Query stream s1 on table t1.

Step 3: Update rows in table t1.

Step 4: Query stream s1.

Step 5: Commit transaction.

Step 6: Begin transaction.

Step 7: Query stream s1.

Mark the Incorrect Operational statements:

For Step 2, The stream returns the change data capture records between the current position to the Transaction 1 start time. If the stream is used in a DML statement, the stream is then locked to avoid changes by concurrent transactions.

For Step 4, Returns the CDC data records by streams with updated rows happened in the Step 3 because Streams works in Repeated committed mode in which statements see any changes made by previous statements executed within the same transaction, even though those changes are not yet committed.

For Step 5, If the stream was consumed in DML statements within the transaction, the stream position advances to the transaction start time.

For Step 7, Results do include table changes committed by Transaction 1.

if Transaction 2 had begun before Transaction 1 was committed, queries to the stream would have returned a snapshot of the stream from the position of the stream to the be-ginning time of Transaction 2 and would not see any changes committed by Transaction 1.

7. Streams record the differences between two offsets.

If a row is added and then updated in the cur-rent offset, what will be the value of METADATA$ISUPDATE Columns in this scenario?

TRUE

FALSE

UPDATE

INSERT

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

9. Stuart, a Lead Data Engineer in MACRO Data Company created streams on set of External tables.

He has been asked to extend the data retention period of the stream for 90 days, which parameter he can utilize to enable this extension?

MAX_DATA_EXTENSION_TIME_IN_DAYS

DATA_RETENTION_TIME_IN_DAYS

DATA_EXTENSION_TIME_IN_DAYS

None of the above

10. at(timestamp => (select current_timestamp())); Select the Correct Query Execution Output option below:

Developer missed to create stream on the source table which can further query to capture DML records.

Select query will fail with error: 'SQL compilation error-Incorrect Keyword "Chang-es()" found'

No Error reported, select command gives Changed records with Metadata columns as change tracking enabled on the Source views & its underlying tables.

Select statement complied but gives erroneous results.

Page 2 of 3

11. Which column provides information when the stream became stale or may become stale if not consumed?

STREAM_STALE_PERIOD

STALE_PERIOD_AFTER

STALE_STREAM_PERIOD

STALE_AFTER

12. When created, a stream logically takes an initial snapshot of every row in the source object and the contents of a stream change as DML statements execute on the source table.

A Data Engineer, Sophie Created a view that queries the table and returns the CURRENT_USER and CURRENT_TIMESTAMP values for the query transaction. A Stream has been created on views to capture CDC.

Tony, another user inserted the data e.g.

insert into <table> values (1),(2),(3);

Emily, another user also inserted the data e.g.

insert into <table> values (4),(5),(6);

What will happened when Different user queries the same stream after 1 hour?

All the 6 records would be shown with METADATA$ACTION as 'INSERT' out of which 3 records would be displayed with username 'Tony' & rest 3 records would be displayed with username 'Emily'.

All the Six Records would be displayed with CURRENT_USER & CUR-RENT_TIMESTAMP while querying Streams.

All the Six records would be displayed with User 'Sohpie' Who is the owner of the View.

User would be displayed with the one who queried during the session, but Recorded timestamp would be of past 1 hour i.e. actual records insertion time.

13. Which Function would Data engineer used to recursively resume all tasks in Chain of Tasks rather than resuming each task individually (using ALTER TASK … RESUME)?

SYSTEM$TASK_DEPENDENTS

SYSTEM$TASK_DEPENDENTS_ENABLE

SYSTEM$TASK_DEPENDENTS_RESUME

SYSTEM$TASK_RECURSIVE_ENABLE

14. Steven created the task, what additional privileges required by Steven on the task so that he can suspend or resume the tasks?

Steven is already owner of the task; he can execute the task & suspend/resume the task without any additional privileges.

In addition to the task owner, a Steven Role must have OPERATE privilege on the task so that he can suspend or resume the task.

Steven must have SUSPEND privilege on the task so that he can suspend or resume the task.

Steven needs to have Global Managed RESUME privilege by TASK administrator.

15. John, Data Engineer, do have technical requirements to refresh the External tables Metadata periodically or in auto mode, which approach John can take to meet this technical specification?

John can use AUTO_REFRESH parameter if the underlying External Cloud host sup-ports this for External tables.

He can create a task that executes an ALTER EXTERNAL TABL

.. REFRESH statement every 5 minutes.

External table cannot be scheduled via Snowflake Tasks, 3rd party tools/scripts needs to be used provided by External cloud storage provider.

Snowflake implicitly take care this Infrastructure needs, as underlying warehouse layer internally manage the refresh. No action needed from John.

16. If you need to connect to Snowflake using a BI tool or technology, which of the following BI tools and technologies are known to provide native connectivity to Snowflake?

SISENSE

SELECT STAR

ALATION

PROTEGRITY

17. Which of the following security and governance tools/technologies are known to provide native connectivity to Snowflake? [Select 2]

ALTR

Baffle

BIG Squid

Dataiku

Zepl

18. print(cur.sfqid)

B. When he used the Snowflake Connector for Python to execute a query, he can access the query ID through the pyqueryid attribute in the Cursor object.

C. He needs to query history views to get the queryID as best practices.

D. Using python connector, snowflake does not support queryID retrieval for both syn-chronous & asynchronous query.

19. Which connector creates the RECORD_CONTENT and RECORD_METADATA columns in the existing Snowflake table while connecting to Snowflake?

Python Connector

Spark Connector

Node.js connector

Kafka Connector

20. Ryan, a Data Engineer, accidently drop the Share named SF_SHARE which results in immediate access revoke for all the consumers (i.e., accounts who have created a database from that SF_SHARE).

What action he can take to recover the dropped Share?

By Executing UNDROP command he could possibly recover the dropped Share SF_SHARE & its associated Databases for immediate consumer access.

He can recreate a share with the same name as a previous share which does restore the databases created (by any consumers) from the share SF_SHAR

A dropped share cannot be restored. The share must be created again using the CRE-ATE SHARE command and then configured using GRANT <privilege> … TO SHARE and ALTER SHAR

Consumer accounts that have created databases from the share will still be able to query these databases as Share is separate securable object & it’s still accessible using time travel feature.

Page 3 of 3

21. For enabling non-ACCOUNTADMIN Roles to Perform Data Sharing Tasks, which two glob-al/account privileges snowflake provide?

CREATE SHARE

IMPORT SHARE

REFERENCE USAGE

OPERATE

22. Mark the correct Statements with respect to Secure views & its creation in the SnowFlake Account?

For a secure view, internal optimizations can indirectly expose data & the view definition is visible to other users.

Secure views should not be used for views that are defined solely for query convenience, such as views created to simplify queries for which users do not need to under-stand the underlying data representation.

To convert an existing view to a secure view and back to a regular view, set/unset the SECURE keyword in the ALTER VIEW or ALTER MATERIALIZED VIEW command.

For non-materialized views, the IS_SECURE column in the Information Schema and Account Usage views identifies whether a view is secure.

The internals of a secure view are not exposed in Query Profile (in the web interface). This is the case even for the owner of the secure view, because non-owners might have access to an owner’s Query Profile.

23. When using the CURRENT_ROLE and CURRENT_USER functions with secure views that will be shared to other Snowflake accounts, Snowflake returns a NULL value for these functions?

FALSE

TRUE

24. Snowflake computes and adds partitions based on the defined partition column expressions when an external table metadata is refreshed.

What are the Correct Statements to configure Partition metadata refresh in case of External Tables?

By default, the metadata is refreshed automatically when the object is created.

The object owner can configure the metadata to refresh automatically when new or updated data files are available in the external stage.

Metadata refresh is not required as its Managed implicitly by Snowflake.

Partitions of External tables is managed by External Stage Cloud provider.

There is nothing like adding partitions on External tables.

25. PARTITION_TYPE = USER_SPECIFIED must be used when you prefer to add and remove partitions selectively rather than automatically adding partitions for all new files in an external storage location that match an expression?

TRUE

FALSE

26. Which Scenario Data engineer decide Materialized views are not useful. Select All that apply.

Query results contain a small number of rows and/or columns relative to the base table (the table on which the view is defined).

Query results contain results that require significant processing.

The query is on an external table (i.e. data sets stored in files in an external stage), which might have slower performance compared to querying native database tables.

The view’s base table change frequently.

SnowPro Advanced Data Engineer Certification DEA-C01 Exam Questions

SnowPro Advanced Data Engineer Certification DEA-C01 Exam Questions

Share this post

Leave a Reply Cancel reply

Related Posts

DSA-C02 SnowPro Advanced: Data Scientist Certification Exam Dumps

New SnowPro Core Certification Exam COF-C02 Study Materials

2021 New Updated SnowPro Core Exam Dumps Questions

Actual Questions for Snowflake SnowPro Advanced Architect Exam

SnowPro Advanced Administrator ADA-C01 Exam Free Dumps

Snowflake SnowPro Advanced Architect ARA-C01 Exam Questions