PDE Preparing and Using Data for Analysis — 25 Questions

Question 1

To enable data lineage tracking in BigQuery, which feature should be activated?

Accepted Answer

Dataplex Lineage. The correct feature for data lineage tracking in BigQuery is Dataplex Lineage. Dataplex provides end-to-end lineage tracking across data assets, including BigQuery tables and views. BigQuery Lineage API is not a distinct feature; lineage capabilities are integrated into Dataplex. BigQuery Audit Logs capture metadata changes but are not lineage-specific, and Data Catalog is for metadata management, not lineage.

Answer

BigQuery Audit Logs

Answer

Data Catalog

Answer

BigQuery Lineage API

Question 2

You are designing a data pipeline for ML training with Vertex AI. You need to split time-series data into train/validation/test sets without leaking future data. Which THREE practices should you follow?

Accepted Answer

Use a sliding window validation approach for hyperparameter tuning.. Time-series data requires temporal splitting: use a date column to define split boundaries (E), ensure all data points for a given time period are in the same split (B), and use a sliding window validation approach for hyperparameter tuning (A). Random assignment (D) would leak future data, and Looker (C) is not a data splitting tool.

Answer

Use Looker to generate the splits automatically.

Answer

Randomly assign rows to each split to ensure statistical distribution.

Question 3

Your team uses Looker to develop a model on top of BigQuery. The data is partitioned by ingestion time, and analysts frequently query the last 7 days. However, Looker queries are scanning the entire table, causing high costs. Which change should you implement?

Accepted Answer

Apply a LookML access_filter to dynamically filter on the partition column.. The single best approach is to apply a partition filter requirement in LookML and enable partition pruning in BigQuery. Other options are not directly about Looker or are suboptimal.

Answer

Enable BI Engine on the BigQuery table to accelerate queries.

Answer

Create a materialized view that aggregates data daily and point Looker to that view.

Answer

Use clustering on the order_date column to improve query performance.

Question 4

You are building a binary classification model using AutoML Tables on Vertex AI. The dataset has a severe class imbalance (1% positive class). Which strategy should you use to handle the imbalance?

Accepted Answer

Use the class_weight parameter in AutoML Tables training.. The correct approach is to use the class_weight parameter available in AutoML Tables. This parameter allows you to assign higher weight to the minority class during training, directly addressing the imbalance. AutoML Tables does not automatically apply class weighting without configuration; you need to set the class_weight parameter. Oversampling (SMOTE) or downsampling are not recommended as they can distort the data distribution and may not integrate well with AutoML's training pipeline. Option B is incorrect because while AutoML has built-in capabilities, explicitly setting class_weight yields better results.

Answer

Oversample the minority class using SMOTE before training.

Answer

Do nothing; AutoML Tables automatically handles class imbalance.

Answer

Downsample the majority class to match the minority class size.

Question 5

A data engineer is building a Looker Studio dashboard that requires a calculated field to compute the running total of sales per day per store. Which Looker Studio function should they use?

Accepted Answer

RUNNING_SUM(). Looker Studio's RUNNING_SUM function computes a running total within a group, exactly what is needed for a running total per store partitioned by date.

Answer

RANK()

Answer

TOTAL()

Answer

PERCENTILE()

Question 6

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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