Skip to content

Associate Data Practitioner · Domain 2: Data Analysis and Presentation · 27% of the exam

Task 2.3: Define, train, evaluate, and use ML models.

ML use cases for BigQuery ML and AutoML, Google LLMs through a remote connection in BigQuery, the steps of an ML project, creating, evaluating and predicting with BigQuery ML, and the Model Registry.

Study it

Sample questions

Try each one before opening the answer. Every option is explained, with the Google Cloud documentation page that proves it.

Question 1 · choose 1

A subscription business keeps a BigQuery table with one row per customer: usage figures, plan details and a `cancelled` column that is TRUE or FALSE for last year's customers. An analyst who knows SQL wants a BigQuery ML model that, for current customers, predicts whether each one will cancel. Which model type fits this use case?

  1. A`KMEANS`
  2. B`ARIMA_PLUS`
  3. C`LOGISTIC_REG`
  4. D`PCA`
Show the answer and why
  • A`KMEANS`

    Incorrect

    K-means is unsupervised clustering for segmentation; it ignores the cancelled label and produces cluster numbers, not a yes or no prediction.

  • B`ARIMA_PLUS`

    Incorrect

    ARIMA_PLUS forecasts a single time series into the future; it does not classify individual customers from their attributes.

  • C`LOGISTIC_REG`

    Correct

    Logistic regression is the BigQuery ML model for binary or multi-class classification, such as whether a customer will make a purchase or, here, cancel.

  • D`PCA`

    Incorrect

    Principal component analysis reduces the number of dimensions in the data; it does not predict a label.

Start from the shape of the problem. A labeled yes or no outcome is supervised classification, which LOGISTIC_REG (or a boosted tree or DNN classifier) handles in BigQuery ML with input_label_cols naming the label. Clustering, forecasting and dimensionality reduction answer other questions.

Question 2 · choose 1

An analyst trained a BigQuery ML linear regression model that predicts each parcel's delivery time in minutes and ran `ML.EVALUATE` on held-out data. The operations manager asks one question: on average, by how many minutes is a prediction off, in either direction? Which output column answers that directly?

  1. A`mean_absolute_error`
  2. B`mean_squared_error`
  3. C`r2_score`
  4. D`roc_auc`
Show the answer and why
  • A`mean_absolute_error`

    Correct

    Mean absolute error averages the size of each error without its sign, in the label's own units, so it reads directly as minutes off on average.

  • B`mean_squared_error`

    Incorrect

    Mean squared error averages squared errors, so it is in squared minutes and weighs large misses heavily; it is not a plain average miss.

  • C`r2_score`

    Incorrect

    The r2 score is a unitless measure of how much of the variance the model explains; it does not give a number of minutes.

  • D`roc_auc`

    Incorrect

    ROC AUC is reported for classification models; ML.EVALUATE does not return it for a linear regression model.

ML.EVALUATE returns different metrics by model type. Regression models report mean absolute error, mean squared error, mean squared log error, median absolute error, r2 score and explained variance; classification models report precision, recall, accuracy, F1 score, log loss and ROC AUC.

Question 3 · choose 1

An analyst wants to use a Gemini model from BigQuery. She created a Cloud resource connection in the same location as her dataset and ran CREATE MODEL catalog.gemini REMOTE WITH CONNECTION `proj.us.gen_conn` OPTIONS (ENDPOINT = 'gemini-3.5-flash-lite'). The statement fails with a permission error that names the service account shown in the connection's details. What should be done?

  1. AGrant the analyst's own user account the Agent Platform User role on the project
  2. BGrant the connection's service account the Agent Platform User role on the project
  3. CGrant the connection's service account the Storage Object Viewer role on the bucket
  4. DGrant the connection's service account the BigQuery Data Viewer role on the dataset
Show the answer and why
  • AGrant the analyst's own user account the Agent Platform User role on the project

    Incorrect

    BigQuery calls the model endpoint through the connection's service account, so a role on the analyst's account does not fix the error that names that service account.

  • BGrant the connection's service account the Agent Platform User role on the project

    Correct

    For a remote model over a Gemini model that is not tuned, the connection's service account needs the Agent Platform User role in the project where the model is created.

  • CGrant the connection's service account the Storage Object Viewer role on the bucket

    Incorrect

    Storage roles let a connection read objects in Cloud Storage for BigLake or object tables; they do not allow calls to a Gemini endpoint.

  • DGrant the connection's service account the BigQuery Data Viewer role on the dataset

    Incorrect

    Reading the dataset is not what fails; the service account lacks the role that lets it use the Agent Platform model endpoint.

A remote model reaches a Gemini Enterprise Agent Platform endpoint through a Cloud resource connection, which has its own service account. That service account needs the Agent Platform User role (the Vertex AI Service Agent role for a tuned model), and the connection must be in the same location as the model's dataset.

Question 4 · choose 1

A store manager trained a BigQuery ML logistic regression model on a table of 420 labeled past promotions, using the default CREATE MODEL options and no data split settings. Running `ML.EVALUATE` on the model with no input data reports excellent precision and recall, but the model does poorly on the first new promotions. What best explains the misleading evaluation, and what should the manager do?

  1. AThe default split held back 20% of the rows, which is too small a sample; raise DATA_SPLIT_EVAL_FRACTION to 0.5
  2. BWith fewer than 500 rows the default split used every row for training, so the metrics describe training data; evaluate on rows the model never saw
  3. CThe metrics were computed at the default 0.5 threshold; pass a lower THRESHOLD to ML.EVALUATE to get realistic precision and recall
  4. DRetrain with DATA_SPLIT_METHOD = 'RANDOM' so that rows are held out at random, then run ML.EVALUATE again with no input data
Show the answer and why
  • AThe default split held back 20% of the rows, which is too small a sample; raise DATA_SPLIT_EVAL_FRACTION to 0.5

    Incorrect

    The 20% evaluation split applies only to tables of 500 to 50,000 rows; with 420 rows nothing was held back at all, so the premise is wrong.

  • BWith fewer than 500 rows the default split used every row for training, so the metrics describe training data; evaluate on rows the model never saw

    Correct

    Under AUTO_SPLIT, input with fewer than 500 rows is used entirely as training data, so ML.EVALUATE without input data measures the model on rows it learned from. A separate evaluation table, or a split with an evaluation fraction set explicitly, gives an honest measure.

  • CThe metrics were computed at the default 0.5 threshold; pass a lower THRESHOLD to ML.EVALUATE to get realistic precision and recall

    Incorrect

    The threshold only moves the trade-off between precision and recall for the same rows; it cannot make metrics computed on training data honest.

  • DRetrain with DATA_SPLIT_METHOD = 'RANDOM' so that rows are held out at random, then run ML.EVALUATE again with no input data

    Incorrect

    Without DATA_SPLIT_EVAL_FRACTION, a RANDOM split divides the data the same way as AUTO_SPLIT, so all 420 rows are still used for training and the metrics stay just as optimistic.

BigQuery ML's default data split, AUTO_SPLIT, depends on the number of rows: under 500 rows all data is used for training, from 500 to 50,000 rows 20% is held out for evaluation, and above 50,000 rows 10,000 rows are held out. On a small table, evaluate the model with ML.EVALUATE on a separate table of rows it never saw, or set the evaluation fraction of the split explicitly; a RANDOM split without one splits the same way as AUTO_SPLIT.

Question 5 · choose 1

Each month an analyst retrains a BigQuery ML churn model with SQL. The ML team wants every monthly model to appear in Model Registry as another version of one registered model, `churn_model`, and wants all earlier versions to stay in the registry so that results can be compared over the year. How should the analyst write each month's CREATE MODEL statement?

  1. ACREATE OR REPLACE MODEL on the same BigQuery model name each month, with MODEL_REGISTRY = 'VERTEX_AI' and VERTEX_AI_MODEL_ID = 'churn_model'
  2. BCREATE MODEL under a new model name each month, with MODEL_REGISTRY = 'VERTEX_AI' and VERTEX_AI_MODEL_VERSION_ALIASES = ['churn_model']
  3. CCREATE MODEL under the same name without registry options, then change the registered model ID each month with ALTER MODEL
  4. DCREATE MODEL under a new model name each month, with MODEL_REGISTRY = 'VERTEX_AI' and VERTEX_AI_MODEL_ID = 'churn_model'
Show the answer and why
  • ACREATE OR REPLACE MODEL on the same BigQuery model name each month, with MODEL_REGISTRY = 'VERTEX_AI' and VERTEX_AI_MODEL_ID = 'churn_model'

    Incorrect

    Replacing a BigQuery ML model that is already registered deletes its registry version and adds the new one, so last month's version would disappear instead of being kept.

  • BCREATE MODEL under a new model name each month, with MODEL_REGISTRY = 'VERTEX_AI' and VERTEX_AI_MODEL_VERSION_ALIASES = ['churn_model']

    Incorrect

    A version alias only labels a model version; which registered model the new version joins is set by VERTEX_AI_MODEL_ID, so the monthly models would not become versions of churn_model.

  • CCREATE MODEL under the same name without registry options, then change the registered model ID each month with ALTER MODEL

    Incorrect

    Once a BigQuery ML model is registered, its VERTEX_AI_MODEL_ID cannot be changed, and each BigQuery ML model can be registered to only one model ID.

  • DCREATE MODEL under a new model name each month, with MODEL_REGISTRY = 'VERTEX_AI' and VERTEX_AI_MODEL_ID = 'churn_model'

    Correct

    Each new BigQuery ML model registered under the same model ID appears as the next version of that registry model, and the earlier versions remain because no registered model is replaced.

The MODEL_REGISTRY option registers a BigQuery ML model in Model Registry when training completes, and VERTEX_AI_MODEL_ID chooses the registry model. New BigQuery ML models registered under the same ID become versions 2, 3 and so on, while VERTEX_AI_MODEL_VERSION_ALIASES only names versions. Recreating a model name that is already registered with CREATE OR REPLACE deletes that version and adds a new one.

Question 6 · choose 1

A remote model over a Gemini model already exists in a BigQuery dataset. An analyst is writing a new query that sends each product's specification sheet with a prompt to the model and returns a short marketing description per row. Which function does Google recommend for this new query?

  1. A`AI.GENERATE_TEXT`
  2. B`ML.GENERATE_TEXT`
  3. C`ML.PREDICT`
  4. D`ML.GENERATE_EMBEDDING`
Show the answer and why
  • A`AI.GENERATE_TEXT`

    Correct

    AI.GENERATE_TEXT sends a prompt per row to the remote model and returns the generated text; Google recommends it for new queries.

  • B`ML.GENERATE_TEXT`

    Incorrect

    ML.GENERATE_TEXT still works, but its page says AI.GENERATE_TEXT offers the same functionality with simpler output column names and is recommended for new queries.

  • C`ML.PREDICT`

    Incorrect

    ML.PREDICT runs inference on models trained or imported into BigQuery ML, such as a regression model; text generation with a Gemini remote model uses the generate text function.

  • D`ML.GENERATE_EMBEDDING`

    Incorrect

    An embedding function returns numeric vectors that represent the text, used for similarity search, not a written description.

With a remote model over a Gemini Enterprise Agent Platform model, BigQuery can call a Google LLM row by row from SQL. AI.GENERATE_TEXT is the current function for generating text; ML.GENERATE_TEXT offers the same functionality with longer output column names and is no longer the recommendation for new queries.

Question 7 · choose 1

A binary logistic regression model was trained with `input_label_cols = ['churned']`. An analyst runs `ML.PREDICT` on a table of current customers and wants to sort them by how likely they are to churn. Where in the output is that likelihood?

  1. AIn a column named `probability`, one per row
  2. BIn a column named `predicted_label_score`, one value for each row
  3. CIn the `churned` column, which ML.PREDICT overwrites
  4. DIn `predicted_churned_probs`, with each label's probability
Show the answer and why
  • AIn a column named `probability`, one per row

    Incorrect

    ML.PREDICT does not add a generic probability column; its output column names are built from the label column's name.

  • BIn a column named `predicted_label_score`, one value for each row

    Incorrect

    The output columns are named after the label column used in training, churned, not a fixed predicted_label name.

  • CIn the `churned` column, which ML.PREDICT overwrites

    Incorrect

    ML.PREDICT passes the input columns through and adds new columns for the prediction; it does not overwrite an input column.

  • DIn `predicted_churned_probs`, with each label's probability

    Correct

    For a binary classification model, predicted_churned holds the label with the higher probability and predicted_churned_probs is an ARRAY<STRUCT> of each label and its predicted probability.

ML.PREDICT returns every input row with its input columns, plus output columns named predicted_<label_column_name> and, for classification models, predicted_<label_column_name>_probs. To rank by likelihood, read the probability for the TRUE label from the _probs array.

Question 8 · choose 1

A team plans its first BigQuery ML project to predict late payments. It has prepared the data and trained the model `ds.late_payments` with CREATE MODEL. Following a standard ML project plan, which function should it run next, before the sales team acts on any predictions?

  1. A`ML.EVALUATE`
  2. B`ML.PREDICT`
  3. C`ML.FEATURE_INFO`
  4. D`ML.WEIGHTS`
Show the answer and why
  • A`ML.EVALUATE`

    Correct

    Evaluation comes after training and before prediction: ML.EVALUATE returns the model's quality metrics, such as precision and recall for a classifier, so the team can judge whether the model is good enough.

  • B`ML.PREDICT`

    Incorrect

    ML.PREDICT is the prediction step; running it before evaluation would hand out predictions from a model whose quality nobody has checked.

  • C`ML.FEATURE_INFO`

    Incorrect

    ML.FEATURE_INFO describes the input features used in training, such as their statistics; it does not measure how well the model predicts.

  • D`ML.WEIGHTS`

    Incorrect

    ML.WEIGHTS shows the weight the model gave each feature; it explains the model rather than measuring its accuracy.

A standard ML project runs from data collection and preparation, through training with CREATE MODEL, to evaluation with ML.EVALUATE and only then to prediction with ML.PREDICT. Functions such as ML.FEATURE_INFO and ML.WEIGHTS inspect a trained model but do not replace the evaluation step.

Question 9 · choose 1

An analytics team at an online bookstore has just learned how to train a BigQuery ML `MATRIX_FACTORIZATION` model. Four requests are waiting in its queue. Which request is this model type built for?

  1. ASuggest books each reader is likely to rate highly, from the ratings readers gave
  2. BForecast next quarter's daily book sales from three years of daily sales totals
  3. CEstimate how much each customer will spend next year from age, region and plan
  4. DFlag individual card payments that look unusual compared with normal activity
Show the answer and why
  • ASuggest books each reader is likely to rate highly, from the ratings readers gave

    Correct

    Matrix factorization is BigQuery ML's model for recommendations: from users, items and the ratings users gave some of the items, it predicts how users would rate the items they have not rated.

  • BForecast next quarter's daily book sales from three years of daily sales totals

    Incorrect

    Forecasting a time series from its own history is the job of the ARIMA_PLUS model type, not of matrix factorization.

  • CEstimate how much each customer will spend next year from age, region and plan

    Incorrect

    Predicting a number from the feature columns of each row is regression, done with model types such as linear regression or boosted tree regressors, not with matrix factorization.

  • DFlag individual card payments that look unusual compared with normal activity

    Incorrect

    Finding unusual records is anomaly detection, which BigQuery ML runs with ML.DETECT_ANOMALIES on supported model types; matrix factorization does not flag outliers.

BigQuery ML groups model types by use case: ARIMA_PLUS for time-series forecasting, regression models for numeric values, and matrix factorization for recommendations, where it learns from the ratings that users gave to items in order to predict the missing ratings.

Question 10 · choose 1

A BigQuery ML logistic regression model predicts whether a subscriber will cancel (label `cancelled`, values 0 and 1). On a table of held-out subscribers, the retention lead wants a small grid with one row per actual value of `cancelled` and one column per predicted value, each cell holding a count, without writing any aggregation SQL. Which function returns that grid?

  1. A`ML.EVALUATE`
  2. B`ML.FEATURE_INFO`
  3. C`ML.PREDICT`
  4. D`ML.CONFUSION_MATRIX`
Show the answer and why
  • A`ML.EVALUATE`

    Incorrect

    For a classification model it returns one row of metrics such as precision, recall, accuracy and roc_auc, not counts per actual and predicted value.

  • B`ML.FEATURE_INFO`

    Incorrect

    It returns statistics about the input feature columns, such as minimum, maximum and mean values, without comparing predictions with labels.

  • C`ML.PREDICT`

    Incorrect

    It returns one output row per input row with the model's prediction for that row; turning that into counts would need the aggregation SQL the lead wants to avoid.

  • D`ML.CONFUSION_MATRIX`

    Correct

    Its output lists the actual labels one per row in expected_label and has one column per class for the predicted labels; a confusion matrix holds the number of correct and incorrect predictions.

ML.CONFUSION_MATRIX returns a confusion matrix for a classification model and evaluation data: actual labels as rows, predicted labels as columns, true positives on the diagonal. ML.EVALUATE summarizes the same comparison as single metrics, and ML.PREDICT scores rows one by one.

Question 11 · choose 1

An analyst built a BigQuery ML k-means model to segment customers and runs `ML.EVALUATE` on it. Which metric does the output report for this model?

  1. AThe model's accuracy and F1 score
  2. BThe share of customers whose cluster assignment changed since the model was trained
  3. CThe Davies-Bouldin index and the mean squared distance to the centroids
  4. DThe model's ROC AUC and log loss
Show the answer and why
  • AThe model's accuracy and F1 score

    Incorrect

    Accuracy and F1 score are classification metrics; a k-means model has no labels to compare its clusters with.

  • BThe share of customers whose cluster assignment changed since the model was trained

    Incorrect

    ML.EVALUATE does not track how cluster assignments change over time; for k-means it reports measures of how compact the clusters are.

  • CThe Davies-Bouldin index and the mean squared distance to the centroids

    Correct

    For k-means, ML.EVALUATE reports the Davies-Bouldin index and the mean squared distance between data points and the centroids of their clusters.

  • DThe model's ROC AUC and log loss

    Incorrect

    ROC AUC and log loss evaluate classification models; they are not reported for clustering.

Evaluation metrics follow the model type. Unsupervised k-means models are judged by how compact and separate their clusters are, so ML.EVALUATE reports the Davies-Bouldin index (lower is better) and the mean squared distance from points to their centroids.

Question 12 · choose 1

A data science team serves a custom fraud model from a Gemini Enterprise Agent Platform endpoint, and the model must keep running there. Analysts now want fraud scores for millions of BigQuery rows, written in SQL, without exporting the rows or retraining anything in BigQuery. How should they run this inference?

  1. ATrain a BigQuery ML copy of the model with CREATE MODEL and run ML.PREDICT on it
  2. BExport the rows to Cloud Storage, run a batch job on the endpoint, then load the scores
  3. CRun ML.EVALUATE against the endpoint to get a score for each row
  4. DCreate a remote model that references the endpoint, then call ML.PREDICT
Show the answer and why
  • ATrain a BigQuery ML copy of the model with CREATE MODEL and run ML.PREDICT on it

    Incorrect

    Training a new BigQuery ML model is retraining, and the copy would not be the team's custom model on the endpoint.

  • BExport the rows to Cloud Storage, run a batch job on the endpoint, then load the scores

    Incorrect

    This moves the rows out of BigQuery and adds jobs outside SQL, which the analysts want to avoid.

  • CRun ML.EVALUATE against the endpoint to get a score for each row

    Incorrect

    ML.EVALUATE returns a model's quality metrics, not a prediction for each row.

  • DCreate a remote model that references the endpoint, then call ML.PREDICT

    Correct

    A remote model created with CREATE MODEL references a model hosted on Agent Platform; ML.PREDICT on it sends BigQuery data to the endpoint, and the inference runs there.

BigQuery ML runs inference with ML.PREDICT on three kinds of models: models trained in BigQuery ML, imported models whose inference runs in BigQuery, and remote models that reference a model hosted on Agent Platform, where the inference runs on the endpoint with data from BigQuery.

Question 13 · choose 1

Large batch jobs that call a supported Gemini model through a BigQuery remote model keep failing with resource exhausted (429) errors from the regional endpoint's quota. The company has no requirement about the region where the requests are processed. What change does the remote model page suggest?

  1. ARecreate the remote model in a second dataset in another region
  2. BGive the connection's service account a second role
  3. CUse a smaller Gemini model with the same regional endpoint
  4. DPoint the remote model's ENDPOINT at the global endpoint
Show the answer and why
  • ARecreate the remote model in a second dataset in another region

    Incorrect

    The model would still call a regional endpoint with its own regional quota, and the data would have to be in that region too.

  • BGive the connection's service account a second role

    Incorrect

    429 errors come from exceeding a quota, not from missing permissions, so another role does not help.

  • CUse a smaller Gemini model with the same regional endpoint

    Incorrect

    Changing the model does not lift the quota on the regional endpoint that the requests exceed.

  • DPoint the remote model's ENDPOINT at the global endpoint

    Correct

    For supported Gemini models, the global endpoint can improve overall availability while reducing 429 errors from regional quotas; it suits cases with no requirement on the processing location.

A remote model's ENDPOINT can be a model name, a regional endpoint URL or, for supported Gemini models, the global endpoint. The global endpoint reduces resource exhausted errors, but you can't control the region where requests are handled, so avoid it when data processing location matters.

Practice domain 2 →Practice all domains →