Professional Data Engineer Free Practice Questions — Page 5
Question 40
You are monitoring your organization’s data lake hosted on BigQuery. The ingestion pipelines read data from Pub/Sub and write the data into tables on BigQuery. After a new version of the ingestion pipelines is deployed, the daily stored data increased by 50%. The volumes of data in Pub/Sub remained the same and only some tables had their daily partition data size doubled. You need to investigate and fix the cause of the data increase. What should you do?
A. 1. Check for duplicate rows in the BigQuery tables that have the daily partition data size doubled. 2. Schedule daily SQL jobs to deduplicate the affected tables. 3. Share the deduplication script with the other operational teams to reuse if this occurs to other tables.
B. 1. Check for code errors in the deployed pipelines. 2. Check for multiple writing to pipeline BigQuery sink. 3. Check for errors in Cloud Logging during the day of the release of the new pipelines. 4. If no errors, restore the BigQuery tables to their content before the last release by using time travel.
C. 1. Check for duplicate rows in the BigQuery tables that have the daily partition data size doubled. 2. Check the BigQuery Audit logs to find job IDs. 3. Use Cloud Monitoring to determine when the identified Dataflow jobs started and the pipeline code version. 4. When more than one pipeline ingests data into a table, stop all versions except the latest one.
D. 1. Roll back the last deployment. 2. Restore the BigQuery tables to their content before the last release by using time travel. 3. Restart the Dataflow jobs and replay the messages by seeking the subscription to the timestamp of the release.
Show Answer
Correct Answer: C
Explanation: The symptoms indicate duplicate ingestion after a deployment: Pub/Sub volume is unchanged, while only some BigQuery partitions doubled. The most likely cause is multiple pipeline versions writing to the same tables or duplicate writes. The appropriate investigation is to verify duplicate rows, inspect BigQuery Audit Logs for the jobs that wrote the data, correlate Dataflow job start times and pipeline versions with Cloud Monitoring, and stop any duplicate/older pipeline versions so only the intended pipeline writes. The other options either focus on cleanup rather than root cause (A), include unnecessary restoration without identifying the actual cause (B), or roll back/replay without investigating the issue (D).
Question 41
Your organization's data assets are stored in BigQuery, Pub/Sub, and a PostgreSQL instance running on Compute Engine. Because there are multiple domains and diverse teams using the data, teams in your organization are unable to discover existing data assets. You need to design a solution to improve data discoverability while keeping development and configuration efforts to a minimum. What should you do?
A. Use Data Catalog to automatically catalog BigQuery datasets. Use Data Catalog APIs to manually catalog Pub/Sub topics and PostgreSQL tables.
B. Use Data Catalog to automatically catalog BigQuery datasets and Pub/Sub topics. Use Data Catalog APIs to manually catalog PostgreSQL tables.
C. Use Data Catalog to automatically catalog BigQuery datasets and Pub/Sub topics. Use custom connectors to manually catalog PostgreSQL tables.
D. Use customer connectors to manually catalog BigQuery datasets, Pub/Sub topics, and PostgreSQL tables.
Show Answer
Correct Answer: B
Explanation: Data Catalog automatically catalogs BigQuery and Pub/Sub resources. For a PostgreSQL database running on Compute Engine, automatic cataloging is not available; using Data Catalog APIs to create custom entries is the standard approach in exam context. Although community connectors exist for some sources, they are optional integrations and the exam's expected answer is the API-based manual cataloging.
Question 42
You are designing a fault-tolerant architecture to store data in a regional BigQuery dataset. You need to ensure that your application is able to recover from a corruption event in your tables that occurred within the past seven days. You want to adopt managed services with the lowest RPO and most cost-effective solution. What should you do?
A. Access historical data by using time travel in BigQuery.
B. Export the data from BigQuery into a new table that excludes the corrupted data
C. Create a BigQuery table snapshot on a daily basis.
D. Migrate your data to multi-region BigQuery buckets.
Show Answer
Correct Answer: A
Explanation: BigQuery time travel is a built-in managed feature that allows point-in-time recovery of table data for the retention window (commonly 7 days by default). It provides the lowest RPO within that window because you can restore data to a point just before the corruption, and it is more cost-effective than maintaining separate daily snapshots. Exporting data does not provide recovery, and multi-region datasets improve availability rather than protection from logical corruption.
Question 43
You are running your BigQuery project in the on-demand billing model and are executing a change data capture (CDC) process that ingests data. The CDC process loads 1 GB of data every 10 minutes into a temporary table, and then performs a merge into a 10 TB target table. This process is very scan intensive and you want to explore options to enable a predictable cost model. You need to create a BigQuery reservation based on utilization information gathered from BigQuery Monitoring and apply the reservation to the CDC process. What should you do?
A. Create a BigQuery reservation for the dataset.
B. Create a BigQuery reservation for the job.
C. Create a BigQuery reservation for the service account running the job.
D. Create a BigQuery reservation for the project.
Show Answer
Correct Answer: D
Explanation: BigQuery Reservations (slot capacity) are assigned through reservation assignments at the organization, folder, or project level—not to datasets or individual jobs. To move this CDC workload from on-demand to capacity-based pricing, create a reservation and assign the project running the CDC process to it. Service accounts are not the standard assignment target in this exam context, and jobs cannot be assigned directly.
Question 44
Your team is building a data lake platform on Google Cloud. As a part of the data foundation design, you are planning to store all the raw data in Cloud Storage. You are expecting to ingest approximately 25 GB of data a day and your billing department is worried about the increasing cost of storing old data. The current business requirements are:
• The old data can be deleted anytime.
• There is no predefined access pattern of the old data.
• The old data should be available instantly when accessed.
• There should not be any charges for data retrieval.
What should you do to optimize for cost?
A. Create the bucket with the Autoclass storage class feature.
B. Create an Object Lifecycle Management policy to modify the storage class for data older than 30 days to nearline, 90 days to coldline, and 365 days to archive storage class. Delete old data as needed.
C. Create an Object Lifecycle Management policy to modify the storage class for data older than 30 days to coldline, 90 days to nearline, and 365 days to archive storage class. Delete old data as needed.
D. Create an Object Lifecycle Management policy to modify the storage class for data older than 30 days to nearline, 45 days to coldline, and 60 days to archive storage class. Delete old data as needed.
Show Answer
Correct Answer: A
Explanation: Autoclass is designed to optimize storage costs automatically based on object access patterns while preserving immediate availability. Unlike manually transitioning objects to Nearline, Coldline, or Archive via lifecycle rules, Autoclass avoids data retrieval charges and class transition fees, satisfying the requirement that old data be instantly available with no retrieval charges. The lifecycle options using Coldline or Archive would incur retrieval costs, violating the stated requirements.
Question 45
You are preparing data that your machine learning team will use to train a model using BigQueryML. They want to predict the price per square foot of real estate. The training data has a column for the price and a column for the number of square feet. Another feature column called ‘feature1’ contains null values due to missing data. You want to replace the nulls with zeros to keep more data points. Which query should you use?
A.
B.
C.
D.
Show Answer
Correct Answer: C
Explanation: To predict price per square foot, the training label should be derived as price/square_feet, while replacing NULL values in feature1 with 0 using IFNULL or COALESCE. The query that creates the price_per_square_foot target and imputes feature1 is the best fit. Excluding the original price and square_feet columns avoids leaking the components of the target into the features.
Sources:
https://cloud.google.com/blog/products/data-analytics/how-to-apply-machine-learning-to-unstructured-data-using-bigqueryml
Question 46
You recently deployed several data processing jobs into your Cloud Composer 2 environment. You notice that some tasks are failing in Apache Airflow. On the monitoring dashboard, you see an increase in the total workers memory usage, and there were worker pod evictions. You need to resolve these errors. What should you do? (Choose two.)
A. Increase the directed acyclic graph (DAG) file parsing interval.
B. Increase the Cloud Composer 2 environment size from medium to large.
C. Increase the maximum number of workers and reduce worker concurrency.
D. Increase the memory available to the Airflow workers.
E. Increase the memory available to the Airflow triggerer.
Show Answer
Correct Answer: C, D
Explanation: Worker pod evictions accompanied by increased worker memory usage indicate memory pressure on Airflow workers. Increasing worker memory directly addresses out-of-memory evictions. Reducing worker concurrency lowers the number of tasks running simultaneously on each worker, reducing per-worker memory pressure; increasing the maximum number of workers compensates for reduced concurrency to maintain throughput.
Sources:
https://cloud.google.com/composer/docs/composer-2/troubleshooting-dags
Question 47
You have an upstream process that writes data to Cloud Storage. This data is then read by an Apache Spark job that runs on Dataproc. These jobs are run in the us-central1 region, but the data could be stored anywhere in the United States. You need to have a recovery process in place in case of a catastrophic single region failure. You need an approach with a maximum of 15 minutes of data loss (RPO=15 mins). You want to ensure that there is minimal latency when reading the data. What should you do?
A. 1. Create two regional Cloud Storage buckets, one in the us-central1 region and one in the us-south1 region. 2. Have the upstream process write data to the us-central1 bucket. Use the Storage Transfer Service to copy data hourly from the us-central1 bucket to the us-south1 bucket. 3. Run the Dataproc cluster in a zone in the us-central1 region, reading from the bucket in that region. 4. In case of regional failure, redeploy your Dataproc clusters to the us-south1 region and read from the bucket in that region instead.
B. 1. Create a Cloud Storage bucket in the US multi-region. 2. Run the Dataproc cluster in a zone in the us-central1 region, reading data from the US multi-region bucket. 3. In case of a regional failure, redeploy the Dataproc cluster to the us-central2 region and continue reading from the same bucket.
C. 1. Create a dual-region Cloud Storage bucket in the us-central1 and us-south1 regions. 2. Enable turbo replication. 3. Run the Dataproc cluster in a zone in the us-central1 region, reading from the bucket in the us-south1 region. 4. In case of a regional failure, redeploy your Dataproc cluster to the us-south1 region and continue reading from the same bucket.
D. 1. Create a dual-region Cloud Storage bucket in the us-central1 and us-south1 regions. 2. Enable turbo replication. 3. Run the Dataproc cluster in a zone in the us-central1 region, reading from the bucket in the same region. 4. In case of a regional failure, redeploy the Dataproc clusters to the us-south1 region and read from the same bucket.
Show Answer
Correct Answer: D
Explanation: A dual-region Cloud Storage bucket with turbo replication provides an RPO of 15 minutes or less across the paired regions. Reading from the local replica in the same region as the Dataproc cluster minimizes latency. If us-central1 fails, redeploy Dataproc in us-south1 and continue using the same dual-region bucket, which already contains the replicated data.
Question 48
You work for a farming company. You have one BigQuery table named sensors, which is about 500 MB and contains the list of your 5000 sensors, with columns for id, name, and location. This table is updated every hour. Each sensor generates one metric every 30 seconds along with a timestamp, which you want to store in BigQuery. You want to run an analytical query on the data once a week for monitoring purposes. You also want to minimize costs. What data model should you use?
A. 1. Create a metrics column in the sensors table. 2. Set RECORD type and REPEATED mode for the metrics column. 3. Use an UPDATE statement every 30 seconds to add new metrics.
B. 1. Create a metrics column in the sensors table. 2. Set RECORD type and REPEATED mode for the metrics column. 3. Use an INSERT statement every 30 seconds to add new metrics.
C. 1. Create a metrics table partitioned by timestamp. 2. Create a sensorId column in the metrics table, that points to the id column in the sensors table. 3. Use an INSERT statement every 30 seconds to append new metrics to the metrics table. 4. Join the two tables, if needed, when running the analytical query.
D. 1. Create a metrics table partitioned by timestamp. 2. Create a sensorId column in the metrics table, which points to the id column in the sensors table. 3. Use an UPDATE statement every 30 seconds to append new metrics to the metrics table. 4. Join the two tables, if needed, when running the analytical query.
Show Answer
Correct Answer: C
Explanation: Use a separate time-series metrics table partitioned by timestamp and append new readings with INSERTs. BigQuery is optimized for append-heavy analytical workloads, while frequent UPDATEs are inefficient. Keeping sensor metadata in a separate dimension table avoids repeatedly rewriting large nested records. Partitioning the metrics table enables efficient weekly queries by scanning only relevant partitions, and joining to the relatively small sensors table when needed is appropriate.
Question 49
You are managing a Dataplex environment with raw and curated zones. A data engineering team is uploading JSON and CSV files to a bucket asset in the curated zone but the files are not being automatically discovered by Dataplex. What should you do to ensure that the files are discovered by Dataplex?
A. Move the JSON and CSV files to the raw zone.
B. Enable auto-discovery of files for the curated zone.
C. Use the bg command-line tool to load the JSON and CSV files into BigQuery tables.
D. Grant object level access to the CSV and JSON files in Cloud Storage.
Show Answer
Correct Answer: A
Explanation: Dataplex Discovery treats JSON and CSV as valid formats for raw zones, but curated zones are intended for curated columnar formats such as Parquet, Avro, and ORC. Files in unsupported formats in a curated zone are considered invalid for discovery. Moving the JSON and CSV files to a raw zone allows Dataplex to automatically discover them. Enabling discovery alone does not overcome the curated-zone format restriction.
$19
Get all 332 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.