Study with Snowflake : DSA-C03 Exam Torrent as your best preparation materials

You're building a model to predict whether a user will click on an ad (binary classification: click or no-click) using Snowflake. The data is structured and includes features like user demographics, ad characteristics, and past user interactions. You've trained a logistic regression model using SNOWFLAKE.ML and are now evaluating its performance. You notice that while the overall accuracy is high (around 95%), the model performs poorly at predicting clicks (low recall for the 'click' class). Which of the following steps could you take to diagnose the issue and improve the model's ability to predict clicks, and how would you implement them using Snowflake SQL? SELECT ALL THAT APPLY.

• A. Generate a confusion matrix using SQL to visualize the model's performance across both classes. Example SQL:
  
• B. Reduce the amount of training data to avoid overfitting. Overfitting is known to produce low recall for the 'click' class.
• C. Calculate precision, recall, F I-score, and AUC for the 'click' class using SQL queries to get a more detailed understanding of the model's performance on the minority class. Example:
  
• D. Implement feature engineering by creating interaction terms or polynomial features from existing features using SQL, to capture potentially non-linear relationships between features and the target variable. Example:
  
• E. Increase the complexity of the model by switching to a non-linear algorithm like Random Forest or Gradient Boosting without performing hyperparameter tuning, as more complex models always perform better.

You are working with a large sales transaction dataset in Snowflake, stored in a table named 'SALES DATA'. This table contains columns such as 'TRANSACTION_ID (unique identifier), 'CUSTOMER_ID', 'PRODUCT_ID, 'TRANSACTION_DATE' , and 'AMOUNT'. Due to a system error, some transactions were duplicated in the table. Your goal is to remove these duplicates efficiently using Snowpark for Python. You want to use the 'window.partitionBy()' and functions. Which of the following code snippets correctly removes duplicates based on all columns, while also creating a new column 'ROW NUM' to indicate the row number within each partition?

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

A data scientist is tasked with creating features for a machine learning model predicting customer churn. They have access to the following data in a Snowflake table named 'CUSTOMER ID, 'DATE, 'ACTIVITY _ TYPE' (e.g., 'login', 'purchase', 'support_ticket'), and 'ACTIVITY VALUE (e.g., amount spent, duration of login). Which of the following feature engineering strategies, leveraging Snowflake's capabilities, could be useful for predicting customer churn? (Select all that apply)

• A. Directly use the ACTIVITY TYPE column as a categorical feature without any transformation or engineering.
• B. Use 'APPROX COUNT DISTINCT to estimate the number of unique product categories purchased by each customer within the last 3 months to create a features.
• C. Calculate the recency, frequency, and monetary value (RFM) for each customer using window functions and aggregate functions.
• D. Create features that capture the trend of customer activity over time (e.g., increasing or decreasing activity) using LACY and 'LEAD' window functions.
• E. Create a feature representing the number of days since the customer's last login using "DATEDIFF and window functions.

You are building a data science pipeline in Snowflake to predict customer churn. The pipeline includes a Python UDF that uses a pre- trained scikit-learn model stored as a binary file in a Snowflake stage. The UDF needs to load this model for prediction. You've encountered an issue where the UDF intermittently fails, seemingly related to resource limits when multiple concurrent queries invoke the UDF. Which of the following strategies would best optimize the UDF for concurrency and resource efficiency, minimizing the risk of failure?

• A. Load the scikit-learn model outside the UDF function in the global scope of the module so that all invocations share the same loaded model instance. Use the 'context.getExecutionContext(Y to track execution, making sure it is thread safe.
• B. Increase the memory allocated to the Snowflake warehouse to accommodate multiple UDF invocations.
• C. Load the scikit-learn model inside the UDF function on every invocation to ensure the latest version is used.
• D. Utilize Snowflake's session-level caching by storing the loaded model in 'session.get('model')' to be reused across multiple UDF calls within the same session. Reload the model if 'session.get('model')' is None.
• E. Implement a global, lazy-loaded cache for the scikit-learn model within the UDF's module. The model is loaded only once during the first invocation and shared across subsequent calls. Protect the loading process with a lock to prevent race conditions in concurrent environments.

A data science team is developing a churn prediction model using Snowpark Python. They have a feature engineering pipeline defined as a series of User Defined Functions (UDFs) that transform raw customer data stored in a Snowflake table named 'CUSTOMER DATA'. Due to the volume of data (billions of rows), they need to optimize UDF execution for performance. Which of the following strategies, when applied individually or in combination, will MOST effectively improve the performance of these UDFs within Snowpark?

• A. Leveraging external functions that call an API endpoint hosted on a cloud provider to perform data transformation. The API endpoint should utilize a serverless architecture.
• B. Using temporary tables to store intermediate results calculated by the UDFs instead of directly writing to the target table.
• C. Utilizing vectorized UDFs with NumPy data types wherever possible and carefully tuning batch sizes. Ensure that the input data is already sorted before passing to the UDF.
• D. Converting Python UDFs to Java UDFs, compiling the Java code, and deploying as a JAR file in Snowflake. Using a larger warehouse size is always the best first option.
• E. Repartitioning the DataFrame by a key that distributes data evenly across nodes before applying the UDFs, using the method and minimizing data shuffling.