Preparing and using data for analysis — PDE Practice Quiz

Question 1

Your company publishes a curated BigQuery dataset as an Analytics Hub listing so that external partner organizations can subscribe to it. Leadership wants to know which partners are actually querying the shared data and how often, so they can justify continued investment and identify inactive subscribers. What is the most appropriate way to obtain this usage information?

Accepted Answer

Enable the Analytics Hub usage metrics feature on the listing to view subscriber-level query counts and activity in the exchange/listing metrics.

Answer

Grant each partner the BigQuery Data Viewer role on your source dataset and query their audit logs directly from your project.

Answer

Convert the listing into an authorized view and count rows returned per partner in your billing export.

Answer

Ask each partner to email a monthly report of their query volume against the linked dataset.

Question 2

A retail analytics company wants to monetize a curated BigQuery dataset by sharing it with dozens of external partner organizations. Requirements: partners must query the data from their own projects (paying for their own compute), the provider must be able to revoke access and publish updates centrally without copying data to each partner, and the provider must track which organizations have subscribed. Which approach best meets these requirements?

Accepted Answer

Publish the dataset as a listing in an Analytics Hub data exchange; partners subscribe to create linked datasets in their own projects.

Answer

Create authorized views over the dataset and grant each partner's service account the BigQuery Data Viewer role on the view.

Answer

Export the dataset to Cloud Storage as Avro files and share signed URLs with each partner organization.

Answer

Schedule a BigQuery data transfer to copy the dataset into each partner's project on a nightly basis.

Question 3

Your finance team uses a Looker dashboard backed by BigQuery that runs the same set of aggregation queries against a 400 GB partitioned sales table hundreds of times each morning. Executives complain that each dashboard tile takes 6-9 seconds to load, and on-demand query costs are climbing. You need sub-second dashboard response times with minimal changes to the existing SQL and no separate infrastructure to manage. What should you do?

Accepted Answer

Enable BigQuery BI Engine by creating a reservation in the project and let it cache and accelerate the recurring dashboard queries in memory.

Answer

Export the aggregated results to a Cloud SQL instance and repoint the Looker connection to Cloud SQL for faster reads.

Answer

Move the sales table to Bigtable and rewrite the dashboard queries to use row-key lookups for low latency.

Answer

Increase the BigQuery slot reservation for the project so the aggregation queries have more compute available.

Question 4

A retail analytics team uses BigQuery ML to forecast weekly sales. Business stakeholders want to understand how much of each forecast is driven by trend, weekly seasonality, and holiday effects so they can validate the model with domain knowledge. The team already has a trained ARIMA_PLUS model. Which approach lets them retrieve the decomposed forecast components alongside the point forecasts with the least effort?

Accepted Answer

Use ML.EXPLAIN_FORECAST on the model, which returns the forecast plus its decomposed time-series components such as trend, seasonality, and holiday effects.

Answer

Use ML.FORECAST and manually compute component contributions by differencing consecutive forecast values in a SQL query.

Answer

Export the ARIMA_PLUS model to Vertex AI and enable Vertex Explainable AI feature attributions to obtain the component breakdown.

Answer

Retrain the model with ENABLE_GLOBAL_EXPLAIN=TRUE and then call ML.GLOBAL_EXPLAIN to get the seasonal and trend contributions.

Question 5

A retail analytics team stores three years of daily sales records in BigQuery, including columns for date, store_id, product_category, and units_sold. They want to produce a 90-day forecast of units_sold per store and product category directly in BigQuery, without exporting data to another platform. The analysts are SQL-proficient but have no experience managing ML infrastructure. Which approach best meets these requirements?

Accepted Answer

Train a BigQuery ML ARIMA_PLUS model with TIME_SERIES_ID_COL set to the store and category, then call ML.FORECAST to generate the 90-day projection.

Answer

Train a BigQuery ML linear regression model using the date's ordinal value as the single feature and predict units_sold for future dates.

Answer

Export the sales data to Vertex AI and train a custom TensorFlow sequence model, then batch-predict the next 90 days.

Answer

Train a BigQuery ML k-means clustering model to group stores by sales pattern and use the cluster centroids as the forecast.

Question 6

A data scientist is building a logistic regression model in BigQuery ML to predict customer churn. The training table includes a numeric 'monthly_spend' column with a wide range of values and a 'subscription_tier' column containing text values like 'Basic', 'Pro', and 'Enterprise'. They want the same preprocessing (standardizing the numeric column and one-hot encoding the categorical column) to be automatically applied during both training and prediction without maintaining separate SQL logic. What is the most appropriate approach?

Accepted Answer

Use the TRANSFORM clause in the CREATE MODEL statement with ML.STANDARD_SCALER for monthly_spend, letting BigQuery ML handle categorical encoding automatically.

Answer

Manually create a preprocessed view that scales and one-hot encodes the columns, then reference it in both training and prediction queries.

Answer

Export the data to Vertex AI, apply preprocessing in a custom container, and import the model back into BigQuery ML.

Answer

Cast monthly_spend to a STRING and concatenate it with subscription_tier so BigQuery ML treats them as a single categorical feature.

Question 7

A data science team trained a boosted tree classifier using BigQuery ML to predict customer churn. Batch scoring in BigQuery works well for their nightly reports, but the product team now needs sub-100ms online predictions served from a customer-facing web application. What is the most appropriate way to meet this low-latency serving requirement while reusing the already-trained model?

Accepted Answer

Export the BigQuery ML model to Cloud Storage and deploy it to a Vertex AI endpoint for online prediction.

Answer

Run ML.PREDICT queries against BigQuery from the web application for each incoming request.

Answer

Schedule a materialized view in BigQuery that precomputes predictions for every possible customer state.

Answer

Use BigQuery BI Engine to accelerate ML.PREDICT so the query returns within 100ms.

Question 8

A data engineer is building a BigQuery ML logistic regression model to predict ride-cancellation likelihood. Analysts note that cancellation behavior depends heavily on the *combination* of pickup_city and time_of_day (e.g., 'downtown at rush hour' behaves very differently from 'suburb at midnight'), rather than either feature alone. The engineer wants the model to learn these interaction effects directly during training without manually pre-joining string values in a staging table. Which approach should the engineer use inside the CREATE MODEL statement?

Accepted Answer

Apply ML.FEATURE_CROSS to pickup_city and time_of_day within the TRANSFORM clause so the crossed categorical feature is generated at both training and prediction time.

Answer

Concatenate pickup_city and time_of_day with CONCAT in the SELECT statement of a temporary table, then reference the resulting column as a feature.

Answer

Add both pickup_city and time_of_day as separate features and increase the number of training iterations so the model discovers the interaction.

Answer

Apply ML.BUCKETIZE to time_of_day and ML.STANDARD_SCALER to pickup_city inside the TRANSFORM clause to normalize the inputs before training.

Question 9

A retail analytics team uses BigQuery to store transaction data. A data scientist wants to build a churn prediction model using Vertex AI's custom training with scikit-learn, but the raw features live in BigQuery. The team needs to generate a training dataset where numeric columns are standardized and categorical columns are one-hot encoded, and they want this preprocessing logic to be reusable and version-controlled outside of BigQuery ML. What is the most appropriate approach to prepare this data for AI/ML?

Accepted Answer

Export the raw BigQuery table to Cloud Storage as CSV and apply all preprocessing in the Vertex AI training container using a scikit-learn Pipeline, reading the data via the BigQuery Storage Read API.

Answer

Create a BigQuery ML model with a TRANSFORM clause to standardize and encode features, then export the model for use in Vertex AI.

Answer

Use Connected Sheets to pivot and normalize the data, then download the sheet as input for Vertex AI training.

Answer

Build a Dataflow streaming pipeline that reads from BigQuery, applies preprocessing, and writes to Pub/Sub for Vertex AI to consume.

Question 10

A retail analytics team stores three years of daily sales history in a BigQuery table with columns sale_date (DATE), store_id (STRING), and revenue (FLOAT64). They want to generate a 30-day revenue forecast per store directly in BigQuery, using SQL only, and need the model to automatically handle seasonality, holidays, and trend without manual feature engineering. Which approach best meets these requirements?

Accepted Answer

Create a BigQuery ML model with model_type='ARIMA_PLUS', specifying time_series_timestamp_col='sale_date', time_series_data_col='revenue', and time_series_id_col='store_id', then call ML.FORECAST.

Answer

Create a BigQuery ML model with model_type='LINEAR_REG' using sale_date converted to an integer offset as the only feature, then predict future rows with ML.PREDICT.

Answer

Export the table to Vertex AI and train a custom TensorFlow LSTM model, then batch-predict and load results back into BigQuery.

Answer

Create a BigQuery ML model with model_type='KMEANS' on revenue and sale_date to cluster seasonal patterns, then extrapolate cluster centroids forward 30 days.

Question 11

A data engineering team at a media company wants to build a content recommendation feature. They have 5 million article titles and descriptions stored in a BigQuery table. They need to compute semantic similarity between articles so that editors can find related content using SQL, without exporting data or managing separate infrastructure. Which approach best prepares this text data for the similarity use case?

Accepted Answer

Use ML.GENERATE_EMBEDDING with a remote model referencing a Vertex AI text embedding endpoint, store the resulting vectors in BigQuery, then compute similarity with ML.DISTANCE or a VECTOR_SEARCH index.

Answer

Train a BigQuery ML k-means clustering model on the raw title strings and use the cluster assignments as similarity scores.

Answer

Concatenate title and description into a single column and apply ML.NGRAMS, then join rows on matching n-grams to identify related articles.

Answer

Export the text to Cloud Storage, run a Dataflow job with a custom embedding library, and re-import the vectors as a federated external table.

Question 12

A data science team at a retail company is training a boosted tree classifier in BigQuery ML to predict customer churn. Their initial model underperforms, and they suspect the default values for parameters like the number of trees, learning rate, and max tree depth are suboptimal. They want to systematically search across ranges of these parameters within BigQuery ML without exporting the data or building a separate Vertex AI pipeline. What is the most appropriate approach?

Accepted Answer

Use CREATE MODEL with NUM_TRIALS and the hyperparameter search ranges (e.g., HPARAM_RANGE and HPARAM_CANDIDATES) so BigQuery ML runs built-in hyperparameter tuning across trials.

Answer

Train several separate models manually, each with a different hardcoded combination of parameters, then compare their evaluation metrics with ML.EVALUATE.

Answer

Export the training data to Vertex AI and use Vertex AI Vizier as the only supported way to tune BigQuery model parameters.

Answer

Use the TRANSFORM clause to automatically select the best learning rate and tree depth during preprocessing.

Question 13

A data scientist is training a BigQuery ML logistic regression model to predict customer churn. In the training table, only about 2% of the rows represent churned customers. After training, the model reports very high accuracy but almost never predicts the positive (churn) class, making it useless for the business. The team wants to improve the model's ability to identify the minority class while continuing to use BigQuery ML. What is the most appropriate action?

Accepted Answer

Set the AUTO_CLASS_WEIGHTS option to TRUE in the CREATE MODEL statement so class weights are balanced inversely to frequency.

Answer

Increase the MAX_ITERATIONS option so the model trains longer and eventually learns the minority class.

Answer

Switch the model_type to LINEAR_REG since regression handles rare events better than classification.

Answer

Remove the L2_REG regularization so the model can memorize the rare churn examples.

Question 14

A data science team trains a BigQuery ML model to classify incoming support tickets into one of five priority categories. The categories are roughly balanced. After running ML.EVALUATE, they want a single metric that summarizes how often the model assigns the correct category across all five classes, treating each class equally. Which evaluation output should they focus on?

Accepted Answer

The accuracy value, which reflects the overall proportion of correctly classified tickets across the balanced classes

Answer

The roc_auc value, since it directly measures multi-class separability across the five categories

Answer

The log_loss value, because a lower log loss guarantees the highest number of correct category assignments

Answer

The mean_squared_error value returned by ML.EVALUATE for the classification model

Question 15

A data science team is building a BigQuery ML logistic regression model to predict customer churn. One key input is customer_age, a continuous integer, and another is annual_income, which ranges from a few thousand to several million and is heavily right-skewed. During evaluation the model underperforms, and the team suspects the raw scale and skew of these numeric features are hurting training. They want the preprocessing logic to be applied consistently and automatically at both training and prediction time, without maintaining a separate transformation pipeline. What should they do?

Accepted Answer

Define a TRANSFORM clause in the CREATE MODEL statement that applies ML.BUCKETIZE to customer_age and ML.STANDARD_SCALER (with log handling) to annual_income so the transformations are stored with the model.

Answer

Manually compute bucketed and scaled columns in a staging table before training, then remember to apply the same SQL logic in every prediction query.

Answer

Enable AUTO_CLASS_WEIGHTS in the OPTIONS clause so BigQuery ML automatically normalizes the numeric feature distributions during training.

Answer

Export the data to Vertex AI, run feature scaling in a custom container, and re-import the scaled features into BigQuery for training.

Question 16

A data scientist trains a BigQuery ML boosted tree model daily on an orders table that grows continuously. They want the model evaluation metrics to be comparable across daily training runs, and they need each row deterministically assigned to either training or evaluation so that a given order always lands in the same split even as new rows arrive. Which approach best meets these requirements?

Accepted Answer

Use DATA_SPLIT_METHOD='CUSTOM' with a precomputed split column derived from a deterministic hash (e.g., FARM_FINGERPRINT) of the order_id

Answer

Use DATA_SPLIT_METHOD='RANDOM' with DATA_SPLIT_EVAL_FRACTION=0.2 so BigQuery ML shuffles rows on each run

Answer

Use DATA_SPLIT_METHOD='AUTO_SPLIT' and let BigQuery ML decide the split based on table size

Answer

Use DATA_SPLIT_METHOD='SEQ' on the order timestamp so the newest 20% of orders are always used for evaluation

Question 17

A data engineer is building a BigQuery ML logistic regression model to predict customer churn. The raw table contains a transaction timestamp, several engineered numeric features, and the churn label. During evaluation, the model reports 98% accuracy on the test set, but performance drops sharply in production. On review, the engineer notices they computed the mean and standard deviation for feature normalization across the entire table before running CREATE MODEL, and BigQuery ML randomly split rows into training and evaluation sets. What is the most likely cause of the inflated evaluation metrics, and how should it be fixed?

Accepted Answer

The normalization statistics were computed over the full dataset including evaluation rows, causing data leakage; use the TRANSFORM clause so preprocessing statistics are learned only from training data and applied consistently at prediction.

Answer

Random splitting is invalid for churn models; switch to DATA_SPLIT_METHOD='SEQ' on the timestamp and keep the manual normalization outside the model.

Answer

Logistic regression overfits by default; add L2 regularization with the l2_reg option and retain the precomputed normalization values.

Answer

The evaluation set is too small; increase DATA_SPLIT_EVAL_FRACTION to 0.5 and rerun the training with the same preprocessing pipeline.

Question 18

A data science team trains a BigQuery ML model to predict customer churn. During training they compute several engineered features in the SELECT statement: a log transform of monthly_spend, a ratio of support_tickets to tenure_months, and a bucketized age. When the model is deployed and analysts run ML.PREDICT with raw customer rows, predictions are wildly inaccurate compared to evaluation results. The team wants the preprocessing to be applied automatically and identically at both training and prediction time with minimal maintenance. What should they do?

Accepted Answer

Wrap all feature engineering expressions inside a TRANSFORM clause in the CREATE MODEL statement so BigQuery ML applies them automatically during training and prediction.

Answer

Create a scheduled query that materializes the engineered features into a separate table and point ML.PREDICT at that table.

Answer

Document the preprocessing SQL and require analysts to reproduce the same expressions in every ML.PREDICT query.

Answer

Export the model to Vertex AI and rebuild the preprocessing as a separate Vertex AI pipeline component.

Question 19

A data science team is training a BigQuery ML linear regression model on a sales dataset. About 8% of rows have NULL values in the numeric feature `avg_basket_value`. They want the imputation logic applied automatically and identically during both training and prediction, without maintaining separate preprocessing SQL for inference. Which approach should they use?

Accepted Answer

Wrap an ML.IMPUTER(avg_basket_value, 'mean') expression inside the TRANSFORM clause of the CREATE MODEL statement.

Answer

Run an UPDATE statement to replace NULLs with the column mean in the source table before every training and prediction job.

Answer

Create a view that uses IFNULL(avg_basket_value, 0) and train the model against that view, then query the view at prediction time.

Answer

Set the model option AUTO_IMPUTE=TRUE so BigQuery ML fills missing values automatically.

Question 20

A data science team at a media company wants to build a BigQuery ML logistic regression model to classify news articles by topic. The raw training table contains a single free-text column called 'body'. They want the model to learn from word-frequency signals in the text, and they need the same text preprocessing to be applied automatically at prediction time without re-writing transformation SQL. Which approach best meets these requirements?

Accepted Answer

Use the ML.TF_IDF or ML.NGRAMS-based feature engineering functions inside a TRANSFORM clause when creating the model so the text preprocessing is stored with the model and reapplied automatically during prediction.

Answer

Precompute a TF-IDF matrix in a separate SQL job, store it in a staging table, and train the model on that table; re-run the same SQL manually before each prediction batch.

Answer

Export the text to Vertex AI, train a custom AutoML text model, and import predictions back into BigQuery via scheduled queries.

Answer

Pass the raw 'body' column directly as a feature to CREATE MODEL; BigQuery ML automatically tokenizes and vectorizes any STRING column into TF-IDF features.

More PDE practice