Study with DEA-C02 most valid questions & verified answers

Snowflake DEA-C02 Exam Overview:

Snowflake DEA-C02 Exam Syllabus Topics:

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

1. You have created a JavaScript UDF named 'calculate discount' in Snowflake that takes two arguments: 'product_price' (NUMBER) and 'discount_percentage' (NUMBER). The UDF calculates the discounted price using the formula: 'product_price (1 - discount_percentage / 100)'. However, when you call the UDF with certain input values, you are encountering unexpected results, specifically with very large or very small numbers due to JavaScript's number precision limitations. Which of the following strategies can you implement to mitigate this issue and ensure accurate calculations within your JavaScript UDF?

A) Avoid large or small number and stick to the limited range of input values.
B) Convert the input numbers to strings within the JavaScript UDF before performing the calculation.
C) Utilize a JavaScript library specifically designed for handling arbitrary-precision arithmetic, such as 'Big.js' or 'Decimal.jS , within the UDF.
D) Use JavaScript's 'toFixed(V method to round the result to a fixed number of decimal places.
E) Cast input arguments and the result to 'FLOAT within the UDF.

2. A data warehousing team is experiencing inconsistent query performance on a large fact table C SALES FACT) that is updated daily. Some queries involving complex joins and aggregations take significantly longer to execute than others, even when run with the same virtual warehouse size. You suspect that the query result cache is not being effectively utilized due to variations in query syntax and the dynamic nature of the data'. Which of the following strategies could you implement to maximize the effectiveness of the query result cache and improve query performance consistency? Assume virtual warehouse size is large and the data is skewed across days.

A) Optimize the 'SALES_FACT table by clustering it on the most frequently used filter columns and enabling automatic clustering. This will improve data locality and reduce the amount of data that needs to be scanned.
B) Create a separate virtual warehouse specifically for running these queries. This will isolate the cache and prevent it from being invalidated by other queries.
C) Implement a data masking policy on the 'SALES_FACT table. Data masking will reduce the size of the data that needs to be cached, improving cache utilization.
D) Use stored procedures with parameters to encapsulate the queries. This will ensure that the query syntax is consistent, regardless of the specific parameters used.
E) Implement query tagging to standardize query syntax. By applying consistent tags to queries, you can ensure that similar queries are recognized as identical and reuse cached results.

3. You are tasked with calculating the daily moving average of sales for each product category in your Snowflake data warehouse using Snowpark Python. You need to handle cases where there might be missing sales data for certain days. You have the following sales data available in a table named with columns: (DATE), (VARCHAR), and 'sales_amount' (NUMBER). Which of the following Snowpark Python code snippets correctly calculates the 7-day moving average of sales amount per product category, filling in missing sales days with 0, and handles potential division by zero?

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

4. You are managing a Snowflake environment where data retention is set to the default 1 day for all databases and tables. You need to clone a production table, 'CUSTOMER DATA, to a development environment to test some complex transformations. However, after cloning, you realize that the original 'CUSTOMER DATA' table in production was accidentally dropped 2 days ago. Which of the following statements accurately describe the situation and what can be done, if anything?

A) While the source 'CUSTOMER DATA' is dropped and outside the retention period, the clone created will remain available and fully functional for querying. It is an independent copy. The clone is usable indefinitely, within the limits of your Snowflake account storage.
B) The cloned 'CUSTOMER DATA' table in the development environment will inherit the data retention period from the original 'CUSTOMER DATA' table, so it will also be inaccessible after 1 day if dropped in development.
C) The cloned 'CUSTOMER DATA' table in the development environment will remain accessible because the clone operation created a point-in-time snapshot.
D) The cloned 'CUSTOMER DATA' table in the development environment will no longer be accessible because the source table has been dropped and the data retention period has expired. The clone is effectively useless.
E) The cloned 'CUSTOMER DATA' table will only remain accessible if the source database was also cloned at the same time. If only the table was cloned, the cloned table is unusable after 1 day.

5. You have a base table 'ORDERS' with columns 'ORDER ID, 'CUSTOMER D', 'ORDER DATE, and 'ORDER AMOUNT'. You need to create a view that aggregates the total order amount per customer per month. However, for data governance purposes, you need to ensure that the view only shows data for the last 3 months. What is the MOST efficient and secure way to create this view in Snowflake?

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

Solutions: