Professional Data Engineer Free Practice Questions — Page 14
Question 113
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: Analysts query package lifecycles and geospatial trends, which commonly filter or group by package-tracking ID. Clustering the new BigQuery table on the package-tracking ID physically co-locates rows for the same package, significantly reducing data scanned and improving query performance as the table grows. Re-partitioning by delivery date does not address lifecycle-based access patterns, clustering on ingest date adds little benefit beyond existing partitioning, and tiering to external storage would worsen query performance.
Question 114
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: The system must absorb a very high, short-lived write spike, show low-latency partial results, and guarantee exactly-once counting while controlling cost. Pub/Sub provides scalable ingestion. A Dataflow pipeline provides exactly-once processing and fan-out. Bigtable supports very low-latency reads/writes for real-time partial results during the vote, while BigQuery is ideal for accurate final counts and post-event analysis. Shutting down Bigtable after voting minimizes cost, making this architecture the best fit.
Question 115
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: The query pattern focuses on recent data and applies filters on location_id and device_version. Partitioning by create_date allows BigQuery to prune old partitions and scan only recent data, which significantly reduces cost. Clustering within each date partition by location_id and device_version physically groups related rows, improving filter efficiency and performance. Other options are invalid or suboptimal because BigQuery supports only one partitioning field, and clustering alone cannot provide the same level of partition pruning as date-based partitioning.
Question 116
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: The requirement is near-real-time data cleansing and identification of longtail and outlier data points before serving AI models, while also retaining historical data for analytics. Dataflow is designed for streaming and batch processing with low latency and allows custom programmatic logic (for example, Apache Beam) to detect and cleanse outliers as data arrives. Writing the processed output to BigQuery provides a scalable sink for historical analysis and downstream AI consumption. Other options focus on batch analytics or orchestration and do not meet the near-real-time processing requirement.
Question 117
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: You need an automated, efficient way to both provision specialized GKE infrastructure (GPUs, local SSDs, high-bandwidth nodes) and deploy containers using the latest images. Using Infrastructure as Code with Terraform is the best practice for provisioning GKE node pools with GPUs, local SSDs, and network settings. Cloud Build provides a managed CI/CD mechanism to run Terraform and deploy workloads, ensuring the latest container images from the registry are used. The other options rely on manual gcloud commands, startup scripts, or services (Dataflow) that do not address GKE infrastructure provisioning and deployment automation.
Question 118
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: To handle continuous near-real-time streaming data from multiple vendors with potential invalid values, you need a scalable streaming ingestion and data sanitization layer before training and serving a BigQuery ML model via Vertex AI. Pub/Sub is the appropriate ingestion service for streaming data. Dataflow provides managed, scalable stream processing with transformations (such as filtering, validation, and cleansing) using ParDo before writing clean data to BigQuery. The other options either lack proper data validation/sanitization or misuse BigQuery streaming directly without a robust processing layer.
Question 119
A TensorFlow machine learning model on Compute Engine virtual machines (n2-standard-32) takes two days to complete training. The model has custom TensorFlow operations that must run partially on a CPU. You want to reduce the training time in a cost-effective manner. What should you do?
A. Change the VM type to n2-highmem-32.
B. Change the VM type to e2-standard-32.
C. Train the model using a VM with a GPU hardware accelerator.
D. Train the model using a VM with a TPU hardware accelerator.
Show Answer
Correct Answer: C
Explanation: Using a GPU hardware accelerator is the most cost-effective way to significantly reduce TensorFlow training time. GPUs are well supported by TensorFlow, can accelerate most training workloads, and still allow custom operations to run on the CPU. TPUs are less suitable because models with custom TensorFlow operations that must run partially on CPUs are not a good fit for TPUs, and changing VM types alone would not yield a comparable speedup.
Question 120
You are a head of BI at a large enterprise company with multiple business units that each have different priorities and budgets. You use on-demand pricing for
BigQuery with a quota of 2K concurrent on-demand slots per project. Users at your organization sometimes don't get slots to execute their query and you need to correct this. You'd like to avoid introducing new projects to your account.
What should you do?
A. Convert your batch BQ queries into interactive BQ queries.
B. Create an additional project to overcome the 2K on-demand per-project quota.
C. Switch to flat-rate pricing and establish a hierarchical priority model for your projects.
D. Increase the amount of concurrent slots per project at the Quotas page at the Cloud Console.
Show Answer
Correct Answer: C
Explanation: On-demand BigQuery has a hard per-project concurrent slot quota (2,000) that cannot be meaningfully increased and causes contention when many users run queries. You want to avoid creating new projects, so splitting load across projects is not an option. Switching to flat-rate (slot-based) pricing lets you purchase dedicated slots and use reservations with hierarchical priorities to allocate capacity across business units, ensuring critical workloads get slots and eliminating on-demand slot starvation.
Question 121
You have enabled the free integration between Firebase Analytics and Google BigQuery. Firebase now automatically creates a new table daily in BigQuery in the format app_events_YYYYMMDD. You want to query all of the tables for the past 30 days in legacy SQL. What should you do?
A. Use the TABLE_DATE_RANGE function
B. Use the WHERE_PARTITIONTIME pseudo column
C. Use WHERE date BETWEEN YYYY-MM-DD AND YYYY-MM-DD
D. Use SELECT IF.(date >= YYYY-MM-DD AND date <= YYYY-MM-DD
Show Answer
Correct Answer: A
Explanation: In legacy SQL, Firebase Analytics exports create separate daily tables with date suffixes. To query multiple date-suffixed tables over a range (such as the last 30 days), BigQuery legacy SQL requires a table wildcard function. TABLE_DATE_RANGE is specifically designed to scan multiple daily tables matching a prefix and date range. The other options apply to partitioned tables or standard SQL, not legacy SQL date-sharded tables.
Question 122
Each analytics team in your organization is running BigQuery jobs in their own projects. You want to enable each team to monitor slot usage within their projects.
What should you do?
A. Create a Cloud Monitoring dashboard based on the BigQuery metric query/scanned_bytes
B. Create a Cloud Monitoring dashboard based on the BigQuery metric slots/allocated_for_project
C. Create a log export for each project, capture the BigQuery job execution logs, create a custom metric based on the totalSlotMs, and create a Cloud Monitoring dashboard based on the custom metric
D. Create an aggregated log export at the organization level, capture the BigQuery job execution logs, create a custom metric based on the totalSlotMs, and create a Cloud Monitoring dashboard based on the custom metric
Show Answer
Correct Answer: B
Explanation: BigQuery exposes native Cloud Monitoring metrics for slot usage at the project level. The metric `slots/allocated_for_project` directly reports slots used by each project and is designed for monitoring slot consumption without additional setup. Creating a Cloud Monitoring dashboard with this metric allows each team to monitor their own slot usage in their respective projects. The other options rely on log exports and custom metrics, which are more complex and unnecessary when a built-in metric already exists.
$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.