Google Practice Questions, Discussions & Exam Topics by our Authors
You have a Cloud SQL for PostgreSQL instance in Region' with one read replica in Region2 and another read replica in Region3. An unexpected event in Region' requires that you perform disaster recovery by promoting a read replica in Region2. You need to ensure that yo...
Let's evaluate each option to determine the best solution for the given disaster recovery scenario.
Option A: Enable zonal high availability on the primary instance. Create a new read replica in a new region.
- Pros:
- Enabling zonal high availability ensures that the primary instance has a failover standby in the same zone, improving availability within the region.
- Creating a new read replica in another region (like Region1) gives additional redundancy.
- Cons:
- This option does not immediately address the disaster recovery requirement. It only ensures that the primary instance has a standby and that read replicas exist in different regions. However, the action to promote a read replica in Region2 is still required, and this option does not directly help with switching to a new primary quickly.
- When to use: This is useful for increasing availability of the primary instance and ensuring redundancy but doesn’t directly help with the promotion of a read replica as the new primary.
Option B: Create a cascading read replica from the existing read replica in Region3.
- Pros:
- Cascading replicas can help distribute the load and further increase redundancy by adding a new read replica to handle read queries.
- Cons:
- This option doesn’t provide any immediate solution for promoting the read replica in Region2 to become the new primary. The cascading replica will only add more read capacity and does not ensure that Region2's read replica is prepared to handle the primary load.
- The disaster recovery goal is to promote Region2’s read replica to primary, which is not achieved by cascading replicas from Region3.
- When to use: Cascading read replicas are useful for further distributing read queries but do not meet the disaster recovery requirement in this case.
Option C: Create two new read replicas from the new prima...
Author: Harper · Last updated Jul 15, 2026
You orchestrate ETL pipelines by using Cloud Composer. One of the tasks in the Apache Airflow directed acyclic graph (DAG) relies on a third-party service. You w...
Let’s break down each option and evaluate it in the context of the requirement: notifying when a task does not succeed in an Apache Airflow DAG orchestrated by Cloud Composer.
Option A: Assign a function with notification logic to the on_retry_callback parameter for the operator responsible for the task at risk.
- Pros:
- The `on_retry_callback` is executed when the task retries, so it allows you to capture when a task is retrying and possibly send a notification about the retry event.
- Cons:
- This is not the correct option for notifying when a task fails, as it triggers when a task is being retried, not when it fails permanently. If you want to notify on failure (not retry), this won't work as expected.
- When to use: This would be useful if you specifically need to notify about retries, but it is not the right solution for failure notifications.
Option B: Configure a Cloud Monitoring alert on the sla_missed metric associated with the task at risk to trigger a notification.
- Pros:
- The `sla_missed` metric can help monitor if a task misses its Service Level Agreement (SLA), which is related to performance deadlines, and could be used to trigger notifications when a task does not meet its SLA.
- Cons:
- This is focused on SLA violations, not on task failure. If a task is not successful but doesn't miss an SLA (for example, it completes but doesn't succeed), this approach wouldn’t be appropriate for failure notifications. It also doesn’t directly notify you about the failure of the task itself.
- When to use: This option is suitable if you want to monitor SLA compliance specifically, but it’s not ideal for general ta...
Author: Amira · Last updated Jul 15, 2026
You are migrating your on-premises data warehouse to BigQuery. One of the upstream data sources resides on a MySQL. database that runs in your on-premises data center with no public IP addresses. You want to ensure that the data inge...
Let's evaluate the available options in detail to find the best solution for securely ingesting data from your on-premises MySQL database into BigQuery, ensuring that data doesn't traverse the public internet.
Option A: Update your existing on-premises ETL tool to write to BigQuery by using the BigQuery Open Database Connectivity (ODBC) driver. Set up the proxy parameter in the simba.googlebigqueryodbc.ini file to point to your data center's NAT gateway.
- Pros:
- This option allows the ETL tool to securely write data to BigQuery using ODBC, and using a proxy through the NAT gateway helps route the traffic within the private network.
- Cons:
- ODBC driver-based solutions are generally not as scalable, flexible, or efficient for large data migrations like the one you're doing.
- The NAT gateway would still involve public internet communication unless it's set up very carefully, which could be difficult to manage securely.
- Using an existing on-premises ETL tool may not fully leverage cloud-native tools like Datastream, which are optimized for cloud-to-cloud integrations and can handle this migration more seamlessly.
- When to use: This approach is suitable if you already have an ETL tool in place and just need a quick fix, but it doesn't offer the scalability or security best practices required for cloud migrations.
Option B: Use Datastream to replicate data from your on-premises MySQL database to BigQuery. Set up Cloud Interconnect between your on-premises data center and Google Cloud. Use Private connectivity as the connectivity method and allocate an IP address range within your VPC network to the Datastream connectivity configuration. Use Server-only as the encryption type when setting up the connection profile in Datastream.
- Pros:
- Datastream is a fully managed service designed for replicating data from on-premises sources (like MySQL) to BigQuery, making it ideal for this use case.
- Cloud Interconnect provides private connectivity between your on-premises data center and Google Cloud, ensuring that the data transfer does not go through the public internet, enhancing security and performance.
- Using Private connectivity ensures the data is securely transferred within a private network, maintaining confidentiality and compliance.
- Server-only encryption adds an extra layer of security for the connection.
- Cons:
- Cloud Interconnect might require some setup and is typically more expensive than other methods, but the security and performance benefits justify its use for large-scale migrations.
- When to use: This is the best approach when you need secure, efficient, and scalable data replication from on-premises to BigQuery, especially when you're focused on ensuring no public internet exposure.
Option C: Use Datastream to replicate data from your on-premises MySQL database to BigQuery. Use Forward-SSH tu...
Author: MoonlitPantherX · Last updated Jul 15, 2026
You store and analyze your relational data in BigQuery on Google Cloud with all data that resides in US regions. You also have a variety of object stores across Microsoft Azure and Amazon Web Services (AWS), also in US regions. You want to...
Let's analyze the options based on the goal of minimizing data movement while querying data daily across BigQuery, Azure, and AWS in US regions.
Option A: Use BigQuery Data Transfer Service to load files from Azure and AWS into BigQuery.
- Reasoning: BigQuery Data Transfer Service is a fully-managed tool for transferring data into BigQuery, but it focuses on scheduled data transfers. It supports a limited set of sources such as Google Cloud Storage, Salesforce, and others, but does not directly support Azure or AWS as native sources. Thus, this method would involve unnecessary data movement and may not be suitable given your requirement to minimize data movement.
- Rejected: The Data Transfer Service isn't suitable for querying files in Azure and AWS directly.
Option B: Create a Dataflow pipeline to ingest files from Azure and AWS to BigQuery.
- Reasoning: Dataflow is a fully managed service for processing data in real-time or batch mode. It can ingest data from various sources like AWS S3, Azure Blob Storage, and then write to BigQuery. However, it would still involve significant data movement as files need to be moved from AWS and Azure into Google Cloud Storage or BigQuery. This is a solution that requires more processing time and infrastructure to continuously process the files.
- Rejected: While Dataflow is flexible, it does not reduce data movement, which is a key factor for minimizing costs and maximizing performance in your case.
Option C: Load files from AWS and Azure to Cloud Storage with Cloud Shell gsutil rsync arguments.
- Reasoning: This option involv...
Author: Emily · Last updated Jul 15, 2026
You have a variety of files in Cloud Storage that your data science team wants to use in their models. Currently, users do not have a method to explore, cleanse, and validate the data in Cloud Storage. You are looking for a low code solution that can be...
Let's break down each option based on the key factors of low-code accessibility, quick exploration, and data cleansing for the data science team.
Option A: Provide the data science team access to Dataflow to create a pipeline to prepare and validate the raw data and load data into BigQuery for data exploration.
- Reasoning: Dataflow is a powerful, fully managed service for processing data with Apache Beam. It requires users to create custom pipelines for data transformation and validation, which can be quite complex and requires coding expertise. Although it is flexible, Dataflow isn't a low-code solution. Data science teams might need to write code to define the pipeline, which could be cumbersome if they are looking for an easy-to-use tool for quick data exploration and cleansing.
- Rejected: This option involves more development work and is not a low-code solution.
Option B: Create an external table in BigQuery and use SQL to transform the data as necessary. Provide the data science team access to the external tables to explore the raw data.
- Reasoning: An external table in BigQuery allows users to query data in Cloud Storage directly without moving it into BigQuery. This is useful for fast querying and exploration, but it requires knowledge of SQL, which may not be as intuitive for a data science team looking for a low-code solution. While it can provide quick access to the raw data, the data cleansing and transformation steps will still need to be handled manually with SQL, which may not be the easiest approach for non-technical users.
- Rejected: While this approach offers data exploration via SQL, it lacks the low-code aspect that the team requires for cleansing and validation.
Option C: Load the data into BigQuery and use SQL to transform the data as necessary. Provide the data science team access to staging tables to explore the raw data.
- Reasoning: This ap...
Author: Amira99 · Last updated Jul 15, 2026
You are building an ELT solution in BigQuery by using Dataform. You need to perform uniqueness and null value checks on your final tables. What should...
Let's break down the options based on key factors such as efficiency, integration into the pipeline, simplicity, and the specific use case of performing checks on uniqueness and null values in BigQuery.
Option A: Build BigQuery user-defined functions (UDFs).
- Reasoning: User-defined functions (UDFs) in BigQuery allow custom SQL functions to be created. While this can be useful for custom transformations, UDFs are not ideal for data quality checks like ensuring uniqueness or checking for null values. They would need to be explicitly called within queries, which could be inefficient for data validation in the pipeline, especially when handling large datasets.
- Rejected: UDFs are not designed specifically for data quality tasks and can introduce overhead in terms of performance and complexity. There are more efficient ways to perform these checks in the context of an ELT pipeline.
Option B: Create Dataplex data quality tasks.
- Reasoning: Dataplex is a unified data governance and management solution. It allows for the creation of data quality tasks that can perform checks like uniqueness, null values, and data validation at the dataset level. However, Dataplex tasks are more suited for governance and monitoring across datasets and are not necessarily tightly integrated into the ELT pipeline itself. They are better for ongoing data quality management and less for efficient integration into the actual ETL/ELT workflow.
- Rejected: While useful for governance, Dataplex is not specifically designed for seamlessly integrating data quality checks directly into an ELT pipeline with the level of control and efficiency required for this task.
Option C: Build Dataform assertions into your code.
- Reasoning: Dataform is a powerful tool specifically designed for building and managing ELT pipelines within BigQuery. It supports as...
Author: Vivaan · Last updated Jul 15, 2026
A web server sends click events to a Pub/Sub topic as messages. The web server includes an eventTimestamp attribute in the messages, which is the time when the click occurred. You have a Dataflow streaming job that reads from this Pub/Sub topic through a subscription, applies some transformations, and writes the result to another Pub/Sub topic for use by the advertising department. The advertising department needs to receive each message within 30 seconds of the corresponding click occurrence, but they report receiving the messages late. Your ...
Let's analyze the problem and each option based on the given scenario.
Key Information:
- The web server sends click events to a Pub/Sub topic.
- EventTimestamp is included in the messages to indicate when the click happened.
- Dataflow job reads from the Pub/Sub topic, processes the messages, and writes the result to another Pub/Sub topic for the advertising department.
- The advertising department needs the messages within 30 seconds of the click occurrence, but reports receiving them late (about 40 seconds).
- Dataflow system lag is about 5 seconds, and data freshness is about 40 seconds.
- The eventTimestamp and publishTime differ by only 1 second, suggesting that the messages are being sent from the web server at an appropriate time.
Given Constraints:
- The advertising department needs the messages within 30 seconds of the corresponding click, but is receiving them late by approximately 40 seconds.
- Dataflow lag is 5 seconds, indicating that the delay is mainly due to the processing time within Dataflow or issues with backlog, rather than issues with message arrival or event timestamps.
---
Option A) The advertising department is causing delays when consuming the messages. Work with the advertising department to fix this.
- Explanation: While it's possible that the advertising department could have delays in processing the messages, the key observation in this case is the data freshness is 40 seconds, not due to a delay in consumption. The system lag in Dataflow and the eventTimestamp's small gap from publishTime suggest the issue is earlier in the data pipeline.
- Rejected: The issue is likely upstream in the processing pipeline (Dataflow), not with the consumption of messages by the advertising department.
Option B) Messages in your Dataflow job are taking more than 30 seconds to process. Optimize your job or increase the number of workers to fix this.
- Explanation: If your Dataflow job were processing messages slowly (i.e., taking more than 30 seconds per message), this could explain the delays. Since the system la...
Author: Sophia Clark · Last updated Jul 15, 2026
Your organization stores customer data in an on-premises Apache Hadoop cluster in Apache Parquet format. Data is processed on a daily basis by Apache Spark jobs that run on the cluster. You are migrating the Spark jobs and Parquet data to Google Cloud. BigQuery will be used on future transformation pipelines so you need to ensure that your d...
In order to determine the best approach for migrating your data and processing pipelines to Google Cloud, we need to evaluate the options based on key factors such as:
1. Managed Services: Since you want to leverage managed services, it's crucial to choose a solution that minimizes overhead and reduces the need for manual management.
2. Minimizing ETL Changes: You want to minimize ETL processing changes, which means the migration should be as seamless as possible, without a significant refactor of the existing pipeline.
3. Cost Considerations: The solution should aim to minimize additional operational or overhead costs associated with managing data and processing.
Option Analysis:
A) Migrate your data to Cloud Storage and migrate the metadata to Dataproc Metastore (DPMS). Refactor Spark pipelines to write and read data on Cloud Storage, and run them on Dataproc Serverless.
- Advantages:
- Cloud Storage is highly scalable and cost-effective.
- Dataproc Metastore provides managed metadata services for Spark, which integrates with BigQuery in the future.
- Dataproc Serverless reduces operational overhead by automatically provisioning and scaling clusters based on the job's needs.
- Challenges:
- Requires a significant refactor of your Spark pipelines to read from Cloud Storage and utilize Dataproc Metastore, potentially involving more effort than desired.
- Although Dataproc Serverless helps with scaling, the management of Cloud Storage may still introduce overhead.
B) Migrate your data to Cloud Storage and register the bucket as a Dataplex asset. Refactor Spark pipelines to write and read data on Cloud Storage, and run them on Dataproc Serverless.
- Advantages:
- Cloud Storage is scalable and cost-effective.
- Dataplex provides a unified data governance layer, which helps manage data in a consistent manner, ensuring easy access and security.
- Dataproc Serverless again reduces operational overhead.
- Challenges:
- Similar to option A, this option requires pipeline refactors to utilize Dataplex, which might not be seamless, especially in the short term.
- While Dataplex adds value in data management...
Author: Aarav · Last updated Jul 15, 2026
Your organization has two Google Cloud projects, project A and project B. In project A, you have a Pub/Sub topic that receives data from confidential sources. Only the resources in project A should be able to access the data in that topic. You want to...
To ensure that only resources in project A can access the Pub/Sub topic in project A, while blocking access from project B or any future projects, we need to carefully consider the different options in terms of the scope and enforceability of access controls. Let's evaluate each option based on this requirement.
A) Add firewall rules in project A so only traffic from the VPC in project A is permitted.
- Reasoning: Firewall rules operate at the network level and allow you to control traffic between different Virtual Private Cloud (VPC) networks. However, Pub/Sub is a service provided by Google Cloud at a higher layer (application layer), and the access control for Pub/Sub topics is not handled at the network level. Firewall rules are primarily designed for controlling ingress/egress of network traffic and wouldn't effectively control access to Pub/Sub resources.
- Rejected: Firewall rules won't prevent other projects from accessing the Pub/Sub topic because they don't control access at the service level (Pub/Sub access control is done through IAM policies).
B) Configure VPC Service Controls in the organization with a perimeter around project A.
- Reasoning: VPC Service Controls can help to define a security perimeter around resources to control communication between services across Google Cloud projects. Configuring a perimeter around project A would restrict access to services like Pub/Sub within project A and prevent services from other projects (including project B) from accessing the resources inside the perimeter.
- Rejected: While VPC Service Controls are powerful for controlling data access across services, they are more appropriate when trying to restrict communication between different services (e.g., Cloud Storage, BigQuery) across projects. However, Pub/Sub's access control is primarily based on IAM roles, not VPC-level service perimeter settings. Hence, ...
Author: Emma · Last updated Jul 15, 2026
You stream order data by using a Dataflow pipeline, and write the aggregated result to Memorystore. You provisioned a Memorystore for Redis instance with Basic Tier, 4 GB capacity, which is used by 40 clients for read-only access. You are expecting the number of read-only clients to increase significantly to a few hundred and you need to be able to support th...
Let's analyze each option in the context of your requirements:
Requirements:
- Significant increase in read-only clients (a few hundred).
- Read and write access availability should not be impacted.
- Quick deployment.
A) Create a new Memorystore for Redis instance with Standard Tier. Set capacity to 4 GB and read replica to No read replicas (high availability only). Delete the old instance.
- Reasoning: The Standard Tier in Redis offers high availability (HA) by providing automatic failover. However, you are only adding a read replica with no read replicas, which means you will not improve the read performance for the growing number of clients. The 4 GB capacity might also be insufficient as your demand increases.
- Rejected: This option lacks scalability (as it does not improve read performance with replicas), and 4 GB of memory might not be enough as the number of clients grows. The read-only clients will be bottlenecked by this, and the change doesn’t address all performance concerns.
B) Create a new Memorystore for Redis instance with Standard Tier. Set capacity to 5 GB and create multiple read replicas. Delete the old instance.
- Reasoning: This option is promising because the Standard Tier provides high availability and better scaling through read replicas, which improves read performance for the growing number of clients. Increasing the capacity to 5 GB gives a bit more headroom, and having multiple read replicas allows for better distribution of read traffic, reducing latency and potential bottlenecks.
- Selected: This is the best option because it directly addresses the scalability and availability concerns. The Standard Tier with multiple read replicas ensures both better performance (scalability for read-heavy workloads) and high availability (HA for both reads and writes).
C) Create a new Memorystore for Memcached instance. Set a minimum of three nodes, and memory per node to 4 GB. Modify the Dataflow pipeline and all clients to use the Memcached instance. Delete the old instan...
Author: Elijah · Last updated Jul 15, 2026
You have a streaming pipeline that ingests data from Pub/Sub in production. You need to update this streaming pipeline with improved business logic. You need to ensure that the updated pipeline reprocesse...
Let's break down the options in the context of your requirement: reprocessing the previous two days of delivered Pub/Sub messages after updating your streaming pipeline.
Requirements:
- You want to reprocess data from the previous two days of Pub/Sub messages in your pipeline after deploying an updated business logic.
A) Use the Pub/Sub subscription clear-retry-policy flag.
- Reasoning: The `clear-retry-policy` flag is used to clear the retry policy of a Pub/Sub subscription, which essentially controls whether unacknowledged messages should be retried. This flag does not help with reprocessing past messages that have already been delivered. It mainly addresses message delivery retries.
- Rejected: This option is not relevant for reprocessing historical messages because it deals with retry policies, not with controlling which messages are delivered for processing.
B) Use Pub/Sub Snapshot capture two days before the deployment.
- Reasoning: A snapshot in Pub/Sub captures the state of a subscription at a specific point in time, allowing you to replay messages from that snapshot. By capturing a snapshot two days before the deployment, you can effectively "bookmark" the state of the messages as they were delivered at that time. This allows you to reprocess those messages.
- Selected: This is a viable option. Using a Pub/Sub snapshot allows you to replay the messages from a specific point in time, ensuring that you can reprocess the messages that were delivered in the past two days. It's a straightforward and reliable method for achieving this task.
C) Create a new Pub/Sub subscription two days before the deployment.
- Reasoning: Creating a new subscription to a topic does not give you the ability to reprocess messages that were already delivered to the original subscription. New subscriptions will only receive new messages going for...
Author: Mia · Last updated Jul 15, 2026
You currently use a SQL-based tool to visualize your data stored in BigQuery. The data visualizations require the use of outer joins and analytic functions. Visualizations must be based on data that is no less than 4 hours old. Business users are complaining that the visualizations are too slow to generate. You want to improve...
Let's break down the options based on your requirements:
Requirements:
- You need to improve the performance of visualization queries.
- You want to minimize the maintenance overhead of the data preparation pipeline.
- The data visualizations must be based on data that is at least 4 hours old.
- The queries use outer joins and analytic functions, which can be computationally expensive.
A) Create materialized views with the allow_non_incremental_definition option set to true for the visualization queries. Specify the max_staleness parameter to 4 hours and the enable_refresh parameter to true. Reference the materialized views in the data visualization tool.
- Reasoning: Materialized views precompute query results and store them, which can significantly improve performance. The max_staleness parameter ensures that the data is at least 4 hours old, and enable_refresh keeps the materialized view updated. The `allow_non_incremental_definition` option allows for more complex queries (including those with outer joins and analytic functions), though it may require a full refresh instead of incremental updates. This option strikes a good balance by improving performance and minimizing maintenance overhead.
- Selected: This option addresses the performance issue while ensuring that the data is not too fresh (ensuring it is at least 4 hours old) and minimizes maintenance overhead with automatic refresh. Materialized views are well-suited for query optimization in such scenarios.
B) Create views for the visualization queries. Reference the views in the data visualization tool.
- Reasoning: Views are simply stored SQL queries that are executed on demand. While they are useful for managing complex logic and abstractions, they don't offer performance improvements because they execute the underlying query each time they are called. Since your queries are already slow due to the complexity (e.g., outer joins, analytic functions), using views won't significantly improve performance.
- Rejected: Views will not address the performance issue. They do not offer the precomputed benefits that materialized views provide, and they will still incur the cost of running complex queries on each execution.
C) Create a Cloud Function instance to export the visualization query results as parquet files to a Cloud Storage bucket. Use Cloud Scheduler to trigger the...
Author: Liam · Last updated Jul 15, 2026
You need to modernize your existing on-premises data strategy. Your organization currently uses:
* Apache Hadoop clusters for processing multiple large data sets, including on-premises Hadoop Distributed File System (HDFS) for data replication.
* Apache Airflow to orchestrate hundreds of ETL pipelines with thousands of job steps.
You need to set up a new a...
Let's analyze each option based on the current architecture and requirements:
Current architecture:
- Apache Hadoop clusters: Used for processing large datasets, including on-premises HDFS for data replication.
- Apache Airflow: Orchestrates hundreds of ETL pipelines with thousands of job steps.
Requirement:
- You need a solution in Google Cloud that can handle Hadoop workloads and requires minimal changes to your existing orchestration processes (which are currently using Apache Airflow).
A) Use Bigtable for your large workloads, with connections to Cloud Storage to handle any HDFS use cases. Orchestrate your pipelines with Cloud Composer.
- Reasoning: Bigtable is a NoSQL database optimized for real-time analytics and large workloads, but it is not designed to run traditional Hadoop workloads, such as MapReduce or Spark jobs, which are common in your current setup. Using Bigtable for Hadoop workloads may require significant changes to your data processing logic, and it's not well-suited for HDFS use cases. Additionally, orchestrating with Cloud Composer (which is based on Apache Airflow) would be compatible with your existing processes but doesn't address the key requirement of running Hadoop workloads efficiently.
- Rejected: Bigtable isn't suitable for running Hadoop jobs or managing HDFS workloads, making it a poor fit for your existing needs.
B) Use Dataproc to migrate Hadoop clusters to Google Cloud, and Cloud Storage to handle any HDFS use cases. Orchestrate your pipelines with Cloud Composer.
- Reasoning: Dataproc is Google Cloud’s fully managed Spark and Hadoop service, which is designed to run existing Hadoop workloads without significant changes. It integrates well with Cloud Storage, which can handle HDFS use cases (as Cloud Storage serves as a scalable, durable alternative to HDFS). Since you're already using Apache Airflow to orchestrate ETL pipelines, Cloud Composer, which is based on Airflow, would fit well for maintaining your existing orchestration processes.
- Selected: This option allows you to lift and shift your existing Hadoop workloads to Google Cloud using Dataproc, ...
Author: Zara1234 · Last updated Jul 15, 2026
You recently deployed several data processing jobs into your Cloud Composer 2 environment. You notice that some tasks are failing in Apache Airflow. On the monitoring dashboard, you see an increase in the total workers memory usage, an...
In this scenario, where you’re seeing worker pod evictions and increased memory usage, the primary issue is likely related to resource allocation (particularly memory) and worker capacity. Here’s an analysis of the options and their appropriateness:
Option A: Increase the directed acyclic graph (DAG) file parsing interval
- Rejected: Increasing the DAG file parsing interval addresses how frequently Airflow parses DAG files to check for updates. While it might reduce the frequency of file parsing, it doesn't directly address the issue of memory usage or worker pod evictions. The problem here is more related to worker capacity and memory usage rather than DAG file parsing.
Option B: Increase the Cloud Composer 2 environment size from medium to large
- Partially Relevant, but Rejected: This could be a helpful option to increase the overall resources available to your environment. However, it’s not the most targeted approach. Increasing the environment size doesn't specifically address the memory issues related to worker pods. Scaling up your environment size might help by providing more general resources, but increasing memory allocation for workers or increasing worker count would more directly address the issue.
Option C: Increase the maximum number of workers and reduce worker concurrency
- Selected: Increasing the maximum number of workers ensures that more resources are available for task execution, helping mitigate the evictions due to insufficient worker capacity. Reducing worker concurrency can balance the number of tasks a worker processes at...
Author: Ming · Last updated Jul 15, 2026
You are on the data governance team and are implementing security requirements to deploy resources. You need to ensure that resources are limited to only the europe-west3 regi...
Option A: Set the constraints/gcp.resourceLocations organization policy constraint to in:europe-west3-locations
- Selected: This is the best solution and aligns with Google’s recommended practices for enforcing resource location restrictions. The `constraints/gcp.resourceLocations` policy allows you to set which regions are acceptable for resource deployment. By specifying `in:europe-west3-locations`, this ensures that all resources are constrained to the `europe-west3` region, preventing the creation of resources outside of it. This is a declarative and automated way to enforce regional restrictions across your organization, ensuring compliance without manual checks.
Option B: Deploy resources with Terraform and implement a variable validation rule to ensure that the region is set to the europe-west3 region for all resources
- Rejected: While this can be a good practice for ensuring resource deployment in the correct region during infrastructure provisioning, it is not the best approach for enforcing security requirements across the entire organization. Using Terraform to enforce a region might work for Terraform-managed resources but doesn’t provide organization-wide enforcement. Additionally, manual validation rules are prone to errors and may be bypassed or misconfigured, whereas an organization-wide policy is more enforceable.
Option C: Set the constraints/gcp.resourceLocations organization policy constraint to in:eu-locations
- Rejected: This option would restrict resources to any region within the European Union, not just `europe-west3`. While this is a geographically ...
Author: Alexander · Last updated Jul 15, 2026
You are a BigQuery admin supporting a team of data consumers who run ad hoc queries and downstream reporting in tools such as Looker. All data and users are combined under a single organizational project. You recently noticed some slowness in query results and want to troubleshoot where the slowdowns are occurring. You think that there might be some job queuing or slot contention occurring as u...
Option A: Use slot reservations for your project to ensure that you have enough query processing capacity and are able to allocate available slots to the slower queries
- Rejected: Slot reservations can help manage BigQuery's query processing capacity and prioritize certain queries, but this approach is more of a preventive measure for future resource allocation rather than troubleshooting existing performance issues. While it may solve slowdowns due to insufficient slots in the long run, it doesn’t help in investigating why queries are slow at this moment. Troubleshooting the current situation requires understanding the root cause (e.g., job queuing or slot contention), which is better accomplished through monitoring and query performance analysis.
Option B: Use Cloud Monitoring to view BigQuery metrics and set up alerts that let you know when a certain percentage of slots were used
- Partially Relevant, but Rejected: Cloud Monitoring can provide helpful metrics about BigQuery’s resource utilization (like slot usage), and setting up alerts is a proactive measure. However, for troubleshooting current performance issues, this option alone is not sufficient. It would help track when contention is occurring, but it doesn't provide detailed job-level insights or analysis on the specific queries that are affected by queuing or slot contention. You'd still need additional data to pinpoint the exact performance bottlenecks.
Option C: Use available administrative resource charts to determine how slots are being used and how jobs are performing over time. Run a query on the INFORMATION_SCHEMA to review query performance
- Selected: This option provides a direct and detailed way to investigate the performance of individual queries. Administrative resource charts and the INFORMATION_SCHEMA allow you to ...
Author: Nathan · Last updated Jul 15, 2026
You migrated a data backend for an application that serves 10 PB of historical product data for analytics. Only the last known state for a product, which is about 10 GB of data, needs to be served through an API to the other applications. You need to choose a cost-effective persistent storage solution that can accommodate th...
Option A: 1. Store the historical data in BigQuery for analytics. 2. Use a materialized view to precompute the last state of a product. 3. Serve the last state data directly from BigQuery to the API.
- Rejected: While BigQuery is an excellent choice for analytics and can handle large datasets efficiently, serving data directly from BigQuery to an API at high QPS (1000 queries per second) with low latency (<1 second) is not optimal. BigQuery is designed for batch processing and analytical queries, not for serving low-latency, high-throughput API requests. Even with a materialized view, querying data directly from BigQuery for real-time API responses could result in latency issues and cost inefficiencies, as BigQuery is typically more expensive for frequent small queries.
Option B: 1. Store the products as a collection in Firestore with each product having a set of historical changes. 2. Use simple and compound queries for analytics. 3. Serve the last state data directly from Firestore to the API.
- Partially Relevant, but Rejected: Firestore is a scalable NoSQL database designed for low-latency reads and high throughput, making it a good option for serving API requests. However, using Firestore to store both historical changes and querying for analytics could become costly and inefficient for large datasets (like 10 PB). Firestore is optimized for small-scale, document-oriented use cases rather than large-scale analytics. It also lacks the advanced analytical features of BigQuery, making it less suitable for the scale and complexity of the analytics needs in this scenario.
Option C: 1. Store the historical data in Cloud SQL for analytics. 2. In a separate table, store the last state of the product after every product change. 3. Serve the last state data directly from Cloud SQL to the API.
- Rejected: Cloud SQL is a good relational database solution for transactional data and simple analytics. However, serving the last state data directly from Cloud SQL at a high QPS with low latency (<1 second) could lead to performance bottlenecks as Cloud SQL might no...
Author: John · Last updated Jul 15, 2026
You want to schedule a number of sequential load and transformation jobs. Data files will be added to a Cloud Storage bucket by an upstream process. There is no fixed schedule for when the new data arrives. Next, a Dataproc job is triggered to perform some transformations and write the data to BigQuery. You then need to run additional transformation jobs in BigQuery. The transformation jobs are different for every table. These jobs might...
Option A: 1. Create an Apache Airflow directed acyclic graph (DAG) in Cloud Composer with sequential tasks by using the Cloud Storage, Dataproc, and BigQuery operators. 2. Use a single shared DAG for all tables that need to go through the pipeline. 3. Schedule the DAG to run hourly.
- Rejected: While using a single DAG for all tables sounds convenient, the lack of a fixed schedule for when data arrives (i.e., the data loading is event-driven) makes this approach less suitable. Running the DAG hourly does not efficiently respond to the real-time data arrivals, as you are depending on a fixed schedule regardless of whether new data has arrived. This would likely lead to unnecessary or redundant task executions and could introduce delays in processing the new data. Additionally, having a single shared DAG for hundreds of tables could lead to complexity and maintenance challenges over time.
Option B: 1. Create an Apache Airflow directed acyclic graph (DAG) in Cloud Composer with sequential tasks by using the Cloud Storage, Dataproc, and BigQuery operators. 2. Create a separate DAG for each table that needs to go through the pipeline. 3. Schedule the DAGs to run hourly.
- Rejected: Similar to Option A, this approach suffers from the same problem: it assumes data arrives on a fixed schedule (hourly), which is not the case here. While creating a separate DAG for each table can allow for better granularity and isolation, it introduces unnecessary complexity and overhead. If you're processing hundreds of tables, maintaining a separate DAG for each one would quickly become unmanageable. It also doesn’t address the event-driven nature of the data arrivals, making this approach inefficient.
Option C: 1. Create an Apache Airflow directed acyclic graph (DAG) in Cloud Composer with sequential tasks by using the Dataproc and BigQuery operators. 2. Use a single shared DAG for all tables that need to go through the pipeline. 3. Use a Cloud Storage object trigger to launch a Cloud Function that triggers the DAG.
- Selected: This option offers the most eff...
Author: Ravi Patel · Last updated Jul 15, 2026
You are deploying a MySQL database workload onto Cloud SQL. The database must be able to scale up to support several readers from various geographic regions. The database must be highly available and meet low RTO and RPO requirements, even in the event of a regional out...
Let's analyze each option to determine the best approach based on the requirement for minimal interruptions, high availability, low RTO (Recovery Time Objective), and low RPO (Recovery Point Objective):
Option A:
- Create a highly available Cloud SQL instance in region A. Create a highly available read replica in region B.
- Scale up read workloads by creating cascading read replicas in multiple regions.
- Backup the Cloud SQL instances to a multi-regional Cloud Storage bucket. Restore the Cloud SQL backup to a new instance in another region when Region A is down.
Analysis:
- Pros:
- Multi-regional read replicas help support geographic distribution of the database's read traffic.
- Backup to multi-regional Cloud Storage ensures that data is available for disaster recovery.
- Cons:
- Long RTO and RPO: The need to restore a backup when Region A goes down introduces a significant RTO and RPO. Restoring from backups is not fast enough to meet low RTO and RPO requirements.
- Not highly available during failover: During a regional failure, the system would be down until a new instance is restored, which violates the requirement of minimizing interruptions.
This option is not suitable due to high downtime during failover.
Option B:
- Create a highly available Cloud SQL instance in region A.
- Scale up read workloads by creating read replicas in multiple regions.
- Promote one of the read replicas when region A is down.
Analysis:
- Pros:
- Geographic distribution of read replicas helps with scaling and minimizing latency for readers.
- Promoting a read replica in another region can ensure the database continues to operate in case of a regional failure.
- Cons:
- Moderate RTO: The failover process involves promoting a read replica, which might take some time, but it is faster than restoring from backups.
- RPO issues: There could be data loss if the read replica is not fully synced with the primary instance at the time of the failure.
This option provides better availability than Option A, but there may still be some risk in terms of data consistency and the promotion process, which could impact RPO.
Option C:
-...
Author: James · Last updated Jul 15, 2026
You are planning to load some of your existing on-premises data into BigQuery on Google Cloud. You want to either stream or batch-load data, depending on your use case. Additionally, you want to mask some sensitive data before loading into B...
Let's break down each option to determine the best solution for your use case, which involves loading data into BigQuery while masking sensitive information programmatically and keeping costs minimal.
Option A:
- Use Cloud Data Fusion to design your pipeline, use the Cloud DLP plug-in to de-identify data within your pipeline, and then move the data into BigQuery.
Analysis:
- Pros:
- Cloud Data Fusion provides a graphical interface for building ETL pipelines, making it easy to design and manage data flows.
- The Cloud DLP plug-in can handle the de-identification of sensitive data during the pipeline processing.
- Cons:
- Higher costs: Cloud Data Fusion can be more expensive compared to simpler solutions like Dataflow, especially for smaller or less complex data processing needs.
- Complexity: While the tool is flexible, it might be overkill for simple data transformation and de-identification, adding unnecessary complexity and cost for relatively straightforward tasks.
This option might be suitable for larger, more complex data workflows, but it could introduce unnecessary overhead for simpler use cases.
Option B:
- Use the BigQuery Data Transfer Service to schedule your migration. After the data is populated in BigQuery, use the connection to the Cloud Data Loss Prevention (Cloud DLP) API to de-identify the necessary data.
Analysis:
- Pros:
- BigQuery Data Transfer Service is easy to use for scheduling data migrations from external systems (including on-premises).
- Cloud DLP can be used to de-identify sensitive data after it's loaded into BigQuery.
- Cons:
- De-identification after load: This approach requires the data to first be loaded into BigQuery, and then the de-identification process is applied. This can increase costs and introduce additional complexity.
- Inefficiency: Running DLP processes on a large dataset already in BigQuery can be more expensive than performing transformations during the load process itself.
This option is viable but inefficient, as it requires an additional step post-loading and incurs higher costs for processing data within BigQuery.
Option C:
- Create your pipeline with Dataflow thr...
Author: Aria · Last updated Jul 15, 2026
You want to encrypt the customer data stored in BigQuery. You need to implement per-user crypto-deletion on data stored in your tables. You want to adopt native f...
To address the requirements of encrypting customer data stored in BigQuery and implementing per-user crypto-deletion using native Google Cloud features, let's analyze each option:
Option A:
- Implement Authenticated Encryption with Associated Data (AEAD) BigQuery functions while storing your data in BigQuery.
Analysis:
- Pros:
- AEAD encryption in BigQuery would ensure that data is encrypted and only accessible by authorized users.
- This option focuses on encryption during storage within BigQuery, ensuring data confidentiality.
- Cons:
- Lack of per-user crypto-deletion: While AEAD encryption can provide secure encryption, it does not inherently support per-user crypto-deletion. AEAD focuses on encrypting data but does not provide a mechanism for selective data removal or key management at the user level.
- No native support for per-user key management: This option does not directly solve the need for per-user crypto-deletion, where data needs to be deleted or inaccessible based on individual users.
This option focuses on encryption but does not fully address the need for crypto-deletion at a user level, making it unsuitable.
Option B:
- Create a customer-managed encryption key (CMEK) in Cloud KMS. Associate the key to the table while creating the table.
Analysis:
- Pros:
- CMEK provides full control over encryption keys, offering a higher level of security and compliance.
- By associating the CMEK with the table, you ensure that all data in that table is encrypted with the specified key.
- Cons:
- No per-user crypto-deletion: Although CMEK gives control over the encryption keys, it does not provide the granularity needed for per-user crypto-deletion. You can manage keys for a table, but the key cannot be selectively deleted based on users, as the data is encrypted at the table level.
- Key management complexity: Managing keys at the table level does not address the need to delete or revoke access to specific user data dynamically.
While CMEK provides encryption control, it doesn't offer the per-user crypto-deletion functionality required in this case.
Option C:
- Create a customer-managed encryption key (CMEK) in Cloud KMS. Use the key to encrypt data before storing in BigQuery.
Analysis:
- Pros:
- CMEK encryption ensures that you retain control over the encryption keys.
- It supports encryption before data is loaded into BigQuery, offering strong protection during data stor...
Author: Zara1234 · Last updated Jul 15, 2026
The data analyst team at your company uses BigQuery for ad-hoc queries and scheduled SQL pipelines in a Google Cloud project with a slot reservation of 2000 slots. However, with the recent introduction of hundreds of new non time-sensitive SQL pipelines, the team is encountering frequent quota errors. You examine the logs and notice t...
Let's break down each option and determine which is the best fit for resolving the concurrency issue while keeping in mind factors like cost efficiency, query performance, and optimal resource management.
Option A:
- Increase the slot capacity of the project with baseline as 0 and maximum reservation size as 3000.
Analysis:
- Pros:
- Increased capacity: Increasing the slot reservation to 3000 would directly address the concurrent query issue by providing more slots for processing.
- Cons:
- Unpredictable cost: With a baseline of 0, the system will dynamically allocate up to 3000 slots based on demand. While this increases available capacity during peak times, it also opens the door for potentially high costs since you’re reserving a maximum without a guaranteed baseline allocation.
- Cost control issue: This option doesn’t provide a steady baseline of resources, meaning the project could hit a sudden, high cost if the demand spikes unexpectedly. Without a baseline, you may encounter over-provisioning, leading to inefficiencies and high costs.
This option might work in specific cases but could lead to cost unpredictability, making it less ideal for long-term use.
Option B:
- Update SQL pipelines to run as a batch query, and run ad-hoc queries as interactive query jobs.
Analysis:
- Pros:
- Batch queries: Converting SQL pipelines to batch queries helps manage resources efficiently since batch queries are queued and run sequentially, minimizing resource contention and potential concurrency issues.
- Interactive queries: Ad-hoc queries being run interactively ensures that users can perform queries as needed without impacting the performance of scheduled pipelines.
- Cons:
- Not solving the root issue: This option reorganizes the queries into different categories, but it doesn’t directly address the core issue of concurrency and slot reservation. You still may face high load during peak times.
- Increased wait times for batch jobs: Batch queries tend to be slower than interactive queries because they run in sequence, which could introduce delays for non-time-sensitive workloads.
This approach helps prioritize ad-hoc queries over pipelines but doesn’t resolve the fundamental slot reservation issue or concurrency problems during peak periods.
...
Author: Aria · Last updated Jul 15, 2026
You are designing a data mesh on Google Cloud by using Dataplex to manage data in BigQuery and Cloud Storage. You want to simplify data asset permissions. You are creating a customer virtual lake with two user groups:
* Data engineers, which require full data lake access
* Analyt...
To design a data mesh on Google Cloud using Dataplex and simplify data asset permissions while ensuring the correct access rights are assigned to different user groups, let's evaluate each option carefully based on the need to:
- Grant full data lake access to the Data Engineers.
- Grant access to curated data to the Analytic Users.
Option A:
- 1. Grant the dataplex.dataOwner role to the data engineer group on the customer data lake.
- 2. Grant the dataplex.dataReader role to the analytic user group on the customer curated zone.
Analysis:
- Pros:
- The dataplex.dataOwner role grants full access to the data lake, which is what Data Engineers require to manage and process data.
- The dataplex.dataReader role grants read-only access to the curated zone, which is ideal for Analytic users to access the data without modification rights.
- Cons:
- The dataplex.dataOwner role is very powerful, and it may grant more access than needed to the Data Engineers. However, this is generally acceptable since they need full control of the lake to manage the data and assets.
This option appropriately assigns the correct roles for both groups, ensuring proper access control with minimal complexity. It is a valid and ideal choice.
Option B:
- 1. Grant the dataplex.dataReader role to the data engineer group on the customer data lake.
- 2. Grant the dataplex.dataOwner role to the analytic user group on the customer curated zone.
Analysis:
- Pros:
- The dataplex.dataReader role for Data Engineers allows them to read the data lake, which is useful for them to analyze the data.
- The dataplex.dataOwner role for Analytic users on the curated zone would grant them full control over the curated data.
- Cons:
- Data Engineers require full access (Owner) to perform actions like data transformation and pipeline creation. Assigning them only read access (dataReader) will limit their ability to manage or work with the data lake, making this option unsuitable for Data Engineers' needs.
- Granting dataOwner to Analytic users on the curated zone is inappropriate since they only need read-only access, not the ability to modify or manage data assets.
This option is incorrect because it doesn't align with the correct access needs for Data Engineers and Analytic users.
Option C:
- 1. Grant the bigquery.dataOwner role on BigQuery datasets and the storage.objectCreator role on Cloud Storage buckets to data engineers.
- 2. Grant the bigquery.dataViewer role on BigQuery datasets and the storage.objectViewer role on Cloud Storage b...
Author: Sara · Last updated Jul 15, 2026
You are designing the architecture of your application to store data in Cloud Storage. Your application consists of pipelines that read data from a Cloud Storage bucket that contains raw data, and write the data to a second bucket after processing. You want to design an architecture with Cloud Storage resources that are capable of being resilient if a Google Cloud regional failu...
To design a resilient architecture that minimizes the recovery point objective (RPO) and ensures no impact on applications during a regional failure, let’s analyze each option:
A) Adopt multi-regional Cloud Storage buckets in your architecture.
- Explanation: Multi-regional buckets automatically replicate data across multiple Google Cloud regions. This ensures high availability and resiliency in case of a regional failure, as data will still be accessible from another region. Multi-regional buckets provide low latency and high availability, making them ideal for use cases where access speed and data redundancy are key.
- RPO Consideration: This option minimizes RPO as data is continuously replicated and available in different regions, which ensures near-zero data loss even in case of a regional outage.
- Drawback: Multi-regional buckets come with higher costs due to the increased replication and cross-region data transfer. This may be an overkill in some cost-sensitive scenarios.
B) Adopt two regional Cloud Storage buckets, and update your application to write the output on both buckets.
- Explanation: This option involves writing the same data to two different regional buckets. It helps with resiliency, as data is spread across different regions, but it requires your application to handle writes to two places.
- RPO Consideration: This approach can be effective for resilience. However, it may result in increased complexity for your application, especially if the data needs to be written at exactly the same time to both regions. It does not guarantee near-zero RPO if one of the regions is unavailable, as writes may fail depending on how the application handles failure scenarios.
- Drawback: Complexity in managing writes to both regions, and potential for data inconsistency if one region becomes unavailable. If your application does not have mechanisms to handle failures gracefully, it could impact availability.
C) Adopt a dual-region Cloud Storage bucket, and enable turbo replication in your architecture.
- Explanation: A dual-region Cloud Storage bucket is designed for high availability, a...
Author: Ella · Last updated Jul 15, 2026
You have designed an Apache Beam processing pipeline that reads from a Pub/Sub topic. The topic has a message retention duration of one day, and writes to a Cloud Storage bucket. You need to select a bucket location and processing stra...
To design a solution that ensures data durability with an RPO (Recovery Point Objective) of 15 minutes in the event of a regional outage, let's evaluate each option based on key factors such as data redundancy, RPO, recovery mechanisms, and the impact of regional outages.
A) 1. Use a dual-region Cloud Storage bucket.
2. Monitor Dataflow metrics with Cloud Monitoring to determine when an outage occurs.
3. Seek the subscription back in time by 15 minutes to recover the acknowledged messages.
4. Start the Dataflow job in a secondary region.
- Explanation:
- A dual-region Cloud Storage bucket is highly suitable for high availability as it replicates data across two regions. This will protect against a regional failure and ensure near-zero data loss.
- Monitoring Dataflow metrics and seeking back in time by 15 minutes would allow the pipeline to recover messages from Pub/Sub in the event of an outage, minimizing RPO.
- Starting the Dataflow job in a secondary region ensures that processing can continue without significant downtime during a regional failure.
- RPO Consideration: By seeking back only 15 minutes, this ensures that data loss will be minimal.
- Drawback: The solution is complex in terms of the need for detailed monitoring and manual recovery actions but is highly resilient for an RPO of 15 minutes.
B) 1. Use a multi-regional Cloud Storage bucket.
2. Monitor Dataflow metrics with Cloud Monitoring to determine when an outage occurs.
3. Seek the subscription back in time by 60 minutes to recover the acknowledged messages.
4. Start the Dataflow job in a secondary region.
- Explanation:
- A multi-regional Cloud Storage bucket ensures high availability by automatically replicating data across multiple regions. This protects against regional failures.
- Monitoring metrics and starting Dataflow in a secondary region are good steps for resiliency.
- However, seeking back 60 minutes for recovery is too long compared to the required RPO of 15 minutes. This results in a higher potential for data loss, as you would have missed up to 45 minutes of data.
- RPO Consideration: This does not meet the 15-minute RPO requirement, as the data loss window is much larger.
- Drawback: Longer recovery time increases RPO, which contradicts the objective of minimizing data loss to 15 minutes.
C) 1. Use a regional Cloud Storage bucket.
2. Monitor Dataflow metrics with Cloud Monitoring to det...
Author: VioletCheetah55 · Last updated Jul 15, 2026
You are preparing data that your machine learning team will use to train a model using BigQueryML. They want to predict the price per square foot of real estate. The training data has a column for the price and a column for the number of square feet. Another feature column called =E2=80=98feature1' conta...
When preparing data for machine learning, especially in cases where a feature column contains null values, it is essential to handle these nulls in a way that minimizes data loss without introducing potential bias or incorrect assumptions.
Let’s analyze the situation and the possible query options.
Query Explanation:
You want to replace null values in the `feature1` column with zeros to retain more data points, as null values might reduce the dataset size if removed entirely.
Possible SQL Approaches:
1. COALESCE Function:
- The `COALESCE` function is used to return the first non-null expression among its arguments. If `feature1` is null, it will be replaced with 0.
Example Query:
```sql
SELECT
price,
square_feet,
COALESCE(feature1, 0) AS feature1
FROM
your_table
```
Explanation:
- The `COALESCE` function effectively replaces `NULL` values in the `feature1` column with `0` and ensures that the query works well for both null and non-null entries.
- This approach is optimal because it handles missing data in a simple and efficient way without altering the dataset size, ensuring the model can use as much data as possible.
2. IFNULL Function:
- The `IFNULL` function works similarly to `COALESCE` but with only two arguments. It returns the first argument if it’s not null, and the second argument (in this case, `0`) if it is null.
Example Query:
```sql
SELECT
price,
square_feet,
IFNULL(feature1, 0) AS feature1
FROM
your_table
```
Explanation:
- `IFNULL` is equivalent to `COALESCE` when only two values are involved. It is simpler to use in cases where only two arguments are needed and works perfectly well for replacing `NULL` with `0`. It is just another approach, and is similar in terms of functionality to `COALESCE`.
3. Using CASE State...
Author: FlamePhoenix2025 · Last updated Jul 15, 2026
Different teams in your organization store customer and performance data in BigQuery. Each team needs to keep full control of their collected data, be able to query data within their projects, and be able to exchange their data with other teams. You need...
To address the scenario where different teams need full control over their own data, the ability to query their own data, and exchange data with others in a cost-effective and operationally simple manner, let’s evaluate each option:
A) Ask each team to create authorized views of their data. Grant the `bigquery.jobUser` role to each team.
- Explanation:
- Authorized views allow one project to grant access to specific datasets in another project without copying or replicating data. This maintains central control over the original data while enabling secure access to specific teams.
- Granting the `bigquery.jobUser` role enables teams to run queries and interact with BigQuery jobs in their project, but it does not provide access to the datasets themselves unless explicitly authorized through views.
- Why this is good:
- Low operational overhead: There is no need to replicate or duplicate data. Teams can query data across projects using authorized views.
- Cost-effective: No additional storage costs arise from data replication, as authorized views simply reference the data in the original project.
- Drawbacks:
- Requires setup of views, and there might be some complexity in managing the permissions, especially if there are a lot of teams or frequent changes in access needs.
- Limited access flexibility: Teams can only query data through authorized views and not directly access the underlying tables or perform other actions like updates.
B) Create a BigQuery scheduled query to replicate all customer data into team projects.
- Explanation:
- A scheduled query replicates data from one project to others on a regular basis.
- Why this is not ideal:
- Operational overhead: This method requires managing regular data replication tasks, which increases operational complexity.
- Cost: Data replication incurs storage and potentially additional costs due to the need to maintain copies of data in multiple projects.
- Potential for inconsistencies: This method introduces the risk of out-of-sync data, as the replication could fall behind or miss updates.
- Scalability issues: If there are many teams or large datasets, the replication setup could become difficult to manage and scale.
C) Ask each team to publish their data in Analytics Hub. Direct the other teams to subscribe to them.
- Explanation:
- Analytics Hub allows teams to share their datasets within an organization while retaining full control over their own data. Tea...
Author: Emma · Last updated Jul 15, 2026
You are developing a model to identify the factors that lead to sales conversions for your customers. You have completed processing your data. You want to conti...
To continue through the model development lifecycle, let’s evaluate the options based on the typical flow of machine learning model development, from data preparation through model evaluation, and on to deployment.
A) Use your model to run predictions on fresh customer input data.
- Explanation: This step is crucial in the deployment phase, where the model is used to make predictions on real-world data. However, this is not the immediate next step after data processing and model development. The model must first be thoroughly tested and evaluated before making predictions on fresh data.
- Why it’s not ideal: Running predictions prematurely without evaluating model performance can lead to incorrect conclusions, as you might be using a model that has not yet been validated.
B) Monitor your model performance, and make any adjustments needed.
- Explanation: Monitoring and adjusting the model is an ongoing process during and after deployment. It involves tracking how the model performs in production and updating it when necessary. However, this comes after the model has been tested and deployed.
- Why it’s not ideal: The model cannot be monitored or adjusted meaningfully until it has been fully tested and deployed, so this step should occur later in the lifecycle.
C) Delineate what data will be used for testing and what will be used for training the model.
- Explanation: This step is essential during the data preparation phase, particularly before training a model. It ensures that you have separate datasets for train...
Author: Zara · Last updated Jul 15, 2026
You have one BigQuery dataset which includes customers' street addresses. You want to retrieve all occurrences of str...
To retrieve all occurrences of street addresses from the dataset in BigQuery, let’s break down the options and evaluate each one:
Option A: Write a SQL query in BigQuery by using REGEXP_CONTAINS on all tables in your dataset to find rows where the word "street" appears.
- Pros:
- Can directly query the dataset and filter out rows where the word "street" appears in the address.
- Flexible and can be customized based on the query needs.
- Straightforward approach in BigQuery itself.
- Cons:
- This method relies on the word “street” appearing explicitly in the dataset, which could be inconsistent or inaccurate since addresses might not always contain the word "street."
- It may not account for variations such as “St,” “Street,” or "Ave." Additionally, it might miss variations in how street addresses are written or formatted (such as abbreviations).
Best used for: Simple keyword-based searches where you have more control over how data is structured and you're searching for specific terms in text fields.
Option B: Create a deep inspection job on each table in your dataset with Cloud Data Loss Prevention (DLP) and create an inspection template that includes the STREET_ADDRESS infoType.
- Pros:
- DLP can automatically detect sensitive information such as street addresses based on predefined patterns.
- Doesn’t rely on explicit keywords but uses the DLP service to understand address patterns.
- More reliable for detecting street addresses without needing to manually specify every possible variation of the word “street.”
- Cons:
- Requires setting up DLP jobs for each table, which can be cumbersome if the dataset is large.
- This is more focused on discovering sensitive information rather than simply querying data.
Best used for: Situations where sensitive data discovery (like addresses) needs to be handled comprehensively across multiple tables.
Option C: Create a discovery scan configuration on your organization with Cloud Da...
Author: Charlotte · Last updated Jul 15, 2026
Your company operates in three domains: airlines, hotels, and ride-hailing services. Each domain has two teams: analytics and data science, which create data assets in BigQuery with the help of a central data platform team. However, as each domain is evolving rapidly, the central data platform team is becoming a bottleneck. This is causing delays in deriving insights from data, and result...
To design a data mesh architecture using Dataplex that eliminates the bottleneck caused by the central data platform team, let’s analyze each option carefully:
Option A: 1. Create one lake for each team. Inside each lake, create one zone for each domain. 2. Attach each of the BigQuery datasets created by the individual teams as assets to the respective zone. 3. Have the central data platform team manage all zones' data assets.
- Pros:
- Centralized management by the data platform team might ensure consistency in data governance and quality across domains.
- Cons:
- The central data platform team remains a bottleneck, as they are managing all the zones’ data assets. This doesn’t solve the problem of delays in deriving insights and keeping data up to date.
- This approach still puts the responsibility on a single team for managing everything, preventing the desired flexibility and decentralization that a data mesh is meant to enable.
Best used for: Scenarios where centralized control is needed, but it doesn’t fit the goal of removing the bottleneck.
Option B: 1. Create one lake for each team. Inside each lake, create one zone for each domain. 2. Attach each of the BigQuery datasets created by the individual teams as assets to the respective zone. 3. Direct each domain to manage their own zones' data assets.
- Pros:
- Decentralizes the management of data assets. Each domain team manages their own data, which should reduce bottlenecks and allow teams to be more agile and responsive.
- Supports the concept of a data mesh where the responsibility of data ownership and management lies within the domain, promoting flexibility and speed.
- Cons:
- May require more governance to ensure consistency and standards are followed across teams and domains.
- Managing the zones within each lake can become cumbersome if not properly orchestrated, as each domain would have to implement their own processes for data management.
Best used for: Situations where decentralization is essential, and domain teams should have full control over their respective data assets.
Option C: 1. Create one lake for each domain. Inside each lake, create one zone for each team. 2. Atta...
Author: Sophia Clark · Last updated Jul 15, 2026
dataset.inventory_vm sample records:
You have an inventory of VM data stored in the BigQuery table. You want to prepare the data for regular reporting in the most cost-effective way. You...
To prepare the VM data in BigQuery for regular reporting, we want to exclude VM rows with fewer than 8 vCPU. Let’s break down each option based on key factors like cost-effectiveness, performance, and ease of implementation:
Option A: Create a view with a filter to drop rows with fewer than 8 vCPU, and use the UNNEST operator.
- Pros:
- Views in BigQuery are cost-effective because they don’t store data permanently, they just execute queries when accessed.
- A view can filter out unwanted rows (those with fewer than 8 vCPU), meaning we can directly query the filtered data.
- The use of UNNEST is typically used for handling nested or repeated fields. If the dataset involves such fields, this can be an appropriate operator to flatten them, but it's not necessary for this specific scenario unless the data is structured that way.
- Cons:
- The UNNEST operator may not be needed unless the dataset includes arrays or nested data structures.
- The performance of the view will depend on the size of the dataset, as it will query the full dataset each time, potentially increasing costs if the dataset is large.
Best used for: Simple filtering of rows with a small to moderate-sized dataset where data isn't nested.
Option B: Create a materialized view with a filter to drop rows with fewer than 8 vCPU, and use the WITH common table expression.
- Pros:
- Materialized views store precomputed results, which improves performance on frequently run queries. This is beneficial for regular reporting because it reduces query time and cost.
- WITH common table expression (CTE) can be used to write a more readable query but isn’t directly necessary here unless needed for further complex transformations.
- Cons:
- Materialized views incur storage costs since they store the results of the query. While this improves query performance, it may not be the most cost-effective option if data changes infrequently or if frequent updates are needed to the materialized view.
- Using a materialized view could be overkill for simply excluding rows based on a filter unless reporting speed is a top priority.
Best used for: Scenarios where reporting needs to be extr...
Author: Grace · Last updated Jul 15, 2026
Your team is building a data lake platform on Google Cloud. As a part of the data foundation design, you are planning to store all the raw data in Cloud Storage. You are expecting to ingest approximately 25 GB of data a day and your billing department is worried about the increasing cost of storing old data. The current business requirements are:
* The old data can be deleted anytime.
* There is no predefined access p...
To optimize the storage costs for your data lake on Google Cloud, we need to consider the following factors:
- Data Retrieval Charges: There should be no charges for retrieving old data.
- Instant Availability: The old data should be available instantly when accessed, meaning it should be readily accessible but at the lowest possible cost.
- Data Deletion: The old data can be deleted anytime, which means there is flexibility in lifecycle management.
Let’s analyze the options based on these requirements:
Option A: Create the bucket with the Autoclass storage class feature.
- Pros:
- Autoclass automatically moves data between storage classes (like Nearline, Coldline, Archive) based on usage patterns. This means that it will optimize the data storage cost dynamically.
- No need for manual configuration or lifecycle management policies.
- Cons:
- Autoclass is designed for dynamic optimization, but your specific business requirement emphasizes no predefined access pattern for the old data. If data isn’t accessed for a while, it may get moved to a lower-cost storage class (e.g., Coldline, Archive), but Autoclass may not be ideal for data that is expected to be deleted soon. It can still move data into storage classes that incur retrieval charges when accessed.
- The ability to instantly retrieve old data might be compromised depending on which storage class Autoclass moves the data to, which may not align perfectly with your requirements for instant access without retrieval charges.
Best used for: Scenarios where there are variable access patterns over time and automatic storage optimization is required.
Option B: Create an Object Lifecycle Management policy to modify the storage class for data older than 30 days to nearline, 90 days to coldline, and 365 days to archive storage class. Delete old data as needed.
- Pros:
- Lifecycle management policies provide a clear, predefined structure for managing the data over time and will move old data to lower-cost storage classes (Coldline, Archive) as it ages.
- Data older than 365 days will be moved to Archive, which is the most cost-effective storage class for infrequently accessed data.
- Since the data can be deleted as needed, the lifecycle management will help keep costs low by automatically removing the data after a certain period.
- Cons:
- Coldline and Archive storage classes incur retrieval charges, which conflicts with the requirement to avoid any retrieval charges.
- Coldline storage is designed for infrequent access, and Archive storage is for long-term cold storage. These options ...
Author: Lucas · Last updated Jul 15, 2026
Your company's data platform ingests CSV file dumps of booking and user profile data from upstream sources into Cloud Storage. The data analyst team wants to join these datasets on the email field available in both the datasets to perform analysis. However, personally identifiable information (PII) should not be accessible to t...
The task at hand is to de-identify the email field in both datasets before they are loaded into BigQuery for analysis, ensuring that personally identifiable information (PII) is not accessible to analysts. Let's evaluate the options based on key factors: data privacy (de-identification), ease of implementation, and alignment with requirements.
Option A: 1. Create a pipeline to de-identify the email field by using recordTransformations in Cloud Data Loss Prevention (Cloud DLP) with masking as the de-identification transformations type. 2. Load the booking and user profile data into a BigQuery table.
- Pros:
- Cloud DLP offers powerful de-identification capabilities, and masking is a standard transformation type for replacing sensitive data with a generic placeholder.
- This option focuses on de-identification at the point of ingestion, ensuring PII is not stored in the original form.
- Cons:
- Masking does not retain the ability to perform meaningful analysis on the email field. Masked data typically results in the email being completely obfuscated (e.g., "xxxx@xxxx.com"), which is fine for privacy but prevents joining on this field because the data is not usable anymore.
- Analysts would not be able to perform joins based on the email field since the original values are replaced with masks.
Best used for: When the email field should be completely obfuscated for privacy but without the need for analysis or joining based on the original email values.
Option B: 1. Create a pipeline to de-identify the email field by using recordTransformations in Cloud DLP with format-preserving encryption with FFX as the de-identification transformation type. 2. Load the booking and user profile data into a BigQuery table.
- Pros:
- Format-preserving encryption (FFX) allows you to encrypt the email field while maintaining its format, meaning the email addresses will still appear as valid email addresses.
- This encryption preserves the ability to join on the email field because the transformation is reversible and consistent, allowing secure analysis while maintaining privacy.
- FFX is useful when analysts need to perform operations or analysis that require using the original structure of the data but not the actual value.
- Cons:
- FFX encryption is more complex to implement than simple masking and requires careful key management to ensure proper decryption by authorized parties.
- Though it preserves the format, the original email addresses are still somewhat hidden and would need proper access control to ensure PII protection.
Best used for: When the email field needs to be usable for analysis and joining while ensuring that the actual values are not exposed, such as in cases where reversible encryption is appropriate.
Option C: 1. Load the CSV files from Cloud Storage into a BigQuery table, ...
Author: Max · Last updated Jul 15, 2026
You have important legal hold documents in a Cloud Storage bucket. You need to ensure that these documents a...
In this scenario, the objective is to ensure that important legal hold documents cannot be deleted or modified in a Cloud Storage bucket. We need to look for a solution that offers protection from both deletion and modification.
Option A: Set a retention policy. Lock the retention policy.
- Explanation: A retention policy ensures that objects cannot be deleted before a specified retention period expires. Locking the retention policy makes this policy immutable, meaning no one can modify or delete the retention policy itself or the objects until the retention period ends. This is an ideal option for ensuring compliance with legal hold requirements, as it makes sure the documents are not altered or deleted.
- Why Rejected: This option is not rejected; in fact, it is highly suitable for this scenario as it directly addresses the need for preventing deletion and modification of legal hold documents.
Option B: Set a retention policy. Set the default storage class to Archive for long-term digital preservation.
- Explanation: A retention policy will help prevent deletion, but setting the default storage class to Archive mainly optimizes for cost-effective long-term storage rather than preventing modifications. The Archive class is useful for long-term storage but doesn't necessarily help with the protection against deletion or modification.
- Why Rejected: This option doesn't fully address the legal hold requirement of preventing both deletion and modification. It's ...
Author: Max · Last updated Jul 15, 2026
You are designing a data warehouse in BigQuery to analyze sales data for a telecommunication service provider. You need to create a data model for customers, products, and subscriptions. All customers, products, and subscriptions can be updated monthly, but you must maintain a historical record of all data. You plan to use the visualization...
When designing a data model for a data warehouse in BigQuery to analyze sales data, the goal is to ensure that the model is simple, easy-to-use, and cost-effective, while maintaining a historical record of all data for analysis. Let’s examine each option:
Option A: Create a normalized model with tables for each entity. Use snapshots before updates to track historical data.
- Explanation: A normalized model breaks the data into distinct tables (e.g., one for customers, one for products, one for subscriptions) and uses relationships between them to ensure data integrity. Snapshots before updates allow you to capture the state of the data at specific points in time, maintaining a historical record.
- Why Rejected: While normalized models are useful for reducing redundancy and ensuring data integrity, they can be more complex to query and may not be as cost-efficient in BigQuery due to the need for multiple joins. Additionally, using snapshots before every update can lead to higher storage costs and complexity in managing and querying historical data.
Option B: Create a normalized model with tables for each entity. Keep all input files in a Cloud Storage bucket to track historical data.
- Explanation: This option suggests storing raw input data in Cloud Storage and using a normalized model in BigQuery for analysis. Storing input files in Cloud Storage can help maintain a historical record of raw data.
- Why Rejected: This option introduces extra complexity in managing raw input files in Cloud Storage. It doesn’t directly address how to store and maintain historical data within BigQuery. While raw files can be useful for auditing purposes, this approach is less efficient compared to a more structured approach where historical data is maintained directly within the data warehouse.
Option C: Create a denormalized model with nested and repeated fields. U...
Author: Maya · Last updated Jul 15, 2026
You are deploying a batch pipeline in Dataflow. This pipeline reads data from Cloud Storage, transforms the data, and then writes the data into BigQuery. The security team has enabled an organizational constraint in Google Cloud, requiring all Co...
In this scenario, you are deploying a batch pipeline in Dataflow, and your security team has enabled an organizational constraint to prevent Compute Engine instances from using external IP addresses. This means you need to ensure that Dataflow workers are using only internal IP addresses to access both Cloud Storage and BigQuery. Let's evaluate each option:
Option A: Ensure that your workers have network tags to access Cloud Storage and BigQuery. Use Dataflow with only internal IP addresses.
- Explanation: Network tags help identify instances for firewall rule configuration, but simply using tags may not ensure that Dataflow workers can access Cloud Storage and BigQuery without external IP addresses. This doesn't fully address the need for private access, as it doesn't directly mention configurations like Private Google Access or VPC peering.
- Why Rejected: While network tags are useful for setting up firewall rules, they don't guarantee that the workers can access the required services (Cloud Storage and BigQuery) via internal IPs only. It lacks details about how Dataflow should interact with Google Cloud services without external IPs.
Option B: Ensure that the firewall rules allow access to Cloud Storage and BigQuery. Use Dataflow with only internal IPs.
- Explanation: This option ensures that the firewall rules are correctly configured to allow traffic from internal IP addresses to Cloud Storage and BigQuery. However, while this is a good start, it still doesn't address the need for ensuring that Dataflow can access Google Cloud services without using external IPs.
- Why Rejected: Although firewall rules are important for access control, simply modifying them without enabling Private Google Access or setting up VPC peering doesn't guarantee proper internal access for Dataflow to Cloud Storage and BigQuery. Therefore, this option doesn’t fully meet the requirement.
Option C: Create a...
Author: Olivia Johnson · Last updated Jul 15, 2026
You are running a Dataflow streaming pipeline, with Streaming Engine and Horizontal Autoscaling enabled. You have set the maximum number of workers to 1000. The input of your pipeline is Pub/Sub messages with notifications from Cloud Storage. One of the pipeline transforms reads CSV files and emits an element for every CSV line. The job performance is low, ...
In this scenario, you are running a streaming pipeline with Dataflow, and the performance is low even though Horizontal Autoscaling is enabled and the maximum number of workers is set to 1000. Despite this, only 10 workers are being used, and the autoscaler isn't scaling up the worker count. Let's evaluate each option based on the key factors:
Option A: Enable Vertical Autoscaling to let the pipeline use larger workers.
- Explanation: Vertical autoscaling adjusts the size of individual workers, making them more powerful to handle larger amounts of data or complex operations. This option could help when the worker nodes are too small to handle the processing load.
- Why Rejected: In this case, the problem isn't necessarily that the workers are too small but that the pipeline isn’t utilizing enough workers. Enabling vertical autoscaling might improve worker capacity but doesn’t address the core issue of the autoscaler not scaling out the worker count. The bottleneck is likely in the autoscaler not being able to scale horizontally, not the individual worker capacity.
Option B: Change the pipeline code, and introduce a Reshuffle step to prevent fusion.
- Explanation: Fusion in Dataflow refers to combining multiple steps of the pipeline into a single step to optimize performance. However, this can also result in performance bottlenecks if not managed correctly. Adding a `Reshuffle` step breaks up the fusion, potentially allowing for more parallelism and better performance.
- Why Selected: This is the most likely option to improve performance. Fusion can cause a bottleneck where multiple transforms are fused into a single step, preventing horizontal scaling. By adding a `Reshuffle` step, you can increase parallelism, and it can help Dataflow split the work across more workers. Th...
Author: Andrew · Last updated Jul 15, 2026
You have an Oracle database deployed in a VM as part of a Virtual Private Cloud (VPC) network. You want to replicate and continuously synchronize 50 tables to BigQuery. Y...
The goal is to replicate and continuously synchronize 50 tables from an Oracle database to BigQuery while minimizing infrastructure management. Let's evaluate each option:
Option A: Deploy Apache Kafka in the same VPC network, use Kafka Connect Oracle Change Data Capture (CDC), and Dataflow to stream the Kafka topic to BigQuery.
- Explanation: This solution involves deploying Apache Kafka in the VPC, using Kafka Connect with the Oracle CDC connector to capture changes from Oracle, and then using Dataflow to stream data from Kafka to BigQuery.
- Why Rejected: This solution is quite complex and requires managing both Kafka and Dataflow infrastructure. Although it can handle large-scale, real-time data replication, managing Kafka and the CDC process adds significant overhead. If the goal is to minimize infrastructure management, this option is not ideal because of the complexity involved in maintaining Kafka, CDC connectors, and Dataflow.
Option B: Create a Pub/Sub subscription to write to BigQuery directly. Deploy the Debezium Oracle connector to capture changes in the Oracle database, and sink to the Pub/Sub topic.
- Explanation: This solution uses the Debezium Oracle connector to capture changes from Oracle and writes the changes to a Pub/Sub topic, which then streams directly into BigQuery. Pub/Sub is a fully managed service that reduces infrastructure overhead.
- Why Rejected: While this option reduces infrastructure management by using fully managed services (Pub/Sub, BigQuery), it still requires setting up and managing the Debezium connector manually. Additionally, managing schema changes and handling data in a way that's efficient for BigQuery might require extra configuration and oversight, making it more complex than needed.
...
Author: Isabella · Last updated Jul 15, 2026
You are deploying an Apache Airflow directed acyclic graph (DAG) in a Cloud Composer 2 instance. You have incoming files in a Cloud Storage bucket that the DAG processes, one file at a time. The Cloud Composer instance is deployed in a subnetwork with no Internet access. Instead of running ...
To choose the best option for your use case, let's break down the requirements and evaluate each choice:
Key Requirements:
1. No Internet Access in the Subnetwork – The Cloud Composer instance is in a subnetwork with no direct internet access, so we need methods that work in an isolated environment.
2. Reacting to New Files in Cloud Storage – You want to trigger the DAG when a new file arrives in Cloud Storage.
3. No Scheduled Runs – Instead of running the DAG on a fixed schedule, you want to trigger it based on an event (file arrival).
Evaluation of Options:
A) Enable Private Google Access, Cloud Storage Notifications to Pub/Sub, and Push Subscription to Web Server URL
- Private Google Access enables your resources in a private subnetwork to reach Google APIs. However, setting up Cloud Storage notifications to a Pub/Sub topic and then pushing to the web server URL isn't feasible here because of the lack of internet access.
- Without internet access, the push to the web server URL wouldn't work unless you have access to Google APIs via Private Google Access, which wouldn't be sufficient for direct HTTP(S) requests to the Composer's web server URL.
Rejected: The push subscription to the web server URL cannot work without internet access.
B) Enable Cloud Composer API, Cloud Storage Notifications to Cloud Function, Cloud Function Calls DAG via Cloud Composer API
- The Cloud Composer API can be used to trigger DAGs, and Cloud Functions can call this API.
- However, VPC Serverless Access is needed to make the Cloud Function call reach the Cloud Composer web server, but since the subnetwork has no internet access, the VPC Serverless Access is unnecessary and impractical unless it’s a...
Author: Deepak · Last updated Jul 15, 2026
You are planning to use Cloud Storage as part of your data lake solution. The Cloud Storage bucket will contain objects ingested from external systems. Each object will be ingested once, and the access patterns of individual objects will be random. You want to minimize the cost of storing and retrieving the...
To select the best option, we need to evaluate the requirements and key factors involved in the data lake storage solution:
Key Requirements:
1. Objects are ingested once – Each object will be stored in Cloud Storage without frequent updates, suggesting minimal modifications to objects after their ingestion.
2. Random access patterns – The objects will be accessed randomly, meaning we should prioritize storage solutions that allow fast retrieval when needed.
3. Cost minimization – The goal is to minimize the cost of storing and retrieving the objects.
4. Transparency – Any cost optimization should be transparent to users and applications, meaning minimal disruption and no need for manual intervention once the system is set up.
Evaluation of Options:
A) Create a Cloud Storage bucket with Autoclass enabled
- Autoclass automatically transitions objects between storage classes based on their access patterns, making it an ideal choice for random access patterns. Autoclass will keep frequently accessed objects in the Standard storage class, while less frequently accessed objects will automatically transition to more cost-efficient storage classes, such as Nearline, Coldline, or Archive.
- This approach optimizes storage costs while being transparent to users, as the transition between storage classes happens automatically based on access frequency.
- Best fit: This meets the needs for random access and cost minimization, with automatic transitions.
Selected option: A is the best choice because it efficiently minimizes costs by automatically adjusting the storage class based on object access patterns, and it is transparent to users and applications.
B) Create a Cloud Storage bucket with an Object Lifecycle Management policy to transition objects from Standard to Coldline storage class if an object age reaches 30 days
- A Lifecycle Management policy can transition objects based on age, which would help reduce costs by moving older objects to Coldline. However, this option assumes objects become infrequently ...
Author: Emma · Last updated Jul 15, 2026
You have several different file type data sources, such as Apache Parquet and CSV. You want to store the data in Cloud Storage. You need to set up an object sink for your data that allows you to us...
To determine the best approach for storing data in Cloud Storage with your own encryption keys and a GUI-based solution, let's break down the key requirements and evaluate the options:
Key Requirements:
1. Use your own encryption keys – You need to ensure that you can use custom encryption keys for the data stored in Cloud Storage.
2. GUI-based solution – A graphical user interface (GUI) should be available to make the setup and management easier, rather than a command-line interface (CLI).
3. Multiple file types (Parquet, CSV) – The solution should support multiple file types such as Apache Parquet and CSV, which are common formats for data storage.
4. Data movement to Cloud Storage – You want to move your data to Cloud Storage, rather than any other service like BigQuery.
Evaluation of Options:
A) Use Storage Transfer Service to move files into Cloud Storage
- Storage Transfer Service is a managed service for transferring data from external locations (like other cloud storage services or on-premise) to Cloud Storage. While it supports custom encryption keys during the transfer process, Storage Transfer Service doesn’t offer much flexibility or GUI-based configuration when working with file types like Apache Parquet and CSV. It is generally used for transferring large amounts of data, not necessarily for a range of file types in a more flexible pipeline.
- Rejected: It’s not tailored for varied file types or a GUI-based solution with flexible data handling (Parquet, CSV).
B) Use Cloud Data Fusion to move files into Cloud Storage
- Cloud Data Fusion is a fully managed, GUI-based data integration service that allows you ...
Author: Sophia · Last updated Jul 15, 2026
Your business users need a way to clean and prepare data before using the data for analysis. Your business users are less technically savvy and prefer to work with graphical user interfaces to define their transformations. After the data has been transformed, the business users want t...
To determine the best solution for cleaning and preparing data for business users, let's break down the requirements and evaluate the options:
Key Requirements:
1. Non-technical users – Business users are less technically savvy, so they prefer solutions with graphical user interfaces (GUIs) to define data transformations.
2. Data Cleaning and Preparation – Users need a way to clean and transform data before analysis.
3. Spreadsheet Integration – After the data has been prepared, users want to analyze the data directly in a spreadsheet.
4. Simplicity and Transparency – The solution should be easy for users to use without needing technical expertise.
Evaluation of Options:
A) Use Dataprep to clean the data, and write the results to BigQuery. Analyze the data by using Connected Sheets.
- Dataprep (now part of Dataprep by Trifacta) is a user-friendly, graphical data preparation tool designed specifically for business users. It provides an intuitive, GUI-based interface to clean and transform data.
- After cleaning the data, BigQuery is used as the storage solution, which can store large datasets.
- Connected Sheets allows users to analyze data stored in BigQuery directly from Google Sheets. This integrates seamlessly with spreadsheets and is ideal for non-technical users.
- Best Fit: This solution meets all the requirements perfectly. Users can clean the data in Dataprep, store it in BigQuery, and then analyze it in a familiar spreadsheet interface (Google Sheets), making it accessible and simple for them.
Selected option: A is the best choice because it provides a GUI-based solution for cleaning data (Dataprep), stores the results in BigQuery, and allows users to analyze the data directly in Google Sheets, which is their preferred tool.
B) Use Dataprep to clean the data, and write the results to BigQuery. Analyze the data by using Looker Studio.
- While Dataprep provides the necessary data transformation ...
Author: Amira99 · Last updated Jul 15, 2026
You have two projects where you run BigQuery jobs:
* One project runs production jobs that have strict completion time SLAs. These are high priority jobs that must have the required compute resources available when needed. These jobs generally never go below a 300 slot utilization, but occasionally spike up an additional 500 slots.
* The other project is for users to run ad-hoc analytical queries. This project generally never uses more than 200 slots at a time. You want th...
To select the best option, let's evaluate the requirements for each project and how the options align with those needs:
Key Requirements:
1. SLA Project (Production Jobs):
- High priority jobs that must have 300 slots guaranteed and the ability to scale up to 500 slots when needed.
- Strict completion time SLAs require that compute resources are available as needed.
- The slot reservation should guarantee resources are available for the job, and the scaling should be handled automatically to accommodate spikes.
2. Ad-hoc Project (Analytical Queries):
- These are ad-hoc queries and should be billed based on data scanned, not by slot capacity.
- The ad-hoc project rarely uses more than 200 slots and should be able to scale dynamically when needed, but it shouldn't be reserved for fixed compute resources.
Evaluation of Options:
A) Create a single Enterprise Edition reservation for both projects. Set a baseline of 300 slots. Enable autoscaling up to 700 slots.
- This solution puts both projects into a single reservation, which could lead to resource contention between the high-priority SLA jobs and the lower-priority ad-hoc queries.
- SLA Project requirements are met (300 slots with scaling), but the ad-hoc project would be stuck with the same reservation, which is not ideal since you want the ad-hoc queries to be billed by data scanned, not by slot capacity.
- Rejected: This option does not separate the two projects properly, especially when it comes to billing ad-hoc queries by data scanned.
B) Create two reservations, one for each of the projects. For the SLA project, use an Enterprise Edition with a baseline of 300 slots and enable autoscaling up to 500 slots. For the ad-hoc project, configure on-demand billing.
- This option creates two separate reservations for the two projects, which is ideal for ensuring that the SLA project gets the dedicated resources it needs while also allowing the ad-hoc project to be billed based on data scanned (on-demand).
- The SLA project gets the necessary baseline of 300 slots and can autoscale up to 500 slots as required.
- The ad-hoc project will be billed based on data scanned without reserving specific slots, which aligns with the...
Author: RadiantJaguar56 · Last updated Jul 15, 2026
You want to migrate your existing Teradata data warehouse to BigQuery. You want to move the historical data to BigQuery by using the most efficient method that requires the least amount of programming, ...
When migrating data from Teradata to BigQuery, the goal is to choose the most efficient method in terms of programming, storage, and performance. Below, I will analyze each option and why some may be better suited for the scenario where local storage space on the existing data warehouse is limited:
A) Use BigQuery Data Transfer Service by using the Java Database Connectivity (JDBC) driver with FastExport connection
- Pros:
- The BigQuery Data Transfer Service (DTS) can simplify the migration process by providing an automated approach to load data from Teradata to BigQuery.
- JDBC allows direct connection to Teradata and can help move data efficiently.
- Cons:
- This approach requires that Teradata supports the JDBC connection and FastExport, which can be resource-heavy on the Teradata system.
- If local storage is limited, the size of the data being exported through JDBC could overwhelm the available capacity.
- The complexity of configuring and maintaining the JDBC connection could require significant programming effort.
Not recommended: The dependency on JDBC for data export could be inefficient and add complexity in dealing with large datasets, especially with storage constraints.
B) Create a Teradata Parallel Transporter (TPT) export script to export the historical data, and import to BigQuery by using the bq command-line tool
- Pros:
- Teradata Parallel Transporter (TPT) is a high-performance tool designed to move large amounts of data efficiently.
- Using the `bq` command-line tool allows direct interaction with BigQuery and the automation of data imports.
- Cons:
- TPT typically requires more local disk space on the Teradata system to store exported data before transferring it to BigQuery.
- Since local storage is limited, this could cause issues with storing large datasets before migration.
Not recommended: TPT could require a considerable amount of local storage for temporary export files, which is problematic ...
Author: Sofia · Last updated Jul 15, 2026
You are on the data governance team and are implementing security requirements. You need to encrypt all your data in BigQuery by using an encryption key managed by your team. You must implement a mechanism to generate and store encryption material only on you...
When implementing security requirements for encrypting data in BigQuery with encryption material stored only on on-premises hardware security modules (HSM), it's essential to choose a solution that complies with your team's need to store and manage the keys securely, while also leveraging Google Cloud's managed services for integration and ease of use. Let’s break down the options:
A) Create the encryption key in the on-premises HSM, and import it into a Cloud Key Management Service (Cloud KMS) key. Associate the created Cloud KMS key while creating the BigQuery resources.
- Pros:
- Cloud KMS allows you to easily manage keys for encryption and integrate them into BigQuery resources. It’s fully managed by Google Cloud.
- You can import keys from your on-premises HSM into Cloud KMS, and it provides a unified interface to use the keys in BigQuery.
- Cons:
- Cloud KMS will store and manage the encryption key once it’s imported, which is not aligned with the requirement that encryption material should only reside in the on-premises HSM.
Not recommended: Cloud KMS will manage the key after import, which violates the requirement to store the key only in your on-premises HSM.
B) Create the encryption key in the on-premises HSM and link it to a Cloud External Key Manager (Cloud EKM) key. Associate the created Cloud KMS key while creating the BigQuery resources.
- Pros:
- Cloud External Key Manager (Cloud EKM) allows you to use your own HSM for key storage and management while letting Google Cloud services access the key.
- The Cloud EKM solution maintains control over the key material on your on-premises HSM, while allowing integration with Google services.
- This option provides the best of both worlds: keeping key management on your own hardware while leveraging Google Cloud services for key use.
- Cons:
- Requires additional setup for Cloud EKM and may have some complexities in integration, but this is part of maintaining contr...
Author: Chloe · Last updated Jul 15, 2026
You maintain ETL pipelines. You notice that a streaming pipeline running on Dataflow is taking a long time to process incoming data, which causes output delays. You also noticed that the pipeline graph was automatically optimized by Dataflow and m...
When you're experiencing delays in a streaming pipeline on Dataflow, it’s important to identify where the bottleneck is happening. Each option here targets a different aspect of troubleshooting. Let's analyze each one to determine which is the best approach:
A) Insert a Reshuffle operation after each processing step, and monitor the execution details in the Dataflow console.
- Pros:
- Reshuffling can help redistribute data more evenly across workers, especially if data is skewed or if certain workers are overburdened with specific keys.
- Monitoring execution details in the Dataflow console can help identify which parts of the pipeline are taking longer than expected.
- Cons:
- Introducing a Reshuffle operation after every processing step could add unnecessary overhead. While reshuffling helps with skewed data, it’s not the first thing to try when looking for bottlenecks. This could also complicate the pipeline unnecessarily.
Not recommended: The reshuffling can introduce additional costs and overhead. It’s better to first focus on observing and logging to isolate where the delays are happening before applying transformations like reshuffling.
B) Insert output sinks after each key processing step, and observe the writing throughput of each block.
- Pros:
- This option helps identify if the bottleneck is occurring at the writing phase (i.e., when data is being sent to the output sinks).
- By measuring the throughput after each step, you can see if a particular output step is slow and if it’s affecting the pipeline’s overall performance.
- Cons:
- While useful for identifying issues in writing throughput, it doesn't address potential bottlenecks within the processing steps themselves.
- Adding sinks after every step can significantly slow down the pipeline and create unnecessary output, making it harder to identify the true root cause.
Not recommended: It's better to focus on performance within the processing steps first. Output sinks after each step are excessive and might not directly point to the bottleneck if it’s...
Author: Jack · Last updated Jul 15, 2026
You are running your BigQuery project in the on-demand billing model and are executing a change data capture (CDC) process that ingests data. The CDC process loads 1 GB of data every 10 minutes into a temporary table, and then performs a merge into a 10 TB target table. This process is very scan intensive and you want to explore options to enable a predictable cost model. You need ...
When working with a change data capture (CDC) process in BigQuery that involves frequent data loads (1 GB every 10 minutes) and large, scan-intensive operations (e.g., merging into a 10 TB target table), it’s important to find a cost-effective and predictable way to allocate resources. BigQuery reservations allow you to allocate dedicated resources for workloads and manage costs more predictably. Let's evaluate each option and determine which one is most suitable.
A) Create a BigQuery reservation for the dataset
- Pros:
- Dataset-level reservations are not currently a valid option in BigQuery. Reservations apply to jobs and projects, not directly to datasets.
- Cons:
- This option does not align with how reservations are structured in BigQuery.
Not recommended: Since BigQuery does not allow dataset-specific reservations, this option is not feasible.
B) Create a BigQuery reservation for the job
- Pros:
- BigQuery reservations are applied at the job level, meaning you can allocate resources (slots) to specific queries or jobs, ensuring that the CDC process and merge operations get consistent and predictable resources.
- Cons:
- Jobs are typically short-lived, and this approach would require dynamically assigning resources every time the CDC job runs, which can be cumbersome.
Not recommended: While job-level reservations could provide predictability, it's better to apply reservations at the project level to ensure that all jobs (especially the frequent CDC loads) benefit from consistent resources.
C) Create a BigQuery reservation for the service account running the job
- Pros:
- ...
Author: BlazingPhoenix22 · Last updated Jul 15, 2026
You are designing a fault-tolerant architecture to store data in a regional BigQuery dataset. You need to ensure that your application is able to recover from a corruption event in your tables that occurred within the past seven days. You w...
When designing a fault-tolerant architecture for BigQuery with a focus on data recovery and minimizing data loss, it's essential to consider options that align with managed services, low recovery point objectives (RPO), and cost-effectiveness. Let's examine each option and determine the best fit.
A) Access historical data by using time travel in BigQuery
- Pros:
- BigQuery's time travel feature allows you to query historical data from up to 7 days ago, which is exactly what you need for recovering data that was corrupted within the past seven days.
- It provides a low RPO because it enables you to restore data directly from BigQuery without requiring external backups or manual intervention.
- This solution is managed by Google Cloud and doesn’t incur significant additional costs beyond storage.
- Cons:
- The primary limitation is that time travel is only available for 7 days, and any data loss beyond that timeframe cannot be recovered.
- If data corruption extends beyond 7 days, this option would no longer be viable, but it covers the stated requirement.
Recommended option: Time travel in BigQuery is a cost-effective and managed solution that meets both your low RPO and fault-tolerance requirements. It is the best choice for recovering from recent data corruption.
B) Export the data from BigQuery into a new table that excludes the corrupted data
- Pros:
- Exporting data to a new table allows you to create a backup of the data while excluding corrupted records.
- Cons:
- This is a manual solution that requires effort to detect and exclude corrupted data.
- It introduces additional complexity because you'd need to create and manage these export processes. Also, it doesn't guarantee a quick recovery if corruption occurs, and you have to manage the backup files.
- It's not a fully managed or seamless solution, and it could incur additional costs for exporting data.
Not recommended: This approach is more labor-intensive and not as cost-effective as using BigQuery’s built-in features for fault tolerance, like time travel.
C...
Author: Charlotte · Last updated Jul 15, 2026
You are building a streaming Dataflow pipeline that ingests noise level data from hundreds of sensors placed near construction sites across a city. The sensors measure noise level every ten seconds, and send that data to the pipeline when levels reach above 70 dBA. You need to detect the average noise level from a sensor when d...
To select the best windowing strategy for this streaming Dataflow pipeline, we need to evaluate the scenario based on the following criteria:
1. Noise levels should be detected continuously for 30 minutes: This means that when data arrives, we need to track it for a span of at least 30 minutes, and the window should close if there is no new data after 15 minutes.
2. Window ends if no data has been received for 15 minutes: This implies that the window should not extend beyond 15 minutes without receiving new data from the sensor.
Let’s go through the options:
A) Use session windows with a 15-minute gap duration:
- Session windows are ideal for data streams with irregular, time-varying intervals. A session window will group data when the difference between events is smaller than the gap duration. If there is a gap greater than the gap duration (15 minutes), the window will close.
- In this case, a 15-minute gap is too small to ensure continuous monitoring of noise levels for 30 minutes. The window may close too early if data doesn’t arrive for 15 minutes.
- Rejection Reason: This option does not fulfill the requirement of tracking data for at least 30 minutes when data is received for that duration.
B) Use session windows with a 30-minute gap duration:
- Session windows with a 30-minute gap will track data continuously for 30 minutes. If no new data arrives after 30 minutes, the window closes.
- This is more aligned with the requirement of having a window span at least 30 minutes when data is coming in continuously.
- However, the window will remain open even if there is no new data for 30 minutes, which is not ideal for this case since the window should close if no new data is received for 15 minutes.
- Rejection Reason: While it allows for tracking for 30 minutes, it does not automatically close the window after 15 minutes of inactivity, which...
Author: Ryan · Last updated Jul 15, 2026
You are creating a data model in BigQuery that will hold retail transaction data. Your two largest tables, sales_transaction_header and sales_transaction_line, have a tightly coupled immutable relationship. These tables are rarely modified after load and are frequently joined when queried. You need to model th...
Let's evaluate each option for modeling the `sales_transaction_header` and `sales_transaction_line` tables in BigQuery based on the fact that they have a tightly coupled immutable relationship and are frequently queried together.
A) Create a sales_transaction table that holds the sales_transaction_header information as rows and the sales_transaction_line rows as nested and repeated fields:
- Advantages:
- BigQuery is optimized for querying nested and repeated fields in a single table.
- This approach reduces the need for expensive joins, as both the header and line data are stored in a single, efficient structure.
- Nested fields are ideal for one-to-many relationships like this, where each header can have multiple lines.
- Disadvantages:
- There is some potential for the table to become very large, as both header and line data are stored in the same table, but BigQuery handles large tables well with partitioning and clustering.
- Best Use Case:
- When the header and line data are frequently queried together and form a tightly coupled relationship, this design maximizes query performance by reducing the need for joins.
- Selection Reason:
- This option aligns with BigQuery's strengths in handling nested and repeated fields, especially for frequently queried data that is tightly coupled.
B) Create a sales_transaction table that holds the sales_transaction_header and sales_transaction_line information as rows, duplicating the sales_transaction_header data for each line:
- Advantages:
- This approach might provide performance benefits for specific queries that need to access both header and line data together, as it avoids joins.
- Disadvantages:
- Duplicating the `sales_transaction_header` for each line results in redundant data, which wastes storage and leads to inefficiencies in querying and maintenance.
- It can also lead to difficulties in maintaining consistency between the header and line data, especially when changes to the header are needed.
- Best Use Case:
- This is generally a less efficient approach than Option A and is not ideal for tables with a high cardinality of lines relative to headers.
- Rejection Reason:
- Redundancy and inefficiency in terms of storage and querying make this option less suitable for your use case.
C) Create a sales_transaction table that stores the sales_transaction_header and sales_transaction_line data as a JS...
