ExamGecko
Home / Snowflake / DEA-C01 / List of questions
Ask Question

Snowflake DEA-C01 Practice Test - Questions Answers, Page 10

Question list
Search

List of questions

Search

Question 91

Report
Export
Collapse

Data Engineer wants to analyze query performance & looking out for profiling information, He went to Query/Operator Details also called Profile Overview of Query Profile Interface & searching for statistics attributes around I/O. Which of the following information he can't get from there?

Percentage scanned from cache — the percentage of data scanned from the local disk cache.
Percentage scanned from cache — the percentage of data scanned from the local disk cache.
Bytes written — bytes written (e.g. when loading into a table).
Bytes written — bytes written (e.g. when loading into a table).
External bytes scanned — bytes read from an external object, e.g. a stage.
External bytes scanned — bytes read from an external object, e.g. a stage.
Bytes sent over the wireframe — amount of data sent over the wireframe
Bytes sent over the wireframe — amount of data sent over the wireframe
Bytes read from result — bytes read from the result object.
Bytes read from result — bytes read from the result object.
Suggested answer: D

Explanation:

To help you analyze query performance, Query/Operator Details panel also called Profile overview panel provides two classes of profiling information:

· Execution time, broken down into categories

· Detailed statistics

Apart from Option à Bytes sent over the wireframe — amount of data sent over the wireframe , Rest of the Statistics Information provided by Query/Operator details in the Query Profile Inter-face.

To Know More about the Query/Operator Details options , please refer the link:

https://docs.snowflake.com/en/user-guide/ui-query-profile#query-operator-details

asked 23/09/2024
Brian Carlo Hubilla
36 questions

Question 92

Report
Export
Collapse

What are Common Query Problems a Data Engineer can identified using Query Profiler?

"Exploding" Joins i.e Joins resulting due to a "Cartesian product"
"Exploding" Joins i.e Joins resulting due to a "Cartesian product"
Queries Too Large to Fit in Memory
Queries Too Large to Fit in Memory
Inefficient Pruning
Inefficient Pruning
Ineffective Data Sharing
Ineffective Data Sharing
Suggested answer: A, B, C

Explanation:

"Exploding" Joins

One of the common mistakes SQL users make is joining tables without providing a join condition (resulting in a "Cartesian product"), or providing a condition where records from one table match multiple records from another table. For such queries, the Join operator produces significantly (often by orders of magnitude) more tuples than it consumes.

This can be observed by looking at the number of records produced by a Join operator in the profile interface, and typically is also reflected in Join operator consuming a lot of time.

Queries Too Large to Fit in Memory

For some operations (e.g. duplicate elimination for a huge data set), the amount of memory available for the compute resources used to execute the operation might not be sufficient to hold intermediate results. As a result, the query processing engine will start spilling the data to local disk.

If the local disk space is not sufficient, the spilled data is then saved to remote disks.

This spilling can have a profound effect on query performance (especially if remote disk is used for spilling).

Spilling statistics can be checked in Query Profile Interface.

Inefficient Pruning

Snowflake collects rich statistics on data allowing it not to read unnecessary parts of a table based on the query filters. However, for this to have an effect, the data storage order needs to be correlat-ed with the query filter attributes.

The efficiency of pruning can be observed by comparing Partitions scanned and Partitions total statistics in the TableScan operators. If the former is a small fraction of the latter, pruning is efficient. If not, the pruning did not have an effect.

Of course, pruning can only help for queries that actually filter out a significant amount of data. If the pruning statistics do not show data reduction, but there is a Filter operator above TableScan which filters out a number of records, this might signal that a different data organization might be beneficial for this query.

asked 23/09/2024
bijay ghimire
37 questions

Question 93

Report
Export
Collapse

Michael, a Data Engineer Running a Data query to achieve Union of Data sets coming from Multi-ple data sources, later he figured out that Data processing query is taking more time than expected. He started analyzing the Query performance using query profile interface. He discovered & realized that he used UNION when the UNION ALL semantics was sufficient.

Which Extra Data Processing Operator Michael figured out while doing query profile analysis in this case which helps him to identify this performance bottlenecks?

Aggregate
Aggregate
UNION ALL
UNION ALL
Flatten
Flatten
Join
Join
Filter
Filter
Suggested answer: A

Explanation:

In SQL, it is possible to combine two sets of data with either UNION or UNION ALL constructs. The difference between them is that UNION ALL simply concatenates inputs, while UNION does the same, but also performs duplicate elimination.

A common mistake is to use UNION when the UNION ALL semantics are sufficient. These que-ries show in Query Profile as a UnionAll operator with an extra Aggregate operator on top (which performs duplicate elimination).

To Know more about Data Processing Operators, please do refer:

https://docs.snowflake.com/en/user-guide/ui-query-profile#operator-types

asked 23/09/2024
Tuukka Valkeasuo
38 questions

Question 94

Report
Export
Collapse

Jonas, a Lead Performance Engineer,identifed that some of the operation of his query which functionally remove the duplicates from huge data set is spilling the data to remote disk. How can he alleviate spilling to a remote disk for better query performance?

Jonas can recommend using a large warehouse which effectively increase the available memory/local disk space for the operations.
Jonas can recommend using a large warehouse which effectively increase the available memory/local disk space for the operations.
He can Process data in smaller batches to manage workload.
He can Process data in smaller batches to manage workload.
Spilling do not have a profound effect on query performance (especially if remote disk is used for spilling).
Spilling do not have a profound effect on query performance (especially if remote disk is used for spilling).
Data Sharing can be helpful to improve query performance.
Data Sharing can be helpful to improve query performance.
Suggested answer: A, B

Explanation:

For some operations (e.g. duplicate elimination for a huge data set), the amount of memory available for the compute resources used to execute the operation might not be sufficient to hold intermediate results. As a result, the query processing engine will start spilling the data to local disk.

If the local disk space is not sufficient, the spilled data is then saved to remote disks.

This spilling can have a profound effect on query performance (especially if remote disk is used for spilling). To alleviate this, It is recommend that:

· Using a larger warehouse (effectively increasing the available memory/local disk space for the operation), and/or Processing data in smaller batches.

asked 23/09/2024
asd asd
32 questions

Question 95

Report
Export
Collapse

You as Data engineer might want to consider disabling auto-suspend for a warehouse if?

You have a low, fluctuating workload for the warehouse.
You have a low, fluctuating workload for the warehouse.
You have a heavy, steady workload for the warehouse.
You have a heavy, steady workload for the warehouse.
You require the warehouse to be available with delay.
You require the warehouse to be available with delay.
You require the warehouse to be available with no delay or lag time.
You require the warehouse to be available with no delay or lag time.
Suggested answer: B, D

Explanation:

Automating Warehouse Suspension

Data Engineer might want to consider disabling auto-suspend for a warehouse if:

He/She have a heavy, steady workload for the warehouse.

He/She require the warehouse to be available with no delay or lag time. Warehouse provisioning is generally very fast (e.g. 1 or 2 seconds); however, depending on the size of the warehouse and the availability of compute resources to provision, it can take longer.

If he/she chose to disable auto-suspend, He/she must carefully consider the costs associated with running a warehouse continually, even when the warehouse is not processing queries. The costs can be significant, especially for larger warehouses (X-Large, 2X-Large, etc.).

To disable auto-suspend, Engineer must explicitly select Never in the web interface, or specify 0 or NULL in SQL.

asked 23/09/2024
Reatlehile Motaung
25 questions

Question 96

Report
Export
Collapse

Harry using Snowflake Enterprise Edition & decided to scale in/out the Cluster in automatic mode.

He needs to configure some warehouses as multi cluster mode and some among them in Standard mode as per needs.

If Harry is using Snowflake Enterprise Edition (or a higher edition), all his warehouses should be configured as multi-cluster warehouses only.

TRUE
TRUE
FALSE
FALSE
Suggested answer: A

Explanation:

If you are using Snowflake Enterprise Edition (or a higher edition), all your warehouses should be configured as multi-cluster warehouses.

asked 23/09/2024
Anna Fagulova
31 questions

Question 97

Report
Export
Collapse

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]

Become a Premium Member for full access
  Unlock Premium Member

Question 98

Report
Export
Collapse

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

Become a Premium Member for full access
  Unlock Premium Member

Question 99

Report
Export
Collapse

Select the incorrect statement while working with warehouses?

Become a Premium Member for full access
  Unlock Premium Member

Question 100

Report
Export
Collapse

Ira a Data Engineer with TESLA IT systems, looking out to Compare Traditional Partitioning vs Snowflake micro-partitions for one of the Snowflake Project implementations. Which one of the following is incorrect understanding of Ira about Micro Partitioning?

Become a Premium Member for full access
  Unlock Premium Member
Total 130 questions
Go to page: of 13