Snowflake SnowPro Advanced: Data Scientist Certification Sample Questions:

1. You're developing a fraud detection system in Snowflake. You're using Snowflake Cortex to generate embeddings from transaction descriptions, aiming to cluster similar fraudulent transactions. Which of the following approaches are MOST effective for optimizing the performance and cost of generating embeddings for a large dataset of millions of transaction descriptions using Snowflake Cortex, especially considering the potential cost implications of generating embeddings at scale? Select two options.

A) Generate embeddings on the entire dataset every day to capture all potential fraudulent transactions and ensure the model is always up-to-date.
B) Use a Snowflake Task to incrementally generate embeddings only for new transactions that have been added since the last embedding generation run.
C) Generate embeddings using snowflake-cortex-embed-text function, using the OPENAI embedding model
D) Create a materialized view containing pre-computed embeddings for all transaction descriptions.
E) Implement caching mechanism based on a hash of transaction description if transaction description does not change then no need to recompute the emebeddings again.

2. You are analyzing sensor data collected from industrial machines, which includes temperature readings. You need to identify machines with unusually high temperature variance compared to their peers. You have a table named 'sensor _ readings' with columns 'machine_id', 'timestamp', and 'temperature'. Which of the following SQL queries will help you identify machines with a temperature variance that is significantly higher than the average temperature variance across all machines? Assume 'significantly higher' means more than two standard deviations above the mean variance.

A) Option E
B) Option D
C) Option A
D) Option B
E) Option C

3. You are building a machine learning model to predict customer churn for a telecommunications company. One of the features is 'tariff_plan', which is a string representing different tariff plans (e.g., 'Basic', 'Premium', 'Unlimited'). You need to encode this feature for your model, but you also want to handle potential new tariff plans that might appear in future data'. Which encoding method and Snowflake SQL approach would be MOST suitable to minimize dimensionality and address unseen values effectively, assuming the number of plans is moderately high (around 20-30)?

A) One-Hot Encoding using CREATE OR REPLACE VIEW, handling new values by NULLIF('Unknown', tariff_plan) before encoding, potentially leading to a high number of columns.
B) Label Encoding using a UDF (User-Defined Function) with a predefined mapping, assigning a new integer to unseen values, and storing the mapping in a separate table in Snowflake.
C) Binary Encoding using a UDF to convert each tariff plan into binary code, storing encoded results into snowflake, then splitting the binary representation into separate columns.
D) Hash Encoding (Feature Hashing) using a UDF in Snowflake, with a fixed number of features and a hashing function to map each tariff plan to a feature index, accepting potential collisions. Handle new tariff plans naturally through the hashing function.
E) Target Encoding (Mean Encoding) using Snowflake SQL, calculating the mean churn rate for each tariff plan and using that as the encoded value. Handle unseen values with the global mean churn rate, being mindful of potential target leakage.

4. You are developing a Spark application that needs to read data from a Snowflake table and write the processed data back to a different Snowflake table. Which of the following configurations and code snippets, used in conjunction with the Spark Snowflake Connector, would ensure secure and efficient data transfer, taking into account potential network latency and authentication best practices? Select all that apply.

A) Employ Snowflake's OAuth authentication. Obtain an OAuth token and pass it as a parameter to the Spark Snowflake Connector. You need to also provide 'sfDatabase' and 'sfSchemas properties. Don't set the number of partitions, leaving it to Spark's default behavior.
B)

C) Use Snowflake's Key Pair Authentication. Store the private key securely, and configure 'sflJRL', 'sflJser', 'private_key', 'sfDatabase', and 'sfSchema' properties in the Spark configuration. Ensure the user has appropriate Snowflake privileges. Configure 'numPartitions' parameter based on the scale of data to parallelize read and write operations.
D) Set 'sfURL', 'sfUser', 'sfPassword', 'sfDatabase', and 'sfSchema' properties in the Spark configuration. Use to read data and to write data. Rely on Snowflake's default JDBC driver settings for network optimization.
E) Configure network timeout parameters in the Spark Snowflake Connector options to handle potential network latency, specifically 'networkTimeoutlnMilliSeconds'. Use 'PREACTIONS' and 'POSTACTIONS' to prepare and finalize data loading. Implement robust error handling to retry failed operations.

5. A data scientist at 'Polaris Analytics' wants to estimate the average transaction value of all online purchases made during the Black Friday sale. Due to the enormous volume of data in Snowflake, they decide to use the Central Limit Theorem (CLT). They randomly sample 1000 transactions daily for 30 days and calculate the sample mean for each day. The sample mean values are stored in a Snowflake table named Which of the following SQL queries, assuming the table has a column of 'FLOAT' type, will provide the best estimate of the population mean and its confidence interval using the CLT?

A) Option E
B) Option D
C) Option A
D) Option B
E) Option C

Solutions: