Snowflake DEA-C01 Dumps - Shortcut Key To Success

Snowflake DEA-C01 Dumps – Shortcut Key To Success

Jonnie Karnath2023-04-02T22:28:28+00:00

If you’re looking for a shortcut key to your success in the DEA-C01 SnowPro Advanced Data Engineer Certification exam, Certspots provides up-to-date, accurate, and reliable Snowflake DEA-C01 dumps that have been carefully crafted to help you pass your exam on the first try. These Snowflake DEA-C01 dumps have been designed to cover all the topics and concepts that you’ll need to know to pass the Snowflake DEA-C01 exam. Plus, they’re updated regularly to ensure that they reflect any changes or updates to the exam. They’re available in different formats, including PDF and practice exam software, which allows you to practice and test your knowledge before the actual exam.

Page 1 of 4

1. Select the incorrect statement while working with warehouses?

Compute resources waiting to shut down are considered to be in “quiesce” mode.

Resizing a warehouse to a larger size is useful while loading and unloading significant amounts of data.

Resizing a warehouse will have any immediate impact on statements that are currently being executed by the warehouse.

Resizing a suspended warehouse does not provision any new compute resources for the warehouse.

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

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

4. As Data Engineer, you have been asked to access data held in AWS Glacier Deep Archive storage class for Historical Data Analysis, which one is the correct statement to recommend?

You cannot access data held in archival cloud storage classes that requires restoration before it can be retrieved.

Loading data from AWS cloud storage services is supported regardless of the cloud platform that hosts your Snowflake account.

Data can be accessed from External stage using AWS Private link in this case.

We can simply access AWS Glacier Deep Archive storage External Stage data using PUT command.

Upload (i.e. stage) files to your cloud storage account using the tools provided by the cloud storage service.

5. The COPY command supports several options for loading data files from a stage i.e.

I. By path

II. Specifying a list of specific files to load.

III. Using pattern matching to identify specific files by pattern.

IV. Organize files into logical paths that reflect a scheduling pattern.

Of the aforesaid options for identifying/specifying data files to load from a stage, which option in general is the fastest & best considerate?

III

6. While working with Multi Cluster Warehouses, Select the incorrect understanding of Data Engineer about its usage?

Multi-cluster warehouses are designed specifically for handling queuing and performance issues related to large numbers of concurrent users and/or queries.

Unless you have a specific requirement for running in Maximized mode, multi-cluster warehouses should be configured to run in Auto-scale mode, which enables Snowflake to automatically start and stop clusters as needed.

When choosing the minimum number of clusters for a multi-cluster warehouse keep the default value as 1.

Multi-cluster warehouses generally improve query performance, particularly for larger, more complex queries.

When choosing the maximum number of clusters for a multi-cluster warehouse set its value as large as possible.

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

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

9. While creating External function, Which Database object required with at least ACCOUNTAD-MIN privileges?

STORAGE Integration

SECURITY Integration

API Integration

None of the above required.

10. Jeff, a Data Engineer, accessing elements in JSON object in its 3 data loading scripts, he unknowingly use the upper case while accessing the elements. e.g.

Script 1 --> fruits:apple.sweet

Script 2 --> FRUITS:apple.sweet

Script 3 --> FRUITS:Apple.Sweet

Which are the correct statements?

Script 1 & Script 2 traverse path twill be treated same, but Script 2 will not.

Script 1,2,3 traverse path will treat in same way.

Script 1 & 3 traverse path will be treated in same way.

Script 2&3 traverse path will be same.

Page 2 of 4

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

Truncating a materialized view is not supported.

Time Travel is not currently supported on materialized views.

You cannot directly clone a materialized view by using the CREATE MATERIAL-IZED VIE

.. CLON

.. command.

A materialized view can query only a single table & Joins, including self-joins, are not supported.

A materialized views does not support clustering.

A materialized views cannot be created on Shared Data.

A materialized view cannot include HAVING clauses OR ORDER BY clause.

Context Functions like CURRENT_TIME or CURRENT_TIMESTAMP is not per-mitted.

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

TRUE

FALSE

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

SQL

JAVA

JAVASCRIPT

PYTHON

14. In one of your created Schema, you have been required to create Internal Stages, what are the Incorrect considerations you can noticed from the below options? [Select All that Apply]

User stages can be altered or dropped just like Table Stage.

Table stage type is designed to store files that are staged and managed by one or more users but only loaded into a single table.

A named internal stage type can store files that are staged and managed by one or more users and loaded into one or more tables.

A table stage is available for each table created in Snowflake.

15. Select the Incorrect statement about External Functions in SnowFlake?

An external function is a type of UD

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.

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

TRUE

FALSE

17. +--------------------------------------------------------------+

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

18. External Function is a type of UDF & can be Scaler or Tabular?

TRUE

FALSE

19. Ryan, a Data Engineer, wants to improve the performance of large, complex queries against large data sets. He decided to Scale up underlying warehouse/cluster.

What is correct Snowflake consideration while scaling up so that he can achieve better performance results? [Select all that apply]

Resizing can help reduce the queuing that occurs if a warehouse does not have enough compute resources to process all the queries that are submitted concurrently.

Scaling up is not intended for handling concurrency issues; instead, use additional warehouses to handle the workload or use a multi-cluster warehouse (if this feature is available for your account).

Snowflake supports resizing a warehouse at any time, even while running.

Resizing a running warehouse does not impact queries that are already being processed by the warehouse; the additional compute resources, once fully provisioned, are only used for queued and new queries.

Resizing between a 5XL or 6XL warehouse to a 4XL or smaller warehouse results in a brief period during which the customer is charged for both the new warehouse and the old warehouse while the old warehouse is quiesced.

20. The following CREATE DATABASE command creates a clone of a database snowmy_db i.e.

Create database pods_db clone snowmy_db

before (statement => '7e5d0cb9-005e-94e6-b058-k8f5b37c5725');

What are possible reason of failing cloning operation for this database?

Time Travel Statement query time is beyond the retention time of few current child (e.g., a table) of the Database entity.

Time Travel Statement query time is at or before the point in time when the object was created.

CREATE DATABASE query fails due to compilation error as it do not support statement keyword.

SQL Compilation error: "Incorrect Syntax 'before' while creating database"

Page 3 of 4

21. Dominic, a Data Engineer wants to resume the pipe named stalepipe3 which got stale after 14 days. To do the same, he called the SYSTEM$PIPE_FORCE_RESUME function

select system$pipe_force_resume('snowmydb.mysnowschema.stalepipe3','staleness_check_override');

Let’s say If the pipe is resumed 16 days after it was paused, what will happened to the event notification that were received on the first and second days after the pipe was paused?

Snowpipe generally skips any event notifications that were received on the first and second days after the pipe was paused.

Pipe maintains Metadata history of files for 64 days, so in this scenarios Snowpipe processed all the event notifications that were received for 16 days or so.

Once the Pipe got stale, all the events got purged automatically & pipe needs to be recreated with modified properties.

All the events get processed from day 1 if the PURGE properties in the PIPE object definition set to be FALSE initially.

22. As a Data Engineer, you have requirement to query most recent data from the Large Dataset that reside in the external cloud storage, how would you design your data pipelines keeping in mind fastest time to delivery?

Data pipelines would be created to first load data into internal stages & then into Permanent table with SCD Type 2 transformation.

Direct Querying External tables on top of existing data stored in external cloud storage for analysis without first loading it into Snowflake.

Unload data into SnowFlake Internal data storage using PUT command.

Snowpipe can be leveraged with streams to load data in micro batch fashion with CDC streams that capture most recent data only.

External tables with Materialized views can be created in Snowflake.

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

TRUE

FALSE

24. Let us say you have List of 50 Source files, which needs to be loaded into Snowflake internal stage. All these Source system files are already Brotli-compressed files.

Which statement is correct with respect to Compression of Staged Files?

Even though Source files are already compressed, Snowflake do apply default gzip2 Compression to optimize the storage cost.

Snowflake automatically detect Brotli Compression, will skip further compression of all 50 files.

Auto-detection is not yet supported for Brotli-compressed files; when staging or loading Brotli-compressed files, you must explicitly specify the compression method that was used.

When staging 50 compressed files in a Snowflake stage, the files are automatically com-pressed using gzip.

25. Data Engineer decided to call the public REST endpoints to load data and retrieve load history re-ports.

Which of the following REST endpoints and a Snowflake Information Schema table function for viewing your load history can be used by her? [Select All that apply]

REST endpoints: loadinsertReport or HistoryScan

Information Schema table function: COPY_HISTORY

Account Usage view:COPY_HISTORY

REST endpoints: insertReport or loadHistoryScan

Information Schema table function: LOAD_HISTORY

26. For the most efficient and cost-effective Data load experience, Data Engineer needs to inconsiderate which of the following considerations?

Split larger files into a greater number of smaller files, maximize the processing over-head for each file. (Correct)

Enabling the STRIP_OUTER_ARRAY file format option for the COPY INTO <table> command to remove the outer array structure and load the records into separate table rows.

Amazon Kinesis Firehose can be convenient way to aggregate and batch data files which also allows defining both the desired file size, called the buffer size, and the wait interval after which a new file is sent, called the buffer interval.

When preparing your delimited text (CSV) files for loading, the number of columns in each row should be consistent.

if the “null” values in your files indicate missing values and have no other special meaning, Snowflake recommend setting the file format option STRIP_NULL_VALUES to TRUE when loading the semi-structured data file.

27. In efforts to recover the dropped child tables within schema named SCV_SCHEMA by Data Engineer, She found that DATA_RETENTION_TIME_IN_DAYS parameter set with value 45 days at Schema level & the data retention period for child tables explicitly set at 85 days.

What will happen when she will try to run undrop table command on Child tables to recover them on the 50th day assuming SCV_SCHEMA is already dropped on 45th day?

To honor the data retention period for child tables, She will ab able to recover the child tables on 50th day as DATA_RETENTION_TIME_IN_DAYS is explicitly set with higher retention value.

When a schema is already dropped, the data retention period for child tables, if explicitly set to be different from the retention of the schema, is not honoured. So UNDROP command will fail to run on 50th day for Child tables recovery.

Child tables can be recovered using Fail-Safe SQL commands.

Data Engineer needs to first recover the Schema & then Child tables will automatically be recovered irrespective of Retention Inheritance.

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

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

The table is not clustered.

The table is frequently queried on columns other than the primary cluster key.

Search Query uses Equality predicates (for example, <column_name> = <constant>) OR Predicates that use I

Search Query uses Sort Operations.

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

Page 4 of 4

31. Which is the non-supportable JavaScript UDF data types?

Integers

String

Binary

Double

32. Avoid Using the <?> Role to Create Objects

Which System defined or Custom Role She is mentioning?

SYSADMIN

SECURITYADMIN

CUSTOM Role

USERADMIN

ACCOUNTADMIN

33. To view/monitor the clustering metadata for a table, Snowflake provides which of the following system functions?

SYSTEM$CLUSTERING_DEPTH_KEY

SYSTEM$CLUSTERING_KEY_INFORMATION (including clustering depth)

SYSTEM$CLUSTERING_DEPTH

SYSTEM$CLUSTERING_INFORMATION (including clustering depth)

34. Which Role inherits the privileges of the USERADMIN role via the system role hierarchy?

SYSADMIN

SECURITYADMIN

PUBLIC

CUSTOM ROLE

35. You can execute zero, one, or more transactions inside a stored procedure?

TRUE

FALSE

36. While creating even Secure UDF, snowflake recommended to use randomized identifiers (e.g. generated by UUID_STRING) instead of sequence-generated values?

TRUE (Correct)

FALSE

37. Changing the retention period for your account or individual objects changes the value for all lower-level objects that do not have a retention period explicitly set?

TRUE

FALSE

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

Facebook Twitter LinkedIn Google + Email