Snowflake SnowPro Advanced: Data Scientist Certification Sample Questions:

1. You are building a time-series forecasting model in Snowflake to predict the hourly energy consumption of a building. You have historical data with timestamps and corresponding energy consumption values. You've noticed significant daily seasonality and a weaker weekly seasonality. Which of the following techniques or approaches would be most appropriate for capturing both seasonality patterns within a supervised learning framework using Snowflake?

A) Using Fourier terms (sine and cosine waves) with frequencies corresponding to daily and weekly cycles as features in a regression model.
B) Using a simple moving average to smooth the data before applying a linear regression model.
C) Decomposing the time series using STL (Seasonal-Trend decomposition using Loess) and building separate models for the trend and seasonal components, then combining the predictions.
D) Creating lagged features (e.g., energy consumption from the previous hour, the same hour yesterday, and the same hour last week) and using these features as input to a regression model (e.g., Random Forest or Gradient Boosting).
E) Applying exponential smoothing directly to the original time series without feature engineering.

2. You are tasked with training a complex machine learning model using scikit-learn and need to leverage Snowflake's data for training outside of Snowflake using an external function. The training data resides in a Snowflake table named 'CUSTOMER DATA'. Due to data governance policies, you must ensure minimal data movement and secure communication. You choose to implement the external function using AWS Lambda'. Which of the following steps are crucial to achieve secure and efficient model training outside of Snowflake?

A) Create an external function in Snowflake that accepts a JSON payload containing the necessary parameters for model training, such as features to use and model hyperparameters. This function will call the API integration to invoke the Lambda function.
B) Create an API integration object in Snowflake that points to your AWS API Gateway endpoint, configured to invoke the Lambda function. This API integration must use a service principal and access roles for secure authentication.
C) Utilize Snowflake's data masking policies on the table to anonymize sensitive information before sending it to the external function for training. This ensures data privacy and compliance with regulations.
D) Grant usage privilege on the API integration object to the role that will be calling the external function, ensuring only authorized users can trigger the model training.
E) In the Lambda function, establish a direct connection to the Snowflake database using the Snowflake JDBC driver and Snowflake user credentials stored in the Lambda environment variables. This allows the Lambda function to directly query the 'CUSTOMER DATA' table.

3. You are building a fraud detection model using transaction data stored in Snowflake. The dataset includes features like transaction amount, merchant category, location, and time. Due to regulatory requirements, you need to ensure personally identifiable information (PII) is handled securely and compliantly during the data collection and preprocessing phases. Which of the following combinations of Snowflake features and techniques would be MOST suitable for achieving this goal?

A) Apply differential privacy techniques on aggregated data derived from the transaction data, before using it for model training. Combine this with Snowflake's row access policies to restrict access to sensitive transaction records based on user roles and data attributes.
B) Use Snowflake's data sharing capabilities to share the transaction data with a third-party machine learning platform for model development, without any PII masking or redaction.
C) Use Snowflake's masking policies to redact PII columns before any data is accessed for model training. Ensure role-based access control is configured so that only authorized personnel can access the unmasked data for specific purposes.
D) Create a view that selects only the non-PII columns for model training. Grant access to this view to the data science team.
E) Encrypt the entire database containing the transaction data to protect PII from unauthorized access.

4. You are developing a fraud detection model in Snowflake using Snowpark Python. You've iterated through multiple versions of the model, each with different feature sets and algorithms. To ensure reproducibility and easy rollback in case of performance degradation, how should you implement model versioning within your Snowflake environment, focusing on the lifecycle step of Deployment & Monitoring?

A) Only maintain the current model version. If any problems arise, retrain a new model and redeploy it to replace the faulty one.
B) Store the trained models directly in external cloud storage (e.g., AWS S3, Azure Blob Storage) with explicit versioning enabled on the storage layer, and update Snowflake metadata (e.g., in a table) to point to the current model version. Use a UDF to load the correct model version.
C) Store each model version as a separate Snowflake table, containing serialized model objects and metadata like training date, feature set, and performance metrics. Use views to point to the 'active' version.
D) Implement a custom versioning system using Snowflake stored procedures that track model versions and automatically deploy the latest model by overwriting the existing one. The prior version gets deleted.
E) Utilize Snowflake's Time Travel feature to revert to previous versions of the model artifact stored in a Snowflake stage.

5. You are evaluating a binary classification model's performance using the Area Under the ROC Curve (AUC). You have the following predictions and actual values. What steps can you take to reliably calculate this in Snowflake, and which snippet represents a crucial part of that calculation? (Assume tables 'predictions' with columns 'predicted_probability' (FLOAT) and 'actual_value' (BOOLEAN); TRUE indicates positive class, FALSE indicates negative class). Which of the below code snippet should be used to calculate the 'True positive Rate' and 'False positive Rate' for different thresholds

A) Export the 'predicted_probability' and 'actual_value' columns to a local Python environment and calculate the AUC using scikit-learn.
B) Calculate AUC directly within a Snowpark Python UDF using scikit-learn's function. This avoids data transfer overhead, making it highly efficient for large datasets. No further SQL is needed beyond querying the predictions data.

C) Using only SQL, Create a temporary table with calculated True Positive Rate (TPR) and False Positive Rate (FPR) at different probability thresholds. Then, approximate the AUC using the trapezoidal rule.

D) The best way to calculate AUC is to randomly guess the probabilities and see how it performs.
E) The AUC cannot be reliably calculated within Snowflake due to limitations in SQL functionality for statistical analysis.

Solutions: