Snowflake DSA-C03 Value Pack (Frequently Bought Together)

You are building a fraud detection model using Snowflake and discover a severe class imbalance (99% legitimate transactions, 1% fraudulent). You plan to use down-sampling to address this. Which of the following strategies and Snowflake SQL commands would be MOST effective and efficient for down-sampling the majority class (legitimate transactions) in a large Snowflake table named 'TRANSACTIONS before training a model using Snowpark?

• A. Create a new table 'BALANCED TRANSACTIONS' by sampling the majority class and combining it with the minority class using UNION ALLS. Use the'SAMPLE clause in Snowflake SQL for efficient sampling:
  
• B. Use Snowpark's function with replacement to create a balanced dataset. This is efficient within the Snowpark environment but might be slower than native SQL sampling for initial data preparation.
• C. Randomly delete rows from the 'TRANSACTIONS table where 'IS FRAUD = FALSE until the class distribution is balanced. This avoids data duplication but can be slow on large tables.
• D. Create a new table 'BALANCED_TRANSACTIONS' by sampling the majority class and combining it with the minority class using 'UNION ALL'. Use the 'SAMPLE clause in Snowflake SQL for efficient sampling:
  
• E. Manually iterate through the 'TRANSACTIONS' table using a Snowpark 'DataFrame' and randomly select rows from the majority class. This is the most efficient approach for very large tables.

Consider you are working on a credit risk scoring model using Snowflake. You have a table 'credit data' with the following schema: 'customer id', 'age', 'income', 'credit_score', 'loan_amount', 'loan_duration', 'defaulted'. You want to create several new features using Snowflake SQL to improve your model. Which combination of the following SQL statements will successfully create features for age groups, income-to-loan ratio, and interaction between credit score and loan amount using SQL in Snowflake? Choose all that apply.

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

You are using a Snowflake Notebook to build a churn prediction model. You have engineered several features, and now you want to visualize the relationship between two key features: and , segmented by the target variable 'churned' (boolean). Your goal is to create an interactive scatter plot that allows you to explore the data points and identify any potential patterns.
Which of the following approaches is most appropriate and efficient for creating this visualization within a Snowflake Notebook?

• A. Write a stored procedure in Snowflake that generates the visualization data in a specific format (e.g., JSON) and then use a JavaScript library within the notebook to render the visualization.
• B. Use the Snowflake Connector for Python to fetch the data, then leverage a Python visualization library like Plotly or Bokeh to generate an interactive plot within the notebook.
• C. Leverage Snowflake's native support for Streamlit within the notebook to create an interactive application. Query the data directly from Snowflake within the Streamlit app and use Streamlit's plotting capabilities for visualization.
• D. Create a static scatter plot using Matplotlib directly within the Snowflake Notebook by converting the data to a Pandas DataFrame. This involves pulling all relevant data into the notebook's environment before plotting.
• E. Use the 'snowflake-connector-python' to pull the data and use 'seaborn' to create static plots.

You are working with a large dataset of sensor readings stored in a Snowflake table. You need to perform several complex feature engineering steps, including calculating rolling statistics (e.g., moving average) over a time window for each sensor. You want to use Snowpark Pandas for this task. However, the dataset is too large to fit into the memory of a single Snowpark Pandas worker. How can you efficiently perform the rolling statistics calculation without exceeding memory limits? Select all options that apply.

• A. Utilize the 'window' function in Snowpark SQL to define a window specification for each sensor and calculate the rolling statistics using SQL aggregate functions within Snowflake. Leverage Snowpark to consume the results of the SQL transformation.
• B. Break the Snowpark DataFrame into smaller chunks using 'sample' and 'unionAll', process each chunk with Snowpark Pandas, and then combine the results.
• C. Use the 'grouped' method in Snowpark DataFrame to group the data by sensor ID, then download each group as a Pandas DataFrame to the client and perform the rolling statistics calculation locally. Then upload back to Snowflake.
• D. Increase the memory allocation for the Snowpark Pandas worker nodes to accommodate the entire dataset.
• E. Explore using Snowpark's Pandas user-defined functions (UDFs) with vectorization to apply custom rolling statistics logic directly within Snowflake. UDFs allow you to use Pandas within Snowflake without needing to bring the entire dataset client-side.

You are developing a real-time fraud detection system using Snowpark and deploying it as a Streamlit application connected to Snowflake. The system ingests transaction data continuously and applies a pre-trained machine learning model (stored as a binary file in Snowflake's internal stage) to score each transaction for fraud. You need to ensure the model loading process is efficient, and you're aiming to optimize performance by only loading the model once when the application starts, not for every single transaction. Which combination of approaches will BEST achieve this in a reliable and efficient manner, considering the Streamlit application's lifecycle and potential concurrency issues?

• A. Load the model outside of the Streamlit application's execution context (e.g., in a separate script) and store it in a global variable. Access this global variable within the Streamlit application. This approach requires careful handling of concurrency.
• B. Load the model within a try-except block and set the Snowpark session as a singleton that will guarantee model loads once for the entire application.
• C. Leverage the 'snowflake.snowpark.Session.read_file' to load the model binary directly into a Snowpark DataFrame and then convert to a Pandas DataFrame. Then, use the 'pickle' library for deserialization.
• D. Use Python's built-in 'threading.Lock' to serialize access to the model loading code and the Snowpark session, preventing concurrent access from multiple Streamlit user sessions. Store the loaded model in a module-level variable.
• E. Use the 'st.cache_data' decorator in Streamlit to cache the loaded model and Snowpark session. Load the model directly from the stage within the cached function. This approach handles concurrency and ensures the model is only loaded once per session.