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

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 25

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 26

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 27

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 28

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 29

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 30

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 31

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 32

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 33

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 34

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 35

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.

Question 36

Your team runs a daily Cloud Composer (Airflow) DAG that ingests partner sales files, transforms them in Dataproc Serverless, and loads results into BigQuery. Downstream analysts must have refreshed data by 08:00 each morning. Occasionally a partner uploads files late, causing the pipeline to finish after the deadline without anyone noticing until analysts complain. You want Airflow itself to automatically detect when a task takes longer than expected and notify the on-call engineer, without failing the DAG run. What is the most appropriate approach?

Accepted Answer

Define an SLA on the relevant tasks and configure an sla_miss_callback that sends an alert to the on-call channel when the SLA is exceeded.

Answer

Set retries and retry_delay on each task so that late-completing tasks are retried until they finish before 08:00.

Answer

Set an execution_timeout on each task so Airflow marks the task failed and triggers on_failure_callback when it runs long.

Answer

Add a downstream BashOperator that queries the current time and raises an error if it is past 08:00, failing the DAG.

Question 37

Your team runs about 300 DAGs in a Cloud Composer 2 environment. Recently, the Airflow scheduler has become sluggish: tasks are queued but slow to start, and the DAGs list in the UI takes a long time to refresh. On investigation, you find that several DAG files execute heavy top-level code — including API calls to fetch configuration and expensive pandas transformations — every time the file is parsed. What is the most effective change to improve scheduler performance?

Accepted Answer

Move the heavy top-level code into task callables (inside operators/PythonOperator functions) so it runs only at task execution time, not on every DAG parse.

Answer

Increase the Composer environment's worker node count and worker concurrency so more tasks can run in parallel.

Answer

Set a very short scheduler `dag_dir_list_interval` so the scheduler re-parses DAG files more frequently and stays up to date.

Answer

Combine all 300 DAGs into a single large DAG file to reduce the number of files the scheduler must scan.

Question 38

A retail analytics team uses Cloud Composer (Apache Airflow) to orchestrate a daily batch pipeline that reads the previous day's sales files from Cloud Storage, transforms them in Dataflow, and appends results into a BigQuery table partitioned by date. Occasionally a task fails midway and Airflow automatically retries it. The team also frequently runs manual backfills for past dates. They notice that retries and backfills sometimes produce duplicate rows in BigQuery. What is the best way to redesign the pipeline so that retries and backfills remain safe?

Accepted Answer

Make each run idempotent by having the load step overwrite (WRITE_TRUNCATE) the specific date partition it processes, using the Airflow execution date to target the partition.

Answer

Set the Airflow task retries to 0 so that failed tasks never re-run and cannot create duplicates.

Answer

Switch the pipeline to streaming ingestion with Pub/Sub exactly-once delivery to eliminate duplicates.

Answer

Add a downstream scheduled query that periodically deduplicates the table using ROW_NUMBER() to remove duplicate rows.

Question 39

Your team uses Cloud Composer to orchestrate ingestion DAGs. The same DAG code must run in dev, staging, and prod environments, each pointing to different BigQuery datasets, Pub/Sub topics, and service accounts. Developers currently hardcode these values, causing errors when DAGs are promoted between environments. You want a maintainable approach that keeps a single DAG codebase while allowing per-environment configuration without editing the DAG files. What should you do?

Accepted Answer

Store environment-specific values in Airflow Variables and Connections that are set per Composer environment, and reference them in the DAG code.

Answer

Maintain three separate copies of each DAG, one per environment, and deploy the matching copy to each Composer environment.

Answer

Hardcode the production values in the DAG and use Airflow's built-in test mode to override them automatically in dev and staging.

Answer

Pass all environment values as XComs pushed from an upstream task at DAG runtime.

Question 40

A media analytics company runs nightly Spark batch jobs on a self-managed Hadoop cluster on-premises. Leadership wants a cloud design that avoids vendor lock-in, so future workloads could potentially run on another cloud provider with minimal rewrites. The engineering team plans to use Dataproc but is concerned that tightly coupling their data and job logic to proprietary services will make a future move costly. Which design approach best preserves portability while running on Google Cloud?

Accepted Answer

Store data in Cloud Storage using open formats such as Parquet or ORC, keep Spark job logic in standard open-source APIs, and run on ephemeral Dataproc clusters.

Answer

Load all data into BigQuery and rewrite the Spark jobs as BigQuery SQL stored procedures to take advantage of the managed engine.

Answer

Persist data on HDFS attached to a long-running Dataproc cluster and use Dataproc-specific autoscaling policies for all processing.

Answer

Store data in Bigtable and refactor jobs to use the Bigtable client libraries for high-throughput reads and writes.

Question 41

A European financial services company must store and process customer transaction data in BigQuery. Regulatory requirements mandate that both the data and the encryption keys used to protect it remain physically within the EU, and the company must be able to permanently revoke access to the data across all copies at any time. Which approach best satisfies these requirements?

Accepted Answer

Create datasets in a single EU region and use Cloud KMS customer-managed encryption keys (CMEK) created in the same EU region, then disable or destroy the key version to revoke access.

Answer

Store the BigQuery datasets in a multi-region 'EU' location and rely on Google's default encryption with Google-managed keys.

Answer

Store datasets in a US region for better performance and encrypt them with CMEK keys created in an EU Cloud KMS location.

Answer

Use customer-supplied encryption keys (CSEK) uploaded to a US-based key ring and store the datasets in an EU region.

Question 42

Your team uses Cloud Data Fusion to build a batch ETL pipeline that reads CSV files from Cloud Storage, applies several transformations, and writes to BigQuery. Before deploying the pipeline to production, business analysts report that certain output records have unexpected null values. You need to inspect how each transformation stage processes a small sample of the source data without incurring the cost of provisioning a full Dataproc cluster or writing to the production BigQuery table. What should you do?

Accepted Answer

Run the pipeline in Preview mode within the Data Fusion Studio to view intermediate stage outputs against sample data before deployment.

Answer

Deploy the pipeline and enable Dataproc job logging in Cloud Logging, then trace null values by reviewing the driver logs.

Answer

Add a Wrangler directive to filter out null records and redeploy the pipeline to production immediately.

Answer

Export the pipeline as a JSON configuration and manually simulate each transformation in a local Spark shell.

Question 43

A retail company runs an operational MySQL database on-premises and wants continuous, near-real-time replication of insert, update, and delete operations into BigQuery for analytics. The data engineering team prefers a fully managed, low-code solution that handles ongoing change data capture without writing custom Dataflow code, and that can automatically create and keep target BigQuery tables in sync with the source schema. Which approach best meets these requirements?

Accepted Answer

Use Cloud Data Fusion Replication to connect to MySQL binlog-based CDC and continuously stream changes into BigQuery.

Answer

Schedule a daily Dataflow batch job that reads full table snapshots from MySQL and overwrites the BigQuery tables.

Answer

Export nightly MySQL dumps to Cloud Storage and load them into BigQuery with the bq load command via a Cloud Composer DAG.

Answer

Configure a Pub/Sub topic and write custom triggers in MySQL to publish row changes for a Dataflow pipeline to consume.

Question 44

A retail analytics team includes several business analysts who understand data transformation logic but do not write code. They need to build repeatable ETL pipelines that ingest CSV files from Cloud Storage, join them with reference data, apply cleansing rules, and load the results into BigQuery. Leadership wants the analysts to be self-sufficient, wants the pipelines to be version-controlled, and prefers a managed service that provides a visual, drag-and-drop interface with prebuilt connectors and transformations. Which approach best meets these requirements?

Accepted Answer

Use Cloud Data Fusion to build the pipelines through its graphical interface, leveraging built-in connectors and Wrangler for transformations

Answer

Have the analysts write Apache Beam pipelines in Python and deploy them as Dataflow Flex Templates

Answer

Provision a Dataproc cluster and have the analysts submit PySpark jobs that perform the joins and cleansing

Answer

Load the raw CSVs into BigQuery and have the analysts write scheduled SQL scripts for all transformations

Question 45

A retail analytics team uses Cloud Data Fusion to build a batch pipeline that ingests daily CSV exports from partner SFTP servers into BigQuery. Occasionally a partner adds new columns or sends malformed rows, which currently causes the entire pipeline run to fail. The team wants the pipeline to continue processing valid records, route bad records to a separate location for later inspection, and avoid manual code changes for each new column. What is the most appropriate approach within Data Fusion?

Accepted Answer

Configure the pipeline's error handling to send failed records to an error collector plugin writing to a Cloud Storage error dataset, and enable a flexible schema in the Wrangler/parsing stage so unexpected columns do not abort the run

Answer

Set the pipeline to retry the entire run three times on failure, expecting the malformed rows to eventually succeed

Answer

Replace Data Fusion with a custom Dataflow pipeline because Data Fusion cannot handle schema drift or bad records

Answer

Enforce a strict fixed schema and fail the whole batch whenever any deviation occurs, then have an engineer manually edit the pipeline for every new column

Question 46

A financial services company is consolidating dozens of BigQuery datasets across multiple projects. Analysts complain they cannot easily discover which tables exist or determine which columns contain sensitive PII such as national IDs and account numbers. The compliance team requires that sensitive columns be automatically identified and tagged so that access policies can be applied consistently. Which approach best meets these data governance requirements with the least custom development?

Accepted Answer

Use Dataplex with Data Catalog to index the datasets, and run Cloud DLP inspection jobs to automatically detect and tag columns containing PII, then attach policy tags for column-level access control.

Answer

Export all table schemas to a spreadsheet, manually review each column for sensitive data, and document access rules in a shared wiki for analysts to reference.

Answer

Grant all analysts the BigQuery Data Viewer role at the organization level so they can browse every dataset and self-identify sensitive fields.

Answer

Write a custom Cloud Function that scans table names with regular expressions to guess which tables hold PII and emails a report to the compliance team weekly.

Question 47

A logistics company receives daily inventory files (about 5 GB each) dropped into a Cloud Storage bucket every night at 2 AM. The data engineering team must transform and load these files into BigQuery so that analysts have fresh data by 6 AM. There is no requirement for sub-hourly latency, and the files arrive as a predictable, bounded set each night. Which pipeline design best fits these requirements while minimizing operational cost?

Accepted Answer

Build a batch Dataflow pipeline triggered by Cloud Composer once the nightly files have landed, processing the bounded dataset and writing to BigQuery.

Answer

Build a streaming Dataflow pipeline reading from a Pub/Sub notification topic and keep it running 24/7 to process each file as it lands.

Answer

Configure a Pub/Sub-to-BigQuery direct subscription so the files are written to BigQuery automatically without transformation.

Answer

Use a streaming Dataflow pipeline with fixed 5-minute windows to continuously aggregate the files throughout the day.

Question 48

Your data engineering team maintains several Dataflow pipelines whose source code lives in a Git repository. Currently, engineers manually build templates and deploy them from their laptops, which has led to inconsistent deployments and undetected regressions reaching production. You are asked to implement a CI/CD process that automatically runs unit and integration tests on every merge request, and only promotes validated pipeline artifacts to the production project after approval. Which approach best meets these requirements?

Accepted Answer

Configure Cloud Build triggers on the repository to run the Beam test suite (DirectRunner for unit tests, TestDataflowRunner for integration tests), build a Dataflow Flex Template, store it in Artifact Registry/Cloud Storage, and promote it to the production project via a gated build step.

Answer

Schedule a Cloud Composer DAG that periodically pulls the latest code, runs the pipeline in production, and rolls back if the job fails.

Answer

Have each engineer run pytest locally before pushing, then use a shared service account so everyone deploys templates directly to the production project.

Answer

Enable Dataflow's built-in continuous deployment feature to auto-deploy the newest commit whenever the main branch changes, skipping separate test stages.

Question 49

A retail analytics team runs a streaming Dataflow pipeline that must join two Pub/Sub streams: an 'orders' stream and a 'payments' stream. An order and its matching payment share an order_id but can arrive up to 10 minutes apart and out of order. The team needs to correlate each order with its payment to compute settled revenue, emitting a result once both events for an order_id are seen (or marking the order unsettled if no payment arrives within the window). Which pipeline design best meets these requirements?

Accepted Answer

Key both streams by order_id, apply a matching event-time windowing strategy (e.g., a fixed or sliding window covering the 10-minute skew) with allowed lateness, and use CoGroupByKey to co-group orders and payments per key before emitting settled/unsettled results.

Answer

Apply an event-time session window with a 10-minute gap duration to each stream independently, then use a side input to look up payments while processing orders.

Answer

Write both streams directly to BigQuery via Pub/Sub subscriptions and perform the join with a scheduled SQL query that runs every 10 minutes.

Answer

Use a global window with a GroupByKey on order_id and no triggers so all matching records accumulate indefinitely before the join is emitted.

Question 50

A Dataflow streaming pipeline aggregates ad-impression counts per publisher using a GroupByKey followed by a Sum combiner. A handful of very large publishers generate the vast majority of events, and monitoring shows severe data skew: a few workers are overloaded processing these 'hot keys' while others sit idle, causing backlog growth. You must reduce processing latency without changing the final aggregation results. What is the most effective change?

Accepted Answer

Apply Combine.PerKey with the .withHotKeyFanout() setting so hot keys are partially combined across multiple intermediate workers before the final merge.

Answer

Replace the streaming pipeline with a batch pipeline that reprocesses the data hourly to avoid real-time skew.

Answer

Increase the maximum number of workers so more machines are available to pick up the hot-key work.

Answer

Switch from fixed windows to global windows so the combiner has more time to distribute the load evenly.

Professional Data Engineer (PDE) full-length practice exam

How this timed PDE mock exam works

What your PDE results show

How to get the most out of it

Ready for the full PDE mock exam?