Professional Data Engineer Free Practice Questions — Page 13
Question 120
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 that integrate directly into ELT workflows and can enforce data quality checks such as uniqueness and non-null constraints on tables. This is the native, efficient approach for validating final datasets in BigQuery Dataform pipelines.
Question 121
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 (now Cloud Dataprep/Trifacta, with similar functionality available in Google Cloud's data preparation offerings) is specifically designed for visual, low-code data profiling, cleansing, and validation on data stored in Cloud Storage. The other options require more coding or moving/querying data through BigQuery or Dataflow rather than providing the requested low-code preparation experience.
Question 122
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: BigQuery Omni with BigLake lets you query data stored in AWS and Azure object storage directly from BigQuery, minimizing data movement while enabling cross-cloud analytics. The other options require copying or ingesting data into Google Cloud, which contradicts the requirement to move as little data as possible.
Question 123
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: Datastream is the managed CDC service designed to replicate from on-premises MySQL to BigQuery. To ensure traffic does not traverse the public internet, use Cloud Interconnect with Datastream Private connectivity, allocating an IP range in the VPC for the private connection. Server-only TLS provides encrypted server authentication. The other options either rely on public internet paths (IP allowlisting, SSH tunnel) or are not the recommended ingestion architecture (ODBC via NAT).
Question 124
You orchestrate ETL pipelines by using Cloud Composer. One of the tasks in the Apache Airflow directed acyclic graph (DAG) relies on a third-party service. You want to be notified when the task does not succeed. What should you do?
A. Assign a function with notification logic to the on_retry_callback parameter for the operator responsible for the task at risk.
B. Configure a Cloud Monitoring alert on the sla_missed metric associated with the task at risk to trigger a notification.
C. Assign a function with notification logic to the on_failure_callback parameter tor the operator responsible for the task at risk.
D. Assign a function with notification logic to the sla_miss_callback parameter for the operator responsible for the task at risk.
Show Answer
Correct Answer: C
Explanation: The correct callback for notifying when a task fails is on_failure_callback. It is invoked when the task enters the failed state, allowing you to execute custom notification logic such as sending an email or calling an external alerting service. on_retry_callback only runs on retries, sla_miss_callback is for SLA misses rather than task failures, and Cloud Monitoring alerts on SLA misses do not directly detect all task failures.
Question 125
You have a Cloud SQL for PostgreSQL instance in Region’ with one read replica in Region2 and another read replica in Region3. An unexpected event in Region’ requires that you perform disaster recovery by promoting a read replica in Region2. You need to ensure that your application has the same database capacity available before you switch over the connections. What should you do?
A. Enable zonal high availability on the primary instance. Create a new read replica in a new region.
B. Create a cascading read replica from the existing read replica in Region3.
C. Create two new read replicas from the new primary instance, one in Region3 and one in a new region.
D. Create a new read replica in Region1, promote the new read replica to be the primary instance, and enable zonal high availability.
Show Answer
Correct Answer: C
Explanation: After promoting the Region2 read replica to become the new primary, the remaining replicas based on the old primary are no longer sufficient for restoring the original read capacity and replication topology. To regain equivalent capacity and resilience before directing application traffic to the new primary, create new read replicas from the promoted primary. Option C does this by creating two new replicas, restoring the number of read replicas available and establishing replication from the new primary.
Question 126
You have a BigQuery table that contains customer data, including sensitive information such as names and addresses. You need to share the customer data with your data analytics and consumer support teams securely. The data analytics team needs to access the data of all the customers, but must not be able to access the sensitive data. The consumer support team needs access to all data columns, but must not be able to access customers that no longer have active contracts. You enforced these requirements by using an authorized dataset and policy tags. After implementing these steps, the data analytics team reports that they still have access to the sensitive columns. You need to ensure that the data analytics team does not have access to restricted data. What should you do? (Choose two.)
A. Create two separate authorized datasets; one for the data analytics team and another for the consumer support team.
B. Ensure that the data analytics team members do not have the Data Catalog Fine-Grained Reader role for the policy tags.
C. Replace the authorized dataset with an authorized view. Use row-level security and apply filter_expression to limit data access.
D. Remove the bigquery.dataViewer role from the data analytics team on the authorized datasets.
E. Enforce access control in the policy tag taxonomy.
Show Answer
Correct Answer: B, E
Explanation: Column-level security with policy tags is enforced through the policy tag taxonomy. If users still see sensitive columns, verify that access control enforcement is enabled on the taxonomy and that those users have not been granted the Fine-Grained Reader access that allows access governed by the policy tags. Authorized datasets address dataset access, not column-level restrictions.
Question 127
You are developing an Apache Beam pipeline to extract data from a Cloud SQL instance by using JdbcIO. You have two projects running in Google Cloud. The pipeline will be deployed and executed on Dataflow in Project A. The Cloud SQL. instance is running in Project B and does not have a public IP address. After deploying the pipeline, you noticed that the pipeline failed to extract data from the Cloud SQL instance due to connection failure. You verified that VPC Service Controls and shared VPC are not in use in these projects. You want to resolve this error while ensuring that the data does not go through the public internet. What should you do?
A. Set up VPC Network Peering between Project A and Project B. Add a firewall rule to allow the peered subnet range to access all instances on the network.
B. Turn off the external IP addresses on the Dataflow worker. Enable Cloud NAT in Project A.
C. Add the external IP addresses of the Dataflow worker as authorized networks in the Cloud SQL instance.
D. Set up VPC Network Peering between Project A and Project B. Create a Compute Engine instance without external IP address in Project B on the peered subnet to serve as a proxy server to the Cloud SQL database.
Show Answer
Correct Answer: A
Explanation: The intended solution is to provide private network connectivity between the Dataflow workers in Project A and the Cloud SQL private IP network in Project B using VPC Network Peering and appropriate firewall rules. Disabling external IPs or using Cloud NAT does not create connectivity to a private-only Cloud SQL instance, and authorizing external IPs requires public IP access. Introducing a proxy VM is unnecessary for the exam's intended architecture and adds operational complexity.
Question 128
You are administering a BigQuery dataset that uses a customer-managed encryption key (CMEK). You need to share the dataset with a partner organization that does not have access to your CMEK. What should you do?
A. Provide the partner organization a copy of your CMEKs to decrypt the data.
B. Export the tables to parquet files to a Cloud Storage bucket and grant the storageinsights.viewer role on the bucket to the partner organization.
C. Copy the tables you need to share to a dataset without CMEKs. Create an Analytics Hub listing for this dataset.
D. Create an authorized view that contains the CMEK to decrypt the data when accessed.
Show Answer
Correct Answer: C
Explanation: A is incorrect because CMEKs should never be shared with external organizations. B is incorrect because exporting to Cloud Storage and granting Storage Insights Viewer does not provide access to the data files and does not address CMEK access. D is incorrect because authorized views control table access but do not decrypt data for principals that lack access to the CMEK. Copying the required tables into a dataset encrypted with Google-managed keys (or otherwise not requiring the partner to access your CMEK) and sharing them through Analytics Hub is the appropriate approach for external sharing.
Question 129
You have a Standard Tier Memorystore for Redis instance deployed in a production environment. You need to simulate a Redis instance failover in the most accurate disaster recovery situation, and ensure that the failover has no impact on production data. What should you do?
A. Create a Standard Tier Memorystore for Redis instance in the development environment. Initiate a manual failover by using the limited-data-loss data protection mode.
B. Create a Standard Tier Memorystore for Redis instance in a development environment. Initiate a manual failover by using the force-data-loss data protection mode.
C. Increase one replica to Redis instance in production environment. Initiate a manual failover by using the force-data-loss data protection mode.
D. Initiate a manual failover by using the limited-data-loss data protection mode to the Memorystore for Redis instance in the production environment.
Show Answer
Correct Answer: B
Explanation: To avoid any impact on production data, the failover test should be performed on a separate Standard Tier Memorystore for Redis instance in a development environment. To simulate the most severe and realistic disaster recovery behavior, use the force-data-loss manual failover mode, which does not wait for replica synchronization and exercises the aggressive failover path. Running the test in production (C or D) contradicts the requirement to avoid impacting production data, while limited-data-loss mode (A) prioritizes minimizing data loss rather than simulating the most aggressive disaster recovery scenario.
$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.