Professional Data Engineer Free Practice Questions — Page 8
Question 53
You need to create a SQL pipeline. The pipeline runs an aggregate SQL transformation on a BigQuery table every two hours and appends the result to another existing BigQuery table. You need to configure the pipeline to retry if errors occur. You want the pipeline to send an email notification after three consecutive failures. What should you do?
A. Use the BigQueryUpsertTableOperator in Cloud Composer, set the retry parameter to three, and set the email_on_failure parameter to true.
B. Use the BigQueryInsertJobOperator in Cloud Composer, set the retry parameter to three, and set the email_on_failure parameter to true.
C. Create a BigQuery scheduled query to run the SQL transformation with schedule options that repeats every two hours, and enable email notifications.
D. Create a BigQuery scheduled query to run the SQL transformation with schedule options that repeats every two hours, and enable notification to Pub/Sub topic. Use Pub/Sub and Cloud Functions to send an email after three failed executions.
Show Answer
Correct Answer: D
Explanation: The requirement is to run every two hours, append results, retry on errors, and send an email only after three consecutive failed executions. Cloud Composer retries (options A and B) apply within a single task instance and email_on_failure triggers after that task’s final retry, not after three consecutive scheduled runs. BigQuery scheduled queries handle the two-hour schedule and append naturally, and using Pub/Sub notifications with Cloud Functions allows tracking consecutive failures across runs and sending an email only after the third failure. Therefore, D best satisfies all requirements.
Question 54
You created a new version of a Dataflow streaming data ingestion pipeline that reads from Pub/Sub and writes to BigQuery. The previous version of the pipeline that runs in production uses a 5-minute window for processing. You need to deploy the new version of the pipeline without losing any data, creating inconsistencies, or increasing the processing latency by more than 10 minutes. What should you do?
A. Update the old pipeline with the new pipeline code.
B. Snapshot the old pipeline, stop the old pipeline, and then start the new pipeline from the snapshot.
C. Drain the old pipeline, then start the new pipeline.
D. Cancel the old pipeline, then start the new pipeline.
Show Answer
Correct Answer: B
Explanation: To deploy a new version of a streaming Dataflow pipeline without data loss, inconsistencies, or excessive latency, you should use a snapshot. Creating a snapshot captures the full state of the running pipeline, including Pub/Sub offsets, in-flight elements, and windowing state. You can then stop the old job and start the new version from the snapshot, allowing processing to continue seamlessly. Draining can prematurely close windows and may lead to duplicate or inconsistent results, while canceling causes data loss and updating in place is not supported for code changes. Using a snapshot is the recommended stop-and-replace approach for safe streaming pipeline upgrades.
Question 55
You are creating a data model in BigQuery that will hold retail transaction data. Your two largest tables, sales_transaction_header and sales_transaction_line, have a tightly coupled immutable relationship. These tables are rarely modified after load and are frequently joined when queried. You need to model the sales_transaction_header and sales_transaction_line tables to improve the performance of data analytics queries. What should you do?
A. Create a sales_transaction table that holds the sales_transaction_header information as rows and the sales_transaction_line rows as nested and repeated fields.
B. Create a sales_transaction table that holds the sales_transaction_header and sales_transaction_line information as rows, duplicating the sales_transaction_header data for each line.
C. Create a sales_transaction table that stores the sales_transaction_header and sales_transaction_line data as a JSON data type.
D. Create separate sales_transaction_header and sales_transaction_line tables and, when querying, specify the sales_transaction_line first in the WHERE clause.
Show Answer
Correct Answer: A
Explanation: BigQuery performs best when tightly coupled, immutable one-to-many relationships are modeled using nested and repeated fields. Nesting sales_transaction_line records inside each sales_transaction_header keeps related data co-located, eliminates the need for expensive joins, and reduces data scanned, which improves query performance. This is a recommended BigQuery best practice for analytics workloads with infrequently updated data.
Question 56
You are building a streaming Dataflow pipeline that ingests noise level data from hundreds of sensors placed near construction sites across a city. The sensors measure noise level every ten seconds, and send that data to the pipeline when levels reach above 70 dBA. You need to detect the average noise level from a sensor when data is received for a duration of more than 30 minutes, but the window ends when no data has been received for 15 minutes. What should you do?
A. Use session windows with a 15-minute gap duration.
B. Use session windows with a 30-minute gap duration.
C. Use hopping windows with a 15-minute window, and a thirty-minute period.
D. Use tumbling windows with a 15-minute window and a fifteen-minute .withAllowedLateness operator.
Show Answer
Correct Answer: A
Explanation: The requirement is to aggregate data over a period of continuous activity from each sensor, where the window should close when no data has arrived for 15 minutes. Session windows are specifically designed for this pattern: they grow as long as events keep arriving and close after a defined inactivity gap. Setting a 15-minute gap duration ensures the window ends after 15 minutes of silence. The condition of "more than 30 minutes" is handled by applying a filter or trigger on the session length or accumulated duration, not by the window type itself. Hopping and tumbling windows are fixed-size and do not naturally model inactivity-based window closure.
Question 57
You maintain ETL pipelines. You notice that a streaming pipeline running on Dataflow is taking a long time to process incoming data, which causes output delays. You also noticed that the pipeline graph was automatically optimized by Dataflow and merged into one step. You want to identify where the potential bottleneck is occurring. What should you do?
A. Insert a Reshuffle operation after each processing step, and monitor the execution details in the Dataflow console.
B. Insert output sinks after each key processing step, and observe the writing throughput of each block.
C. Log debug information in each ParDo function, and analyze the logs at execution time.
D. Verify that the Dataflow service accounts have appropriate permissions to write the processed data to the output sinks.
Show Answer
Correct Answer: A
Explanation: Dataflow automatically fuses pipeline stages, which can hide where time is being spent. Inserting Reshuffle breaks fusion and forces materialization and redistribution between steps. This makes each stage visible as a separate step in the Dataflow UI, allowing you to inspect per-step throughput, latency, and backlog to identify the bottleneck. Logging or extra sinks add overhead and noise, and permissions issues do not explain slow processing.
Question 58
You are on the data governance team and are implementing security requirements. You need to encrypt all your data in BigQuery by using an encryption key managed by your team. You must implement a mechanism to generate and store encryption material only on your on-premises hardware security module (HSM). You want to rely on Google managed solutions. What should you do?
A. Create the encryption key in the on-premises HSM, and import it into a Cloud Key Management Service (Cloud KMS) key. Associate the created Cloud KMS key while creating the BigQuery resources.
B. Create the encryption key in the on-premises HSM and link it to a Cloud External Key Manager (Cloud EKM) key. Associate the created Cloud KMS key while creating the BigQuery resources.
C. Create the encryption key in the on-premises HSM, and import it into Cloud Key Management Service (Cloud HSM) key. Associate the created Cloud HSM key while creating the BigQuery resources.
D. Create the encryption key in the on-premises HSM. Create BigQuery resources and encrypt data while ingesting them into BigQuery.
Show Answer
Correct Answer: B
Explanation: The requirement is that encryption key material must be generated and stored only on an on‑premises HSM while using Google‑managed services for BigQuery encryption. Cloud External Key Manager (Cloud EKM) is designed exactly for this use case: it allows Google Cloud services, including BigQuery, to use customer‑managed keys that reside in external systems such as on‑premises HSMs. The key material never leaves the on‑premises HSM. Cloud KMS import (A) and Cloud HSM (C) both result in key material being managed within Google Cloud, which violates the requirement. Option D does not provide CMEK integration or centralized key management. Therefore, option B is correct.
Question 59
You have two projects where you run BigQuery jobs:
• One project runs production jobs that have strict completion time SLAs. These are high priority jobs that must have the required compute resources available when needed. These jobs generally never go below a 300 slot utilization, but occasionally spike up an additional 500 slots.
• The other project is for users to run ad-hoc analytical queries. This project generally never uses more than 200 slots at a time. You want these ad-hoc queries to be billed based on how much data users scan rather than by slot capacity.
You need to ensure that both projects have the appropriate compute resources available. What should you do?
A. Create a single Enterprise Edition reservation for both projects. Set a baseline of 300 slots. Enable autoscaling up to 700 slots.
B. Create two reservations, one for each of the projects. For the SLA project, use an Enterprise Edition with a baseline of 300 slots and enable autoscaling up to 500 slots. For the ad-hoc project, configure on-demand billing.
C. Create two Enterprise Edition reservations, one for each of the projects. For the SLA project, set a baseline of 300 slots and enable autoscaling up to 500 slots. For the ad-hoc project, set a reservation baseline of 0 slots and set the ignore idle slots flag to False.
D. Create two Enterprise Edition reservations, one for each of the projects. For the SLA project, set a baseline of 800 slots. For the ad-hoc project, enable autoscaling up to 200 slots.
Show Answer
Correct Answer: B
Explanation: The production project has strict SLAs and a steady baseline need of ~300 slots with occasional spikes of an additional ~500 slots. An Enterprise Edition reservation with a 300-slot baseline and autoscaling up to 500 slots ensures guaranteed capacity for steady load and elastic capacity for spikes (autoscaling slots are added on top of the baseline). The ad-hoc project explicitly needs to be billed by data scanned rather than slot capacity, which requires on-demand billing rather than a reservation. Other options either force slot-based billing for ad-hoc queries or misallocate capacity.
Question 60
Your business users need a way to clean and prepare data before using the data for analysis. Your business users are less technically savvy and prefer to work with graphical user interfaces to define their transformations. After the data has been transformed, the business users want to perform their analysis directly in a spreadsheet. You need to recommend a solution that they can use. What should you do?
A. Use Dataprep to clean the data, and write the results to BigQuery. Analyze the data by using Connected Sheets.
B. Use Dataprep to clean the data, and write the results to BigQuery. Analyze the data by using Looker Studio.
C. Use Dataflow to clean the data, and write the results to BigQuery. Analyze the data by using Connected Sheets.
D. Use Dataflow to clean the data, and write the results to BigQuery. Analyze the data by using Looker Studio.
Show Answer
Correct Answer: A
Explanation: Business users want a graphical, low-code interface for data preparation and then analysis directly in a spreadsheet. Dataprep provides a GUI-based data cleaning and transformation experience suitable for non-technical users, BigQuery stores the transformed data, and Connected Sheets allows users to analyze BigQuery data directly within Google Sheets. Dataflow is code-centric, and Looker Studio is a BI dashboard tool rather than a spreadsheet.
Question 61
You have several different file type data sources, such as Apache Parquet and CSV. You want to store the data in Cloud Storage. You need to set up an object sink for your data that allows you to use your own encryption keys. You want to use a GUI-based solution. What should you do?
A. Use Storage Transfer Service to move files into Cloud Storage.
B. Use Cloud Data Fusion to move files into Cloud Storage.
C. Use Dataflow to move files into Cloud Storage.
D. Use BigQuery Data Transfer Service to move files into BigQuery.
Show Answer
Correct Answer: B
Explanation: The requirements are: a GUI-based solution, an object sink into Cloud Storage, support for multiple file formats (CSV, Parquet), and the ability to use customer-managed encryption keys (CMEK). Cloud Data Fusion is a fully managed, no-code/GUI data integration service that uses the concept of sources and sinks, supports these file formats, writes directly to Cloud Storage, and explicitly supports CMEK for its instances and pipelines. Storage Transfer Service is GUI-based but does not support CMEK for encrypting data at rest, Dataflow is code-based, and BigQuery Data Transfer Service targets BigQuery rather than Cloud Storage.
Question 62
You are planning to use Cloud Storage as part of your data lake solution. The Cloud Storage bucket will contain objects ingested from external systems. Each object will be ingested once, and the access patterns of individual objects will be random. You want to minimize the cost of storing and retrieving these objects. You want to ensure that any cost optimization efforts are transparent to the users and applications. What should you do?
A. Create a Cloud Storage bucket with Autoclass enabled.
B. Create a Cloud Storage bucket with an Object Lifecycle Management policy to transition objects from Standard to Coldline storage class if an object age reaches 30 days.
C. Create a Cloud Storage bucket with an Object Lifecycle Management policy to transition objects from Standard to Coldline storage class if an object is not live.
D. Create two Cloud Storage buckets. Use the Standard storage class for the first bucket, and use the Coldline storage class for the second bucket. Migrate objects from the first bucket to the second bucket after 30 days.
Show Answer
Correct Answer: A
Explanation: Autoclass automatically analyzes object access patterns and transparently moves objects among Standard, Nearline, Coldline, and Archive to minimize storage and access costs without requiring lifecycle rules or application changes. This fits random access, one-time ingest, and the requirement for user-transparent optimization.
$19
Get all 328 questions with detailed answers and explanations
Instant download HTML + PDF delivered the moment payment clears.
Secure Stripe checkout we never see or store your card details.
7-day refund if files are defective see our refund policy.