Free Google Professional Data Engineer Sample Questions

Correct answer: B — Use Bigtable Mutations to group the modifications into a single row mutation request.

• A. Performing read-modify-write operations on the client side (a manual read followed by a write) introduces race conditions and is not atomic. While Bigtable offers a specific ReadModifyWriteRow API for atomic appends and increments, standard multi-column updates should be bundled in a single mutation request to guarantee atomicity without network-trip overhead or consistency risks.
• B. Cloud Bigtable guarantees that all modifications within a single 'MutateRow' request (which can contain multiple column updates, deletions, or 'SetCell' operations) are applied atomically. This ensures that the update for the single row either succeeds completely or fails completely, meeting the requirement for row-level atomicity.
• C. Cloud Bigtable does not support multi-row transactions, and there is no configuration setting to enable them. Atomicity in Bigtable is strictly scoped to the single row level.
• D. Orchestrating updates sequentially using Cloud Functions does not provide atomicity. If a failure occurs midway through the sequence, the data remains in an inconsistent, partially updated state. True atomicity is handled at the storage layer via single-row mutations.

Correct answer: B — Use BigQuery Data Transfer Service for Google Ads.

• A. Developing a Cloud Function requires writing and maintaining custom code, handling OAuth/API keys, retries, and setting up a scheduling mechanism like Cloud Scheduler. This significantly increases engineering effort and operational overhead compared to a managed service.
• B. BigQuery Data Transfer Service (DTS) provides a native, fully managed connector for Google Ads. It automates data ingestion with built-in authentication, scheduling, and incremental loads, requiring zero custom code or manual API key management. This is the most cost-effective and lowest-effort solution for this requirement.
• C. Exporting Google Ads data to CSV and using a Google Drive-backed federated table involves manual steps or additional custom automation. Federated queries have performance limitations and quota restrictions, and this method does not provide a reliable, automated daily refresh without significant extra effort.
• D. Using a Dataflow template is a more complex and heavyweight approach compared to BigQuery DTS. It requires managing job executions, handling credentials, and potentially customizing templates, which leads to higher engineering effort and operational costs for a standard data migration task.

Correct answers: A, D — Configure the Oracle source to use LogMiner or XStream to capture changes.; Create a connection profile for the Oracle source and the Cloud Storage destination.

• A. Correct. To perform Change Data Capture (CDC) from an Oracle database, Datastream requires either Oracle LogMiner or XStream to be configured on the source database to allow the service to read the redo logs.
• B. Incorrect. The Cloud SQL Proxy is used to provide secure access to Cloud SQL instances for client applications, but it is not a requirement or a step for configuring Datastream.
• C. Incorrect. While a Reverse SSH Tunnel is one of the connectivity options for Datastream to reach a source database, it is not a mandatory requirement for all configurations (other methods include VPC peering or IP allowlisting).
• D. Correct. Datastream does not support Cloud SQL as a direct destination. The standard migration path for Oracle to Cloud SQL via Datastream requires streaming the changes into a Cloud Storage bucket (the destination profile) before they are applied to Cloud SQL using a tool like Dataflow.
• E. Incorrect. Datastream natively supports Cloud Storage, BigQuery, Spanner, and AlloyDB as destinations. It does not have a direct destination connection profile for Cloud SQL for PostgreSQL.

Correct answer: B — Load the CSVs into BigQuery as a single column of type STRING and parse it with SQL.

• A. Defining a strict schema causes frequent pipeline failures and requires manual intervention whenever vendors change columns. This approach is the opposite of resilient and prevents the immediate analysis of raw data because non-conforming files are blocked entirely.
• B. This is a common 'landing table' pattern. Loading CSVs as a single STRING column preserves the raw row content regardless of additions, removals, or reordering of columns. It allows for immediate ingestion and querying using BigQuery's SQL functions (like SPLIT, REGEXP_EXTRACT, or JSON functions if the string is structured) to parse the data dynamically, providing maximum flexibility for schema drift.
• C. The 'ALLOW_FIELD_ADDITION' flag only permits adding new columns at the end of the schema. It does not robustly handle column removals, reordering, or data type changes. Because CSV loading in BigQuery is often position-dependent, this flag is insufficient for handling frequent and unpredictable schema drift.
• D. Converting CSVs to Avro adds operational complexity and an extra processing step via Cloud Functions. While Avro supports schema evolution, the conversion logic itself would likely break or require updates whenever the source CSV schema changes. This hinders the goal of immediate analysis compared to direct loading.

Correct answer: B — Cloud Spanner

• A. Cloud Bigtable is a high-performance NoSQL wide-column store designed for high-throughput, low-latency workloads. However, it does not support standard SQL and only offers single-row consistency rather than the global transactional consistency required for this use case.
• B. Cloud Spanner is a globally distributed, horizontally scalable relational database that provides strong external consistency. It supports a standard SQL dialect, making it the only service that meets the combined requirements of massive scale, global consistency, and query logic portability for analysts.
• C. Cloud SQL is a managed relational database service (MySQL, PostgreSQL, SQL Server) that supports standard SQL. While familiar to analysts, it is primarily designed for vertical scaling and lacks the built-in global horizontal write scalability and global transactional consistency of Cloud Spanner.
• D. Firestore is a NoSQL document database optimized for mobile and web development. It does not support standard SQL and its query model is distinct from relational databases, which would fail to meet the requirement for query logic portability for SQL-based analysts.

Correct answers: A, B — Configure the 'Resource Location Restriction' (gcp.resourceLocations) organization policy constraint.; Set the policy value to 'allow' only 'in:europe-west3'.

• A. The 'Resource Location Restriction' (constraints/gcp.resourceLocations) organization policy constraint is the standard Google Cloud mechanism for enforcing geographic compliance. Applying it at the organization level ensures the constraint is inherited by all projects, effectively preventing any resource creation outside of the specified locations.
• B. To meet strict sovereignty requirements for a specific region, you must use an 'allow' list approach. Setting the policy value to 'allow' for 'in:europe-west3' ensures that only resources within the Frankfurt region can be provisioned, while implicitly denying all other regions globally.
• C. The 'Domain Restricted Sharing' organization policy is used to limit resource sharing to specific Cloud Identity or Google Workspace domains. It does not control the physical location of resources or data residency.
• D. IAM roles like 'Owner' or 'Editor' define what actions a user can perform, but they do not define where those actions can take place. Changing these roles does not enforce geographic restrictions.
• E. Using a 'deny' list for a specific region like 'us-central1' only blocks that single region. Project admins would still be able to create resources in any other available region (e.g., asia-east1, us-east4), which violates the requirement to restrict data solely to europe-west3.

Correct answers: A, B, C — Create a KeyRing and a CryptoKey in Cloud KMS in the same location as the BigQuery dataset.; Grant the 'BigQuery Service Agent' the 'Cloud KMS CryptoKey Encrypter/Decrypter' role on the CryptoKey.; Configure the default encryption key for the BigQuery dataset to use the Cloud KMS key resource ID.

• A. Correct. BigQuery requires that the Cloud KMS key be located in the same region or multi-region as the dataset to ensure low latency and comply with data residency requirements.
• B. Correct. The BigQuery Service Agent (a Google-managed service account specific to your project) must have the 'Cloud KMS CryptoKey Encrypter/Decrypter' role to programmatically use the key for encrypting and decrypting the data stored in BigQuery.
• C. Correct. By setting the default encryption key at the dataset level, you ensure that all new tables created within that dataset are automatically encrypted using the specified CMEK.
• D. Incorrect. The Storage Admin role relates to Cloud Storage (GCS) permissions and is not required for configuring Cloud KMS encryption within BigQuery.
• E. Incorrect. Cloud KMS is a hosted service where the private key material never leaves the provider's infrastructure. You cannot download the key; instead, you reference it via its resource ID.
• F. Incorrect. Granting the 'Owner' role violates the principle of least privilege. The BigQuery Service Agent only needs specific permissions on the CryptoKey itself, not project-wide ownership.

Correct answer: B — Cloud Workflows

• A. Cloud Composer is a managed Apache Airflow service suitable for complex workflows and long-running DAGs. However, it requires a persistent environment (GKE cluster and SQL database), which results in significant idle costs. For a sporadic task running only a few times a day, the operational overhead and cost would be disproportionately high.
• B. Cloud Workflows is a serverless, pay-per-use orchestration service designed for lightweight tasks and API-based integrations. It has no idle compute costs and scales to zero when not in use, making it the most cost-effective and low-overhead solution for sporadic tasks that chain Cloud Functions and Firestore updates.
• C. Dataflow is a fully managed service for unified stream and batch data processing using Apache Beam. It involves provisioning VM workers, which is overkill for a simple orchestration task involving a single function and a document update. It would result in higher latency and unnecessary infrastructure costs.
• D. Dataproc is a managed service for running Spark and Hadoop clusters. It is intended for large-scale big data processing. Even with ephemeral clusters, the time and cost associated with provisioning nodes for a lightweight, sporadic task are much higher than serverless alternatives.

Correct answers: A, B — Run unit tests on the pipeline code using the DirectRunner.; Run integration tests using the DataflowRunner with a sampling of data.

• A. Correct. Running unit tests with the DirectRunner is a standard practice for local verification. It allows for fast, lightweight testing of individual PTransforms and business logic without provisioning cloud resources, making it ideal for CI builds to catch regressions early.
• B. Correct. Integration testing using the DataflowRunner ensures the pipeline operates correctly within the managed cloud environment. Using a sampling of data and a non-production environment allows you to validate service interactions and runner-specific behavior while keeping costs and execution time low.
• C. Incorrect. Running a build-stage test against a production dataset is unsafe and violates isolation principles. It can lead to data corruption, excessive costs, and slow CI/CD feedback loops.
• D. Incorrect. Monitoring production after deployment is an operational practice, not a build-time testing strategy. Logic correctness should be verified in a staging or test environment before code is promoted to production.
• E. Incorrect. Cloud Profiler is designed to analyze performance bottlenecks (such as CPU or memory usage) in running applications. It is not used to validate the functional or logical correctness of the pipeline's data processing.

Correct answer: B — Use Cloud Dataflow with the streaming engine. Rely on Dataflow's built-in exactly-once semantics when consuming from Pub/Sub and writing to BigQuery.

• A. Incorrect. A custom Python script on Compute Engine does not inherently provide exactly-once guarantees. Both Pub/Sub (at-least-once delivery) and BigQuery streaming inserts do not automatically coordinate to ensure exactly-once processing. Implementing custom deduplication and transactional logic is complex, error-prone, and not recommended for strict financial requirements.
• B. Correct. Cloud Dataflow, especially with the Streaming Engine, provides built-in exactly-once processing semantics. When using the standard Pub/Sub source and BigQuery sink connectors, Dataflow manages message deduplication, checkpointing, and state, ensuring that each transaction is processed exactly once even in the event of retries or failures.
• C. Incorrect. While Cloud Dataproc with Spark Streaming and checkpointing can provide strong processing guarantees, it does not provide the same managed, turnkey exactly-once semantics for the Pub/Sub to BigQuery path as Dataflow. Achieving end-to-end exactly-once with Spark and BigQuery often requires additional manual deduplication logic.
• D. Incorrect. Cloud Functions triggered by Pub/Sub have at-least-once delivery semantics. If a function is retried due to a timeout or failure, it can result in duplicate writes to BigQuery. Cloud Functions lack the built-in state management and global deduplication infrastructure needed for exactly-once processing.

Correct answer: B — Use Pub/Sub with ordering keys enabled. Publish messages with Match_ID as the ordering key. In Dataflow, simply read from the subscription.

• A. Standard Pub/Sub topics do not guarantee message delivery order. Attempting to sort by timestamp in Dataflow cannot guarantee original generation order because of network delays, clock skew, and late-arriving messages. Additionally, sorting requires buffering or watermark delays, which increases latency and complicates the pipeline.
• B. Pub/Sub ordering keys, when enabled on a topic, ensure that messages with the same ordering key are delivered to subscribers in the order they were published. By using Match_ID as the ordering key, you guarantee that events for a specific match are processed in sequence. Dataflow's PubSubIO respects these ordering keys, allowing for scalable, parallel processing across different keys while maintaining strict order for each individual key.
• C. Pub/Sub Lite guarantees ordering within a single partition, but it does not provide the same managed per-key ordering functionality as standard Pub/Sub. Using Lite would require you to manually manage the mapping of Match_IDs to partitions to ensure related events stay together, making it more complex and less robust than the standard ordering key feature.
• D. A single-threaded script is not a scalable or fault-tolerant solution for high-throughput game events. It fails to utilize the distributed processing capabilities of Dataflow and creates a performance bottleneck and a single point of failure in the architecture.

Correct answer: D — Use distcp (distributed copy) from the Hadoop cluster to the GCS bucket.

• A. Incorrect. Running gsutil cp on a single edge node creates a significant bottleneck. It is limited by the single node's CPU, disk I/O, and network stack, failing to leverage the distributed nature of the Hadoop cluster or the full 10 Gbps bandwidth of the Dedicated Interconnect.
• B. Incorrect. The Storage Transfer Service for on-premises data is designed for POSIX-compliant filesystems and NFS. Since HDFS is not a POSIX filesystem, using STS would typically require mounting HDFS via an NFS gateway or staging data to local disks first, which adds significant overhead and complexity compared to native Hadoop tools.
• C. Incorrect. Cloud Functions is a serverless environment with strict execution time limits (max 9 minutes), memory constraints, and concurrency limits. It is entirely unsuitable for a bulk migration of 500 TB of data, which would require massive parallelization and long-running processes.
• D. Correct. DistCp (distributed copy) is the standard Hadoop utility for large-scale data inter-cluster and intra-cluster copying. When used with the Google Cloud Storage connector, it leverages the MapReduce engine of the Hadoop cluster to parallelize the transfer across all nodes. This approach maximizes the utilization of the 10 Gbps Dedicated Interconnect and is the recommended practice for HDFS-to-GCS migrations.

Correct answers: C, D — Provide the partner with a Google Cloud Storage Transfer Service SFTP endpoint (or trigger a Transfer job).; Configure a Pub/Sub notification on the GCS bucket to trigger a Cloud Function or Dataflow job upon file finalization.

• A. Incorrect. Running an FTP server on a Compute Engine instance requires managing virtual machines and OS-level services, which is Infrastructure-as-a-Service (IaaS) rather than serverless. It increases operational overhead and lacks native event-driven integration into the Google Cloud ecosystem.
• B. Incorrect. Cloud Composer is a managed Apache Airflow service. While it can orchestrate workflows, it relies on polling mechanisms to check for new data, which is not truly event-driven. Additionally, it involves more operational overhead and cost than a simple event-driven function for daily ingest tasks.
• C. Correct. Storage Transfer Service (STS) is a fully managed, serverless service that can be used to transfer data from external SFTP sources into Cloud Storage. Using STS automates the ingestion process and eliminates the need to manage custom transfer scripts or infrastructure.
• D. Correct. Configuring Pub/Sub notifications (Object Change Notifications or GCS Pub/Sub notifications) for object finalization events is the standard event-driven pattern in Google Cloud. This can trigger Cloud Functions or Dataflow to process the CSV and load it into BigQuery immediately after the file arrives in the bucket.
• E. Incorrect. Dataproc is a managed Spark and Hadoop service that typically involves cluster management. It is not designed to 'watch' a bucket in an event-driven, serverless manner for file ingestion, making it inefficient for this specific use case compared to Cloud Functions or Dataflow.

Correct answer: C — Autoclass

• A. Standard storage is optimized for frequent access and has no retrieval fees. However, because it has the highest storage cost, it is not cost-effective for data that remains untouched for a year. While it avoids retrieval fees, it fails the cost-optimization requirement for cold data.
• B. Nearline storage is intended for data accessed less than once a month. It has lower storage costs than Standard but incurs retrieval fees and has a 30-day minimum storage duration, which violates the requirement to avoid retrieval fees.
• C. Autoclass is the correct choice because it is specifically designed for unpredictable access patterns. It automatically moves objects to colder storage classes (Nearline, Coldline, Archive) as they age without access, and moves them back to Standard storage immediately upon access. Crucially, Autoclass eliminates retrieval fees and transition fees, and does not charge for minimum storage durations, making it the most cost-effective and management-free option for this scenario.
• D. Archive storage offers the lowest storage price but the highest retrieval fees and a 365-day minimum storage duration. It is meant for long-term backup and disaster recovery where data is rarely accessed, and would be very expensive when the data is eventually retrieved.

Correct answer: B — Dataplex Zones

• A. Incorrect. VPC Service Controls are used to create security perimeters and manage network-level access to Google Cloud resources. They do not serve as a mechanism for logical data organization or grouping physical storage assets within a Dataplex Lake.
• B. Correct. Dataplex uses a hierarchy of Lake > Zone > Asset. Dataplex Zones (such as Raw or Curated) are the logical subdivisions within a Lake used to organize and group assets. You can register physical resources like GCS buckets and BigQuery datasets as 'Assets' within these zones, even if they reside in different projects, enabling the 'Sales Domain' to manage them centrally.
• C. Incorrect. Data Catalog tags are used for metadata management, classification, and discovery. While they provide context for your data, they are not the structural construct used to register or logically group physical storage resources into the Dataplex architecture.
• D. Incorrect. Cloud Asset Inventory is a global Google Cloud service used to track and monitor resources across your organization. While Dataplex uses the term 'Asset' to represent the physical resources it manages, 'Asset Inventory' is not the grouping construct; 'Zones' are the designated grouping mechanism within Dataplex.

Correct answer: C — AlloyDB for PostgreSQL, utilizing the columnar engine for analytical queries.

• A. Incorrect. While Cloud SQL for PostgreSQL provides managed PostgreSQL with HA, running complex analytical queries against the live transactional instance will consume CPU and IOPS, significantly impacting OLTP performance. It lacks a built-in columnar engine to offload heavy analytics natively.
• B. Incorrect. Cloud Spanner with the PostgreSQL interface provides horizontal scalability, but it does not offer full PostgreSQL 14 feature parity (it is a subset). Furthermore, it does not provide the specific HTAP (Hybrid Transactional/Analytical Processing) columnar acceleration required to run heavy analytics on live data without potential performance interference.
• C. Correct. AlloyDB for PostgreSQL is fully compatible with PostgreSQL 14 and is designed for enterprise-grade HTAP workloads. It includes an integrated columnar engine that uses machine learning to automatically populate a columnar cache for analytical queries. This allows high-performance OLAP queries to run on the live data with minimal impact on transactional performance, eliminating the immediate need for a separate ETL pipeline.
• D. Incorrect. BigQuery federated queries allow you to query data in Cloud SQL without ETL, but the query execution actually occurs on the source database. This means complex analytical queries would place a high load on the Cloud SQL instance, degrading the performance of the e-commerce application.

Correct answer: B — Store data in Cloud Storage Standard class. Use a Lifecycle Management rule to move data to Nearline after 30 days and to Archive after 90 days. Delete after 7 years.

• A. Incorrect. While keeping data in the Standard class for 7 years meets the availability and retention requirements, it is the most expensive option. For petabytes of data that is rarely accessed after the first month, failing to utilize lower-cost storage classes results in significantly higher costs.
• B. Correct. This strategy optimizes for the specific access patterns provided. Cloud Storage Standard handles the first 30 days of frequent access. Transitioning to Nearline at 30 days and then to Archive at 90 days places the data into the lowest-cost storage tier for the vast majority of its 7-year lifespan. Archive storage is significantly cheaper than all other tiers for long-term retention of rarely accessed data.
• C. Incorrect. While this option eventually moves data to Archive, it keeps data in the Coldline tier for a full year. Since the prompt states access is rare (less than once a year) after the first 30 days, moving to Archive at 90 days (as in Option B) is more cost-effective than waiting a full year.
• D. Incorrect. BigQuery is not suitable for raw video footage. Furthermore, even with long-term storage pricing (which is approximately half the cost of active storage), BigQuery is roughly 10 times more expensive per gigabyte than Cloud Storage Archive storage.

Correct answer: B — Configure GCS Object Lifecycle Management to move objects to Coldline or Archive storage after 90 days. Configure BigQuery Partition Expiration or move older partitions to long-term storage automatically.

• A. Using Dataplex TTL or BigQuery table expiration results in the permanent deletion of data. The requirement states data is 'rarely accessed' after specific periods, not that it should be destroyed. Furthermore, table expiration deletes the entire table rather than just the older segments of the data.
• B. This is the most cost-effective and automated approach. GCS Object Lifecycle Management (OLM) allows for transitioning objects to cheaper storage classes (Coldline/Archive) without deleting them. For BigQuery, data that has not been modified for 90 days automatically transitions to long-term storage pricing (a ~50% reduction), and partitioning allows for granular management of data lifecycle and performance.
• C. Implementing custom Cloud Functions and manual DELETE statements introduces significant operational overhead and is prone to errors. Deletion also permanently removes data, which contradicts the scenario where data is still needed for occasional/rare access.
• D. BigQuery Omni is a multi-cloud analytics solution designed to query data residing in AWS S3 or Azure Blob Storage. It does not provide a storage cost optimization mechanism for data already in Google Cloud and would likely increase query costs and architectural complexity.

Correct answer: B — Use BigQuery Omni to compute on the data in AWS and Azure, and return only the results to Google Cloud for the final join.

• A. Incorrect. Storage Transfer Service is designed to move data from other clouds into Google Cloud Storage. This process incurs egress fees from the source cloud provider and violates the requirement to avoid moving the data.
• B. Correct. BigQuery Omni is a multi-cloud analytics solution that allows you to run the BigQuery engine on AWS (S3) and Azure (Blob Storage) via Anthos. By running the compute where the data resides, you avoid large-scale data movement and associated egress fees, returning only the final result set to Google Cloud for joining with internal BigQuery datasets.
• C. Incorrect. Standard BigQuery external tables do not support direct mounting of AWS S3 or Azure Blob Storage via signed URLs for querying. Even if data were accessed via external connections, the processing would typically occur in GCP, which would pull the data across cloud boundaries and incur egress charges.
• D. Incorrect. Using Dataflow to stream or batch process 150 TB of data from S3 and Azure into BigQuery involves moving the data into Google Cloud. This would result in significant egress costs and fails to meet the requirement of keeping the data in its original location.

Correct answer: A — Partition by Day and Cluster by a timestamp/integer representing the hour.

• A. Correct. Partitioning by day results in approximately 3,650 partitions for 10 years, which stays within the 4,000-partition hard limit. By clustering the table on the hour field, BigQuery can efficiently colocate and prune data within those daily partitions, enabling performant and cost-effective hourly-level queries.
• B. Incorrect. Creating thousands of separate tables (table sharding) is an outdated practice in BigQuery. It introduces significant operational complexity, makes cross-table analytics more difficult, and schema management becomes burdensome compared to using a single partitioned and clustered table.
• C. Incorrect. The limit of 4,000 partitions per table is a hard limit in BigQuery and cannot be increased. Attempting to partition by hour over 10 years would require over 87,000 partitions, leading to errors during data ingestion.
• D. Incorrect. Ingestion-time partitioning is based on when the data is loaded, not on the timestamps within the data. Furthermore, even ingestion-time partitioning is subject to the same partition limits per table, so it does not solve the constraint of having 10 years of granular data.

Correct answer: B — Load the data into BigQuery native storage.

• A. Converting CSVs to Avro can reduce parsing overhead and provide a schema, but Avro is primarily a row-based format and does not offer the same columnar storage benefits as BigQuery's native format. Furthermore, querying data in Cloud Storage still involves remote I/O bottlenecks compared to managed storage.
• B. Loading data into BigQuery native storage is the most effective way to improve performance. It leverages BigQuery's proprietary columnar storage format (Capacitor), which is highly optimized for complex analytical queries at petabyte scale. This also simplifies the architecture by using BigQuery's fully managed storage and ingestion capabilities.
• C. Hive partitioning on an external table helps with file pruning (skipping irrelevant files), but it doesn't eliminate the fundamental overhead of reading raw data from Cloud Storage. For complex queries on a 500 TB dataset, the performance gain is negligible compared to native storage.
• D. Increasing the slot quota adds more compute power but does not address the underlying bottleneck of storage I/O and data parsing associated with external CSV files. It is also a less cost-efficient and more complex way to resolve performance issues compared to optimizing storage.

Correct answer: A — Use Google Connected Sheets to analyze the BigQuery data directly from Google Sheets.

• A. Google Connected Sheets (part of the BigQuery Data Connector) allows users to analyze massive BigQuery datasets directly within Google Sheets. It supports familiar spreadsheet tools like pivot tables, charts, and formulas while executing the heavy processing in BigQuery. This satisfies the 5 TB requirement without needing SQL or extracting data to local CSV files.
• B. Exporting 5 TB of data to Cloud Storage as CSV is impractical due to the massive file size and performance bottlenecks. Furthermore, traditional desktop applications like Excel cannot handle datasets of this magnitude (which exceed row limits), and the prompt explicitly forbids CSV extraction due to security and size constraints.
• C. The BigQuery console requires users to write SQL queries to manipulate data, which the analyst does not know. Additionally, saving large result sets to Google Drive often involves row limits or data extraction that could violate security policies.
• D. While teaching SQL is a valid long-term skill, it does not provide an immediate solution that leverages the analyst's existing expertise in pivot tables and formulas. Connected Sheets is specifically designed to bridge the gap between BigQuery's scale and a user's spreadsheet skills.

Correct answer: B — BigQuery BI Engine

• A. BigQuery Omni is a multi-cloud analytics solution that allows you to run BigQuery queries on data residing in other cloud providers like AWS and Azure. It is not designed as an in-memory acceleration layer for BI dashboards.
• B. BigQuery BI Engine is a fast, in-memory analysis service that accelerates query response times for interactive, high-concurrency BI dashboards. By caching data in memory and using a specialized execution engine, it provides sub-second latency and high throughput for visualization tools like Looker Studio.
• C. BigQuery ML allows data scientists and analysts to build and deploy machine learning models directly within BigQuery using standard SQL. While powerful for predictive analytics, it does not serve as an in-memory cache for dashboard acceleration.
• D. BigQuery Data Transfer Service automates the movement of data from various SaaS applications and external sources into BigQuery. It is a data ingestion and automation tool, not an in-memory analysis service.

Correct answer: B — Table Snapshots

• A. Table Clones create a fast, zero-copy copy of a table that is writable. While they capture the state at the time of creation, they are primarily used for development and testing. Furthermore, a clone cannot be retroactively created for a point in time outside of the standard 7-day time travel window.
• B. Table Snapshots are specifically designed to create an immutable, read-only, point-in-time copy of a table. Once a snapshot is created, it remains unchanged even if the base table is modified or deleted. While creating a snapshot from a past state is limited to BigQuery's time travel window (7 days), a snapshot that was taken 30 days ago and preserved is the standard mechanism for sharing historical, point-in-time data without the overhead of data exports.
• C. Time Travel (using the FOR SYSTEM_TIME AS OF clause) allows you to query historical data, but it is limited to a maximum retention window of 7 days. It does not create a persistent, shareable object, and it cannot access data from 30 days ago unless a snapshot was already taken.
• D. Materialized Views are precomputed results used to improve query performance and are automatically updated as the base table changes. They do not represent static historical snapshots and are not used for point-in-time data sharing.

Correct answers: A, B — Define a schema for your Entity Types and Features.; Write a Dataflow job to ingest historical data into the Offline Store.

• A. Establishing the hierarchy of Entity Types (e.g., 'user', 'product') and Features (e.g., 'average_spend') is a fundamental step in setting up Vertex AI Feature Store. This schema ensures features are properly registered, typed, and associated with the correct entities for both online serving and offline training.
• B. To populate the Offline Store with historical data for point-in-time joins (crucial for avoiding data leakage), you must ingest historical feature values. A Dataflow job is a common and recommended method to transform source data and write timestamped feature records into the Feature Store's managed offline storage.
• C. Vertex AI Feature Store manages the backend storage internally. While the Online Store might use Bigtable under the hood for low-latency serving, the Offline Store uses BigQuery. Users do not manually configure Bigtable as an offline backend.
• D. Vertex AI Feature Store provides its own managed low-latency online serving layer. Manually configuring an external cache like Memcached is unnecessary and is not part of the standard Feature Store architecture.
• E. A BigQuery view exposed as an API does not provide the specialized metadata management, automated point-in-time correctness for training, or the low-latency serving infrastructure provided by Vertex AI Feature Store.

Correct answer: B — Use BigQuery Time Travel to copy the table from a snapshot 2 days ago.

• A. Restoring from exported Avro files in Cloud Storage is only possible if a recent export actually exists. Even then, re-importing 50TB of data is time-consuming and may incur additional costs. It is far less efficient than using built-in BigQuery features for recent deletions.
• B. BigQuery Time Travel allows you to access and restore data from any point within the last seven days. Since the deletion occurred 2 days ago, you can use the snapshot decorator or a 'CREATE TABLE ... AS SELECT ...' statement with a 'FOR SYSTEM_TIME AS OF' clause to recover the table immediately without reprocessing or moving data from external storage.
• C. Contacting Google Cloud Support for physical backup restoration is a last-resort measure and is not a standard recovery path for user-deleted tables. It involves manual intervention and significant delays, making it neither fast nor the primary recommended method.
• D. Re-running ETL pipelines to repopulate 50TB is extremely resource-intensive, expensive, and slow. Additionally, it may be impossible to perfectly replicate the historical state if the source systems have since changed or do not retain full historical logs.

Correct answers: A, C — Create a cross-region read replica in `us-east1`.; In the event of a failure, promote the `us-east1` replica to be the primary instance.

• A. Creating a cross-region read replica in `us-east1` provides a redundant copy of the instance in a different region, which is necessary to survive a total region failure of `us-central1`. Cross-region replicas use asynchronous replication, which typically achieves minute-level RPOs suitable for a 5-minute RPO target.
• B. High Availability (HA) in Cloud SQL provides redundancy across zones within a single region. While this protects against a zone outage, it does not provide protection against a total region failure.
• C. In a disaster recovery scenario where the primary region is unavailable, promoting the cross-region replica converts it into a standalone, writable primary instance. This failover process is the standard way to meet aggressive RTO targets (like 1 hour) compared to restoring from backups.
• D. While backups are important, restoring a database from Cloud Storage is generally too slow to reliably meet a 1-hour RTO for large instances. Additionally, standard backup schedules may not satisfy a strict 5-minute RPO.
• E. Cloud SQL does not support synchronous replication for cross-region read replicas. Synchronous replication is used for regional High Availability (between zones), whereas cross-region replication is always asynchronous.

Correct answer: B — Move the database queries and API calls inside the Python callables or operators.

• A. Increasing the machine type (vertical scaling) might provide temporary relief but fails to address the root cause: expensive operations executed during DAG parsing. The scheduler continuously parses DAG files, so inefficient top-level code will persist in consuming excessive resources regardless of the node size.
• B. Moving database queries and API calls inside operators or Python callables ensures these operations only execute at task runtime on workers, rather than during every scheduler parsing cycle. This significantly reduces CPU load and latency for the Airflow scheduler by keeping DAG parsing lightweight and fast.
• C. SubDags are generally discouraged due to performance issues and added complexity. They do not prevent top-level code execution during parsing and would not solve the problem of high scheduler CPU consumption; in fact, they often increase scheduling overhead.
• D. Fetching Airflow Variables at the top level of a DAG file is an anti-pattern. Since the scheduler parses DAG files frequently (usually every few seconds), accessing variables at the top level results in constant database lookups to the Airflow metadata DB, which contributes to high scheduler latency rather than reducing it.

Correct answer: B — Use a Pub/Sub topic. The 'Ingest' DAG publishes a message upon completion, and the 'Process' DAG uses a PubSubPullSensor.

• A. The ExternalTaskSensor monitors the status of a task or DAG within the same Airflow instance because it queries the local Airflow metadata database. Since these are two separate Cloud Composer environments, they have isolated metadata databases, making the ExternalTaskSensor unable to see the status of the other environment.
• B. This is the most robust and decoupled approach for cross-environment communication. Using Google Cloud Pub/Sub allows the 'Ingest' environment to signal completion as an event. The 'Process' environment can then react to this event using a PubSubPullSensor, effectively bridging the two isolated environments.
• C. While using a GCS file as a signal (the 'landing file' pattern) is technically possible across environments, it relies on polling and is generally considered less elegant and more prone to maintenance overhead than event-driven messaging like Pub/Sub.
• D. The TriggerDagRunOperator is designed to initiate a DAG run, not to act as a sensor for completion. Additionally, it is primarily intended for use within a single Airflow instance and is proposed here in the incorrect logical order (Process triggering Ingest instead of waiting for it).

Correct answer: B — A hot key issue is occurring; check the log files for distribution of keys and consider using Dataflow Shuffle or adding a fan-out step.

• A. Incorrect. If the pipeline were under-provisioned, you would expect many workers to be busy or the autoscaler to add more workers. While Dataflow supports Vertical Autoscaling, it cannot resolve a data skew/hot-key issue that concentrates work on a single worker.
• B. Correct. A hot key (skewed key distribution) will concentrate processing for a specific key/window on a single worker, causing that worker's CPU to spike while others remain idle. This creates a bottleneck that increases system lag. Troubleshooting involves identifying the skewed key and mitigating it with Dataflow Shuffle, re-keying, or implementing a fan-out/reshard step.
• C. Incorrect. BigQuery quota issues manifest as streaming insert errors or retries visible in logs and metrics. A quota issue would affect the sink stage generally and is unlikely to produce a specific CPU skew where only one worker is pegged at 100%.
• D. Incorrect. A memory leak typically results in Out-of-Memory (OOM) errors or garbage collection thrashing, not sustained 100% CPU on a single worker while others are idle. Restarting the job is a temporary workaround and does not address the root cause of the processing hotspot.

Correct answer: A — The subscriber application is processing messages slower than the rate of publication, causing a backlog.

• A. Correct. A steady, non-zero Pull Request Count combined with an increasing Oldest Unacked Message Age indicates that the subscriber is successfully pulling messages but cannot process and acknowledge them as fast as they are being published. This lead to a growing backlog. Since the logs show no errors, it confirms the application is functional but likely under-provisioned or experiencing processing latency.
• B. Incorrect. If a Pub/Sub topic quota were exceeded, publish operations would be throttled or rejected, typically resulting in 429 Resource Exhausted errors for publishers. It would not explain an increasing backlog age on the subscription side if pull requests are still successfully occurring at a consistent rate.
• C. Incorrect. If the subscriber were crashing and restarting, application logs would show termination signals or stack traces. Additionally, crashes would usually lead to inconsistent Pull Request counts and higher message redelivery rates, which is not what is observed here.
• D. Incorrect. Pub/Sub enforces the 10 MB message size limit during publication. Any message exceeding this limit is rejected immediately with an error to the publisher and never enters the subscription. Therefore, it cannot contribute to the unacked message age metric.

Correct answer: B — Create a central Administration Project that holds the Capacity Commitment. Create Reservations for each department and assign their respective projects to these Reservations.

• A. Incorrect. Creating separate Billing Accounts for each department fragments billing and prevents the centralized purchase and management of Capacity Commitments. This setup makes it impossible to leverage organization-wide volume discounts or share idle slots between departments.
• B. Correct. The recommended approach for centralized capacity management in BigQuery is to create a central Administration Project to hold the Capacity Commitment (slots). You then create Reservations for each department and assign their specific projects to those Reservations. This allows for centralized billing and volume discounts while supporting idle slot sharing across the organization.
• C. Incorrect. Using a single project with Labels can assist with cost attribution in billing exports, but it does not provide the compute isolation or capacity management required for a robust chargeback model. It lacks the ability to formally manage slots or enforce departmental compute quotas through Reservations.
• D. Incorrect. The BigQuery On-Demand model charges based on the amount of data processed per query and does not support Capacity Commitments. Because it lacks the reservation mechanism, you cannot centrally manage slot capacity, leverage volume discounts for compute, or enable idle slot sharing.

Correct answers: A, B — Create a specific Reservation for the Finance team with a fixed baseline capacity.; Assign the Marketing projects to a separate Reservation with no baseline (0 slots) but access to idle slots, or a lower baseline.

• A. Creating a specific Reservation for the Finance team with a fixed baseline capacity ensures that the Finance team always has the necessary slots available for their end-of-quarter reporting, regardless of the marketing team's activities. Reservations and assignments are the primary BigQuery mechanism for providing capacity guarantees.
• B. Assigning Marketing projects to a separate Reservation isolates their workload from the Finance reservation. Setting a low or zero baseline while enabling access to idle slots allows marketing to burst into unused capacity when available without ever competing with or blocking the Finance team's guaranteed baseline capacity.
• C. Instructing the Finance team to use BATCH priority would be counterproductive. BATCH priority queries are for non-time-sensitive workloads and are queued until idle resources are available; they do not provide guaranteed capacity and could lead to significant delays.
• D. Putting all projects in the default reservation relies on the fair scheduler. While the fair scheduler prevents a single query from monopolizing all slots, it does not provide hard capacity guarantees or a baseline, meaning the Finance team could still face performance degradation during high contention.
• E. Granting the roles/bigquery.admin role provides administrative permissions (like managing datasets and jobs) but has no impact on slot allocation, query performance, or capacity management.

Correct answer: B — Set up BigQuery Reservations. Assign a dedicated reservation to the Executive dashboard project and a separate reservation for the Data Science team.

• A. Creating a separate BigQuery project alone does not guarantee slot capacity. In the default on-demand billing model, projects still share a pool of slots, and moving workloads to different projects does not isolate them from slot contention caused by heavy queries elsewhere in the organization.
• B. BigQuery Reservations allows you to purchase dedicated slot capacity (commitments) and allocate them to specific projects, folders, or organizations. By assigning a dedicated reservation to the Executive dashboard and a separate one to the Data Science team, you provide workload isolation and guaranteed capacity for the dashboard while optimizing overall resource usage through slot sharing between the reservations if configured.
• C. Instructing teams to run queries at specific times is an operational workaround rather than a technical solution. It is not scalable, relies on human compliance, and does not provide an enforceable technical guarantee of performance for real-time requirements.
• D. The BigQuery Storage Read API provides high-speed data access for reading records directly from storage (useful for tools like Spark), but it does not address SQL query engine slot contention. It does not provide the slot isolation or execution guarantees needed for low-latency SQL dashboards.

Correct answer: A — Enable Streaming Engine to offload state and shuffle operations to the backend service.

• A. Correct. Enabling the Streaming Engine offloads state storage, windowing, and shuffle operations from the worker VMs to a Google-managed backend service. This significantly reduces CPU and memory pressure on the worker nodes, typically leading to increased throughput and reduced system lag without requiring a massive increase in the number of worker VMs.
• B. Incorrect. Switching to Batch mode is inappropriate for a continuous streaming source like Pub/Sub. Batch jobs are designed for bounded datasets and would break the real-time processing requirements of the pipeline.
• C. Incorrect. Disabling autoscaling and fixing the worker count removes the pipeline's ability to adapt to varying data volumes. While it might temporarily provide more capacity if set to a high number, it is inefficient, costly, and does not address the underlying resource contention on the workers as effectively as offloading tasks.
• D. Incorrect. For CPU-bound workloads, using a smaller machine type would reduce the available processing power per worker, likely exacerbating the high CPU usage and increasing the system lag further.