Purchase Snowflake : DEA-C02 Exam Materials and then pass exam easily

Snowflake DEA-C02 Exam Overview:

Snowflake DEA-C02 Exam Syllabus Topics:

Snowflake SnowPro Advanced: Data Engineer (DEA-C02) Sample Questions:

1. You're working on a data transformation pipeline in Snowflake. You need to create a SQL UDF called that accepts the following parameters: 'price' (NUMBER) (NUMBER) (BOOLEAN) The function should calculate the final price after applying the discount. If the customer , an additional 5% discount should be applied on top of the initial discount. Choose all the valid SQL UDF definitions that accurately implement the requirements. Only one Discount Percentage needs to apply to the base price.

A) Option E
B) Option D
C) Option A
D) Option B
E) Option C

2. You are tasked with designing a data sharing solution where data from multiple tables residing in different databases within the same Snowflake account needs to be combined into a single view that is then shared with a consumer account. The view must also implement row-level security based on the consumer's role. Which of the following options represent valid approaches for implementing this solution? Select all that apply.

A) Create a secure view that joins tables from different databases using fully qualified names (e.g., 'DATABASEI .SCHEMAI . TABLET) and implement row-level security using a masking policy based on the CURRENT_ROLE() function.
B) Create a view for each table and then build a final view using 'UNION ALL' to combine data from all the views and implement row-level security with a role based row access policy. Standard views should not be used in data sharing.
C) Create a secure view that joins tables from different databases and implement row-level security using a row access policy based on the CURRENT ROLE() function. Masking policy cannot provide role based access control so will not work.
D) Create a standard view that joins tables from different databases using aliases and implement row-level security using a UDF that checks the consumer's role and filters the data accordingly.
E) Create a standard view with a stored procedure to handle the joins across databases and use EXECUTE AS OWNER to avoid permission issues. This standard view should be shared.

3. You have implemented a row access policy on a 'products' table to restrict access based on the user's group. The policy uses a mapping table 'user_groups' to determine which products a user is allowed to see. After implementing the policy, users are reporting significant performance degradation when querying the 'products' table. What are the MOST likely causes of this performance issue, and what steps can you take to mitigate them? Select all that apply.

A) The row access policy is causing full table scans on the 'products' table. Review the query patterns and consider adding clustering keys to the 'products' table to improve data access patterns.
B) The 'user_groups' table is not properly indexed, causing slow lookups during policy evaluation. Create an index on the 'username' and 'group' columns of the 'user_groups' table.
C) The row access policy is overly complex and contains computationally expensive functions. Simplify the policy logic and avoid using UDFs or complex subqueries within the policy definition.
D) The users do not have sufficient privileges to access the 'user_groups' table. Grant the necessary SELECT privileges to the users on the 'user_groupS table.
E) The row access policy is interfering with Snowflake's data pruning capabilities. Ensure that the policy expression can be evaluated efficiently by Snowflake's query optimizer by using the 'USING' clause of the ROW ACCESS POLICY.

4. You're designing a near real-time data pipeline for clickstream data using Snowpipe Streaming. The data volume is extremely high, with bursts exceeding 1 million events per second. Your team reports intermittent ingestion failures and latency spikes. Considering the constraints of Snowpipe Streaming, which of the following strategies would be MOST effective in mitigating these issues, assuming the data format is optimized and network latency is minimal?

A) Reduce the size of each micro-batch being sent to Snowpipe Streaming to minimize the impact of individual failures.
B) Implement client-side retry logic with exponential backoff and jitter to handle transient errors and avoid overwhelming the service.
C) Implement a message queue (e.g., Kafka) in front of Snowpipe Streaming to buffer incoming events and smooth out the traffic spikes.
D) Switch from Snowpipe Streaming to Classic Snowpipe, as it is more resilient to high data volumes.
E) Increase the number of Snowflake virtual warehouses to handle the increased load.

5. You have a Snowflake table named 'ORDERS clustered on 'ORDER DATE. After a significant data load, you want to evaluate the effectiveness of the clustering. Which of the following SQL queries, using Snowflake system functions, will provide insights into the clustering depth and overlap of micro-partitions in the 'ORDERS' table, specifically helping you identify whether re-clustering is necessary? Assume that the table

A)

B)

C)

D)

E)

Solutions: