Professional Data Engineer Free Practice Questions — Page 5
Question 23
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: The requirements state that old data has no predictable access pattern, must be instantly available, and must not incur retrieval charges. Nearline, Coldline, and Archive storage classes (options B, C, and D) all introduce retrieval costs and, for colder classes, usage constraints that violate the requirements. Autoclass automatically moves objects between Standard, Nearline, Coldline, and Archive based on actual access patterns while preserving instant access and without charging retrieval fees when data is accessed. It optimizes storage cost without requiring predefined lifecycle rules, which best matches all stated business requirements.
Question 24
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: The goal is to prepare training data for BigQuery ML to predict price per square foot. That requires (1) creating the target label as price / square_feet and (2) replacing NULLs in feature1 with 0 to preserve rows. Option C does both: it computes price_per_sqft as the label and uses IFNULL (or equivalent) to replace missing values in feature1, while excluding the original price and square_feet columns that would otherwise make the prediction trivial. The other options either only handle NULL replacement without creating the required target or keep columns that negate the need for an ML model.
Question 25
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 in Cloud Composer 2 are most commonly caused by out-of-memory conditions on Airflow workers. To resolve this, you should (1) reduce worker concurrency while increasing the maximum number of workers so that fewer tasks run per worker pod, lowering per-pod memory pressure, and (2) increase the memory available to Airflow workers to directly prevent OOM evictions. These actions align with Cloud Composer optimization guidance and directly address the observed high worker memory usage and evictions.
Question 26
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 near-real-time replication across two regions, meeting the 15-minute RPO requirement. Running Dataproc in the same region as one of the bucket’s regions minimizes read latency during normal operations. In a regional failure, Dataproc can be redeployed to the other region and continue reading from the same bucket without data loss beyond the RPO, ensuring seamless disaster recovery.
Question 27
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 metrics table partitioned by timestamp with a sensorId foreign key and append data via INSERTs. This matches BigQuery best practices for time-series data: append-only writes, efficient partition pruning for weekly analytics, and lower query costs. Updating or repeatedly modifying nested/repeated fields is inefficient and costly in BigQuery, while UPDATE-heavy designs are discouraged. Timestamp partitioning uses coarse granularity (e.g., daily/hourly), so partition limits are not an issue.
Question 28
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 curated zones only support discovery for columnar analytics formats such as Avro, Parquet, and ORC. JSON and CSV files in a curated zone are treated as invalid for discovery and therefore are not automatically discovered. To ensure discovery without changing formats, the files should be placed in a raw zone, where JSON and CSV are supported by Dataplex Discovery.
Question 29
You have a table that contains millions of rows of sales data, partitioned by date. Various applications and users query this data many times a minute. The query requires aggregating values by using AVG, MAX, and SUM, and does not require joining to other tables. The required aggregations are only computed over the past year of data, though you need to retain full historical data in the base tables. You want to ensure that the query results always include the latest data from the tables, while also reducing computation cost, maintenance overhead, and duration. What should you do?
A. Create a materialized view to aggregate the base table data. Include a filter clause to specify the last one year of partitions.
B. Create a materialized view to aggregate the base table data. Configure a partition expiration on the base table to retain only the last one year of partitions.
C. Create a view to aggregate the base table data. Include a filter clause to specify the last year of partitions.
D. Create a new table that aggregates the base table data. Include a filter clause to specify the last year of partitions. Set up a scheduled query to recreate the new table every hour.
Show Answer
Correct Answer: A
Explanation: A materialized view is designed for repeatedly executed aggregation queries and significantly reduces computation cost and query duration by precomputing AVG, MAX, and SUM. By filtering the materialized view to only the last year of partitions, you limit the amount of data that needs to be maintained and refreshed while still retaining full historical data in the base table. BigQuery materialized views automatically stay up to date and will read from base tables as needed to ensure fresh results, meeting the requirement to always include the latest data with minimal maintenance overhead.
Question 30
You are preparing an organization-wide dataset. You need to preprocess customer data stored in a restricted bucket in Cloud Storage. The data will be used to create consumer analyses. You need to comply with data privacy requirements.
What should you do?
A. Use Dataflow and the Cloud Data Loss Prevention API to mask sensitive data. Write the processed data in BigQuery.
B. Use customer-managed encryption keys (CMEK) to directly encrypt the data in Cloud Storage. Use federated queries from BigQuery. Share the encryption key by following the principle of least privilege.
C. Use the Cloud Data Loss Prevention API and Dataflow to detect and remove sensitive fields from the data in Cloud Storage. Write the filtered data in BigQuery.
D. Use Dataflow and Cloud KMS to encrypt sensitive fields and write the encrypted data in BigQuery. Share the encryption key by following the principle of least privilege.
Show Answer
Correct Answer: A
Explanation: You must preprocess sensitive customer data to meet privacy requirements while still enabling consumer analysis. Using Dataflow with the Cloud Data Loss Prevention (DLP) API allows you to systematically identify and mask sensitive fields (such as PII). Masking reduces exposure risk while preserving analytical usefulness (e.g., trends, counts, segmentation). Writing the sanitized output to BigQuery supports organization-wide analytics.
Option C removes sensitive fields entirely, which can degrade analytical value. Options B and D focus on encryption and key management, but encryption alone does not reduce data sensitivity for analysts and does not satisfy typical privacy requirements for shared analytical datasets. Therefore, A is the best choice.
Question 31
You need to connect multiple applications with dynamic public IP addresses to a Cloud SQL instance. You configured users with strong passwords and enforced the SSL connection to your Cloud SQL instance. You want to use Cloud SQL public IP and ensure that you have secured connections. What should you do?
A. Add CIDR 0.0.0.0/0 network to Authorized Network. Use Identity and Access Management (IAM) to add users.
B. Add all application networks to Authorized Network and regularly update them.
C. Leave the Authorized Network empty. Use Cloud SQL Auth proxy on all applications.
D. Add CIDR 0.0.0.0/0 network to Authorized Network. Use Cloud SQL Auth proxy on all applications.
Show Answer
Correct Answer: C
Explanation: When applications have dynamic public IPs, managing Authorized Networks is impractical. The Cloud SQL Auth Proxy (Cloud SQL connectors) connects to the Cloud SQL **public IP** but authenticates using IAM and establishes an encrypted tunnel, so no IP-based allowlisting is required. Leaving Authorized Networks empty blocks direct IP connections while still allowing secure access through the proxy, which is the recommended and most secure approach. Options using 0.0.0.0/0 weaken security, and manually updating networks is not feasible with dynamic IPs.
Question 32
You are migrating a large number of files from a public HTTPS endpoint to Cloud Storage. The files are protected from unauthorized access using signed URLs. You created a TSV file that contains the list of object URLs and started a transfer job by using Storage Transfer Service. You notice that the job has run for a long time and eventually failed. Checking the logs of the transfer job reveals that the job was running fine until one point, and then it failed due to HTTP 403 errors on the remaining files. You verified that there were no changes to the source system. You need to fix the problem to resume the migration process. What should you do?
A. Set up Cloud Storage FUSE, and mount the Cloud Storage bucket on a Compute Engine instance. Remove the completed files from the TSV file. Use a shell script to iterate through the TSV file and download the remaining URLs to the FUSE mount point.
B. Renew the TLS certificate of the HTTPS endpoint. Remove the completed files from the TSV file and rerun the Storage Transfer Service job.
C. Create a new TSV file for the remaining files by generating signed URLs with a longer validity period. Split the TSV file into multiple smaller files and submit them as separate Storage Transfer Service jobs in parallel.
D. Update the file checksums in the TSV file from using MD5 to SHA256. Remove the completed files from the TSV file and rerun the Storage Transfer Service job.
Show Answer
Correct Answer: C
Explanation: The HTTP 403 errors indicate that the signed URLs used by Storage Transfer Service became invalid during the long-running job, most likely because they expired. The correct fix is to regenerate signed URLs with a longer validity period for the remaining files and resume the transfer using those fresh URLs. Splitting the TSV into multiple smaller files and running separate jobs in parallel reduces total runtime and lowers the risk of URLs expiring again. Other options do not address expired signed URLs or add unnecessary complexity.
$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.