Professional Data Engineer Free Practice Questions — Page 6
Question 33
You currently have transactional data stored on-premises in a PostgreSQL database. To modernize your data environment, you want to run transactional workloads and support analytics needs with a single database. You need to move to Google Cloud without changing database management systems, and minimize cost and complexity. What should you do?
A. Migrate and modernize your database with Cloud Spanner.
B. Migrate your workloads to AlloyDB for PostgreSQL.
C. Migrate to BigQuery to optimize analytics.
D. Migrate your PostgreSQL database to Cloud SQL for PostgreSQL.
Show Answer
Correct Answer: B
Explanation: The requirement is to run transactional workloads and support analytics in a single database while staying on PostgreSQL and minimizing complexity. AlloyDB for PostgreSQL is purpose-built for hybrid transactional and analytical processing (HTAP), offering PostgreSQL compatibility plus features like columnar storage and high performance for mixed workloads. Cloud SQL for PostgreSQL is primarily optimized for OLTP and does not natively address analytics on the same database. Therefore, AlloyDB best fits the stated goals.
Question 34
You migrated your on-premises Apache Hadoop Distributed File System (HDFS) data lake to Cloud Storage. The data scientist team needs to process the data by using Apache Spark and SQL. Security policies need to be enforced at the column level. You need a cost-effective solution that can scale into a data mesh. What should you do?
A. 1. Deploy a long-living Dataproc cluster with Apache Hive and Ranger enabled. 2. Configure Ranger for column level security. 3. Process with Dataproc Spark or Hive SQL.
B. 1. Define a BigLake table. 2. Create a taxonomy of policy tags in Data Catalog. 3. Add policy tags to columns. 4. Process with the Spark-BigQuery connector or BigQuery SQL.
C. 1. Load the data to BigQuery tables. 2. Create a taxonomy of policy tags in Data Catalog. 3. Add policy tags to columns. 4. Process with the Spark-BigQuery connector or BigQuery SQL.
D. 1. Apply an Identity and Access Management (IAM) policy at the file level in Cloud Storage. 2. Define a BigQuery external table for SQL processing. 3. Use Dataproc Spark to process the Cloud Storage files.
Show Answer
Correct Answer: B
Explanation: BigLake lets you define tables directly on data stored in Cloud Storage and enforce fine-grained (column-level) security using Data Catalog policy tags. This avoids loading data into BigQuery, keeping costs lower while remaining serverless and highly scalable. Data scientists can process the data using either BigQuery SQL or Apache Spark via the Spark–BigQuery connector, which fits the requirement for Spark and SQL. BigLake and Data Catalog are designed to support decentralized governance, making this approach well suited for scaling into a data mesh.
Question 35
You are designing the architecture to process your data from Cloud Storage to BigQuery by using Dataflow. The network team provided you with the Shared VPC network and subnetwork to be used by your pipelines. You need to enable the deployment of the pipeline on the Shared VPC network. What should you do?
A. Assign the compute.networkUser role to the Dataflow service agent.
B. Assign the compute.networkUser role to the service account that executes the Dataflow pipeline.
C. Assign the dataflow.admin role to the Dataflow service agent.
D. Assign the dataflow.admin role to the service account that executes the Dataflow pipeline.
Show Answer
Correct Answer: B
Explanation: To run Dataflow jobs on a Shared VPC, the identity that creates and runs the worker VMs must be able to use the host project's subnetwork. That identity is the service account that executes the Dataflow pipeline (the Dataflow worker/runtime service account). Granting it the compute.networkUser role on the Shared VPC subnet allows the workers to attach to the network. The Dataflow service agent does not require this role for worker network attachment, and dataflow.admin is unrelated to network access.
Question 36
You work for a large ecommerce company. You are using Pub/Sub to ingest the clickstream data to Google Cloud for analytics. You observe that when a new subscriber connects to an existing topic to analyze data, they are unable to subscribe to older data. For an upcoming yearly sale event in two months, you need a solution that, once implemented, will enable any new subscriber to read the last 30 days of data. What should you do?
A. Create a new topic, and publish the last 30 days of data each time a new subscriber connects to an existing topic.
B. Set the topic retention policy to 30 days.
C. Set the subscriber retention policy to 30 days.
D. Ask the source system to re-push the data to Pub/Sub, and subscribe to it.
Show Answer
Correct Answer: B
Explanation: In Pub/Sub, a new subscription normally only receives messages published after it is created. To allow new subscribers to read historical data, the messages must still be retained by the topic. Setting the **topic message retention** to 30 days ensures that all published messages are stored for up to 30 days, regardless of acknowledgment. New subscriptions can then seek back to the earliest retained messages and read the last 30 days of data. Subscription retention does not help new subscribers, and republishing data or re-pushing from the source is unnecessary.
Question 37
Your organization is modernizing their IT services and migrating to Google Cloud. You need to organize the data that will be stored in Cloud Storage and BigQuery. You need to enable a data mesh approach to share the data between sales, product design, and marketing departments. What should you do?
A. 1. Create a project for storage of the data for each of your departments. 2. Enable each department to create Cloud Storage buckets and BigQuery datasets. 3. Create user groups for authorized readers for each bucket and dataset. 4. Enable the IT team to administer the user groups to add or remove users as the departments’ request.
B. 1. Create multiple projects for storage of the data for each of your departments’ applications. 2. Enable each department to create Cloud Storage buckets and BigQuery datasets. 3. Publish the data that each department shared in Analytics Hub. 4. Enable all departments to discover and subscribe to the data they need in Analytics Hub.
C. 1. Create a project for storage of the data for your organization. 2. Create a central Cloud Storage bucket with three folders to store the files for each department. 3. Create a central BigQuery dataset with tables prefixed with the department name. 4. Give viewer rights for the storage project for the users of your departments.
D. 1. Create multiple projects for storage of the data for each of your departments’ applications. 2. Enable each department to create Cloud Storage buckets and BigQuery datasets. 3. In Dataplex, map each department to a data lake and the Cloud Storage buckets, and map the BigQuery datasets to zones. 4. Enable each department to own and share the data of their data lakes.
Show Answer
Correct Answer: D
Explanation: A data mesh requires decentralized data ownership by domains, self-service infrastructure, and governed data sharing across both Cloud Storage and BigQuery. Dataplex is designed for this: each department can own its own projects, buckets, and datasets, map them into domain-specific lakes and zones, and share data products with governance and discovery. Analytics Hub focuses primarily on sharing BigQuery datasets and does not cover Cloud Storage assets, while centralized or folder-based approaches contradict data mesh principles.
Question 38
You have terabytes of customer behavioral data streaming from Google Analytics into BigQuery daily. Your customers’ information, such as their preferences, is hosted on a Cloud SQL for MySQL database. Your CRM database is hosted on a Cloud SQL for PostgreSQL instance. The marketing team wants to use your customers’ information from the two databases and the customer behavioral data to create marketing campaigns for yearly active customers. You need to ensure that the marketing team can run the campaigns over 100 times a day on typical days and up to 300 during sales. At the same time, you want to keep the load on the Cloud SQL databases to a minimum. What should you do?
A. Create BigQuery connections to both Cloud SQL databases. Use BigQuery federated queries on the two databases and the Google Analytics data on BigQuery to run these queries.
B. Create a job on Apache Spark with Dataproc Serverless to query both Cloud SQL databases and the Google Analytics data on BigQuery for these queries.
C. Create streams in Datastream to replicate the required tables from both Cloud SQL databases to BigQuery for these queries.
D. Create a Dataproc cluster with Trino to establish connections to both Cloud SQL databases and BigQuery, to execute the queries.
Show Answer
Correct Answer: C
Explanation: Replicating the required Cloud SQL (MySQL and PostgreSQL) tables into BigQuery using Datastream offloads analytical query traffic from the transactional databases. BigQuery is optimized for high‑frequency, large‑scale analytics (100–300 runs/day) and can efficiently join replicated customer data with existing Google Analytics data. This minimizes load on Cloud SQL, avoids the performance penalties of federated queries, and provides scalable, low‑latency analytics for the marketing team.
Question 39
You designed a data warehouse in BigQuery to analyze sales data. You want a self-serving, low-maintenance, and cost- effective solution to share the sales dataset to other business units in your organization. What should you do?
A. Create an Analytics Hub private exchange, and publish the sales dataset.
B. Enable the other business units’ projects to access the authorized views of the sales dataset.
C. Create and share views with the users in the other business units.
D. Use the BigQuery Data Transfer Service to create a schedule that copies the sales dataset to the other business units’ projects.
Show Answer
Correct Answer: A
Explanation: Analytics Hub is designed for self-service, low-maintenance data sharing in BigQuery. By publishing the sales dataset to a private exchange, other business units can discover and subscribe to the data without copying it, minimizing operational overhead and storage costs. Authorized views or manually shared views require ongoing access management, and copying data via Data Transfer Service increases cost and maintenance.
Question 40
You are designing a messaging system by using Pub/Sub to process clickstream data with an event-driven consumer app that relies on a push subscription. You need to configure the messaging system that is reliable enough to handle temporary downtime of the consumer app. You also need the messaging system to store the input messages that cannot be consumed by the subscriber. The system needs to retry failed messages gradually, avoiding overloading the consumer app, and store the failed messages after a maximum of 10 retries in a topic. How should you configure the Pub/Sub subscription?
A. Increase the acknowledgement deadline to 10 minutes.
B. Use immediate redelivery as the subscription retry policy, and configure dead lettering to a different topic with maximum delivery attempts set to 10.
C. Use exponential backoff as the subscription retry policy, and configure dead lettering to the same source topic with maximum delivery attempts set to 10.
D. Use exponential backoff as the subscription retry policy, and configure dead lettering to a different topic with maximum delivery attempts set to 10.
Show Answer
Correct Answer: D
Explanation: The system must tolerate temporary consumer downtime, retry failed deliveries gradually, and persist messages that ultimately cannot be processed. An exponential backoff retry policy spaces out retries to avoid overwhelming the push consumer during recovery. Configuring a dead-letter topic ensures that messages failing after a defined number of delivery attempts are stored for later inspection or reprocessing. Setting the maximum delivery attempts to 10 meets the requirement to retry up to 10 times before moving messages to a separate topic.
Question 41
You have data located in BigQuery that is used to generate reports for your company. You have noticed some weekly executive report fields do not correspond to format according to company standards. For example, report errors include different telephone formats and different country code identifiers. This is a frequent issue, so you need to create a recurring job to normalize the data. You want a quick solution that requires no coding. What should you do?
A. Use Cloud Data Fusion and Wrangler to normalize the data, and set up a recurring job.
B. Use Dataflow SQL to create a job that normalizes the data, and that after the first run of the job, schedule the pipeline to execute recurrently.
C. Create a Spark job and submit it to Dataproc Serverless.
D. Use BigQuery and GoogleSQL to normalize the data, and schedule recurring queries in BigQuery.
Show Answer
Correct Answer: A
Explanation: The key requirements are a quick solution, no coding, and a recurring job to normalize data already in BigQuery. Cloud Data Fusion Wrangler is specifically designed for no-code/low-code data wrangling through a visual UI, supports common normalization tasks like standardizing phone numbers and country codes, and allows scheduling pipelines to run recurrently. The other options all require writing SQL or code (SQL, Spark, or Dataflow), which violates the no-coding requirement.
Question 42
You are part of a healthcare organization where data is organized and managed by respective data owners in various storage services. As a result of this decentralized ecosystem, discovering and managing data has become difficult. You need to quickly identify and implement a cost-optimized solution to assist your organization with the following:
• Data management and discovery
• Data lineage tracking
• Data quality validation
How should you build the solution?
A. Use BigLake to convert the current solution into a data lake architecture.
B. Build a new data discovery tool on Google Kubernetes Engine that helps with new source onboarding and data lineage tracking.
C. Use BigQuery to track data lineage, and use Dataprep to manage data and perform data quality validation.
D. Use Dataplex to manage data, track data lineage, and perform data quality validation.
Show Answer
Correct Answer: D
Explanation: Dataplex is a managed, cost-optimized data fabric designed for decentralized data environments. It provides centralized data discovery and governance across multiple storage services, built-in data lineage tracking, and native data quality validation through rules and profiling. The other options either address only parts of the requirements or require significant custom development and higher operational cost.
$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.