Prepare and test your skills
Prepare and test your skills
Free sample question, answered and explained below. The Associate Data Practitioner practice bank has 1,156 more like it. Try 10 free: instant score, no account.
A data analyst at a retail company wants to build a machine learning model directly in BigQuery to predict the continuous numerical dollar amount (total_spend) that each customer will spend over the next quarter.
The training data resides in a BigQuery table named ecommerce.customer_features and contains columns for age, country, previous_purchases, and total_spend.
Which SQL statement correctly creates and trains this model?
ecommerce.spend_model OPTIONS(model_type='kmeans', num_clusters=4) AS SELECT age, country, previous_purchases, total_spend FROM ecommerce.customer_features;K-means clustering (model_type='kmeans') is an unsupervised machine learning algorithm used for data segmentation and pattern discovery. It partitions observations into a specified number of clusters (num_clusters) based on feature similarity without using any target label.
total_spend.total_spend into the feature list of a K-means query causes it to be treated as an input attribute for grouping, not as a target variable to forecast.num_clusters parameter in OPTIONS().input_label_cols.The scenario stated: "You want to segment your customer base into 4 distinct behavioral groups based on demographic and purchasing attributes without targeting a predefined label."
ecommerce.spend_model OPTIONS(model_type='logistic_reg', input_label_cols=['total_spend']) AS SELECT age, country, previous_purchases, total_spend FROM ecommerce.customer_features;Logistic regression (model_type='logistic_reg') is a supervised learning algorithm designed specifically for classification tasks. It maps input features to discrete category probabilities using logistic/softmax functions to predict categorical classes rather than continuous numbers.
total_spend.The scenario required classifying customers into discrete categories or predicting a binary outcome, such as: "Train a model to predict whether a customer will renew their annual subscription (TRUE or FALSE) based on historical usage features."
ecommerce.spend_model SET TARGET = 'total_spend' USING 'linear_reg' AS SELECT age, country, previous_purchases, total_spend FROM ecommerce.customer_features;This option uses invalid, non-existent SQL syntax (TRAIN MODEL, SET TARGET, and USING) that is not recognized by BigQuery ML or GoogleSQL standard syntax.
TRAIN MODEL statement. Model creation and training are always initiated using CREATE MODEL or CREATE OR REPLACE MODEL.SET TARGET or USING clauses to configure model architectures. Model types and target labels are defined inside the OPTIONS(...) block using key-value pairs (e.g., model_type='linear_reg', input_label_cols=['total_spend']).CREATE OR REPLACE MODEL `project.dataset.model_name`
OPTIONS(
model_type = 'linear_reg',
input_label_cols = ['target_column']
)
AS
SELECT ...
FROM `project.dataset.table_name`;
This option would not be correct in any standard BigQuery ML scenario because TRAIN MODEL is not valid GoogleSQL syntax.
ecommerce.spend_model OPTIONS(model_type='linear_reg', input_label_cols=['total_spend']) AS SELECT age, country, previous_purchases, total_spend FROM ecommerce.customer_features;Linear regression (model_type='linear_reg') is a supervised machine learning algorithm used to model the relationship between one or more independent input features and a continuous numerical target (label). In BigQuery ML, you train built-in models using standard SQL through the CREATE MODEL (or CREATE OR REPLACE MODEL) statement.
total_spend), which is a real-valued, continuous numerical metric. Setting model_type='linear_reg' creates a linear regression model optimized for continuous target variables.input_label_cols=['total_spend'] parameter explicitly tells BigQuery ML which column in the query output serves as the training label (target) that the model should learn to predict.AS SELECT ... query provides both the training features (age, country, previous_purchases) and the ground truth label (total_spend) directly from the table.CREATE OR REPLACE MODEL ... OPTIONS(...) AS SELECT structure native to BigQuery ML.CREATE MODEL clause: Defines the destination dataset and model name within BigQuery.OPTIONS() clause: Configures model hyperparameters, algorithm selection (model_type), and structural requirements such as input_label_cols.AS SELECT query: Defines the training dataset, including both input feature columns and target label column(s).country.This statement allows analysts to train and store machine learning models directly inside Google Cloud's data warehouse without extracting data to external environments or writing Python/R code. It accurately defines the correct model family and syntax required for continuous numerical forecasting.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated