Professional Data Engineer

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

A retail company is migrating a nightly on-premises ETL job to Google Cloud. During the migration cutover period, both the legacy on-premises pipeline and the new Dataflow pipeline will run in parallel against the same source data for two weeks to build confidence before decommissioning the old system. The data engineering team must prove that the new pipeline produces identical aggregated results to the legacy system before switching over. Which approach best validates output fidelity during this parallel-run period?

Accepted Answer

Load both pipeline outputs into separate BigQuery tables and run scheduled reconciliation queries that compare row counts, checksums, and aggregate totals, flagging any discrepancies for investigation.

Answer

Rely on Dataflow's built-in exactly-once processing guarantees, which ensure the new pipeline output automatically matches the legacy system.

Answer

Decommission the legacy pipeline immediately after the first successful Dataflow run and monitor downstream dashboards for anomalies.

Answer

Enable Cloud Monitoring alerts on the Dataflow job's system lag and element throughput metrics to confirm the outputs are correct.

Question 4

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 5

A retail analytics team stores a large customer transactions table in a BigQuery dataset called 'raw_finance'. A separate marketing team needs read access only to aggregated, non-PII summary data derived from that table. The finance team must retain full control of the underlying dataset and must not grant the marketing team any direct access to 'raw_finance'. What is the most appropriate way to design this data-sharing solution?

Accepted Answer

Create an authorized view in a separate dataset that queries the aggregated data, and grant the marketing team access only to the dataset containing the view.

Answer

Grant the marketing team the BigQuery Data Viewer role on the 'raw_finance' dataset and instruct them to query only the summary columns.

Answer

Export the aggregated results to a Cloud Storage bucket nightly and grant the marketing team read access to the bucket.

Answer

Copy the aggregated data into a new table inside 'raw_finance' and grant the marketing team access to that single table.

Question 6

A data engineering team runs hundreds of ETL queries every night against an on-demand BigQuery project. Occasionally, queries fail immediately with a 'resources exceeded' or concurrency-related error because too many interactive queries are submitted at once and hit the per-project concurrent query limit. These jobs are not time-sensitive as long as they complete before the morning business reports run. Which change should the team make to improve reliability without moving to a flat-rate reservation?

Accepted Answer

Submit the ETL jobs using BATCH priority so BigQuery queues them and starts them as capacity becomes available instead of failing on concurrency limits.

Answer

Set a higher maximum_bytes_billed value on each query so the jobs are allowed to consume more resources.

Answer

Enable BigQuery BI Engine for the project so the ETL queries are served from in-memory acceleration.

Answer

Increase the per-query timeout so queries wait longer before returning the concurrency error.

Question 7

A retail analytics team has a 12 TB BigQuery table already partitioned by DATE(order_timestamp). Most dashboard queries filter on a specific date range AND a specific customer_id (there are roughly 8 million distinct customer IDs). Currently these queries scan the entire day's partition even when only one customer is needed, driving up on-demand costs. Without changing the partitioning strategy, what is the most effective way to reduce bytes scanned for these filtered queries?

Accepted Answer

Add clustering on customer_id so that within each date partition, data is sorted by customer_id and blocks can be pruned during filtered queries

Answer

Repartition the table by customer_id instead of by date to align physical storage with the filter column

Answer

Create a materialized view aggregated by date only, since dashboards read daily totals

Answer

Add an integer-range partition on a hash of customer_id to spread customers evenly across partitions

Question 8

A retail analytics team is designing a BigQuery table to store customer orders. Each order has one customer and multiple line items (product, quantity, price). Analysts run frequent aggregations across orders and line items, and query cost and performance are top priorities. The team is deciding how to model the relationship between orders and their line items in BigQuery. What is the recommended schema design?

Accepted Answer

Create a single orders table with line items stored as a nested, repeated RECORD field within each order row.

Answer

Create separate normalized orders and line_items tables and JOIN them at query time using an order_id foreign key.

Answer

Store each line item as a separate row and duplicate all order-level columns across every line-item row in one flat table.

Answer

Store line items as a JSON string column and parse them with JSON functions at query time.

Question 9

A financial services company stores critical transactional data in a BigQuery dataset located in the us-central1 region. Compliance requires that the company be able to recover the dataset with a Recovery Point Objective (RPO) of no more than 4 hours if the entire region becomes unavailable. The team wants a solution that is automated, cost-effective, and requires minimal ongoing operational overhead. Which approach should they implement?

Accepted Answer

Configure a scheduled query or scheduled data transfer that copies the dataset to a second dataset in a different region at least every 4 hours.

Answer

Rely on BigQuery's built-in 7-day time travel feature to restore the dataset after a regional outage.

Answer

Enable multi-region storage by moving the dataset to the 'US' multi-region location, which guarantees automatic failover with zero RPO.

Answer

Export the dataset to a single-region Cloud Storage bucket in us-central1 nightly using a batch extract job.

Question 10

Your team runs BigQuery in an Enterprise edition reservation for a project that executes heavy ETL only between 01:00 and 05:00 UTC each night. Outside that window, the project runs almost no queries. Finance wants to eliminate the cost of idle capacity during the 20 idle hours per day while still guaranteeing fast throughput during the nightly batch. What is the most cost-effective reservation configuration?

Accepted Answer

Set a baseline of 0 slots and configure a max slots (autoscale) value large enough to cover the nightly batch, so slots are provisioned only when queries run.

Answer

Purchase a one-year slot commitment sized to the nightly peak to guarantee capacity every night at a discounted rate.

Answer

Set the baseline equal to the nightly peak slot demand and leave autoscale disabled so capacity is always available.

Answer

Move the project to on-demand pricing and remove the reservation entirely to avoid managing slots.

Question 11

A retail analytics team lands raw daily CSV and Parquet files in a Cloud Storage data lake. Analysts run ad-hoc exploratory queries only a few times a week over recent files, and the raw files are frequently overwritten by an upstream process. The team wants to minimize storage duplication and avoid an ETL step, while accepting slower query performance for these occasional queries. What is the most appropriate way to make this data queryable in BigQuery?

Accepted Answer

Create BigQuery external tables (BigLake) pointing to the Cloud Storage files and query them in place.

Answer

Load the files into native BigQuery tables each day with a scheduled batch load job.

Answer

Copy the files into a Bigtable instance keyed by date and query through the BigQuery Bigtable connector.

Answer

Stream the file contents into BigQuery using the Storage Write API as files arrive.

Question 12

A retail company runs a mix of workloads in BigQuery. Their nightly ETL jobs consume large, predictable volumes of slots, and their finance team requires stable, predictable monthly billing with no risk of surprise overages. Meanwhile, a small data science team runs occasional ad hoc exploratory queries that are sporadic and low-volume. The data engineering lead must recommend how to organize these workloads to meet the business requirements while minimizing total cost. What should they recommend?

Accepted Answer

Purchase a BigQuery slot commitment (reservation) sized for the predictable ETL workload, and leave the sporadic ad hoc queries on on-demand pricing.

Answer

Put both the nightly ETL and the ad hoc data science queries on on-demand pricing so all teams share a single billing model.

Answer

Purchase a single large slot commitment sized to cover both the ETL and all possible ad hoc query bursts to guarantee no on-demand charges ever occur.

Answer

Move all workloads to on-demand pricing but apply a per-project custom quota to cap monthly bytes billed for the ETL workload.

Question 13

A retail analytics team is designing a BigQuery dataset for daily sales reporting. Analysts frequently query aggregated revenue by product category, region, and date. The team debates whether to keep normalized dimension tables (products, stores) separate from a large fact table or to consolidate the data. Query cost and performance on large scans are the primary concerns, and the dimension tables change infrequently. Which schema approach best fits BigQuery's architecture?

Accepted Answer

Denormalize the dimensions into the fact table (optionally using nested/repeated fields), producing a wide, flattened schema optimized for scans.

Answer

Maintain a strict third-normal-form star schema and rely on runtime JOINs across many small dimension tables for every query.

Answer

Store the fact table in BigQuery but keep dimension tables in Cloud SQL and use federated queries to join them at read time.

Answer

Split the fact table into one physical table per product category to eliminate the need for filtering.

Question 14

Your company runs on-demand BigQuery for most of its ad hoc analytics, but every month-end there is a 3-day period where the finance team executes very heavy scheduled batch reports, causing costs to spike unpredictably and occasionally hitting on-demand concurrency limits. Outside of month-end, query volume is low and steady. Management wants predictable, minimized cost for the month-end workload without over-committing capacity for the rest of the month. What should you do?

Accepted Answer

Create a reservation using flex slot commitments (short-term/autoscaling) that you activate for the 3-day month-end window, and assign the finance batch jobs to it while keeping other analytics on on-demand.

Answer

Purchase an annual slot commitment sized for the month-end peak and route all workloads through the reservation.

Answer

Increase the on-demand per-project concurrency quota and leave all workloads on on-demand pricing.

Answer

Purchase a monthly flat-rate commitment for the peak capacity and keep it for the entire month.

Question 15

A retail analytics team stores a large fact table in BigQuery keyed by a numeric customer_id (values 1 to roughly 5,000,000). Nearly all analytical queries filter on a specific range of customer_id to process a subset of customers at a time, but the table has no meaningful timestamp column that queries filter on. The team wants to prune data scanned and reduce query cost without changing how analysts write their WHERE clauses. Which BigQuery design should they use?

Accepted Answer

Create an integer-range partitioned table on customer_id with a defined start, end, and interval.

Answer

Add an ingestion-time (_PARTITIONTIME) partition and instruct analysts to also filter on _PARTITIONTIME.

Answer

Cluster the table on customer_id without partitioning and rely on clustering to prune scanned bytes.

Answer

Create a DATE-partitioned table using a synthetic date derived by hashing customer_id into buckets.

Question 16

Your finance team wants a weekly automated report attributing BigQuery on-demand query costs to individual users and teams so they can identify the most expensive recurring queries. You need a low-maintenance approach that does not require enabling additional export pipelines and can be scheduled to run without external tooling. What should you do?

Accepted Answer

Query the INFORMATION_SCHEMA.JOBS_BY_PROJECT view to aggregate total_bytes_billed by user_email and query, then wrap it in a scheduled query that writes results to a reporting table.

Answer

Enable Cloud Audit Logs export to a Cloud Storage bucket and build a Dataflow pipeline that parses the logs weekly to compute per-user cost.

Answer

Ask each user to manually record their query bytes from the BigQuery UI job history and submit them to a shared spreadsheet each week.

Answer

Enable the BigQuery billing export to a dataset and run a Data Fusion pipeline that joins billing rows to individual users by timestamp.

Question 17

Your organization runs BigQuery workloads for several departments (marketing, finance, and engineering) within a single billing account. Finance leadership wants a repeatable, automated way to break down monthly BigQuery costs by department for chargeback, without creating separate projects for each team. What is the most effective approach?

Accepted Answer

Apply consistent resource labels (e.g., department:marketing) to datasets and jobs, then analyze costs using labels in the exported Cloud Billing data in BigQuery.

Answer

Enable on-demand pricing and instruct each department to record their query bytes manually in a shared spreadsheet each month.

Answer

Create a separate reservation for each department and rely on the reservation name shown in the INFORMATION_SCHEMA.JOBS view alone for billing.

Answer

Give each department a distinct service account and total the number of jobs each account runs to estimate proportional cost.

Question 18

A retail analytics team runs a dashboard that refreshes every few minutes. The underlying query aggregates daily sales totals by store and product category from a large, continuously appended BigQuery fact table (billions of rows). The same aggregation is executed hundreds of times per hour, and the team is concerned about repeatedly scanning the full table and the associated query cost. They want the aggregated results to stay automatically up to date as new rows arrive, with minimal maintenance overhead. What should they do?

Accepted Answer

Create a BigQuery materialized view over the fact table that pre-aggregates sales by store and category, letting BigQuery incrementally maintain and automatically use it.

Answer

Create a standard (logical) view with the aggregation query so results are always computed against the latest data.

Answer

Schedule a query with Cloud Scheduler and Cloud Composer to rebuild an aggregate table from scratch every few minutes.

Answer

Export the fact table to Cloud Storage and use a BigQuery external table so the dashboard reads pre-aggregated files.

Question 19

Your data engineering team runs an on-demand BigQuery project where analysts frequently write ad hoc queries. Last month a single mistyped query scanned 45 TB and caused an unexpected cost spike. Leadership wants a repeatable, automated guardrail that prevents any individual query from processing more than a set data volume, and rejects the query before it runs if it would exceed that limit. What should you implement?

Accepted Answer

Set a custom query quota (maximum bytes billed per query) as a default at the project level so queries estimated to exceed the threshold fail before execution.

Answer

Configure a BigQuery daily on-demand cost budget in Cloud Billing with an email alert at 90% of the monthly threshold.

Answer

Create a scheduled query that periodically checks INFORMATION_SCHEMA.JOBS and cancels any query that has already scanned too much data.

Answer

Move the project to a flat-rate reservation with a fixed slot baseline so runaway queries cannot increase cost.

Question 20

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 21

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 22

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 23

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 24

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 25

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 26

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 27

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 28

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 29

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 30

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 31

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 32

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 33

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 34

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 35

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 36

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.

Question 37

A data engineer is building a churn-prediction model in BigQuery ML from a table of daily customer transactions. The team wants each training row to include a feature representing the number of days since the customer's previous purchase, computed directly in SQL so the same logic is reproducible. Which approach correctly generates this feature during data preparation?

Accepted Answer

Use the DATE_DIFF function combined with the LAG window function partitioned by customer_id and ordered by purchase_date.

Answer

Use a self-join of the transactions table on customer_id without any ordering, then subtract the two dates.

Answer

Use the RAND function to sample a prior purchase date for each row and compute the difference.

Answer

Use APPROX_COUNT_DISTINCT on purchase_date grouped by customer_id to estimate purchase frequency.

Question 38

Your data team runs ad hoc analytics on BigQuery using on-demand (per-TB) pricing. Last month a few analysts accidentally ran full-table scans on multi-terabyte tables, causing an unexpected spike in your bill. Leadership wants to prevent any single analyst from consuming an excessive amount of on-demand bytes while keeping the flexible on-demand model for exploratory work. What is the most effective way to enforce this?

Accepted Answer

Configure per-user and per-project custom query quotas (maximum bytes billed per day) at the project level

Answer

Purchase a flat-rate slot commitment and place all analysts in a single reservation

Answer

Enable BigQuery BI Engine to cache query results and reduce bytes scanned

Answer

Set a maximum bytes billed limit only in each individual analyst's saved query settings

Question 39

A retail analytics team stores five years of point-of-sale transactions in a single BigQuery table (~8 TB). Nearly all analyst queries filter on a transaction_date range (usually the last 30-90 days) and frequently also filter or group by store_id. Current queries scan the entire table, driving up on-demand query costs. The team wants to minimize bytes scanned without rewriting the analysts' query patterns. What table design should you recommend?

Accepted Answer

Partition the table by transaction_date and cluster by store_id.

Answer

Cluster the table by transaction_date and cluster by store_id, with no partitioning.

Answer

Partition the table by store_id and cluster by transaction_date.

Answer

Create a separate table per month and use a wildcard query across the monthly tables.

Question 40

A retail analytics team loads daily sales events into a BigQuery table partitioned by ingestion date. Compliance requires that only the last 90 days of detailed event data be queryable, and older data should be automatically removed to control storage cost without manual cleanup jobs. Analysts still run frequent queries filtered on the event date. What is the most operationally efficient way to meet this requirement?

Accepted Answer

Set a partition expiration of 90 days on the partitioned table so BigQuery automatically deletes partitions older than the threshold.

Answer

Schedule a daily Cloud Composer DAG that runs a DELETE statement removing rows older than 90 days.

Answer

Create an authorized view that filters to the last 90 days and revoke access to the base table.

Answer

Export partitions older than 90 days to Cloud Storage nightly and truncate the table.

Question 41

Your organization uses BigQuery flat-rate reservations. You created a reservation named 'etl-res' with 500 slots and assigned it at the organization level to a folder called 'data-eng'. A new project 'nightly-loads' was placed inside the 'data-eng' folder, but you also want that single project's queries to run against a separate 'priority-res' reservation with 200 slots instead of the folder's assignment. What is the correct way to achieve this?

Accepted Answer

Create an assignment linking the 'nightly-loads' project directly to 'priority-res'; the more specific project-level assignment overrides the inherited folder-level assignment.

Answer

Delete the folder-level assignment because reservations cannot be assigned at the folder level, then reassign each project individually.

Answer

Move 'nightly-loads' out of the 'data-eng' folder, since a project can only use a reservation from a folder it does not belong to.

Answer

Add the 200 slots from 'priority-res' into 'etl-res' and use query labels to route 'nightly-loads' queries to the additional capacity.

Question 42

Your data team runs BigQuery on a slot-based reservation with editions pricing. Nightly ETL workloads require a consistent minimum of 500 slots to meet SLAs, but they occasionally spike to 1,500 slots during month-end processing. During the day, ad hoc BI queries are unpredictable and light. You want to guarantee the ETL baseline capacity at the lowest committed cost while still handling spikes automatically. How should you configure the reservation?

Accepted Answer

Set a baseline of 500 slots with a 1-year commitment and configure the reservation autoscaler with a max of 1,500 slots so additional capacity is added only when needed.

Answer

Set a baseline of 1,500 slots with no autoscaling to ensure the month-end spikes are always covered.

Answer

Set a baseline of 0 slots and rely entirely on the autoscaler up to 1,500 slots to minimize committed spend.

Answer

Switch the workload to on-demand pricing so BigQuery automatically allocates slots up to the per-project limit without any reservation.

Question 43

Your company purchased a one-year BigQuery Enterprise edition slot commitment 11 months ago to cover predictable ELT workloads. A recent architecture change moved most transformations to Dataflow, and internal reports show the committed slots are now consistently underutilized. Finance asks you to reduce ongoing BigQuery compute spend as the commitment nears expiration, while keeping some guaranteed capacity for remaining BI queries. What should you do?

Accepted Answer

Before the commitment renews, disable auto-renewal and create a new smaller baseline reservation sized to the reduced BI workload, allowing autoscaling for occasional spikes.

Answer

Let the current commitment auto-renew, then delete unused reservations the following month once usage stabilizes.

Answer

Immediately cancel the active commitment and switch all workloads to on-demand pricing to avoid any further capacity charges.

Answer

Move all remaining BI queries to a separate on-demand project and keep the full commitment reservation idle as a backup.

Question 44

Your company runs BigQuery in the Enterprise edition with a single reservation of 1,000 baseline slots assigned to a production project for scheduled ETL. During overnight batch windows the ETL reservation is fully utilized, but during the day it sits nearly idle while a separate ad-hoc analytics project (currently on-demand billing) generates unpredictable, spiky query costs. Leadership wants to reduce overall cost and improve slot utilization without degrading the ETL SLA. What should you do?

Accepted Answer

Move the analytics project into the same reservation, enable idle slot sharing on the reservation, and configure autoscaling so peak demand above the baseline is met by scaled slots.

Answer

Create a second reservation with 1,000 baseline slots for the analytics project and disable idle slot sharing on both reservations to guarantee isolation.

Answer

Keep the analytics project on on-demand pricing and lower the ETL reservation baseline to 200 slots to save money during the day.

Answer

Purchase a three-year capacity commitment for 2,000 slots and assign all projects to a single reservation without autoscaling.

Question 45

Your company runs two BigQuery Enterprise edition reservations: 'prod-etl' (500 baseline slots, autoscale to 1000) for nightly transformation jobs, and 'adhoc-analytics' (200 baseline slots, no autoscale) for data scientists' interactive queries. During business hours, the ETL reservation sits mostly idle, while analysts frequently complain that their queries queue for minutes waiting for slots. Management wants analyst query latency reduced without increasing total committed spend. What should you do?

Accepted Answer

Enable slot sharing by leaving 'ignore idle slots' unset (default) on the 'prod-etl' reservation so its idle slots become available to the 'adhoc-analytics' reservation.

Answer

Increase the 'adhoc-analytics' baseline to 700 slots and reduce 'prod-etl' baseline to 0, relying entirely on autoscaling for ETL.

Answer

Move the 'adhoc-analytics' workload to on-demand billing so it is never constrained by reserved slots.

Answer

Set the 'adhoc-analytics' reservation's max autoscale slots to 1000 to match the ETL reservation.

Question 46

A retail company uses BigQuery with a flat-rate reservation of 1,000 slots. Their nightly ETL jobs and interactive BI dashboard queries share the same reservation. During the day, analysts complain that dashboards are slow whenever ad-hoc data science queries run large scans, and at night the ETL jobs sometimes run long because reporting refresh jobs compete for slots. The team wants to isolate these workloads so critical dashboards always get priority, while still allowing idle capacity to be reused. What should they do?

Accepted Answer

Create separate reservations (e.g., for BI, ETL, and data science) with assigned baseline slots, attach projects/folders via assignments, and leave 'ignore idle slots' disabled so unused slots are shared across reservations.

Answer

Switch all workloads to on-demand pricing so each query is isolated by Google's per-query capacity and no workload can starve another.

Answer

Keep a single reservation but increase the total commitment to 3,000 slots so there is always enough capacity for every workload simultaneously.

Answer

Create one reservation per workload and enable 'ignore idle slots' on all of them so each workload is strictly capped and never borrows capacity.

Question 47

Your team runs a critical BigQuery scheduled query every morning that populates a reporting table consumed by executive dashboards. Last week the query silently failed for three consecutive days because an upstream table was renamed, and no one noticed until executives complained about stale data. You need a low-maintenance, native solution that alerts the on-call engineer whenever any scheduled query fails, without building custom polling scripts. What should you do?

Accepted Answer

Configure Pub/Sub notifications on the scheduled query's transfer config and create a Cloud Monitoring alerting policy on the log-based metric for BigQuery Data Transfer Service run failures, routing notifications to a Pub/Sub or email channel.

Answer

Write a Cloud Function on a Cloud Scheduler trigger that queries the reporting table's last-modified timestamp every hour and emails the team if it is older than expected.

Answer

Migrate the scheduled query into a Cloud Composer DAG and rely on Airflow's default task retry mechanism to re-run the query until it succeeds.

Answer

Enable BigQuery table expiration on the reporting table so that stale data is automatically deleted, forcing analysts to notice the missing table.

Question 48

A data engineering team owns a BigQuery scheduled query that materializes a nightly reporting table. The query was originally created by an engineer who has since left the company; when her user account was deactivated, the scheduled query began failing with authentication errors. The team wants the pipeline to keep running reliably regardless of employee turnover, while following Google-recommended practices. What should they do?

Accepted Answer

Configure the scheduled query to run as a service account that has the required BigQuery permissions, and grant the team the ability to manage that service account.

Answer

Recreate the scheduled query under a shared team member's personal Google account so multiple people can maintain it.

Answer

Move the logic into a Cloud Scheduler cron job that triggers the query using the departed engineer's cached OAuth token.

Answer

Grant the BigQuery Data Editor role to the allUsers principal on the destination dataset so the query no longer needs authentication.

Question 49

A data engineering team runs a nightly transformation that reads from three staging tables and writes into a reporting table used by BI dashboards each morning. They currently trigger the SQL manually. They want a fully managed, low-maintenance way to run this single BigQuery SQL statement on a recurring schedule with built-in run history, retries, and no external infrastructure to manage. Which approach best meets these requirements with the least operational overhead?

Accepted Answer

Configure a BigQuery scheduled query that runs the transformation SQL on a nightly cron schedule.

Answer

Deploy a Cloud Composer environment and author an Airflow DAG with a BigQueryInsertJobOperator to run the SQL nightly.

Answer

Create a Cloud Scheduler job that publishes to Pub/Sub, triggering a Cloud Function that calls the BigQuery API.

Answer

Write a Python script on a Compute Engine VM invoked by a system cron entry to execute the query each night.

Question 50

Your team runs a nightly transformation in BigQuery that consists of a single SQL statement writing to a reporting table. It has no upstream or downstream dependencies on other systems, requires no conditional branching, and only needs to run once per day at 02:00. Currently it is a manual query someone triggers each morning. Operations wants the lowest-maintenance way to automate this while keeping failure notifications visible. Which approach best fits these requirements?

Accepted Answer

Create a BigQuery scheduled query with a daily schedule at 02:00 and configure a Pub/Sub notification (or email) on run completion/failure.

Answer

Deploy a Cloud Composer environment and build a DAG with a single BigQueryInsertJobOperator scheduled daily.

Answer

Create a Cloud Function triggered by Cloud Scheduler that submits the query through the BigQuery client library.

Answer

Write a Dataflow batch pipeline that executes the SQL and schedule it with a Flex Template trigger.

Question 51

Your company recently purchased a 2,000-slot annual flat-rate commitment in BigQuery to handle the analytics workload of several teams. Three months in, the finance team reports the commitment is expensive, and you suspect it is over-provisioned. Before renewing or resizing, you need data-driven evidence about how many slots your queries actually consume during peak and off-peak periods, and how a smaller commitment would affect query performance. What should you do to right-size the commitment?

Accepted Answer

Use the BigQuery slot estimator (autoscaler recommendations) and the INFORMATION_SCHEMA.JOBS slot-consumption views to analyze historical slot usage and model the performance impact of different commitment sizes before adjusting.

Answer

Immediately cancel the annual commitment and switch all workloads to on-demand pricing, since on-demand is always cheaper than flat-rate for underutilized reservations.

Answer

Reduce the commitment to 500 slots right away and wait for user complaints to determine whether the smaller size causes performance problems.

Answer

Enable BI Engine on all datasets so that queries no longer consume reservation slots, eliminating the need to analyze slot usage.

Question 52

Your company purchased 2,000 baseline slots using a BigQuery Enterprise edition capacity commitment. You created two reservations: 'prod-etl' (1,200 slots) for scheduled nightly pipelines and 'adhoc-analytics' (800 slots) for interactive analyst queries during business hours. Overnight, the ETL workload frequently needs more than 1,200 slots while the analytics reservation sits idle, causing pipeline SLAs to be missed. You want to let ETL borrow the unused analytics capacity automatically without buying more slots. What should you do?

Accepted Answer

Enable idle slot sharing on the 'adhoc-analytics' reservation so its unused slots can be consumed by other reservations that need them.

Answer

Increase the baseline slots of the 'prod-etl' reservation to 2,000 and delete the 'adhoc-analytics' reservation.

Answer

Switch the ETL jobs to on-demand pricing so they scale independently of the reservation.

Answer

Set the max autoscaling slots on 'prod-etl' higher, which forces the analytics reservation to release its baseline slots.

Question 53

A retail analytics team runs predictable, heavy BigQuery workloads every business day: several large scheduled ETL jobs plus dashboards that refresh throughout working hours. Their monthly on-demand bytes-scanned costs have become large and unpredictable, and finance wants a stable, capped monthly spend for the analytics workload. They also occasionally run one-off ad hoc exploration queries that should not block the production ETL jobs. Which approach best meets the requirement for predictable cost while isolating workloads?

Accepted Answer

Purchase BigQuery editions capacity with a baseline slot commitment, then create separate reservations and assign the ETL and dashboard projects to dedicated reservations to isolate them.

Answer

Keep on-demand pricing but add clustering and partitioning to every table so that bytes scanned drop below the monthly budget threshold.

Answer

Export all data to Cloud Storage and query it with BigQuery external tables to avoid slot charges entirely.

Answer

Move the workload to a single large Dataproc cluster running Spark SQL to obtain a fixed monthly compute cost.

Question 54

A retail analytics team stores a customer dimension table in BigQuery. They must preserve full historical attribute values (e.g., a customer's address as it was at the time of each past order) so that historical reports remain accurate even after a customer's details change. Analysts frequently join fact tables to this dimension using the value that was effective at the transaction date. Which schema design should the team implement?

Accepted Answer

Implement a Type 2 slowly changing dimension: add surrogate keys plus effective_start_date and effective_end_date columns, inserting a new row each time an attribute changes.

Answer

Overwrite the existing dimension row in place whenever a customer's attribute changes (Type 1), keeping only the current values.

Answer

Store only the current customer attributes and rely on BigQuery time travel to reconstruct historical attribute values for any past date.

Answer

Add a single extra column that holds the previous value of each attribute (Type 3), overwriting it on each subsequent change.

Question 55

Your team runs a nightly BigQuery batch load pipeline plus dozens of interactive analyst queries during business hours, all on a single on-demand billing project. Finance reports monthly BigQuery costs are volatile and unpredictable, and they want a predictable maximum monthly spend without blocking analysts entirely when batch jobs are running. You must reorganize the workloads to give both predictable cost and workload isolation. Which approach best meets these requirements?

Accepted Answer

Purchase a BigQuery Editions capacity commitment and create two separate reservations — one assigned to the ETL project and one to the analytics project — each with a baseline and a capped max autoscale slot count.

Answer

Keep on-demand billing but set a project-level custom quota limiting bytes billed per day, and ask analysts to schedule heavy queries at night.

Answer

Move all workloads into a single reservation with a large baseline and unlimited autoscaling so no job is ever queued.

Answer

Switch the nightly batch load to flex slots purchased each evening and leave analyst queries on on-demand pricing.

Question 56

An industrial IoT company ingests sensor readings from 50,000 devices, each emitting one reading per second. The primary access pattern is retrieving the most recent readings for a single device over the last hour, and occasionally scanning a device's readings across a full day. The team chose Cloud Bigtable for its write throughput and low-latency reads. Which row key design best supports these access patterns while avoiding hotspotting?

Accepted Answer

Use deviceId#reverseTimestamp so rows for a device are contiguous and the newest readings sort first.

Answer

Use a reverse timestamp alone as the row key (e.g., LONG_MAX - timestamp).

Answer

Use a monotonically increasing sequential integer as the row key.

Answer

Use timestamp#deviceId so all readings within the same second are grouped together.

Question 57

An industrial monitoring platform collects sensor readings from 500,000 devices, each emitting one metric every second (roughly 43 billion writes per day). Analysts need single-digit-millisecond lookups of the most recent readings for any given device over a specified time range, and the workload will grow to millions of devices. Which storage system and design best fits these requirements?

Accepted Answer

Cloud Bigtable with a row key of deviceID#reverseTimestamp, storing metrics in column families

Answer

Firestore in Native mode with one document per reading, indexed by device and timestamp fields

Answer

Cloud SQL for PostgreSQL with a composite index on (device_id, reading_time) and read replicas

Answer

BigQuery with a table partitioned by ingestion date and clustered on device_id

Question 58

Your team runs two independent Cloud Composer DAGs. The first DAG ingests raw sales events into BigQuery every hour. A separate downstream DAG builds hourly aggregate reports, but it must only run after the corresponding ingestion DAG run for that hour has fully completed. The teams want to keep the two DAGs separate for ownership reasons, and the downstream DAG should not start processing until the upstream data is confirmed present. What is the best way to coordinate these two DAGs?

Accepted Answer

Use an ExternalTaskSensor in the downstream DAG to wait for the successful completion of the upstream DAG's final task for the matching execution date.

Answer

Merge both DAGs into a single DAG so tasks run sequentially with normal task dependencies.

Answer

Add a fixed time_delta so the downstream DAG always starts 30 minutes after the upstream DAG's scheduled start.

Answer

Trigger the downstream DAG manually each hour after checking the upstream DAG's status in the Airflow UI.

Question 59

A data engineering team runs a nightly Python cleanup script that deletes stale objects from Cloud Storage and updates a small metadata table in BigQuery. The script currently runs on a manually started Compute Engine VM that someone must remember to start and stop, and the team frequently forgets, leaving the VM running and incurring cost. The team wants a fully managed, serverless approach that runs the script on a fixed schedule with no idle infrastructure and minimal operational overhead. Which approach best meets these requirements?

Accepted Answer

Deploy the script as a Cloud Function (2nd gen) and invoke it on a cron schedule using Cloud Scheduler.

Answer

Keep the Compute Engine VM but add an instance schedule to start it nightly, and rely on the script to shut the VM down when finished.

Answer

Create a Cloud Composer environment and build a single DAG that runs the script daily via a PythonOperator.

Answer

Package the script into a Dataproc initialization action and run it on an ephemeral cluster each night.

Question 60

Your team runs a transactional application backed by a Cloud SQL for PostgreSQL instance in the us-central1 region. A compliance review requires that the database withstand a full regional outage with a recovery point objective (RPO) close to zero and a recovery time objective (RTO) of minutes, not hours. Automated backups are already enabled but stored only in the primary region. Which approach best meets these disaster recovery requirements?

Accepted Answer

Configure a cross-region read replica in a second region and promote it to a standalone primary during a regional outage

Answer

Increase the automated backup frequency and retention, and restore from backup into a new instance in another region if the primary region fails

Answer

Enable a high-availability (regional) configuration so the standby is provisioned in a different zone within us-central1

Answer

Export the database to Cloud Storage in a dual-region bucket on a nightly schedule and import it into a new instance when needed

Question 61

A financial services company must retain 7 years of transaction audit logs to satisfy regulatory requirements. The logs are queried frequently during the first 30 days for operational troubleshooting, occasionally between 30 and 90 days for reconciliation, and rarely afterward but must remain immutable and undeletable for the full retention period. The team wants to minimize storage cost while guaranteeing that no user, including project administrators, can delete or overwrite the logs before 7 years elapse. What should you implement?

Accepted Answer

Store logs in a Cloud Storage bucket with a bucket retention policy locked to 7 years, and configure lifecycle rules to transition objects from Standard to Nearline at 30 days and to Coldline at 90 days.

Answer

Store logs in a single Standard-class bucket, apply IAM conditions denying delete permissions to all users, and rely on Cloud Audit Logs to detect any deletion attempts.

Answer

Store logs in a bucket with Object Versioning enabled and a lifecycle rule that deletes noncurrent versions after 7 years, transitioning current objects to Archive storage at 30 days.

Answer

Store logs in BigQuery with a table expiration set to 7 years and partition expiration disabled, granting only read access to analysts.

Question 62

A data engineering team stores critical curated datasets in a Cloud Storage bucket that serves as the source of truth for downstream BigQuery loads. Recently, an automated cleanup job misconfigured a prefix and permanently deleted several current object versions, causing a multi-hour outage while data was rebuilt from raw sources. Leadership wants a solution that allows quick recovery of individual overwritten or deleted objects without maintaining separate backup copies or scripts. What should the team implement?

Accepted Answer

Enable Object Versioning on the bucket and configure lifecycle rules to expire noncurrent versions after a defined retention period.

Answer

Turn on a bucket retention policy that locks all objects for a fixed period so they cannot be deleted or overwritten.

Answer

Enable Turbo Replication to a second-region bucket so deleted objects are automatically restored from the replica.

Answer

Schedule a daily Storage Transfer Service job that copies the bucket contents to another bucket for backup.

Question 63

A financial services company uses Customer-Managed Encryption Keys (CMEK) via Cloud KMS to encrypt data in BigQuery and Cloud Storage. A new compliance policy requires that encryption keys be rotated automatically every 90 days, and that the security team retains sole authority to disable or destroy keys while data engineers continue to run pipelines without interruption. What is the most appropriate way to meet these requirements?

Accepted Answer

Configure automatic rotation on the KMS key with a 90-day rotation period, grant the data engineering service accounts the roles/cloudkms.cryptoKeyEncrypterDecrypter role, and reserve roles/cloudkms.admin for the security team.

Answer

Disable automatic rotation and have data engineers manually create a new key version every 90 days, granting them roles/cloudkms.admin so they can manage the key lifecycle themselves.

Answer

Switch from CMEK to Google-managed encryption keys, since Google automatically rotates keys and no manual key administration is required.

Answer

Grant the data engineering service accounts roles/owner on the KMS project so pipelines never fail, and ask the security team to audit key usage after the fact.

Question 64

A retail company stores a BigQuery table containing customer purchase records. The table includes columns such as customer_id, purchase_amount, and a sensitive credit_card_hash column. A team of financial analysts needs to run aggregate queries on purchase_amount but must never be able to read the credit_card_hash column. Compliance requires that access controls be enforced at query time without duplicating or restructuring the table into separate tables. Which approach best meets these requirements?

Accepted Answer

Apply a policy tag from a Data Catalog taxonomy to the credit_card_hash column and use column-level access control to deny the analysts' group the Fine-Grained Reader role on that tag.

Answer

Grant the analysts the BigQuery Data Viewer role on the dataset and instruct them not to select the credit_card_hash column in their queries.

Answer

Create an authorized view that excludes credit_card_hash and copy the base table's data into a new masked table each night for the analysts.

Answer

Enable customer-managed encryption keys (CMEK) on the table and withhold the KMS decrypt permission from the analysts for the credit_card_hash column.

Exam domain	Exam weight	Practice
Designing data processing systems	22%	Practice this topic
Ingesting and processing the data	25%	Practice this topic
Storing the data	20%	Practice this topic
Preparing and using data for analysis	15%	Practice this topic
Maintaining and automating data workloads	18%	Practice this topic

Professional Data Engineer (PDE) practice exam & study guide

Start studying PDE

All PDE study resources

PDE exam domains

Sample PDE questions

Key PDE terms

PDE frequently asked questions

Designing data processing systems (22%)

Ingesting and processing the data (25%)

Storing the data (20%)

Preparing and using data for analysis (15%)

Maintaining and automating data workloads (18%)