Professional Data Engineer Free Practice Questions — Page 12
Question 93
You currently use a SQL-based tool to visualize your data stored in BigQuery. The data visualizations require the use of outer joins and analytic functions. Visualizations must be based on data that is no less than 4 hours old. Business users are complaining that the visualizations are too slow to generate. You want to improve the performance of the visualization queries while minimizing the maintenance overhead of the data preparation pipeline. What should you do?
A. Create materialized views with the allow_non_incremental_definition option set to true for the visualization queries. Specify the max_staleness parameter to 4 hours and the enable_refresh parameter to true. Reference the materialized views in the data visualization tool.
B. Create views for the visualization queries. Reference the views in the data visualization tool.
C. Create a Cloud Function instance to export the visualization query results as parquet files to a Cloud Storage bucket. Use Cloud Scheduler to trigger the Cloud Function every 4 hours. Reference the parquet files in the data visualization tool.
D. Create materialized views for the visualization queries. Use the incremental updates capability of BigQuery materialized views to handle changed data automatically. Reference the materialized views in the data visualization tool.
Show Answer
Correct Answer: A
Explanation: The visualization queries use outer joins and analytic functions, which are not supported by incrementally refreshed BigQuery materialized views. However, non-incremental materialized views explicitly allow these constructs. Because the business requirement allows data to be up to 4 hours stale, setting max_staleness to 4 hours with enable_refresh provides automatic refresh with minimal maintenance. This precomputes expensive query results, significantly improving performance compared to standard views, while avoiding the operational overhead of custom export pipelines. Option D is incorrect because incremental materialized views cannot handle outer joins, analytic functions, or multiple changing base tables.
Question 94
You have a streaming pipeline that ingests data from Pub/Sub in production. You need to update this streaming pipeline with improved business logic. You need to ensure that the updated pipeline reprocesses the previous two days of delivered Pub/Sub messages. What should you do? (Choose two.)
A. Use the Pub/Sub subscription clear-retry-policy flag
B. Use Pub/Sub Snapshot capture two days before the deployment.
C. Create a new Pub/Sub subscription two days before the deployment.
D. Use the Pub/Sub subscription retain-acked-messages flag.
E. Use Pub/Sub Seek with a timestamp.
Show Answer
Correct Answer: D, E
Explanation: To reprocess the last two days of already delivered (acknowledged) Pub/Sub messages, the subscription must retain acknowledged messages and then be reset to an earlier point in time. Setting the retain-acked-messages flag ensures Pub/Sub keeps acknowledged messages (up to 7 days). Using Pub/Sub Seek with a timestamp allows the subscription to rewind to two days ago so those messages are re-delivered to the updated pipeline. Snapshots only capture the acknowledgement state at a point in time and are not sufficient by themselves for replaying all delivered messages over a time range.
Question 95
You stream order data by using a Dataflow pipeline, and write the aggregated result to Memorystore. You provisioned a Memorystore for Redis instance with Basic Tier, 4 GB capacity, which is used by 40 clients for read-only access. You are expecting the number of read-only clients to increase significantly to a few hundred and you need to be able to support the demand. You want to ensure that read and write access availability is not impacted, and any changes you make can be deployed quickly. What should you do?
A. Create a new Memorystore for Redis instance with Standard Tier. Set capacity to 4 GB and read replica to No read replicas (high availability only). Delete the old instance.
B. Create a new Memorystore for Redis instance with Standard Tier. Set capacity to 5 GB and create multiple read replicas. Delete the old instance.
C. Create a new Memorystore for Memcached instance. Set a minimum of three nodes, and memory per node to 4 GB. Modify the Dataflow pipeline and all clients to use the Memcached instance. Delete the old instance.
D. Create multiple new Memorystore for Redis instances with Basic Tier (4 GB capacity). Modify the Dataflow pipeline and new clients to use all instances.
Show Answer
Correct Answer: B
Explanation: You need to scale read traffic quickly without impacting write availability. Memorystore for Redis Standard Tier supports read replicas, which horizontally scale read-only access while keeping a single writable primary. This directly addresses the expected growth to hundreds of read-only clients and preserves write performance and availability via automatic failover. Migrating to Standard Tier requires minimal application changes compared to sharding across instances or switching to Memcached. Adding slightly more capacity accommodates growth during migration.
Question 96
Your organization has two Google Cloud projects, project A and project B. In project A, you have a Pub/Sub topic that receives data from confidential sources. Only the resources in project A should be able to access the data in that topic. You want to ensure that project B and any future project cannot access data in the project A topic. What should you do?
A. Add firewall rules in project A so only traffic from the VPC in project A is permitted.
B. Configure VPC Service Controls in the organization with a perimeter around project A.
C. Use Identity and Access Management conditions to ensure that only users and service accounts in project A. can access resources in project A.
D. Configure VPC Service Controls in the organization with a perimeter around the VPC of project A.
Show Answer
Correct Answer: B
Explanation: VPC Service Controls are designed to place a security perimeter around Google Cloud projects and supported services like Pub/Sub. By configuring a perimeter around project A, you ensure that only resources within that perimeter can access the Pub/Sub topic, preventing access from project B and any future projects even if IAM permissions are mistakenly granted. Firewall rules and VPC-based controls do not apply to Pub/Sub, and IAM alone is not future-proof against misconfiguration or privilege creep. Therefore, a VPC Service Controls perimeter around project A best meets the requirement.
Question 97
Your organization stores customer data in an on-premises Apache Hadoop cluster in Apache Parquet format. Data is processed on a daily basis by Apache Spark jobs that run on the cluster. You are migrating the Spark jobs and Parquet data to Google Cloud. BigQuery will be used on future transformation pipelines so you need to ensure that your data is available in BigQuery. You want to use managed services, while minimizing ETL data processing changes and overhead costs. What should you do?
A. Migrate your data to Cloud Storage and migrate the metadata to Dataproc Metastore (DPMS). Refactor Spark pipelines to write and read data on Cloud Storage, and run them on Dataproc Serverless.
B. Migrate your data to Cloud Storage and register the bucket as a Dataplex asset. Refactor Spark pipelines to write and read data on Cloud Storage, and run them on Dataproc Serverless.
C. Migrate your data to BigQuery. Refactor Spark pipelines to write and read data on BigQuery, and run them on Dataproc Serverless.
D. Migrate your data to BigLake. Refactor Spark pipelines to write and read data on Cloud Storage, and run them on Dataproc on Compute Engine.
Show Answer
Correct Answer: A
Explanation: The goal is to migrate Hadoop/Spark Parquet workloads to Google Cloud using managed services, minimize ETL changes, control costs, and ensure data is available to BigQuery. Moving Parquet files to Cloud Storage preserves the existing file-based Spark processing model with minimal refactoring. Running Spark on Dataproc Serverless provides a fully managed Spark service with low operational overhead. Using Dataproc Metastore maintains Hive-compatible metadata, closely matching the on-prem Hadoop environment. Data stored as Parquet in Cloud Storage can be queried from BigQuery via external tables, satisfying the requirement that the data be available in BigQuery without forcing an immediate rewrite of Spark pipelines or incurring higher BigQuery storage and processing costs.
Question 98
A web server sends click events to a Pub/Sub topic as messages. The web server includes an eventTimestamp attribute in the messages, which is the time when the click occurred. You have a Dataflow streaming job that reads from this Pub/Sub topic through a subscription, applies some transformations, and writes the result to another Pub/Sub topic for use by the advertising department. The advertising department needs to receive each message within 30 seconds of the corresponding click occurrence, but they report receiving the messages late. Your Dataflow job's system lag is about 5 seconds, and the data freshness is about 40 seconds. Inspecting a few messages show no more than 1 second lag between their eventTimestamp and publishTime. What is the problem and what should you do?
A. The advertising department is causing delays when consuming the messages. Work with the advertising department to fix this.
B. Messages in your Dataflow job are taking more than 30 seconds to process. Optimize your job or increase the number of workers to fix this.
G. Messages in your Dataflow job are processed in less than 30 seconds, but your job cannot keep up with the backlog in the Pub/Sub subscription. Optimize your job or increase the number of workers to fix this.
D. The web server is not pushing messages fast enough to Pub/Sub. Work with the web server team to fix this.
Show Answer
Correct Answer: G
Explanation: System lag (~5 seconds) shows that once messages are picked up by Dataflow, they are processed quickly. However, data freshness (~40 seconds) measures the delay from event time to output, which is high, indicating messages are waiting in a backlog before being processed. Since eventTimestamp to publishTime is only ~1 second, the web server and Pub/Sub publishing are not the issue. The problem is that the Dataflow job cannot keep up with the incoming message rate, causing backlog in the subscription. Increasing resources or optimizing the pipeline is required.
Question 99
You are building an ELT solution in BigQuery by using Dataform. You need to perform uniqueness and null value checks on your final tables. What should you do to efficiently integrate these checks into your pipeline?
A. Build BigQuery user-defined functions (UDFs).
B. Create Dataplex data quality tasks.
C. Build Dataform assertions into your code.
D. Write a Spark-based stored procedure.
Show Answer
Correct Answer: C
Explanation: Dataform has built-in assertions designed for data quality checks such as uniqueness and null validations. They integrate directly into the Dataform pipeline, are SQL-based, and run automatically as part of table builds, making them the most efficient and native way to enforce data quality in a BigQuery ELT workflow.
Question 100
You have a variety of files in Cloud Storage that your data science team wants to use in their models. Currently, users do not have a method to explore, cleanse, and validate the data in Cloud Storage. You are looking for a low code solution that can be used by your data science team to quickly cleanse and explore data within Cloud Storage. What should you do?
A. Provide the data science team access to Dataflow to create a pipeline to prepare and validate the raw data and load data into BigQuery for data exploration.
B. Create an external table in BigQuery and use SQL to transform the data as necessary. Provide the data science team access to the external tables to explore the raw data.
C. Load the data into BigQuery and use SQL to transform the data as necessary. Provide the data science team access to staging tables to explore the raw data.
D. Provide the data science team access to Dataprep to prepare, validate, and explore the data within Cloud Storage.
Show Answer
Correct Answer: D
Explanation: The requirement is a low-code solution that allows data scientists to explore, cleanse, and validate data directly in Cloud Storage. Dataprep is specifically designed for visual, low/no-code data preparation, profiling, and validation, and it can work directly with data stored in Cloud Storage. The other options rely on Dataflow or SQL-based transformations in BigQuery, which are code-centric and do not meet the low-code requirement as well as Dataprep.
Question 101
You store and analyze your relational data in BigQuery on Google Cloud with all data that resides in US regions. You also have a variety of object stores across Microsoft Azure and Amazon Web Services (AWS), also in US regions. You want to query all your data in BigQuery daily with as little movement of data as possible. What should you do?
A. Use BigQuery Data Transfer Service to load files from Azure and AWS into BigQuery.
B. Create a Dataflow pipeline to ingest files from Azure and AWS to BigQuery.
C. Load files from AWS and Azure to Cloud Storage with Cloud Shell gsutil rsync arguments.
D. Use the BigQuery Omni functionality and BigLake tables to query files in Azure and AWS.
Show Answer
Correct Answer: D
Explanation: The requirement is to query data in BigQuery with as little data movement as possible across clouds. BigQuery Omni enables direct querying of data stored in AWS and Azure object stores from BigQuery without copying it into Google Cloud. Using BigLake tables provides a unified abstraction to query both BigQuery and external cloud storage data, minimizing data transfer. Other options require ingesting or copying data.
Question 102
You are migrating your on-premises data warehouse to BigQuery. One of the upstream data sources resides on a MySQL. database that runs in your on-premises data center with no public IP addresses. You want to ensure that the data ingestion into BigQuery is done securely and does not go through the public internet. What should you do?
A. Update your existing on-premises ETL tool to write to BigQuery by using the BigQuery Open Database Connectivity (ODBC) driver. Set up the proxy parameter in the simba.googlebigqueryodbc.ini file to point to your data center’s NAT gateway.
B. Use Datastream to replicate data from your on-premises MySQL database to BigQuery. Set up Cloud Interconnect between your on-premises data center and Google Cloud. Use Private connectivity as the connectivity method and allocate an IP address range within your VPC network to the Datastream connectivity configuration. Use Server-only as the encryption type when setting up the connection profile in Datastream.
C. Use Datastream to replicate data from your on-premises MySQL database to BigQuery. Use Forward-SSH tunnel as the connectivity method to establish a secure tunnel between Datastream and your on-premises MySQL database through a tunnel server in your on-premises data center. Use None as the encryption type when setting up the connection profile in Datastream.
D. Use Datastream to replicate data from your on-premises MySQL database to BigQuery. Gather Datastream public IP addresses of the Google Cloud region that will be used to set up the stream. Add those IP addresses to the firewall allowlist of your on-premises data center. Use IP Allowlisting as the connectivity method and Server-only as the encryption type when setting up the connection profile in Datastream.
Show Answer
Correct Answer: B
Explanation: The requirement is secure ingestion into BigQuery without traversing the public internet from an on‑premises MySQL source that has no public IPs. Datastream is the native CDC service for replicating MySQL data into BigQuery. Using Cloud Interconnect provides a private, dedicated network path between the on‑premises data center and Google Cloud, ensuring traffic does not use the public internet. Datastream private connectivity with an allocated VPC IP range supports this architecture securely. Other options either rely on public IP allowlisting, SSH tunneling, or public internet paths, which do not meet the requirement as cleanly or securely.
$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.