Question 1

A Data Engineer needs to load JSON output from some software into Snowflake using Snowpipe.
Which recommendations apply to this scenario? (Select THREE)

Accepted Answer

The recommendations that apply to this scenario are:
? Ensure that data files are 100-250 MB (or larger) in size compressed: This recommendation will improve Snowpipe performance by reducing the number of files
that need to be loaded and increasing the parallelism of loading. Smallerfiles can cause performance degradation or errors due to excessive metadata operations
or network latency.
? Verify each value of each unique element stores a single native data type (string or number): This recommendation will improve Snowpipe performance by
avoiding data type conversions or errors when loading JSON data into variant columns. Snowflake supports two native data types for JSON elements: string and
number. If an element has mixed data types across different files or records, such as string and boolean, Snowflake will either convert them to string or raise an
error, depending on the FILE_FORMAT option.
? Create data files that are less than 100 MB and stage them in cloud storage at a sequence greater than once each minute: This recommendation will minimize
Snowpipe costs by reducing the number of notifications that need to be sent to Snowpipe for auto-ingestion. Snowpipe charges for notifications based on the
number of files per notification and the frequency of notifications. By creating smaller files and staging them at a lower frequency, fewer notifications will be
needed.

Question 2

Which use case would be BEST suited for the search optimization service?

Accepted Answer

The use case that would be best suited for the search optimization service is business users who need fast response times using highly selective filters. The
search optimization service is a feature that enables faster queries on tables with high cardinality columns by creating inverted indexes on those columns. High
cardinality columns are columns that have a large number of distinct values, such as customer IDs, product SKUs, or email addresses. Queries that use highly
selective filters on high cardinality columns can benefit from the search optimization service because they can quickly locate the relevant rows without scanning the
entire table. The other options are not best suited for the search optimization service. Option A is incorrect because analysts who need to perform aggregates over
high cardinality columns will not benefit from the search optimization service, as they will still need to scan all the rows that match the filter criteria. Option C is
incorrect because data scientists who seek specific JOIN statements with large volumes of data will not benefit from the search optimization service, as they will
still need to perform join operations that may involve shuffling or sorting data across nodes. Option D is incorrect because data engineers who create clustered
tables with frequent reads against clustering keys will not benefit from the search optimization service, as they already have an efficient way to organize and
access data based on clustering keys.

Question 3

The following chart represents the performance of a virtual warehouse over time:
A DataEngineer notices that the warehouse is queueing queries The warehouse is size X- Smallthe minimum and maximum cluster counts are set to 1 the scaling
policy is set to i and auto-suspend is set to 10 minutes.
How canthe performance be improved?

Accepted Answer

The performance can be improved by increasing the size of the warehouse. The chart shows that the warehouse is queueing queries, which means that there are
more queries than the warehouse can handle at its current size. Increasing the size of the warehouse will increase its processing power and concurrency limit,
which could reduce the queueing time and improve the performance. The other options are not likely to improve the performance significantly. Option A, changing
the cluster settings, will not help unless the minimum and maximum cluster countsare increased to allow for multi-cluster scaling. Option C, changing the scaling
policy to economy, will not help because it will reduce the responsiveness of the warehouse to scale up or down based on demand. Option D, changing autosuspend to a longer time frame, will not help because it will only affect how long the warehouse stays idle before suspending itself.

Question 4

A Data Engineer defines the following masking policy:
….
must be applied to the full_name column in the customer table:
Which query will apply the masking policy on the full_name column?

Accepted Answer

The query that will apply the masking policy on the full_name column is ALTER TABLE customer MODIFY COLUMN full_name SET MASKING POLICY
name_policy;. This query will modify the full_name column and associate it with the name_policy masking policy, which will mask the first and last names of the
customers with asterisks. The other options are incorrect because they do not follow the correct syntax for applying a masking policy on a column. Option B is
incorrect because it uses ADD instead of SET, which is not a valid keyword for modifying a column. Option C is incorrect because it tries to apply the masking
policy on two columns, first_name and last_name, which are not part of the table structure. Option D is incorrect because it uses commas instead of dots to
separate the database, schema, and table names

Question 5

A Data Engineer has created table t1 with datatype VARIANT: create or replace table t1 (cl variant);
The Engineer has loaded the following JSON data set. which has information about 4 laptop models into the table:

/DEA-C01-exam/ (65 Q&As)
The Engineer now wants to query that data set so that results are shown as normal structured data. The result should be 4 rows and 4 columns without the double
quotes surrounding the data elements in the JSON data.
The result should be similar to the use case where the data was selected from a normal
relational table z2 where t2 has string data type columns model id. model, manufacturer, and =iccisi_r.an=. and is queried with the SQL clause select * from t2;
Which select command will produce the correct results?
A)
B)
C)

/DEA-C01-exam/ (65 Q&As)
D)

Accepted Answer

Correct answer: B

Question 6

Which query will show a list of the 20 most recent executions of a specified task kttask, that have been scheduled within the last hour that have ended or are
stillrunning’s.
A)
B)
C)
D)

Accepted Answer

Correct answer: B

Question 7

A Data Engineer enables a result cache at the session level with the following command: ALTER SESSION SET USE CACHED RESULT = TRUE;
The Engineer then runs the following select query twice without delay:
The underlying table does not change between executions What are the results of both runs?

Accepted Answer

The result cache is enabled at the session level, which means that repeated queries will return cached results if there is no change in the underlying data or
session parameters. However, in this case, the result cache is not relevant because the query uses a specific SEED value for sampling, which makes it
deterministic. Therefore, both runs will return the same results regardless of caching.

Question 8

A Data Engineer is evaluating the performance of a query in a development environment.

/DEA-C01-exam/ (65 Q&As)
Based on the Query Profile what are some performance tuning options the Engineer can use? (Select TWO)

Accepted Answer

The performance tuning options that the Engineer can use based on the Query Profile are:
? Add a LIMIT to the ORDER BY If possible: This option will improve performance by reducing the amount of data that needs to be sorted and returned by the
query. The ORDER BY clause requires sorting all rows in the input before returning them, which can be expensive and time-consuming. By adding a LIMIT clause,
the query can return only a subset of rows that satisfy the order criteria, which can reduce sorting time and network transfer time.
? Create indexes to ensure sorted access to data: This option will improve performance by reducing the amount of data that needs to be scanned and filtered by
the query. The query contains several predicates on different columns, such as o_orderdate, o_orderpriority, l_shipmode, etc. By creating indexes on these
columns, the query can leverage sorted access to data and prune unnecessary micro-partitions or rows that do not match the predicates. This can reduce IO time
and processing time.
The other options are not optimal because:
? Use a multi-cluster virtual warehouse with the scaling policy set to standard: This option will not improve performance, as the query is already using a multicluster virtual warehouse with the scaling policy set to standard. The Query Profile shows that the query is using a 2XL warehouse with 4 clusters and a standard
scaling policy, which means that the warehouse can automatically scale up or down based on the load. Changing the warehouse size or the number of clusters will
not affect the performance of this query, as it is already using the optimal resources.
? Increase the max cluster count: This option will not improve performance, as the query is not limited by the max cluster count. The max cluster count is a
parameter that specifies the maximum number of clusters that a multi-cluster virtual warehouse can scale up to. The Query Profile shows that the query is using a
2XL warehouse with 4 clusters and a standard scaling policy, which means that the warehouse can automatically scale up or down based on theload. The default
max cluster count for a 2XL warehouse is 10, which means that the warehouse can scale up to 10 clusters if needed. However, the query does not need more than
4 clusters, as it is not CPU-bound or memory-bound. Increasing the max cluster count will not affect the performance of this query, as it will not use more clusters
than necessary.

Question 9

A Data Engineer has developed a dashboard that will issue the same SQL select clause to Snowflake every 12 hours.
---will Snowflake use the persisted query results from the result cache provided that the underlying data has not changed^

Accepted Answer

Snowflake uses the result cache to store the results of queries that have been executed recently. The result cache is maintained at the account level and is
shared across all sessions and users. The result cache is invalidated when any changes are made to the tables or views referenced by the query. Snowflake also
has a retention policy for the result cache, which determines how long the results are kept in the cache before they are purged. The default retention period for the
result cache is 24 hours, but it can be changed at the account, user, or session level. However, there is a maximum retention period of 14 days for the result
cache, which cannot be exceeded. Therefore, if the underlying data has not changed, Snowflake will use the persisted query results from the result cache for up to
14 days.

Question 10

A company is building a dashboard for thousands of Analysts. The dashboard presents the results of a few summary queries on tables that are regularly updated.
The query conditions vary by tope according to what data each Analyst needs Responsiveness of the dashboard queries is a top priority, and the data cache
should be preserved.
How should the Data Engineer configure the compute resources to support this dashboard?

Accepted Answer

This option is the best way to configure the compute resources to support this dashboard. By assigning all queries to a multi-cluster virtual warehouse set to
maximized mode, the Data Engineer can ensure that there is enough compute capacity to handle thousands of concurrent queries from different analysts. A multicluster virtual warehouse can scale up or down by adding or removing clusters based on the load. A maximized scaling policy ensures that there is always at least
one cluster running and that new clusters are added as soon as possible whenneeded. By monitoring the utilization and performance of the virtual warehouse, the
Data Engineer can determine the smallest suitable number of clusters that can meet the responsiveness requirement and minimize costs.