Snowflake DEA-C02 Value Pack (Frequently Bought Together)

You are responsible for monitoring data quality in a Snowflake data warehouse. Your team has identified a critical table, 'CUSTOMER DATA, where the 'EMAIL' column is frequently missing or contains invalid entries. You need to implement a solution that automatically detects and flags these anomalies. Which of the following approaches, or combination of approaches, would be MOST effective in proactively monitoring the data quality of the 'EMAIL' column?

• A. Create a Snowflake Task that executes a SQL query to count NULL 'EMAIL' values and invalid 'EMAIL' formats (using regular expressions). The task logs the results to a separate monitoring table and alerts the team if the count exceeds a predefined threshold.
• B. Utilize an external data quality tool (e.g., Great Expectations, Deequ) to define and run data quality checks on the 'CUSTOMER DATA' table, integrating the results back into Snowflake for reporting and alerting.
• C. Schedule a daily full refresh of the 'CUSTOMER DATA' table from the source system, overwriting any potentially corrupted data.
• D. Implement a Streamlit application connected to Snowflake that visualizes the percentage of NULL and invalid 'EMAIL' values over time, allowing the team to manually monitor trends.
• E. Use Snowflake's Data Quality features (if available) to define data quality rules for the 'EMAILS column, specifying acceptable formats and thresholds for missing values. Configure alerts to be triggered when these rules are violated.

You are designing a data governance strategy for a Snowflake data warehouse. One of the key requirements is to track data lineage for sensitive data, specifically Personally Identifiable Information (PII). You need to understand how PII data flows through various transformations and tables. Which Snowflake feature, when combined with appropriate tagging and metadata management practices, can BEST help you achieve this?

• A. Snowflake Secure Data Sharing
• B. Snowflake Cloning
• C. Snowflake Data Masking policies
• D. Snowflake's Resource Monitors
• E. Snowflake INFORMATION_SCHEMA views and Account Usage views

You have a table named 'TRANSACTIONS which is frequently queried by 'TRANSACTION_DATE and 'CUSTOMER ID. You want to define a clustering strategy for this table. You are aware that defining multiple clustering keys is possible. Given the following considerations, which of the following clustering strategies would provide the BEST performance AND minimize reclustering costs, assuming both columns have similar cardinality and are equally used in WHERE clauses? (Assume cost optimization is the most critical factor if performance difference is minimal.)

• A. Create two separate tables: one clustered by 'TRANSACTION DATE and another clustered by 'CUSTOMER ID', and use appropriate views to redirect queries to the correct table.
• B.
  
• C.
  
• D.
  
• E.
  

You have a Snowflake table 'orders_raw' with a VARIANT column named 'order detailS that contains an array of order items represented as JSON objects. Each object has 'item id', 'quantity' , and 'price'. You need to calculate the total revenue for each order. Which SQL statement efficiently flattens the array and calculates the total revenue using LATERAL FLATTEN and appropriate casting?

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

Consider the following Python Snowpark stored procedure designed to enrich customer data:

What are potential drawbacks or limitations of this approach, and what improvements can be made to enhance its scalability and maintainability? (Select all that apply)

• A. The code is perfectly fine and doesn't require any improvements, as it effectively enriches customer data using a UDF and Snowpark DataFrame operations.
• B. Hardcoding the SQL query within the stored procedure makes it inflexible. Using Snowpark DataFrame operations instead would improve maintainability.
• C. Fetching data from an external API inside a UDF can introduce performance bottlenecks and potential network issues. Snowflake recommends minimizing external dependencies within UDFs.
• D. The operation retrieves all rows into the client's memory, which can be inefficient for large datasets. Consider using 'write' operation to store results to table.
• E. Defining a UDF within the stored procedure creates unnecessary overhead as the UDF is recompiled every time the stored procedure runs. It's more efficient to define the UDF separately and call it from the procedure.