Learn with Snowflake : DEA-C02 training material for 100% pass

A data engineer is using Snowpark Python to build a data pipeline. They need to define a UDF that uses a pre-trained machine learning model stored as a file in a Snowflake stage. The UDF should receive batches of data for scoring. Which of the following is the MOST efficient way to implement this, minimizing data transfer and execution time?

• A. Use '@vectorized' decorator from Snowpark to process each batch of data passed to the UDF and load the model inside it. Specify the appropriate data types in the decorator.
• B. Create a UDF with gudf(packages=['snowflake-snowpark-python', 'scikit-learn'], input_types=[ArrayType(StringType())], return_type=FloatType(), replace=True, is_permanent=True, and load the model within the UDF's initialization using 'session.file.get' .
• C. Use 'session.read.parquet' to load the model file directly into a Snowpark DataFrame and then use 'DataFrame.foreach' to process each row.
• D. Load the model from the stage into a DataFrame, then use 'df.mapPartitionS to apply the model to each partition.
• E. Create a UDF that reads the model from the stage for each row that is passed to it using 'session.file.get' inside the UDF's execution logic.

You're using Snowpark Python to transform data in a Snowflake table called 'employee_data' which includes columns , 'department, 'salary' , and 'performance_rating'. You need to identify the top 3 highest-paid employees within each department based on their salary, but only for departments where the average performance rating is above 4.0. Which of the following approaches using Snowpark efficiently combines window functions, filtering, and aggregations to achieve this?

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

You are building a data pipeline to ingest clickstream data into Snowflake. The raw data is landed in a stage and you are using a Stream on this stage to track new files. The data is then transformed and loaded into a target table 'CLICKSTREAM DATA. However, you notice that sometimes the same files are being processed multiple times, leading to duplicate records in 'CLICKSTREAM DATA. You are using the 'SYSTEM$STREAM HAS DATA' function to check if the stream has data before processing. What are the possible reasons this might be happening, and how can you prevent it? (Select all that apply)

• A. The transformation process is not idempotent. Even with the same input files, it produces different outputs each time it runs.
• B. The stream offset is not being advanced correctly after processing the files. Ensure that the files are consumed completely and a DML operation is performed to acknowledge consumption.
• C. The COPY INTO command used to load the files into Snowflake has the 'ON ERROR = CONTINUE option set, allowing it to skip corrupted files, causing subsequent processing to pick them up again.
• D. The 'SYSTEM$STREAM HAS DATA' function is unreliable and should not be used for production data pipelines. Use 'COUNT( on the stream instead.
• E. The auto-ingest notification integration is configured incorrectly, causing duplicate notifications to be sent for the same files. This is particularly applicable when using cloud storage event triggers.

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 INFORMATION_SCHEMA views and Account Usage views
• B. Snowflake Secure Data Sharing
• C. Snowflake Cloning
• D. Snowflake's Resource Monitors
• E. Snowflake Data Masking policies

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