A data engineer is responsible for a Snowflake data pipeline that ingests data from multiple external sources, transforms it, and loads it into a data warehouse. The engineer needs to implement a notification system to alert them when specific data quality issues occur, such as data duplication exceeding a threshold or a sudden drop in data volume. Which approach offers the MOST flexible and scalable solution for implementing these notifications?

• A. Rely solely on Snowflake's Data Sharing feature to share the data with a data quality team who will manually review the data and report any issues.
• B. Develop custom SQL scripts to periodically query the data for quality issues and send email notifications using Snowflake's stored procedures and the 'EMAIL' external function.
• C. Create a series of Snowflake Tasks that execute SQL queries to check for data quality issues. If an issue is detected, the task triggers an external function to send a notification to a messaging service (e.g., AWS SNS, Azure Event Grid).
• D. Implement a data quality monitoring tool that integrates with Snowflake via JDBC/ODBC and uses its own rules engine and notification system to detect and alert on data quality issues.
• E. Use Snowflake's built-in resource monitors to track data volume and configure alerts based on predefined thresholds. This approach is simple but limited in its ability to detect complex data quality issues.

설명: (ExamPassdump 회원만 볼 수 있음)

You are tasked with designing a data pipeline that ingests JSON data from an external stage (AWS S3). The JSON files contain records for various product types, each having a different set of attributes. Some product types might have attributes that are not present in other types. You want to create a single Snowflake table that can accommodate all product types without defining a rigid schema upfront and also be queryable efficiently. Which of the following approaches, combining external tables, schema evolution and querying, would be MOST effective? (Choose two)

• A. Create a separate external table for each product type, defining the schema for each table based on the attributes present in the corresponding JSON files.
• B. Create a stored procedure that dynamically infers the schema from the JSON files and creates a new Snowflake table based on the inferred schema.
• C. Create a single external table with a VARIANT column to store the entire JSON record for each product. Use LATERAL FLATTEN to extract specific attributes during querying.
• D. Create a single external table with a VARIANT column and use the 'VALIDATE function to identify and handle schema inconsistencies during data loading.
• E. Load all the data into a raw Snowflake internal table. Use dynamic SQL to infer distinct product types and create views on top of the raw table for each product type.

설명: (ExamPassdump 회원만 볼 수 있음)

You are developing a Snowpark Python stored procedure that performs complex data transformations on a large dataset stored in a Snowflake table named 'RAW SALES'. The procedure needs to efficiently handle data skew and leverage Snowflake's distributed processing capabilities. You have the following code snippet:

Which of the following strategies would be MOST effective to optimize the performance of this Snowpark stored procedure, specifically addressing potential data skew in the 'product id' column, assuming 'product_id' is known to cause uneven data distribution across Snowflake's micro-partitions?

• A. Use the 'pandas' API within the Snowpark stored procedure to perform the transformation, as 'pandas' automatically optimizes for data skew.
• B. Increase the warehouse size significantly to compensate for the data skew and improve overall processing speed without modifying the partitioning strategy.
• C. Utilize Snowflake's automatic clustering on the 'TRANSFORMED_SALES table by specifying 'CLUSTER BY when creating or altering the table to ensure future data is efficiently accessed.
• D. Combine salting with repartitioning by adding a random number to the 'product_id' before repartitioning, then removing the salt after the transformation to break up the skew. Then, enable automatic clustering on the 'TRANSFORMED SALES' table.
• E. Implement a custom partitioning strategy using before the transformation logic to redistribute data evenly across the cluster.

설명: (ExamPassdump 회원만 볼 수 있음)

A data engineer is tasked with optimizing query performance on a Snowflake table named 'SALES DATA, which currently has no clustering key defined. The table contains 'SALE (unique identifier), 'SALE DATE, 'PRODUCT CATEGORY, and 'SALE AMOUNT. The business analysts frequently run queries filtering on 'SALE DATE and then aggregating by 'PRODUCT CATEGORY'. Choosing the right clustering keys for the SALES DATA table is crucial for minimizing disk 1/0 and enhancing query speed. Which of the following clustering key strategies would be MOST effective for the specified query patterns, considering both performance and the potential impact on data loading and DML operations?

• A. Clustering on followed by 'SALE_DATE'.
• B. Clustering on 'SALE DATE followed by 'PRODUCT CATEGORY.
• C. Clustering only on PRODUCT_CATEGORY.
• D. Clustering only on 'SALE DATE
• E. Creating separate tables for each 'PRODUCT CATEGORY.

설명: (ExamPassdump 회원만 볼 수 있음)

You are troubleshooting a slow-running query that joins a large fact table 'SALES DATA' (100 billion rows) with a smaller dimension table 'CUSTOMER DIM' (1 million rows) on 'CUSTOMER ID. Initial analysis shows that the query is spending significant time in the join operation. You suspect the issue lies with the join strategy being used by Snowflake. Which of the following actions are MOST likely to improve query performance and optimize the join?

• A. Ensure both 'SALES DATA' and 'CUSTOMER DIM' are clustered on 'CUSTOMER ID.
• B. Ensure that the 'CUSTOMER_ID column in both tables has compatible datatypes and that no implicit type conversions are happening during the join. Also check cardinality of 'CUSTOMER_ID in the SALES DATA table.
• C. Increase the virtual warehouse size and monitor for spillover to local disk. If spilling occurs, further increase the warehouse size.
• D. Analyze the query profile in Snowflake's web UI and identify if a broadcast join is occurring. If so, consider increasing session parameter (within limits) or re-designing the query to avoid the broadcast join.
• E. Convert the query to use a LATERAL FLATTEN function to pre-process the 'CUSTOMER_DIW table before the join.

설명: (ExamPassdump 회원만 볼 수 있음)