GCP Professional Data Engineer Practice Test

The BigQuery Data Editor role (roles/bigquery.dataEditor) is scoped to datasets and grants permissions such as bigquery.tables.create and bigquery.tables.updateData, which allow a principal to create tables and write rows. Although it also includes bigquery.tables.getData (read access), it does not include permissions like bigquery.tables.update (alter table schemas) or bigquery.datasets.update (change access controls). Thus it provides the minimum required capabilities without granting schema-modification rights. BigQuery Data Owner and BigQuery Admin include schema and access-control permissions, violating least privilege. BigQuery Data Viewer is read-only, and BigQuery Job User controls job execution but provides no direct dataset write permissions. Therefore granting roles/bigquery.dataEditor on the dataset is the correct choice.

Memorystore for Redis Cluster is a regional service that automatically shards data and places each shard's primary and replica in separate zones inside the region. If an entire zone fails, clients reconnect to the surviving replicas without manual reconfiguration, so the Dataflow job continues with minimal disruption. Running your own Redis on GKE or managing dual basic instances would require you to build and operate the sharding, replication, and failover logic yourself. A single-zone Standard Tier instance, even with a Cloud SQL read replica, would not protect against a zonal outage and mixes unrelated services. Therefore, a regional Memorystore for Redis Cluster instance with its built-in multi-zone placement and automatic failover delivers the desired high availability with the least operational effort.

AlloyDB for PostgreSQL is a fully managed, PostgreSQL-compatible service that separates compute and log-structured storage to deliver high write throughput and near-linear read scaling through read pools. It offers an integrated columnar engine that accelerates analytical queries by orders of magnitude on the same operational data, and Google handles replication, patching, and zero-downtime maintenance. Cloud SQL provides PostgreSQL compatibility but lacks the columnar accelerator and can suffer from replication lag under heavy write loads. Spanner's PostgreSQL interface is not identical to upstream PostgreSQL and would require more re-engineering, while BigQuery excels at analytics but is not suitable for high-write OLTP workloads and would force a dual-system architecture.

Publishing events to a Pub/Sub topic provides a serverless ingestion layer that automatically scales to high throughput. A streaming Dataflow job can subscribe to the topic, parse the JSON, and write valid rows to BigQuery with the BigQuery Storage Write API, making data queryable within seconds. The pipeline can send parsing failures to a Pub/Sub dead-letter topic (or a side output) so bad records are isolated without stopping the job. This combination is fully managed and autoscaling, requiring no cluster maintenance. Cloud Functions would face concurrency limits and per-invocation overhead at 150 000 msg/s, Dataproc introduces cluster administration work and is not serverless, and batch file loads from Cloud Storage cannot meet the sub-minute latency target.

Dataplex lakes are intended to map to broad business areas or administrative ownership boundaries. Creating a dedicated Enterprise lake allows the central governance team to own and manage a curated zone that contains the shared reference datasets as individual assets (BigQuery datasets or Cloud Storage buckets). IAM policies can then be granted at lake, zone, or asset level so that the domain teams in the Sales, Marketing, and Finance lakes can read the reference data without copying it. Replicating the reference data in every domain lake or maintaining it outside Dataplex would defeat the purpose of centralized metadata, governance, and cost control, and Dataplex today does not support asset-level "sharing" that automatically spans multiple lakes-each asset belongs to exactly one zone within one lake.

A one-year slot commitment provides the lowest unit cost when the same capacity is required every workday. Create a reservation that holds the 1,000 committed slots and assign only the prod-analytics project to it. Then create a second reservation that owns 0 slots and assign the other projects to that reservation. BigQuery automatically lets the 0-slot reservation borrow any idle slots from the 1,000-slot reservation, but when the dashboard starts, the full 1,000 slots are immediately reclaimed for prod-analytics, ensuring its performance target. Flex slots are more expensive per hour and would require daily provisioning, while upgrading editions or lowering query priority would not reserve capacity and therefore cannot guarantee the required latency.

Cloud Bigtable is engineered for very high write throughput and single-row reads in single-digit milliseconds, making it ideal for time-series or sensor data that is appended continuously and retrieved by a well-designed row key such as device-id#timestamp. BigQuery is optimized for analytical scans, not low-latency point reads. Cloud Spanner provides strong relational semantics and distributed transactions, which add cost and complexity unnecessary for this append-only workload. Pub/Sub Lite is a messaging service rather than a serving store and is not intended for low-latency random access queries.

The Editor primitive role grants thousands of permissions across nearly every Google Cloud service, including the ability to create, modify, and delete resources such as Compute Engine instances and Cloud Storage buckets. To comply with least-privilege guidelines, you should remove this broad role and replace it with a predefined BigQuery-specific role that contains only the permissions required for the scientists' tasks. Granting roles/bigquery.dataEditor at the dataset level lets them create and update tables without exposing the project to unnecessary risk. The other options either continue to over-provision access, add unnecessary impersonation complexity, or rely solely on monitoring rather than removing excessive permissions.

Cloud Spanner is a fully managed, horizontally scalable relational store that offers strong consistency, global replication, and ACID transactions with single-digit-millisecond latency-ideal for the high-volume transactional (OLTP) order stream. Spanner change streams can continuously capture mutations, and a Dataflow template can stream them into BigQuery, Google Cloud's serverless data warehouse optimized for interactive analytical (OLAP) SQL. This architecture cleanly separates transactional and analytical paths while remaining fully managed. Bigtable lacks multi-row ACID guarantees and global consistency, Cloud SQL cannot scale to millions of updates per second, and BigQuery alone is not designed for sub-millisecond transactional writes.

Cloud Data Fusion instances that use the Private IP option create their control plane in a Google-managed tenant project. Runtime resources such as Dataproc clusters run in that tenant project's default network. To let these private workers reach services that live in a customer VPC (for example, Cloud Bigtable with only internal IPs), Google recommends creating a VPC-network-peering connection between the tenant project's network and the customer project's VPC. Peering keeps the traffic on Google's private backbone, requires no public IPs, and honours existing firewall rules from both sides; no ingress holes have to be opened because Dataproc workers initiate the outbound connections.
Other options fail to satisfy one or more constraints:

• Using Cloud NAT would still expose Dataproc workers to the public internet.
• Private Service Connect endpoints are not supported for Bigtable yet and would still require deploying PSC back-ends in the customer VPC.
• Shared VPC is not possible because the tenant project is Google-managed and cannot be attached as a service project under your host project.

Define the preprocessing inside the TRANSFORM clause of the CREATE MODEL statement. By calling ML.IMPUTER on usage_minutes and tenure_days you ensure mean imputation for missing numeric values, and applying ML.ONE_HOT_ENCODER to plan_type converts the high-cardinality string column into sparse indicator features, optionally limited by top_k. BigQuery ML stores these transformations with the model and automatically reapplies them during ML.PREDICT, so dashboard queries can run predictions on raw records without repeating the logic. External pipelines, materialized views, or delegating encoding to dashboards would require manual coordination and risk feature skew, whereas BigQuery ML does not offer an auto_preprocess flag in CREATE MODEL.

Submitting the ETL as a batch-priority query queues it until BigQuery detects idle slots, while interactive dashboard queries keep immediate priority. This prevents the ETL from delaying analyst queries, costs no extra money, and requires no SQL changes. Purchasing slots or reservations adds cost, and rescheduling the ETL might still overlap with peak usage.

The linked dataset is already a first-class BigQuery dataset in the consumer project, backed by tables that physically reside in the publisher's project. Looker Studio natively connects to BigQuery through the BigQuery API, so it can query the linked dataset directly without copying or exporting data. Analysts simply need permission to read the dataset (roles/bigquery.dataViewer) and to run query jobs that bill the consumer project (roles/bigquery.jobUser). No data transfer, export, or replication jobs are required, eliminating extra storage cost and maintenance. Copying tables, exporting to Cloud Storage, or materializing data in Cloud SQL introduce unnecessary cost and operational complexity and therefore are not recommended for consuming Analytics Hub exchanges.

Cloud Storage is designed for data-lake use cases where raw, unstructured, or semi-structured files must be kept for long periods at low cost. Bucket lifecycle policies can automatically transition objects from Standard to Nearline, Coldline, or Archive classes to meet five-year retention at minimal expense. Dataflow can natively read from and write to Cloud Storage, allowing downstream cleansing and loading into BigQuery without relocating the data. BigQuery is an analytics warehouse optimized for SQL query performance; loading every raw record would incur storage and streaming-insert costs and prevents tiered lifecycle storage. Cloud Bigtable provides low-latency key/value serving but lacks object lifecycle tiering and is cost-inefficient for write-once archival data. Cloud SQL is a managed relational OLTP service with size limits, higher per-GB cost, and no automated tiering, making it unsuitable for petabyte-scale, append-only raw data.

Create assertion actions in Dataform. An assertion is authored in a .sqlx file whose config block contains type: "assertion". At runtime Dataform executes the query and expects it to return an empty result set; any rows indicate a data-quality violation, causing the assertion to fail and the entire Dataform run to stop with an error. Post-operation scripts, BigQuery schema constraints, or incremental table filters do not provide the same automatic run-blocking behavior for complex business-rule checks such as duplicate detection or referential-integrity validation.

Under BigQuery's on-demand model you pay for the number of bytes each query reads. Converting the log table to a date-partitioned table (for example, partitioned by ingestion or event date) and having analysts filter on the partitioning column limits scanning to just the partitions that hold the last three days of data. Because the amount of data read drops from 180 TB to roughly 6 TB, the on-demand cost of every query falls proportionally. Purchasing slot reservations, exporting to external tables, or applying gzip compression would not cut on-demand query bytes in this scenario: flat-rate slots change the pricing model, external tables are still billed per bytes processed, and storage compression does not change how many logical bytes a query scans.

Using a Cloud Monitoring metrics scope in a dedicated operations project aggregates metrics from multiple service projects automatically, letting engineers build a single dashboard. Dataflow already publishes the job metric system_lag, so an alerting policy that fires when the value is more than 300 seconds can notify PagerDuty. Aggregated Log Router sinks from each project can route Dataflow worker logs into a centralized log bucket where responders are granted the Logs Viewer role, giving them drill-down visibility without exposing wider project access. The other approaches either rely on services that do not natively collect Dataflow lag (Cloud Trace), add unnecessary components and maintenance (custom Pub/Sub → BigQuery or self-hosted Prometheus/Elasticsearch), or still require broader IAM grants.

Because the business definition of a session is "activity separated by a 30-minute idle gap," the pipeline should use session windows with a 30-minute gap duration. To deliver an early, partial aggregation within one minute, add an early firing trigger that emits after one minute of processing time. The default AfterWatermark trigger ensures an on-time pane when the watermark passes the window end, and an allowed lateness of 10 minutes lets Dataflow reopen the window to merge any late events that still arrive within that tolerance. Using ACCUMULATING mode guarantees that every pane adds to the previous result instead of replacing it.

Other options fail to meet one or more requirements:

• Fixed (tumbling) windows cannot adapt to user-driven session boundaries.
• Sliding windows would double-count data and omit the required gap logic.
• A global window with only processing-time triggers would have no concept of a 30-minute session boundary and could not close windows correctly for late data.

Publishing the dataset through Analytics Hub meets every requirement while respecting the security policy. A private data exchange with a listing that references the existing dataset lets the data stay in the publisher project. When each partner subscribes, BigQuery creates a read-only linked dataset in the partner's project that appears in their BigQuery Explorer, and all query charges are billed to the partner's project. Access can be revoked instantly by disabling the listing or removing a subscriber. The other approaches either violate the policy against external IAM roles, require ongoing data movement, or risk billing the publisher.

Cloud Composer is Google Cloud's managed Apache Airflow service. Airflow expresses complex, multi-step workflows as DAGs that support conditional branches, retries, and dependencies. Composer lets you install custom Python packages and Airflow plugins, so you can invoke Cloud Run jobs, BigQuery operators, Storage Transfer Service hooks, SSH operators for on-prem hosts, and custom calls to Vertex AI. It also provides the standard Airflow web UI for visual monitoring, while Google manages the underlying infrastructure.

Workflows can orchestrate Google Cloud APIs but offers limited first-class support for SSH connections, custom Python libraries, or direct reuse of existing Airflow DAGs; authoring complex branching pipelines is possible but less ergonomic. Cloud Scheduler with Pub/Sub and Cloud Functions would require substantial custom code to replicate DAG-level dependency management and monitoring. Dataflow Flex Templates address data processing, not cross-service orchestration. Therefore, Cloud Composer best meets all stated requirements.