Study SnowPro Advanced Data Engineer Exam Effectively with Updated DEA-C01 Exam Dumps V10.03 – Choose DumpsBase to Ace Your Exam

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

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?

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

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?

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]

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:

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

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

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

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

E. 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?

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

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?

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

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

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

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

D. Select statement complied but gives erroneous results.

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

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?

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

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

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

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

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

D. 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?

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

B. He can create a task that executes an ALTER EXTERNAL TABLE ... REFRESH statement every 5 minutes.

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

D. 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?

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

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?

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?

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

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

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

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

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

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

E. 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?

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?

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?

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

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

B. Query results contain results that require significant processing.

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

D. The view’s base table change frequently.

27. Partition columns optimize query performance by pruning out the data files that do not need to be scanned (i.e. partitioning the external table).

Which pseudocolumn of External table evaluate as an expression that parses the path and/or filename information.

28. Data Engineer identified use case where he decided to use materialized view for query performance.

Which one is not the limitation he must be aware of before using MVs in their use case?

29. group by m.item_id;

Step 3: After 1 hour, he decided to temporarily suspend the use (and maintenance) of the DataReportMV materialized view for cost saving purpose.

alter materialized view DataReportMV suspend;

Please select what Alex is doing wrong here?

30. David, a Lead Data engineer with XYZ company looking out to improve query performance & other 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?

31. Melissa, Senior Data Engineer, looking out to optimize query performance for one of the Critical Control Dashboard, she found that most of the searches by the users on the control dashboards are based on Equality search on all the underlying columns mostly.

Which Best techniques she should consider here?

32. Search optimization works best to improve the performance of a query when the following conditions are true: [Select All that apply]

33. Regular views do not cache data, and therefore cannot improve performance by caching?

34. Mark the correct statements about Cache?

35. Marko, a Data Engineer is using Snowpipe for data loading in micro batches for one of the Finance Data workloads. There are set of files he attempted to load into the snowflake table using Snow-pipe. While monitoring he found that there are set of files has multiple issue, He queried the COPY_HISTORY view & checked the STATUS column which indicates whether a particular set of files was loaded, partially loaded, or failed to load.

But he wants to view all errors in the files along with Load status, how he can check all errors?

A. He can check RETURN_ALL_ERROR_MESSAGE column in the COPY_HISTORY view which can provides a reason and view all errors in the files.

B. He can view all errors in the files, by executing a COPY INTO <table> statement with the VALIDATION_ERROR_MODE copy option set to RE-TURN_ALL_PIPE_ERRORS.

C. Marko can look out for FIRST_ERROR_MESSAGE column in the COPY_HISTORY view which can provides a reason why a file partially loaded or failed for all the files.

D. He can view all errors in the files, by executing a COPY INTO <table> statement with the VALIDATION_MODE copy option set to RETURN_ALL_ERRORS.

36. Robert, A Data Engineer, found that Pipe become stale as it was paused for longer than the limited retention period for event messages received for the pipe (14 days by default) & also the previous pipe owner transfers the ownership of this pipe to Robert role while the pipe was paused.

How Robert in this case, Resume this stale pipe?

37. How Data Engineer can do Monitoring of Files which are Staged Internally during Continuous data pipelines loading process? [Select all that apply]

A. She Can Monitor the files using Metadata maintained by Snowflake i.e. file-name,last_modified date etc.

B. Snowflake retains historical data for COPY INTO commands executed within the previous 14 days.

C. She can Monitor the status of each COPY INTO <table> command on the History tab page of the classic web interface.

D. She can use the DATA_LOAD_HISTORY Information Schema view to retrieve the history of data loaded into tables using the COPY INTO command.

E. She can use the DATA_VALIDATE function to validate the data files She have loaded and can retrieve any errors encountered during the load.

38. To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.

Which option he can choose to remove these files either during data loading or afterwards?

A. He can choose to remove stage files during data loading (using the COPY INTO <table> command).

B. Files no longer needed, can be removed using the PURGE=TRUE command.

C. Files no longer needed, can be removed using the REMOVE command.

D. Script can be used during data loading & post data loading with DELETE command.

39. Snowflake does not provide which of following set of SQL functions to support retrieving information about tasks?

40. SYSTEM$CLUSTERING_INFORMATION functions returns clustering information, including average clustering depth, for a table based on one or more columns in the table. The function returns a JSON object containing average_overlaps name/value pairs.

Does High average_overlaps indicates well organized Clustering?

41. The smaller the average depth, the better clustered the table is with regards to the specified column?

42. 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?

43. Mark the Correct Statements:

Statement 1. Snowflake’s zero-copy cloning feature provides a convenient way to quickly take a “snapshot” of any table, schema, or database.

Statement 2. Data Engineer can use zero-copy cloning feature for creating instant backups that do not incur any additional costs (until changes are made to the cloned object).

44. Clones can be cloned, with no limitations on the number or iterations of clones that can be created (e.g. you can create a clone of a clone of a clone, and so on), which results in a n-level hierarchy of cloned objects, each with their own portion of shared and independent data storage?