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

You have data residing in AWS S3 in Parquet format, which is updated daily with new columns being added occasionally. The data is rarely accessed, but when it is, it needs to be queried using SQL within Snowflake. You want to minimize storage costs within Snowflake while ensuring the data can be queried without requiring manual table schema updates every time a new column is added to the S3 data'. Which approach is MOST suitable?

• A. Option C
• B. Option A
• C. Option D
• D. Option E
• E. Option B

Which of the following statements are true regarding data masking policies in Snowflake? (Select all that apply)

• A. Data masking policies can be applied to both tables and views.
• B. Different masking policies cannot be applied to different columns within the same table.
• C. Once a masking policy is applied to a column, the original data is permanently altered.
• D. The 'CURRENT_ROLE()' function can be used within a masking policy to implement role-based data masking.
• E. Data masking policies are supported on external tables.

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. 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.
• 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. Use stored procedures with parameters to encapsulate the queries. This will ensure that the query syntax is consistent, regardless of the specific parameters used.
• D. 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.
• E. 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.

A financial services company is using Snowflake Streams on a table 'TRANSACTIONS' to capture changes for auditing purposes. The 'TRANSACTIONS' table contains sensitive data, and the auditing team requires the stream to only capture changes to specific columns: 'ACCOUNT ID', 'TRANSACTION DATE', and 'TRANSACTION AMOUNT'. Which of the following approaches is the MOST efficient and secure way to achieve this requirement, ensuring minimal performance impact and data exposure?

• A. Create a standard Stream on the 'TRANSACTIONS table and then filter the results in downstream processing to only include the required columns.
• B. Create a Stream on the 'TRANSACTIONS' table. Periodically truncate stream and reload all data from TRANSACTION table by applying filter while loading.
• C. Create a task that clones the TRANSACTIONS table and a stream on that cloned table, limiting what changes are captured using a WHERE clause on the cloning command.
• D. Create a View that selects only the 'ACCOUNT ID, 'TRANSACTION DATE, and 'TRANSACTION AMOUNT columns and create a Stream on the View.
• E. Create a Stream on the 'TRANSACTIONS' table and use a masking policy on the stream's output to redact the unnecessary columns.

You have a Snowpark DataFrame 'df_products' with columns 'product id', 'category', and 'price'. You need to perform the following transformations in a single, optimized query using Snowpark Python: 1. Filter for products in the 'Electronics' or 'Clothing' categories. 2. Group the filtered data by category. 3. Calculate the average price for each category. 4. Rename the aggregated column to 'average_price'. Which of the following code snippets demonstrates the most efficient way to achieve this?

• A. Option C
• B. Option A
• C. Option D
• D. Option E
• E. Option B