Snowflake SnowPro Advanced: Data Scientist Certification 認定 DSA-C03 試験問題:

1. A retail company is using Snowflake to store transaction data'. They want to create a derived feature called 'customer _ recency' to represent the number of days since a customer's last purchase. The transactions table 'TRANSACTIONS has columns 'customer_id' (INT) and 'transaction_date' (DATE). Which of the following SQL queries is the MOST efficient and scalable way to derive this feature as a materialized view in Snowflake?

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

2. A data scientist is tasked with building a predictive maintenance model for industrial equipment. The data is collected from IoT sensors and stored in Snowflake. The raw sensor data is voluminous and contains noise, outliers, and missing values. Which of the following code snippets, executed within a Snowflake environment, demonstrates the MOST efficient and robust approach to cleaning and transforming this sensor data during the data collection phase, specifically addressing outlier removal and missing value imputation using robust statistics? Assume necessary libraries like numpy and pandas are available via Snowpark.

A)

B)

C)

D)

E)

3. You've built a customer churn prediction model in Snowflake, and are using the AUC as your primary performance metric. You notice that your model consistently performs well (AUC > 0.85) on your validation set but significantly worse (AUC < 0.7) in production. What are the possible reasons for this discrepancy? (Select all that apply)

A) Your training and validation sets are not representative of the real-world production data due to sampling bias.
B) The production environment has significantly more missing data compared to the training and validation environments.
C) The AUC metric is inherently unreliable and should not be used for model evaluation.
D) There's a temporal bias: the customer behavior patterns have changed since the training data was collected.
E) Your model is overfitting to the validation data. This causes to give high performance on validation set but less accurate in the real world.

4. You are training a regression model to predict house prices using a Snowflake dataset. The dataset contains various features, including 'number of_bedrooms', , and You want to use time-based partitioning for your training, validation, and holdout sets. However, you also need to ensure that the dataset is properly shuffled within each time partition to mitigate potential bias introduced by the order of data entry. Which of the following strategies is MOST EFFECTIVE and EFFICIENT for partitioning your data into train, validation, and holdout sets in Snowflake, while also ensuring random shuffling within each partition, and addressing potential data leakage issues?

A) Use Snowflake's SAMPLE clause with a 'REPEATABLE seed for each split (train, validation, holdout), filtering by 'sale_date'. Add an 'ORDER BY RANDOM()' clause within each 'SAMPLE query to shuffle the data within each split. This approach does not guarantee non-overlapping sets and can introduce sampling bias.
B) Create separate views for train, validation, and holdout sets, filtering by 'sale_date' . Shuffle the entire dataset using 'ORDER BY RANDOM()' before creating the views to ensure randomness across all sets. This does not address shuffling within parition.
C) Create a new column 'split_group' using a CASE statement based on 'sale_date' to assign each row to 'train', 'validation', or 'holdout'. Calculate a random number within each 'split_group' by using OVER (PARTITION BY split_group ORDER BY RANDOM())'. Then create temporary tables for each split using 'CREATE TABLE AS SELECT FROM WHERE split_group = QUALIFY ROW NUMBER() OVER (ORDER BY RANDOM()) (SELECT COUNT( ) FROM transactions WHERE split_group -- ...) (respective split percentage);'
D) Create a new column 'split_group' using a CASE statement based on 'sale_date' to assign each row to 'train', 'validation', or 'holdout'. Then, create temporary tables for each split using 'CREATE TABLE AS SELECT FROM WHERE split_group = ORDER BY RANDOM()'. This can be very slow because of global RANDOM sort and leakage issues with using full dataset for randomness.
E) Create a user-defined function (UDF) in Python that takes a 'sale_date' as input and returns either 'train', 'validation', or 'holdout' based on pre-defined date ranges. Apply this UDF to each row, creating a 'split_group' column. Then, create temporary tables for each split using 'CREATE TABLE AS SELECT ... FROM . WHERE split_group = ... ORDER BY RANDOM()'. UDF overhead and global RANDOM sort make it very slow.

5. A data scientist is building a linear regression model in Snowflake to predict customer churn based on structured data stored in a table named 'CUSTOMER DATA'. The table includes features like 'CUSTOMER D', 'AGE, 'TENURE MONTHS', 'NUM PRODUCTS', and 'AVG MONTHLY SPEND'. The target variable is 'CHURNED' (1 for churned, 0 for active). After building the model, the data scientist wants to evaluate its performance using Mean Squared Error (MSE) on a held-out test set. Which of the following SQL queries, executed within Snowflake's stored procedure framework, is the MOST efficient and accurate way to calculate the MSE for the linear regression model predictions against the actual 'CHURNED values in the 'CUSTOMER DATA TEST table, assuming the linear regression model is named 'churn _ model' and the predicted values are generated by the MODEL APPLY() function?

A)

B)

C)

D)

E)

質問と回答：