PDE Preparing and Using Data for Analysis — 50 Questions

Question 1

A data engineer wants to train a linear regression model in BigQuery ML to predict sales. The training data includes a categorical feature with 1000+ unique values. Which method is most appropriate to handle this feature in the CREATE MODEL statement?

Accepted Answer

Use TRANSFORM clause with ML.FEATURE_CROSS or manual hashing.. BigQuery ML automatically one-hot encodes categorical features with fewer than a threshold of unique values. For high-cardinality features, you can use TRANSFORM to apply feature engineering like hashing or bucketizing.

Answer

Set max_categorical_features=100 in the model options.

Answer

Use the OPTIONS(ENCODE='ONE_HOT_ENCODING') parameter in the model options.

Answer

The model automatically handles high-cardinality features without any additional steps.

Question 2

You need to create a Looker model that defines a 'sales' view based on a BigQuery table, with a measure for total revenue. Which LookML object defines the table and dimensions?

Accepted Answer

view. In LookML, a view defines the mapping to a database table (or derived table) and contains dimensions and measures.

Answer

explore

Answer

model

Answer

dimension

Question 3

A company uses Looker Studio to build dashboards from BigQuery data. They notice that queries take several seconds to return. They want to improve performance without changing the schema or adding materialized views. Which option should they use?

Accepted Answer

Enable BigQuery BI Engine on the relevant project.. BI Engine accelerates sub-second query response times in Looker Studio by caching data in memory within the BigQuery region.

Answer

Move the data to Cloud SQL.

Answer

Switch to BigQuery Omni for cross-cloud queries.

Answer

Use APPROX_COUNT_DISTINCT to speed up distinct counts.

Question 4

A data scientist is training a binary classification model on an imbalanced dataset (95% negative, 5% positive) using AutoML Tables. Which strategy should they use to handle the class imbalance?

Accepted Answer

Specify a weight column with higher weights for positive examples in the dataset.. AutoML Tables automatically handles class imbalance by applying class weights and downsampling. Users can also specify a weight column explicitly.

Answer

Set the budget to a higher value to allow more training on minority class.

Answer

Use SMOTE in a Dataflow pipeline before importing the data to AutoML Tables.

Answer

Create duplicate copies of the positive class rows to balance the dataset.

Question 5

You need to split a time-series dataset into training and evaluation sets for a forecasting model. The data is ordered by timestamp. Which splitting technique should you use?

Accepted Answer

Sequential split where training data precedes evaluation data in time.. For time-series data, a random split would leak future information into training. A sequential split (earlier data for training, later for evaluation) is required.

Answer

Use k-fold cross-validation with random folds.

Answer

Stratified split based on the target variable.

Answer

Random split with 80% training, 20% evaluation.

Question 6

Which BigQuery SQL function returns the rank of a row within a window, with gaps in the ranking for ties?

Accepted Answer

RANK(). RANK() assigns the same rank to ties and leaves gaps (e.g., 1,1,3). ROW_NUMBER() assigns unique consecutive numbers. DENSE_RANK() does not leave gaps.

Answer

NTILE()

Answer

DENSE_RANK()

Answer

ROW_NUMBER()

Question 7

A company uses Dataplex to manage data quality across multiple BigQuery datasets. They want to define a data quality rule that checks if a column 'email' contains a valid email format. Which Dataplex feature should they use?

Accepted Answer

Create a custom Data Quality rule using the 'regex' type.. Dataplex Data Quality allows predefined rule types including a 'regex' rule for pattern matching. There is no built-in 'email' rule, so a regex check is appropriate.

Answer

Use Cloud DLP to classify and validate emails.

Answer

Use the built-in 'email' rule type in Dataplex.

Answer

Create a Dataflow pipeline to validate emails and write results to a separate table.

Question 8

A data engineer needs to query data across BigQuery (in Google Cloud) and Snowflake (in AWS) without moving the data. Which service should they use?

Accepted Answer

BigQuery Omni. BigQuery Omni allows querying data across multiple clouds using BigQuery's interface, with compute running in the respective cloud. Data stays in place.

Answer

Dataflow

Answer

Cloud SQL

Answer

Vertex AI Feature Store

Question 9

You want to train a custom TensorFlow model on Vertex AI using a managed Jupyter notebook environment. Which service should you use?

Accepted Answer

Vertex AI Workbench. Vertex AI Workbench provides managed Jupyter notebooks with pre-installed frameworks and easy access to Vertex AI services.

Answer

Cloud Datalab

Answer

Vertex AI Training

Answer

AI Platform Notebooks

Question 10

A company uses Looker to define business logic in LookML. They need to create a new measure that calculates the average order value, defined as total revenue divided by number of orders. Which LookML syntax should they use?

Accepted Answer

measure: avg_order_value { type: average; sql: ${revenue} / ${order_count} ;; }. Measures in LookML are defined with type and sql expression. The correct syntax for a calculated measure is: measure: avg_order_value { type: average; sql: ${revenue} / ${order_count} ;; }

Answer

measure: avg_order_value { type: sum; sql: ${revenue} / ${order_count} ;; }

Answer

dimension: avg_order_value { type: number; sql: ${revenue} / ${order_count} ;; }

Answer

dimension: avg_order_value { type: average; sql: ${revenue} / ${order_count} ;; }

Question 11

A data scientist wants to import a pre-trained TensorFlow model into BigQuery ML for batch predictions. The model is stored in a Cloud Storage bucket. Which statement is correct?

Accepted Answer

Use CREATE MODEL with model_type='tensorflow' and model_path='gs://bucket/model'.. BigQuery ML supports importing TensorFlow models via CREATE MODEL with model_type='tensorflow' and model_path pointing to the SavedModel directory in Cloud Storage.

Answer

Use CREATE MODEL with model_type='imported_tensorflow' and model_path='gs://bucket/model'.

Answer

First upload the model to Vertex AI Model Registry, then reference it in BigQuery ML.

Answer

Use the ML.IMPORT_MODEL function to load the model into BigQuery.

Question 12

You need to track the lineage of data in BigQuery, showing how tables are derived from other tables via queries. Which service provides this capability?

Accepted Answer

BigQuery Lineage API. BigQuery lineage API and Dataplex lineage both provide data lineage tracking. BigQuery lineage is built-in, while Dataplex extends it across the data lake.

Answer

Cloud Composer

Answer

Cloud Data Catalog

Answer

Dataflow

Question 13

A data engineer needs to build a feature engineering pipeline using Vertex AI Pipelines. The pipeline should preprocess data, train a model, and deploy it. Which two components are required to define the pipeline? (Choose 2)

Accepted Answer

Kubeflow Pipelines SDK. Vertex AI Pipelines uses the Kubeflow Pipelines SDK (or TFX) to define components and compile them into a pipeline. The pipeline is then run on Vertex AI Pipelines.

Answer

Vertex AI Feature Store

Answer

Dataflow

Answer

Cloud Composer

Question 14

A company uses AutoML Tables to train a classification model. They want to improve model performance by engineering new features from existing timestamp columns. Which three techniques can they apply within AutoML Tables? (Choose 3)

Accepted Answer

Create a new column with the difference between two timestamps.. AutoML Tables automatically extracts features from timestamp columns, such as day of week, month, hour, etc. Users can also manually create new columns via the UI or by preprocessing data before import. However, manual SQL functions cannot be used directly within AutoML Tables.

Answer

Manually add a column with a boolean indicating if the timestamp falls on a weekend.

Answer

Apply a SQL UDF in the AutoML Tables training configuration.

Question 15

A data team wants to use Approximate Aggregation Functions in BigQuery to get faster query results. Which two functions can they use? (Choose 2)

Accepted Answer

APPROX_QUANTILES. BigQuery provides APPROX_COUNT_DISTINCT for approximate distinct counts and APPROX_QUANTILES for approximate quantiles. Other approximate functions include APPROX_TOP_COUNT and APPROX_TOP_SUM.

Answer

APPROX_SUM

Answer

APPROX_AVG

Answer

APPROX_MEDIAN

Question 16

A data engineer needs to create a BigQuery ML model for predicting customer churn using a dataset with 10 million rows and 50 features. The dataset is highly imbalanced (5% churn). Which approach should the engineer use to handle class imbalance during model training?

Accepted Answer

Use the CREATE MODEL statement with CLASS_WEIGHTS = {'0': 0.2, '1': 0.8}. BigQuery ML supports class weights for imbalanced datasets via the CLASS_WEIGHTS option in CREATE MODEL. This assigns higher weight to the minority class without generating synthetic data. SMOTE is not available in BigQuery ML. Undersampling the majority class would lose data, and oversampling with duplication could introduce bias.

Answer

Undersample the majority class before training

Answer

Use SMOTE via TRANSFORM clause in BigQuery ML

Answer

Oversample the minority class by duplicating rows

Question 17

A financial analytics team uses Looker to explore BigQuery data. They need to allow business users to filter by a custom date range that is not tied to an existing dimension. The date range must be user-input at query time. What is the best approach in Looker?

Accepted Answer

Create a parameter in LookML using Liquid templating. Looker uses Liquid templating in LookML to create parameters that render as filter controls at runtime. Users can input values that are then injected into the SQL. Creating a dimension with a yesno filter requires predefined values. The filter parameter on a dimension only allows selecting from existing values, not arbitrary input.

Answer

Create an explore with a custom filter field in the Looker UI

Answer

Use a filter parameter directly on the date dimension

Answer

Add a dimension with a yesno filter that toggles the date range

Question 18

A data scientist wants to train a custom TensorFlow model on Vertex AI using a managed Jupyter notebook. Which Vertex AI service should they use to set up a notebook environment with pre-installed deep learning frameworks?

Accepted Answer

Vertex AI Workbench. Vertex AI Workbench provides managed Jupyter notebooks with pre-installed deep learning frameworks (TensorFlow, PyTorch, etc.) and easy scaling options. Notebooks on Compute Engine would require manual setup. AI Platform Training is for training jobs, not interactive notebooks. Vertex AI Pipelines is for orchestrating ML workflows.

Answer

Compute Engine with Deep Learning VM

Answer

Vertex AI Training via custom job

Answer

Vertex AI Pipelines

Question 19

A retail company uses BigQuery to store sales data and wants to forecast weekly demand for the next 8 weeks using historical data from the past 2 years. They need to account for seasonality and holidays. Which BigQuery ML model type and configuration is most appropriate?

Accepted Answer

ARIMA_PLUS with holiday_region parameter. BigQuery ML's ARIMA_PLUS model is designed for time-series forecasting, automatically detecting seasonality and handling holiday effects via the holiday_region parameter. Linear regression would require manual feature engineering for time components. Time-series DECOMPOSE is not a model type. Boosted trees are not natively time-series aware without feature engineering.

Answer

Boosted tree classifier

Answer

Linear regression with engineered time features

Answer

Time-series DECOMPOSE model

Question 20

A data engineer needs to query data from BigQuery and another cloud provider's storage (AWS S3) using a single SQL query. The data must not be moved or copied to GCP. Which Google Cloud service should they use?

Accepted Answer

BigQuery Omni. BigQuery Omni allows querying data across multiple clouds (AWS S3, Azure Blob Storage) using BigQuery's interface without moving data. BigQuery Omni runs compute in the other cloud's region. BigQuery Transfer Service moves data into BigQuery. Dataplex is for data management, not cross-cloud queries. Cloud Storage Transfer Service is for moving data between clouds.

Answer

Cloud Storage Transfer Service

Answer

Dataplex

Answer

BigQuery Data Transfer Service