Professional Data Engineer Free Practice Questions — Page 14
Question 130
Your company's customer_order table in BigQuery stores the order history for 10 million customers, with a table size of 10 PB. You need to create a dashboard for the support team to view the order history. The dashboard has two filters, country_name and username. Both are string data types in the BigQuery table. When a filter is applied, the dashboard fetches the order history from the table and displays the query results. However, the dashboard is slow to show the results when applying the filters to the following query:
How should you redesign the BigQuery table to support faster access?
A. Cluster the table by country and username fields.
B. Cluster the table by country field, and partition by username field.
C. Partition the table by country and username fields.
D. Partition the table by _PARTITIONTIME.
Show Answer
Correct Answer: A
Explanation: Cluster the table by the country_name and username columns. BigQuery clustering is designed to optimize queries that frequently filter on high-cardinality columns such as strings by colocating similar values, reducing the amount of data scanned. Partitioning is not appropriate here because BigQuery does not support partitioning on arbitrary string columns; partitioning supports time/date/timestamp/datetime columns or integer range partitions. Partitioning by _PARTITIONTIME would not help because the queries filter by country_name and username, not ingestion time.
Question 131
You are troubleshooting your Dataflow pipeline that processes data from Cloud Storage to BigQuery. You have discovered that the Dataflow worker nodes cannot communicate with one another. Your networking team relies on Google Cloud network tags to define firewall rules. You need to identify the issue while following Google-recommended networking security practices. What should you do?
A. Determine whether your Dataflow pipeline has a custom network tag set.
B. Determine whether there is a firewall rule set to allow traffic on TCP ports 12345 and 12346 for the Dataflow network tag.
C. Determine whether there is a firewall rule set to allow traffic on TCP ports 12345 and 12346 on the subnet used by Dataflow workers.
D. Determine whether your Dataflow pipeline is deployed with the external IP address option enabled.
Show Answer
Correct Answer: B
Explanation: Dataflow workers must communicate with each other over TCP ports 12345 and 12346 (or 12346 only for Streaming Engine in some configurations). Google recommends controlling this access with network tags rather than broad subnet-based firewall rules. If workers cannot communicate, verify that an ingress firewall rule allows TCP 12345 and 12346 for the Dataflow worker network tag. Checking only for a custom tag (A) does not identify the connectivity issue, subnet-based rules (C) are not the recommended practice, and external IPs (D) are unrelated to intra-worker communication.
Question 132
You are using BigQuery with a multi-region 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: BigQuery table snapshots are stored in the same region as the source table and do not protect against a regional failure. BigQuery multi-region locations also do not provide cross-region disaster recovery. To achieve an RPO of less than 24 hours at minimal cost, schedule a daily export to a Cloud Storage dual-region or multi-region bucket in a geographically distinct location. This provides off-site backup that can be restored into BigQuery after a regional outage. Copying datasets or dual-writing to another region incurs higher ongoing costs.
Question 133
You are designing a data mesh on Google Cloud with multiple distinct data engineering teams building data products. The typical data curation design pattern consists of landing files in Cloud Storage, transforming raw data in Cloud Storage and BigQuery datasets, and storing the final curated data product in BigQuery datasets. You need to configure Dataplex to ensure that each team can access only the assets needed to build their data products. You also need to ensure that teams can easily share the curated data product. What should you do?
A. 1. Create a single Dataplex virtual lake and create a single zone to contain landing, raw, and curated data. 2. Provide each data engineering team access to the virtual lake.
B. 1. Create a single Dataplex virtual lake and create a single zone to contain landing, raw, and curated data. 2. Build separate assets for each data product within the zone. 3. Assign permissions to the data engineering teams at the zone level.
C. 1. Create a Dataplex virtual lake for each data product, and create a single zone to contain landing, raw, and curated data. 2. Provide the data engineering teams with full access to the virtual lake assigned to their data product.
D. 1. Create a Dataplex virtual lake for each data product, and create multiple zones for landing, raw, and curated data. 2. Provide the data engineering teams with full access to the virtual lake assigned to their data product.
Show Answer
Correct Answer: D
Explanation: Dataplex best practices for a domain-oriented data mesh are to model each data domain/data product as its own lake and organize data lifecycle stages (landing/raw/curated) into separate zones. This provides isolation and governance for each team's assets while allowing curated data products to be shared appropriately. Options A and B incorrectly place all stages into a single zone, and C omits the recommended separation into multiple zones.
Question 134
A shipping company has live package-tracking data that is sent to an Apache Kafka stream in real time. This is then loaded into BigQuery. Analysts in your company want to query the tracking data in BigQuery to analyze geospatial trends in the lifecycle of a package. The table was originally created with ingest-date partitioning. Over time, the query processing time has increased. You need to copy all the data to a new clustered table. What should you do?
A. Re-create the table using data partitioning on the package delivery date.
B. Implement clustering in BigQuery on the package-tracking ID column.
C. Implement clustering in BigQuery on the ingest date column.
D. Tier older data onto Cloud Storage files and create a BigQuery table using Cloud Storage as an external data source.
Show Answer
Correct Answer: B
Explanation: Clustering improves query performance by physically organizing related rows within partitions based on the clustered column. Since analysts analyze the lifecycle of individual packages, clustering on the package-tracking ID co-locates records for the same package and speeds those queries. Clustering on the ingest date provides little benefit because the table is already partitioned by ingest date, changing partitioning to delivery date does not match the stated need to create a clustered table, and moving older data to external tables would generally reduce query performance rather than improve it.
Question 135
A live TV show asks viewers to cast votes using their mobile phones. The event generates a large volume of data during a 3-minute period. You are in charge of the "Voting infrastructure" and must ensure that the platform can handle the load and that all votes are processed. You must display partial results while voting is open. After voting closes, you need to count the votes exactly once while optimizing cost. What should you do?
A. Create a Memorystore instance with a high availability (HA) configuration.
B. Create a Cloud SQL for PostgreSQL database with high availability (HA) configuration and multiple read replicas.
C. Write votes to a Pub/Sub topic and have Cloud Functions subscribe to it and write votes to BigQuery.
D. Write votes to a Pub/Sub topic and load into both Bigtable and BigQuery via a Dataflow pipeline. Query Bigtable for real-time results and BigQuery for later analysis. Shut down the Bigtable instance when voting concludes.
Show Answer
Correct Answer: D
Explanation: Pub/Sub provides durable, scalable ingestion for the vote spike. Dataflow can process the stream and write to both Bigtable and BigQuery. Bigtable is appropriate for low-latency, high-throughput serving of partial results while voting is open. BigQuery is suitable for final aggregation and analysis after voting closes. Using Dataflow with Pub/Sub supports reliable processing, and shutting down the temporary Bigtable instance after the event helps optimize costs.
Question 136
You are collecting IoT sensor data from millions of devices across the world and storing the data in BigQuery. Your access pattern is based on recent data, filtered by location_id and device_version with the following query:
You want to optimize your queries for cost and performance. How should you structure your data?
A. Partition table data by create_date, location_id, and device_version.
B. Partition table data by create_date, cluster table data by location_id, and device_version.
C. Cluster table data by create_date, location_id, and device_version.
D. Cluster table data by create_date, partition by location_id, and device_version.
Show Answer
Correct Answer: B
Explanation: BigQuery supports partitioning by a single partitioning column (commonly a date/timestamp for time-based access) and clustering by up to several columns. Since queries primarily access recent data and filter by location_id and device_version, partitioning on create_date enables partition pruning while clustering on location_id and device_version improves data locality and reduces scanned data within the relevant partitions. Options A and D are invalid because they imply partitioning by multiple fields, and C misses the benefits of partition pruning.
Question 137
You need ads data to serve AI models and historical data for analytics. Longtail and outlier data points need to be identified. You want to cleanse the data in near-real time before running it through AI models. What should you do?
A. Use Cloud Storage as a data warehouse, shell scripts for processing, and BigQuery to create views for desired datasets.
B. Use Dataflow to identify longtail and outlier data points programmatically, with BigQuery as a sink.
C. Use BigQuery to ingest, prepare, and then analyze the data, and then run queries to create views.
D. Use Cloud Composer to identify longtail and outlier data points, and then output a usable dataset to BigQuery.
Show Answer
Correct Answer: B
Explanation: Dataflow is the appropriate service for near-real-time stream processing and data cleansing. It can programmatically detect long-tail and outlier data points as data arrives and write the cleansed results into BigQuery, which is well suited for historical analytics and serving downstream AI workloads. Cloud Storage is not a data warehouse, BigQuery alone is not intended for this kind of streaming transformation pipeline, and Cloud Composer orchestrates workflows rather than performing the data processing itself.
Question 138
You have a data processing application that runs on Google Kubernetes Engine (GKE). Containers need to be launched with their latest available configurations from a container registry. Your GKE nodes need to have GPUs, local SSDs, and 8 Gbps bandwidth. You want to efficiently provision the data processing infrastructure and manage the deployment process. What should you do?
A. Use Compute Engine startup scripts to pull container images, and use gcloud commands to provision the infrastructure.
B. Use Cloud Build to schedule a job using Terraform build to provision the infrastructure and launch with the most current container images.
C. Use GKE to autoscale containers, and use gcloud commands to provision the infrastructure.
D. Use Dataflow to provision the data pipeline, and use Cloud Scheduler to run the job.
Show Answer
Correct Answer: B
Explanation: Option B best matches the requirements for automated infrastructure provisioning and deployment. Terraform provides infrastructure as code to create a GKE cluster with the required node characteristics (such as GPUs, local SSDs, and machine/network configuration), while Cloud Build can automate provisioning and deployment so workloads use the latest container images from the registry. The other options rely on manual gcloud workflows, misuse Compute Engine startup scripts for GKE deployment, or use Dataflow, which is not the service for provisioning GKE infrastructure.
Question 139
You want to create a machine learning model using BigQuery ML and create an endpoint for hosting the model using Vertex AI. This will enable the processing of continuous streaming data in near-real time from multiple vendors. The data may contain invalid values. What should you do?
A. Create a new BigQuery dataset and use streaming inserts to land the data from multiple vendors. Configure your BigQuery ML model to use the "ingestion" dataset as the framing data.
B. Use BigQuery streaming inserts to land the data from multiple vendors where your BigQuery dataset ML model is deployed.
C. Create a Pub/Sub topic and send all vendor data to it. Connect a Cloud Function to the topic to process the data and store it in BigQuery.
D. Create a Pub/Sub topic and send all vendor data to it. Use Dataflow to process and sanitize the Pub/Sub data and stream it to BigQuery.
Show Answer
Correct Answer: D
Explanation: For continuous streaming data from multiple vendors, Pub/Sub is the appropriate ingestion layer. Because the data may contain invalid values, Dataflow is the recommended streaming processing service to validate, clean, transform, and filter records before streaming them into BigQuery. BigQuery ML can then train models on the curated data, and models can be deployed to Vertex AI endpoints as needed. The other options either stream directly into BigQuery without data sanitization or use Cloud Functions, which are less suitable for scalable streaming ETL.
$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.