Professional Data Engineer Free Practice Questions — Page 5
Question 23
You have a data analyst team member who needs to analyze data by using BigQuery. The data analyst wants to create a data pipeline that would load 200 CSV files with an average size of 15MB from a Cloud Storage bucket into BigQuery daily. The data needs to be ingested and transformed before being accessed in BigQuery for analysis. You need to recommend a fully managed, no-code solution for the data analyst. What should you do?
A. Create a Cloud Run function and schedule it to run daily using Cloud Scheduler to load the data into BigQuery.
B. Use the BigQuery Data Transfer Service to load files from Cloud Storage to BigQuery, create a BigQuery job which transforms the data using BigQuery SQL and schedule it to run daily.
C. Build a custom Apache Beam pipeline and run it on Dataflow to load the file from Cloud Storage to BigQuery and schedule it to run daily using Cloud Composer.
D. Create a pipeline by using BigQuery pipelines and schedule it to load the data into BigQuery daily.
Show Answer
Correct Answer: D
Explanation: BigQuery Pipelines is the fully managed, serverless solution designed for building ingestion and transformation pipelines within BigQuery with minimal/no code. It can ingest data from Cloud Storage, perform SQL-based transformations, and schedule recurring runs, matching the requirements more directly than combining separate services or writing custom code.
Question 24
You are configuring networking for a Dataflow job. The data pipeline uses custom container images with the libraries that are required for the transformation logic preinstalled. The data pipeline reads the data from Cloud Storage and writes the data to BigQuery. You need to ensure cost-effective and secure communication between the pipeline and Google APIs and services. What should you do?
A. Disable external IP addresses from worker VMs and enable Private Google Access.
B. Leave external IP addresses assigned to worker VMs while enforcing firewall rules.
C. Disable external IP addresses and establish a Private Service Connect endpoint IP address.
D. Enable Cloud NAT to provide outbound internet connectivity while enforcing firewall rules.
Show Answer
Correct Answer: A
Explanation: Because the Dataflow job only needs to access Google APIs (Cloud Storage and BigQuery) and uses custom container images with dependencies already preinstalled, worker VMs do not require general internet access. Disabling external IP addresses improves security and avoids external networking costs, while enabling Private Google Access allows the VMs to privately reach Google APIs such as Cloud Storage and BigQuery. Private Service Connect is unnecessary for this scenario, Cloud NAT is intended for internet egress, and keeping external IPs is less secure.
Question 25
You are administering a BigQuery on-demand environment. Your business intelligence tool is submitting hundreds of queries each day that aggregate a large (50 TB) sales history fact table at the day and month levels. These queries have a slow response time and are exceeding cost expectations. You need to decrease response time, lower query costs, and minimize maintenance. What should you do?
A. Build authorized views on top of the sales table to aggregate data at the day and month level.
B. Enable BI Engine and add your sales table as a preferred table.
C. Build materialized views on top of the sales table to aggregate data at the day and month level.
D. Create a scheduled query to build sales day and sales month aggregate tables on an hourly basis.
Show Answer
Correct Answer: C
Explanation: Materialized views are designed for repeated aggregate queries over large base tables. They precompute and incrementally maintain aggregated results, reducing data scanned, improving query latency, and minimizing maintenance compared with manually scheduled aggregate tables. Authorized views do not improve performance, BI Engine is primarily an in-memory acceleration layer with capacity limitations and is not appropriate for a 50 TB fact table, and scheduled aggregate tables require ongoing maintenance.
Question 26
You are using Workflows to call an API that returns a 1KB JSON response, apply some complex business logic on this response, wait for the logic to complete, and then perform a load from a Cloud Storage file to BigQuery. The Workflows standard library does not have sufficient capabilities to perform your complex logic, and you want to use Python's standard library instead. You want to optimize your workflow for simplicity and speed of execution. What should you do?
A. Create a Cloud Composer environment and run the logic in Cloud Composer.
B. Create a Dataproc cluster, and use PySpark to apply the logic on your JSON file.
C. Invoke a Cloud Function instance that uses Python to apply the logic on your JSON file.
D. Invoke a subworkflow in Workflows to apply the logic on your JSON file.
Show Answer
Correct Answer: C
Explanation: The best choice is to invoke a Cloud Function (or its modern equivalent, Cloud Run functions) from Workflows to execute the complex Python business logic. Workflows is designed to orchestrate services rather than perform complex computation. A Cloud Function provides serverless, low-latency execution for small payloads like a 1 KB JSON response and integrates directly with Workflows. Cloud Composer is unnecessarily complex for this use case, Dataproc is excessive for such a small task, and a subworkflow would still be limited by the Workflows language and standard library, which the question states is insufficient.
Question 27
You are migrating your on-premises data warehouse to BigQuery. As part of the migration, you want to facilitate cross-team collaboration to get the most value out of the organization’s data. You need to design an architecture that would allow teams within the organization to securely publish, discover, and subscribe to read-only data in a self-service manner. You need to minimize costs while also maximizing data freshness. What should you do?
A. Use Analytics Hub to facilitate data sharing.
B. Create authorized datasets to publish shared data in the subscribing team's project.
C. Create a new dataset for sharing in each individual team’s project. Grant the subscribing team the bigquery.dataViewer role on the dataset.
D. Use BigQuery Data Transfer Service to copy datasets to a centralized BigQuery project for sharing.
Show Answer
Correct Answer: A
Explanation: Analytics Hub is purpose-built for secure, self-service data sharing in BigQuery. It enables teams to publish, discover, and subscribe to read-only datasets without copying data, minimizing storage costs while providing access to the freshest underlying data. Authorized datasets and separate shared datasets require more manual administration, and BigQuery Data Transfer Service copies data, increasing costs and reducing freshness.
Question 28
You have several different unstructured data sources, within your on-premises data center as well as in the cloud. The data is in various formats, such as Apache Parquet and CSV. You want to centralize this 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 BigQuery Data Transfer Service to move files into BigQuery.
B. Use Storage Transfer Service to move files into Cloud Storage
C. Use Dataflow to move files into Cloud Storage
D. Use Cloud Data Fusion to move files into Cloud Storage.
Show Answer
Correct Answer: D
Explanation: Cloud Data Fusion is the GUI-based data integration service designed to ingest data from diverse on-premises and cloud sources into Cloud Storage. It supports configuring Cloud Storage sinks and can use customer-managed encryption keys (CMEK). Storage Transfer Service has a GUI but is primarily for transfer workflows and does not satisfy the encryption-key requirement in the way described. Dataflow is code-based rather than GUI-based, and BigQuery Data Transfer Service targets BigQuery rather than Cloud Storage.
Question 29
You want to migrate an Apache Spark 3 batch job from on-premises to Google Cloud. You need to minimally change the job so that the job reads from Cloud Storage and writes the result to BigQuery. Your job is optimized for Spark, where each executor has 8 vCPU and 16 GB memory, and you want to be able to choose similar settings. You want to minimize installation and management effort to run your job. What should you do?
A. Execute the job as part of a deployment in a new Google Kubernetes Engine cluster.
B. Execute the job from a new Compute Engine VM.
C. Execute the job in a new Dataproc cluster.
D. Execute as a Dataproc Serverless job.
Show Answer
Correct Answer: D
Explanation: Dataproc Serverless is the best fit because it minimizes operational overhead by eliminating cluster provisioning and management while still supporting Apache Spark 3 and allowing configuration of Spark executor resources (such as cores and memory) to approximate the existing executor sizing. It integrates with Cloud Storage and the Spark BigQuery connector for reading and writing data with minimal code changes.
Sources:
https://cloud.google.com/dataproc-serverless/docs/guides/bigquery-connector-spark-example
https://cloud.google.com/blog/products/data-analytics/serverless-spark-on-google-cloud-interactive-tutorial
Question 30
You are using BigQuery with a regional dataset that includes a table with the daily sales volumes. This table is updated multiple times per day. You need to protect your sales table in case of regional failures with a recovery point objective (RPO) of less than 24 hours, while keeping costs to a minimum. What should you do?
A. Schedule a daily export of the table to a Cloud Storage dual or multi-region bucket.
B. Schedule a daily copy of the dataset to a backup region.
C. Schedule a daily BigQuery snapshot of the table.
D. Modify ETL job to load the data into both the current and another backup region.
Show Answer
Correct Answer: A
Explanation: A daily export of the table to a Cloud Storage dual- or multi-region bucket provides an off-region backup that survives a regional BigQuery outage and satisfies an RPO of less than 24 hours when scheduled daily. It is also the lowest-cost option because Cloud Storage is typically cheaper than maintaining a duplicate BigQuery copy. Dataset copies cost more, table snapshots remain in the same region and do not protect against regional failure, and dual-writing ETL provides a better RPO but increases ongoing storage and pipeline costs beyond the stated goal of minimizing cost.
Question 31
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 follow data privacy requirements, including protecting certain sensitive data elements, while also retaining all of the data for potential future use cases. What should you do?
A. 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.
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 Dataflow and the Cloud Data Loss Prevention API to mask sensitive data. Write the processed 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: C
Explanation: The requirement is to protect sensitive data while retaining all data for potential future use. Masking or de-identifying sensitive fields with the Cloud Data Loss Prevention (Sensitive Data Protection) API preserves the dataset while protecting PII. Dataflow is the appropriate pipeline tool to process data and write the masked output to BigQuery. Option A removes data, violating the retention requirement. Option B encrypts data at rest but does not preprocess or protect specific sensitive elements for analytics. Option D encrypts fields, but Cloud DLP is the managed service designed for detecting and masking sensitive data for analytics use cases; field-level encryption is less appropriate for consumer analysis workflows.
Question 32
Your chemical company needs to manually check documentation for customer order. You use a pull subscription in Pub/Sub so that sales agents get details from the order. You must ensure that you do not process orders twice with different sales agents and that you do not add more complexity to this workflow. What should you do?
A. Use a Deduplicate PTransform in Dataflow before sending the messages to the sales agents.
B. Create a transactional database that monitors the pending messages.
C. Use Pub/Sub exactly-once delivery in your pull subscription.
D. Create a new Pub/Sub push subscription to monitor the orders processed in the agent's system.
Show Answer
Correct Answer: C
Explanation: Exactly-once delivery for Pub/Sub pull subscriptions is designed to prevent redelivery of successfully acknowledged messages, reducing the risk of processing the same order twice without adding extra workflow components. The other options introduce additional infrastructure or solve a different problem.
$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.