PDE Preparing and Using Data for Analysis — All Questions With Answers

Question 1mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 2mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 3mediummulti select

Read the full Preparing and Using Data for Analysis explanation →

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)

Question 4hardmulti select

Read the full Preparing and Using Data for Analysis explanation →

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)

Question 5easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 6hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A data engineer is building a production ML pipeline on Vertex AI. The pipeline must preprocess features (e.g., scaling, encoding) and then train a model. The preprocessing logic must be reusable for serving predictions. Which Vertex AI component should they use?

Question 7mediummulti select

Read the full Preparing and Using Data for Analysis explanation →

A data engineer needs to implement data quality rules and governance policies across multiple data lakes in GCP. They want to automatically discover and catalog data assets, and enforce row-level security. Which two services should they use? (Select TWO)

Question 8mediummulti select

Read the full Preparing and Using Data for Analysis explanation →

A company wants to use BigQuery ML to build a recommendation system for movies. The data includes user IDs, movie IDs, and ratings. Which BigQuery ML model types are suitable for this? (Select TWO)

Question 9hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are building a real-time fraud detection system using BigQuery streaming and a BQML logistic regression model. The model must be retrained every hour with new labeled data. What is the MOST cost-effective approach to serve predictions with low latency?

Question 10mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You need to analyze customer churn and want to understand the rank of each customer's churn probability within their subscription plan. Which BigQuery window function computes the relative ranking from 1 (highest probability) to N?

Question 11hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are building a machine learning pipeline for credit risk assessment. The dataset has a severe class imbalance (1% default rate). You want to use AutoML Tables on Vertex AI. Which strategy should you incorporate to handle imbalance?

Question 12mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You need to build a Looker model that joins multiple tables from BigQuery. Which LookML object defines the relationship between tables?

Question 13easymulti select

Read the full Preparing and Using Data for Analysis explanation →

You want to query data across Google Cloud and AWS using a single SQL interface without moving data. Which TWO services can you use?

Question 14easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You have a BigQuery table 'orders' with columns order_id, customer_id, order_amount, and order_date. You need to rank customers by total spend per month, assigning the rank 1 to the highest spender. Which SQL function should you use in a window clause?

Question 15easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You need to preprocess tabular data for training a classification model using Vertex AI. The dataset has missing values in numerical columns and categorical columns with high cardinality. Which Vertex AI service provides automated feature engineering and preprocessing as part of the pipeline?

Question 16mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A retailer wants to use machine learning to predict customer churn based on transaction history and demographic data. The dataset has 500 features, many of which are correlated. The data is highly imbalanced: only 2% churn. They need to deploy a model that provides feature importance and is interpretable. Which model type should they use in BigQuery ML?

Question 17mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

Your team uses Looker Studio to build dashboards on top of BigQuery. The dashboards are slow when filtering on a high-cardinality dimension (e.g., user ID). You want to improve performance without changing the underlying BigQuery table design. Which action should you take?

Question 18easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You need to track data lineage from a BigQuery table through a series of transformations and into a Vertex AI model training pipeline. Which Google Cloud service provides automated data lineage tracking?

Question 19mediummulti select

Read the full Preparing and Using Data for Analysis explanation →

You need to select two BigQuery features that improve query performance by reducing the amount of data read. Which two options accomplish this? (Choose TWO)

Question 20hardmulti select

Read the full Preparing and Using Data for Analysis explanation →

You are building a time-series forecasting model with BigQuery ML. Which three steps should you perform to properly split the data and evaluate the model? (Choose THREE)

Question 21easymulti select

Read the full Preparing and Using Data for Analysis explanation →

You need to implement data quality rules on a Dataplex lake to ensure that critical columns are not null and meet certain constraints. Which two Dataplex features can you use? (Choose TWO)

Question 22mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A data engineer needs to train a linear regression model in BigQuery ML using a table with 10 million rows. The model will predict sales based on features like advertising spend, seasonality, and store location. Which SQL statement should they use to create and train the model?

Question 23easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A data analyst wants to rank products by sales within each category. They need to assign a unique rank to each product, with no gaps in the ranking numbers (i.e., ties should have different ranks). Which window function should they use?

Question 24mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A data engineer needs to build a LookML model in Looker to define business logic and relationships for a new dataset. They want to create an 'explore' that joins an 'orders' view with a 'customers' view. Where should they define this join?

Question 25mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A company uses Vertex AI Workbench notebooks for data exploration and model development. They want to ensure that the notebook environment can access BigQuery data using the same permissions as the user's Google Cloud account. What is the recommended setup?

Question 26mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A data scientist is using AutoML Tables to build a classification model for predicting customer churn. The dataset is highly imbalanced (only 1% churn). Which strategy should they use to handle the class imbalance within AutoML Tables?

Question 27mediummulti select

Read the full Preparing and Using Data for Analysis explanation →

A data engineer is planning a time-series forecasting model using BigQuery ML ARIMA+ on a dataset with daily sales data spanning 3 years. Which TWO actions are required to prepare the data for ARIMA+? (Choose 2.)

Question 28mediummulti select

Read the full Preparing and Using Data for Analysis explanation →

A company wants to track data lineage for their BigQuery tables to understand how data flows from source to derived tables. Which TWO Google Cloud services can be used to capture and visualize data lineage? (Choose 2.)

Question 29mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A company wants to train a machine learning model to predict customer churn using BigQuery ML. The dataset has a severe class imbalance (only 2% churn). Which approach should the data engineer take to handle this imbalance within BigQuery ML?

Question 30hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A company uses BigQuery Omni to query data stored in AWS S3. They need to join this data with data in BigQuery (GCP). The dataset in AWS is large (10 TB) and frequently updated. Which approach minimizes data movement and cost?

Question 31hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A data engineer needs to implement data lineage tracking for a BigQuery data warehouse. They want to automatically capture column-level lineage from ETL jobs run by Dataform and from manual SQL queries executed in the BigQuery console. Which approach meets these requirements?

Question 32easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A company is using Looker to explore their BigQuery data. They have defined a LookML model with an 'explore' that joins two views: 'orders' and 'customers'. The join is a left join. They want to ensure that only customers with orders are shown when exploring. Which LookML parameter should they modify?

Question 33mediummulti select

Read the full Preparing and Using Data for Analysis explanation →

A data engineer is building a feature store for ML models using Vertex AI Feature Store. The features are computed daily from BigQuery and need to be available for both online predictions (low latency) and offline training. Which two actions must the engineer take? (Choose TWO)

Question 34mediummulti select

Read the full Preparing and Using Data for Analysis explanation →

An e-commerce company uses BigQuery to analyze customer behavior. They need to compute the number of distinct customers per day, approximate quantiles of purchase amounts, and assign a row number per customer partition by date. Which BigQuery SQL functions should they use? (Choose THREE)

Question 35hardmulti select

Read the full Preparing and Using Data for Analysis explanation →

A company uses Dataplex to manage data quality across multiple BigQuery datasets. They need to define data quality rules that check for null values in critical columns and enforce uniqueness constraints. Which two Dataplex features should they use? (Choose TWO)

Question 36easymulti select

Read the full Preparing and Using Data for Analysis explanation →

A data engineer is preparing a dataset for ML training in Vertex AI. The dataset includes a timestamp column, a categorical column with high cardinality (1000 distinct values), and a numerical column with outliers. Which two preprocessing steps should they apply? (Choose TWO)

Question 37hardmulti select

Read the full Preparing and Using Data for Analysis explanation →

A company wants to use BigQuery ML to train a time-series forecasting model on historical sales data. The data is recorded daily for 3 years. They need to evaluate model accuracy using time-series aware cross-validation. Which two options should they configure in the CREATE MODEL statement? (Choose TWO)

Question 38mediummulti select

Read the full Preparing and Using Data for Analysis explanation →

A data engineer needs to build a real-time dashboard in Looker Studio that displays live sales data from BigQuery. The dashboard must refresh every minute. The underlying BigQuery table is updated continuously via streaming inserts. Which two approaches can reduce query cost and latency? (Choose TWO)

Question 39mediummulti select

Read the full Preparing and Using Data for Analysis explanation →

A company stores sensitive customer data in BigQuery. They need to implement column-level security to restrict access to personally identifiable information (PII) columns. Which two BigQuery features can they use together? (Choose TWO)

Question 40mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

Your team has a BigQuery table `events` with a TIMESTAMP column `event_time` and a STRING column `user_id`. You need to add a new column that assigns each event to a 10-minute time bucket, and you want the bucket start time as a TIMESTAMP. Which SQL expression should you use?

Question 41mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

Your team uses Cloud Dataflow to process streaming data from Pub/Sub and write to BigQuery. You need to ensure exactly-once processing semantics to avoid duplicate records in BigQuery. Which Dataflow feature should you use?

Question 42hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are designing a BigQuery table to store clickstream events. The events have a nested structure: each event has a `user_id`, `timestamp`, and an array of `items` (each item has `product_id` and `price`). You want to optimize query performance for queries that filter on `product_id` and `price` within the array. Which table schema and partitioning/clustering strategy should you use?

Question 43hardmulti select

Read the full Preparing and Using Data for Analysis explanation →

Your team is using BigQuery to analyze a large dataset of user events. You need to optimize a query that filters on a nested field 'user_details.country' and also joins with a smaller dimension table. The query currently performs a full table scan. You want to reduce the amount of data processed and improve performance. Which TWO actions should you take? (Choose two.)

Question 44mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A retail company ingests point-of-sale events into a BigQuery table named `sales_raw` with a TIMESTAMP column `transaction_ts` and a FLOAT64 column `amount`. Analysts frequently run reports that aggregate sales by calendar month. To reduce query cost and improve performance, a data engineer wants to create a new table `sales_monthly` that stores only the month (as a DATE truncated to the first day) and the total sales amount per month. Which approach should the engineer use?

Question 45hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are designing a Dataflow pipeline that reads from Pub/Sub and writes to BigQuery. The pipeline must handle late-arriving data and ensure that all data is processed exactly once, even in the event of worker failures. You need to choose a windowing strategy and triggering mechanism. Which combination should you use?

Question 46hardmulti select

Read the full Preparing and Using Data for Analysis explanation →

You are designing a BigQuery schema for a new table that will store user clickstream events. The events include a user ID, timestamp, page URL, and a list of products viewed. You want to optimize for query performance and cost. Which two schema design choices should you make? (Choose two.)

Question 47mediummulti select

Read the full Preparing and Using Data for Analysis explanation →

You are preparing data in BigQuery for analysis. You need to create a new table that contains only rows where the `status` column is 'active' and includes a new column `days_since_creation` calculated as the difference between the current date and the `creation_date` column. Which TWO SQL statements or clauses should you use to achieve this? (Choose two.)

Question 48mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are responsible for a BigQuery dataset that receives daily batch loads from an on-premises Oracle database using the Storage Write API. The ingestion pipeline occasionally inserts duplicate rows when the upstream job retries after a network timeout. You need to guarantee that each business transaction appears exactly once in the BigQuery table, without relying on downstream deduplication. What should you do?

Question 49mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You maintain a BigQuery dataset with a table named `transactions` that contains a `customer_id` column and a `purchase_date` DATE column. To reduce query cost and improve performance for dashboards that filter by `customer_id` and `purchase_date`, you want to physically cluster the table. You need to create a new table with the same data but clustered on `customer_id` and `purchase_date`. Which DDL statement should you use?

Question 50hardmulti select

Read the full Preparing and Using Data for Analysis explanation →

A healthcare analytics company uses BigQuery to store patient data. They need to share a subset of this data with an external research partner in a way that the partner can query the data but cannot modify it, and the company wants to minimize data duplication and storage costs. The partner uses their own Google Cloud project and has their own billing account. Which TWO approaches should the company use to meet these requirements? (Choose two.)

Question 51mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

Your team is using Dataflow to build a streaming pipeline that reads from Pub/Sub and writes to BigQuery. You need to perform a stateful transformation that groups events by a session ID and calculates the number of events per session within a 5-minute window. Which Dataflow feature should you use?

Question 52hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are partitioning a BigQuery table by a DATE column `transaction_date`. You need to query data for the last 7 days, but you want to minimize the amount of data scanned. Which WHERE clause is most efficient?

Question 53mediummulti select

Read the full Preparing and Using Data for Analysis explanation →

You are preparing data for analysis in BigQuery. You need to create a new table that combines data from two existing tables, 'orders' and 'customers', and you want to optimize the table for queries that filter on order_date and customer_region. You also need to ensure that the table is partitioned and clustered appropriately. Which two actions should you take? (Choose two.)

Question 54easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You have a BigQuery table `sales.transactions` with a column `amount` of type NUMERIC and a column `transaction_date` of type DATE. You need to create a new table that contains only transactions from the year 2023 and adds a column `amount_usd` that converts `amount` from EUR to USD using a fixed exchange rate of 1.1. Which SQL statement should you use?

Question 55easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are designing a BigQuery table to store IoT sensor readings. Each reading includes a `device_id` (STRING), `reading_time` (TIMESTAMP), and `temperature` (FLOAT64). The table will receive millions of rows per day, and most queries will filter by a specific `device_id` and a time range within the last 7 days. You want to optimize query performance and minimize cost. What should you do?

Question 56easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A data analyst needs to load a 500 GB CSV file from a Cloud Storage bucket into a BigQuery table. The file has a header row and uses comma delimiters. The analyst wants the load to complete as quickly as possible and wants to avoid schema inference errors. What should the analyst do?

Question 57mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A media company streams clickstream events into BigQuery using the Storage Write API. The events are stored in a table `clicks` with a TIMESTAMP column `event_time` and a STRING column `user_id`. The data arrives continuously, and the table is partitioned by day on `event_time`. Analysts need to query the most recent data with low latency, but they also need to run historical analyses over the past year. The data engineer wants to optimize query performance for both use cases while minimizing storage costs. What should the engineer do?

Question 58mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are using Cloud Data Fusion to build a pipeline that reads data from a Cloud Storage bucket, performs transformations, and writes to BigQuery. The data contains sensitive information, and you need to mask credit card numbers before writing to BigQuery. The pipeline must be reusable and the masking logic should be applied consistently. Which approach should you use?

Question 59mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You need to load a large CSV file (approximately 2 TB) from Cloud Storage into a BigQuery table. The CSV has a header row and uses comma delimiters. You want to ensure the load job is efficient and minimizes errors. Which approach should you take?

Question 60hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A logistics company uses BigQuery to analyze shipment data. The main table `shipments` contains a nested and repeated field `events` that records each scan event with a timestamp and location. Analysts frequently need to filter shipments based on the latest event's location. Currently, queries use a correlated subquery with UNNEST to find the latest event, which is slow and expensive. The data engineer wants to improve query performance by precomputing the latest event location for each shipment and storing it in a separate column. Which approach should the engineer use?

Question 61hardmulti select

Read the full Preparing and Using Data for Analysis explanation →

You are using BigQuery to analyze a dataset that contains a nested and repeated field `items` in a table `orders`. The `items` field is an ARRAY of STRUCTs, each with `product_id` (STRING) and `quantity` (INT64). You need to write a query that returns the total quantity sold for each product across all orders. Which TWO approaches will produce the correct result? (Choose two.)

Question 62easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 63mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 64hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 65mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 66easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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

Question 67hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 68mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 69easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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

Question 70hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 71easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 72mediummulti select

Read the full Preparing and Using Data for Analysis explanation →

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

Question 73mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 74mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 75hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 76mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 77easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

What is the primary purpose of Vertex AI Feature Store?

Question 78mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A company uses Looker Studio to create dashboards from BigQuery data. They notice that dashboard queries take several seconds to load. They want to improve performance without changing the underlying data or creating materialized views. Which option should they use?

Question 79easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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

Question 80mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A company needs to predict whether a product image contains a specific defect. They have 10,000 labeled images and want to build a model quickly without writing custom code or training from scratch. Which GCP service should they use?

Question 81hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A data engineer needs to split time-series data for training a forecasting model. The data is sorted by timestamp. The engineer wants to avoid leakage where future data influences training. Which data splitting approach should they use?

Question 82mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A data team uses Looker Studio to create a report that combines data from two different BigQuery tables: one with sales transactions and another with customer demographics. They need to join these tables in the report without writing SQL. Which feature should they use?

Question 83hardmulti select

Read the full Preparing and Using Data for Analysis explanation →

A data analyst wants to compute the rank of sales per region and also the difference in sales between consecutive months for each region. Which BigQuery analytic functions should they use? (Select TWO)

Question 84mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You train a BigQuery ML linear regression model to predict house prices. The model has high bias during evaluation. Which action BEST reduces bias?

Question 85easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You have a BigQuery table with sales data and want to pivot product categories into columns. Which SQL clause should you use?

Question 86mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

Your Looker dashboard uses a BigQuery connection. You notice that some queries take over a minute. Which service can you enable to cache results in memory for sub-second Looker queries?

Question 87easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

Which Google Cloud service would you use to create a unified data catalog that automatically captures lineage from BigQuery, Cloud Storage, and other sources?

Question 88mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You need to create a time-series forecast for inventory demand using BigQuery ML. The data includes daily sales for 5 years. Which model type should you use?

Question 89mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are using Vertex AI Feature Store to serve features for online predictions. Your model requires features from multiple sources with low latency (<10ms). Which type of serving should you use?

Question 90easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You want to quickly estimate the number of distinct visitors to your website from a large BigQuery table. Which function provides an approximate count with low latency?

Question 91hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are migrating a large on-premises data warehouse to BigQuery. The data includes sensitive PII columns that must be masked for certain users. Which BigQuery feature can automatically redact PII in query results based on user roles?

Question 92mediummulti select

Read the full Preparing and Using Data for Analysis explanation →

A data scientist wants to use Vertex AI Workbench for exploratory data analysis. Which TWO statements are true about Vertex AI Workbench?

Question 93hardmulti select

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 94mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are building a forecasting model to predict daily sales for the next 90 days using historical sales data with clear seasonality and trend. You want to use BigQuery ML with minimal manual tuning. Which model type should you choose?

Question 95hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 96mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are building a multi-cloud analytics solution to join data from Google Cloud and AWS S3. You need to query the S3 data using BigQuery without moving it. Which Google Cloud service should you use?

Question 97hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are using BigQuery ML to train a matrix factorization model for a recommendation system. The training data consists of user-item interactions. You notice that the model is overfitting. Which of the following hyperparameter changes would most likely reduce overfitting?

Question 98mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 99hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You have a BigQuery table 'events' with a TIMESTAMP column 'event_time'. You need to compute, for each event, the difference in seconds from the previous event of the same user. Which window function should you use?

Question 100mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are using Looker to model data from BigQuery. You have a dimension that should be filtered by a user attribute (e.g., user's region). Which LookML concept allows you to apply dynamic row-level security based on user attributes?

Question 101hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A company uses BigQuery and wants to reduce query costs by using BI Engine for Looker Studio dashboards. The data is stored in a BigQuery dataset with 5 TB of frequently accessed tables. The dashboards run dozens of concurrent queries. What is the recommended approach to enable BI Engine acceleration?

Question 102easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

Which BigQuery SQL function can be used to get an approximate count of distinct values in a large column faster than COUNT(DISTINCT) with lower accuracy?

Question 103mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

An organization wants to integrate BigQuery Omni to query data stored in AWS S3. They have set up the necessary connections. What is the primary benefit of using BigQuery Omni over simply copying the data to BigQuery?

Question 104hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A company uses Dataplex to manage data lakes on Google Cloud. They want to enforce data quality rules on a BigQuery table, such as ensuring that a 'email' column is not null and matches a regex pattern. Which Dataplex feature should they use?

Question 105mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A machine learning engineer needs to deploy a custom TensorFlow model for online predictions with low latency. The model is already trained and saved in SavedModel format. Which Vertex AI service should they use?

Question 106easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

Which BigQuery function can be used to retrieve the value of a column from the previous row within a partition, ordered by a timestamp?

Question 107hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A company wants to use BigQuery's PIVOT operator to transform their sales data. They have a table with columns: 'year', 'quarter', 'revenue'. They want to create a report where each row is a year and each column is a quarter (Q1, Q2, Q3, Q4) showing revenue. Which SQL statement is correct?

Question 108mediummulti select

Read the full Preparing and Using Data for Analysis explanation →

A data scientist needs to perform feature engineering for a machine learning model using Vertex AI. They want to preprocess data using a pipeline that includes scaling, one-hot encoding, and handling missing values. Which TWO services can they use to define and execute this preprocessing pipeline? (Choose 2.)

Question 109easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A data engineer needs to design a data pipeline that ingests streaming data from Cloud Pub/Sub, performs real-time aggregations, and loads the results into BigQuery for dashboarding. Which Google Cloud service should they use for the streaming aggregation step?

Question 110mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

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?

Question 111mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A company wants to use AutoML Tables to build a classification model on a dataset with 100 features and 500,000 rows. They need to deploy the model for online predictions with low latency (<100 ms). Which deployment option should they choose?

Question 112easymulti select

Read the full Preparing and Using Data for Analysis explanation →

A data engineer is using Vertex AI Workbench to develop a custom ML model. They want to store and version datasets, track experiments, and register models. Which three Vertex AI services should they use? (Choose THREE)

Question 113mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

Your team stores raw JSON event logs in Cloud Storage. Analysts need to run ad-hoc SQL queries on this data with minimal setup and cost, but they also require the ability to join it with existing BigQuery tables. Which approach should you recommend?

Question 114hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

Your company ingests streaming data into BigQuery using the Storage Write API. You need to ensure that duplicate records are not inserted when the streaming job retries due to transient errors. Which feature should you use?

Question 115mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

Your analytics team queries a large BigQuery table `events` that is partitioned by `event_date` and clustered by `user_id`. A new dashboard runs a query filtering on `user_id = 'abc123'` but does not include a filter on `event_date`. You want to minimize the bytes scanned by this query. What should you do?

Question 116easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You need to load a 500 GB CSV file from Cloud Storage into BigQuery. The file has a header row and uses comma delimiters. You want to load it as quickly as possible without transforming the data. Which approach should you use?

Question 117easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You need to load a large CSV file from Cloud Storage into BigQuery. The file contains a header row and is comma-delimited. You want to ensure that the header row is skipped and that the schema is automatically detected. Which BigQuery load option should you use?

Question 118hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You have a BigQuery table 'logs' with a column 'timestamp' of type TIMESTAMP. You need to create a new table that contains only the logs from the last 7 days, partitioned by day on the 'timestamp' column. Which SQL statement should you use?

Question 119mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You have a BigQuery table `orders` with columns `order_id` (STRING), `customer_id` (STRING), `order_date` (DATE), and `amount` (NUMERIC). You need to create a view that shows, for each order, the cumulative sum of `amount` for that customer, ordered by `order_date` ascending. Which SQL window function or clause should you use?

Question 120mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are building a data pipeline that ingests JSON files from Cloud Storage into BigQuery. The JSON files contain deeply nested arrays and objects. You need to load these files with minimal transformation so that analysts can query individual nested fields using dot notation and also unnest arrays when needed. Which approach should you use?

Question 121mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

Your team maintains a BigQuery dataset where a partitioned table 'transactions' is partitioned by DATE(transaction_time) and clustered by customer_id. Analysts frequently run queries filtering on a specific customer_id and a date range of the last 7 days. They report that these queries scan more data than expected, and you notice that the table was created without a partition filter requirement but with a clustering column. Which action should you take to optimize query performance and reduce bytes scanned?

Question 122easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are ingesting streaming data into BigQuery using the Storage Write API. The data arrives with occasional duplicate records due to at-least-once delivery semantics from the source system. You need to ensure that the final BigQuery table contains no duplicates based on a unique event_id column. Which approach should you use?

Question 123hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are preparing data in BigQuery for analysis. You have a table `orders` with columns `order_id`, `customer_id`, `order_date`, and `amount`. You need to create a new table that includes all orders, plus a column `prev_order_amount` that contains the amount of the customer's previous order by `order_date`. If there is no previous order, the value should be NULL. Which SQL feature should you use?

Question 124easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You need to load a 2 TB CSV file from Cloud Storage into BigQuery. The CSV has a header row and uses a comma delimiter. You want to minimize cost and ensure the load completes quickly. Which method should you use?

Question 125mediummulti select

Read the full Preparing and Using Data for Analysis explanation →

You are using BigQuery to analyze a dataset. You need to create a new table that contains only rows from `sales` where the `region` is 'North America' and the `sale_date` is in the year 2023. You also want to add a column `sale_month` that extracts the month from `sale_date`. Which two actions should you take? (Choose two.)

Question 126easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You need to load a large CSV file from Cloud Storage into BigQuery. The file has a header row and contains a column with date values in the format 'YYYY-MM-DD'. You want to ensure the date column is correctly recognized as a DATE type. Which method should you use?

Question 127easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

A financial services firm stores customer transaction data in a BigQuery table. The table contains a column `customer_id` that is frequently used in WHERE clauses, but the table is not partitioned. Queries filtering on a specific `customer_id` scan the entire table, which is large and costly. The data engineer wants to reduce the bytes scanned for these queries without changing the table's partitioning scheme. What should the engineer do?

Question 128easymultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are loading a CSV file into BigQuery. The file has a column `price` that contains values like `$1,234.56`. You want to load it into a NUMERIC column. What should you do?

Question 129mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

Your team stores IoT sensor readings in a BigQuery table `project.sensors.readings` with columns `sensor_id` (STRING), `reading_time` (TIMESTAMP), and `temperature` (FLOAT64). You need to create a new table that adds a column `avg_temp_7d` containing, for each row, the average temperature of that sensor over the preceding 7 days (including the current row). Which SQL feature should you use to compute this efficiently?

Question 130mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You have a BigQuery table `logs` with a column `message` that contains JSON strings. You need to extract the value of the `user_id` field from each JSON string and return it as a separate column. Which function should you use?

Question 131hardmulti select

Read the full Preparing and Using Data for Analysis explanation →

You are designing a BigQuery table to store clickstream events. Queries will frequently filter by a user_id column and a TIMESTAMP column named event_time, and the table will grow to several petabytes. You want to minimize bytes scanned and cost for these queries. Which TWO actions should you take? (Choose two.)

Question 132hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are designing a BigQuery schema for a table that will store user profile data. The data includes a unique user ID, a list of email addresses (each with a type and address), and a timestamp of last update. You need to support efficient queries that retrieve all email addresses for a given user and also filter users by email type. Which schema design should you use?

Question 133hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are using Cloud Dataflow to stream data from Pub/Sub into BigQuery. The incoming messages are JSON strings with a field `event_time` in ISO 8601 format (e.g., "2024-03-15T14:30:00Z"). You need to write the data to a BigQuery table with a column `event_timestamp` of type TIMESTAMP. Which transformation should you apply in your Dataflow pipeline?

Question 134hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You have a BigQuery table `sales` with columns `order_id`, `customer_id`, `order_date` (DATE), and `amount` (NUMERIC). You need to create a new table that contains, for each customer, the total sales amount and the date of their most recent order. The result should include `customer_id`, `total_amount`, and `last_order_date`. Which SQL query achieves this correctly?

Question 135mediummultiple choice

Read the full Preparing and Using Data for Analysis explanation →

Your team uses Cloud Dataflow to stream events from Pub/Sub into BigQuery. Some events arrive late, up to 10 minutes after their event timestamp. You need the pipeline to produce correct aggregations per 5-minute window, including late data, while keeping latency low for on-time events. What should you do?

Question 136hardmultiple choice

Read the full Preparing and Using Data for Analysis explanation →

You are designing a Dataflow pipeline that reads from a Cloud Storage bucket containing thousands of small JSON files, transforms the data, and writes to BigQuery. The pipeline is slow and expensive because of the large number of small files. You want to improve throughput and reduce cost. What should you do?