Google Practice Questions, Discussions & Exam Topics by our Authors
You created a new version of a Dataflow streaming data ingestion pipeline that reads from Pub/Sub and writes to BigQuery. The previous version of the pipeline that runs in production uses a 5-minute window for processing. You need to deploy the new version of the pipeline without lo...
To ensure smooth deployment of a new version of a Dataflow streaming pipeline that reads from Pub/Sub and writes to BigQuery without data loss, inconsistencies, or significant increases in processing latency, let’s analyze each option carefully.
A) Update the old pipeline with the new pipeline code:
- Advantages:
- This option directly updates the existing pipeline, meaning the same pipeline runs with the new version of the code.
- There's no need to manage two separate pipelines.
- Disadvantages:
- In streaming pipelines, updating the existing pipeline might result in potential issues like state or data loss if the update isn’t seamless.
- This could lead to inconsistencies or disruptions in the data stream while transitioning from the old code to the new code, especially if the windowing logic or other key features change.
- Best Use Case:
- This might work for non-streaming batch jobs, but in streaming applications, changes in processing logic (like window size or other data structures) are more prone to causing errors.
- Rejection Reason:
- A direct update without a proper transition mechanism could cause data inconsistencies or delays, which is a major concern when managing streaming pipelines that process real-time data.
B) Snapshot the old pipeline, stop the old pipeline, and then start the new pipeline from the snapshot:
- Advantages:
- Snapshots preserve the state of the pipeline, so you can start the new pipeline from the exact point where the old pipeline was stopped.
- This approach avoids data loss because the state is preserved and maintained.
- The new pipeline can start processing data from the snapshot, reducing the likelihood of inconsistencies.
- Disadvantages:
- Managing snapshots and ensuring that the pipeline state is correctly maintained may be complex in certain situations.
- Some overhead is involved in managing the snapshot, but this is usually a manageable trade-off to ensure consistency.
- Best Use Case:
- This approach is ideal for cases where you need a clean transition, ensuring that no data is lost and the new pipeline starts from the correct point in time.
- Selection Reason:
- This option allows a seamless transition, preserving state and data integrity, making it suitable for real-time data pipelines that need to avoid inconsistencies or data loss.
C) Drain the old pipeli...
Author: Carlos Garcia · Last updated Jun 11, 2026
Your organization's data assets are stored in BigQuery, Pub/Sub, and a PostgreSQL instance running on Compute Engine. Because there are multiple domains and diverse teams using the data, teams in your organization are unable to discover existing data assets. You need to design a solu...
To improve data discoverability across multiple domains and diverse teams, we need to design a solution that allows for centralized management of metadata while minimizing manual effort and configuration. Let's evaluate each option to determine the best approach.
A) Use Data Catalog to automatically catalog BigQuery datasets. Use Data Catalog APIs to manually catalog Pub/Sub topics and PostgreSQL tables.
- Advantages:
- Data Catalog has native integration with BigQuery, which allows it to automatically catalog BigQuery datasets. This reduces manual work for BigQuery assets.
- Data Catalog APIs can be used to catalog Pub/Sub topics and PostgreSQL tables. This gives some flexibility, but still requires manual intervention to catalog Pub/Sub topics and PostgreSQL tables.
- Disadvantages:
- Although Data Catalog can automatically catalog BigQuery datasets, the manual effort for cataloging Pub/Sub topics and PostgreSQL tables might still be too cumbersome. Additionally, the APIs for manual cataloging may require significant development effort.
- Best Use Case:
- This option could work when automation for BigQuery is essential, but manual cataloging for Pub/Sub and PostgreSQL is manageable.
- Rejection Reason:
- The need for manual intervention for both Pub/Sub and PostgreSQL increases the workload, which is contrary to the requirement to minimize development and configuration efforts.
B) Use Data Catalog to automatically catalog BigQuery datasets and Pub/Sub topics. Use Data Catalog APIs to manually catalog PostgreSQL tables.
- Advantages:
- Data Catalog can automatically catalog BigQuery datasets and Pub/Sub topics, streamlining the cataloging process for both of these assets.
- The APIs provide flexibility to manually catalog PostgreSQL tables.
- Disadvantages:
- While this option automates the cataloging of BigQuery and Pub/Sub, it still requires manual effort for PostgreSQL tables. This could be a burden if the number of PostgreSQL tables is large or frequently changing.
- Best Use Case:
- This option might be useful when BigQuery and Pub/Sub are the primary data assets that need automatic cataloging, and PostgreSQL is a secondary asset that can be managed manually.
- Rejection Reason:
- The need to manually catalog PostgreSQL tables is still a drawback, as the goal is to minimize manual work across all assets.
C) Use Data Catalog to automatically catal...
Author: Nathan · Last updated Jun 11, 2026
You need to create a SQL pipeline. The pipeline runs an aggregate SQL transformation on a BigQuery table every two hours and appends the result to another existing BigQuery table. You need to configure the pipeline to retry if errors occur. Y...
To design a pipeline that performs an aggregate SQL transformation on a BigQuery table every two hours, appends the result to another BigQuery table, retries on errors, and sends an email notification after three consecutive failures, let's evaluate each option in detail.
A) Use the BigQueryUpsertTableOperator in Cloud Composer, set the retry parameter to three, and set the email_on_failure parameter to true.
- Advantages:
- The `BigQueryUpsertTableOperator` can be used in Cloud Composer (based on Apache Airflow) to run the transformation and append data to the target table.
- The `retry` parameter allows setting retry attempts, and `email_on_failure` would send an email if the task fails.
- Disadvantages:
- Cloud Composer with the `BigQueryUpsertTableOperator` can be powerful but introduces more complexity than necessary for a simple SQL transformation task.
- It requires managing a fully-fledged Airflow instance in Cloud Composer, which might be an over-engineered solution for just running a SQL job and managing retries.
- Best Use Case:
- This is useful when you already use Cloud Composer for orchestrating complex workflows and need advanced task retry and failure notification capabilities.
- Rejection Reason:
- While feasible, Cloud Composer is likely more complex than required, especially for a task that doesn't need complex orchestration, and it might be an overkill compared to other options.
B) Use the BigQueryInsertJobOperator in Cloud Composer, set the retry parameter to three, and set the email_on_failure parameter to true.
- Advantages:
- The `BigQueryInsertJobOperator` is also an operator in Cloud Composer that could be used to run the BigQuery SQL transformation, and it supports retry and failure notifications via `retry` and `email_on_failure` parameters.
- Disadvantages:
- Similar to option A, this requires Cloud Composer and Airflow, which introduces overhead for managing and configuring a more complex orchestration system.
- Best Use Case:
- This is also an option if you are using Cloud Composer for orchestrating jobs and need to manage retries and notifications.
- Rejection Reason:
- Just like option A, it’s unnecessarily complex for this use case, as you're mainly focused on running a simple SQL query and appending data with error handling and notification.
C) Create a BigQuery scheduled query to run the SQL transformation with schedule options that repeats every two hours, and enable email notifications.
- Advantages:
- BigQuery scheduled queries are simple to configure directly within the BigQuery UI and allow for recurring SQL executions.
- It allows setting up email notifications in case of job failures directly, so no addition...
Author: Zain · Last updated Jun 11, 2026
You are monitoring your organization's data lake hosted on BigQuery. The ingestion pipelines read data from Pub/Sub and write the data into tables on BigQuery. After a new version of the ingestion pipelines is deployed, the daily stored data increased by 50%. The volumes of data in Pub/Sub remained the same and only so...
To investigate and fix the cause of the data increase, let's analyze each option carefully:
Option A:
- Step 1: Check for duplicate rows in BigQuery tables that have doubled daily partition data size.
- Step 2: Schedule daily SQL jobs to deduplicate the affected tables.
- Step 3: Share the deduplication script with the other operational teams to reuse if this occurs to other tables.
Reasoning:
- This option focuses on identifying duplicates in the BigQuery tables and using SQL jobs to clean them up daily. However, this may only be addressing a symptom of the problem (duplicate data) and not the root cause (which is likely to be within the ingestion pipeline).
- It's reactive rather than proactive since deduplication is a workaround rather than fixing the underlying cause of the data duplication.
Rejection: While deduplication might temporarily fix the issue, it doesn't address potential problems in the ingestion process itself, which may cause the duplicated data. Therefore, this option doesn’t fix the root cause.
Option B:
- Step 1: Check for code errors in the deployed pipelines.
- Step 2: Check for multiple writing to the pipeline BigQuery sink.
- Step 3: Check for errors in Cloud Logging during the day of the release of the new pipelines.
- Step 4: If no errors, restore the BigQuery tables to their content before the last release using time travel.
Reasoning:
- This option involves checking for code errors in the ingestion pipelines, which is the most likely cause of the increase in data volume. It also checks for multiple writes to the BigQuery sink (which could lead to data duplication) and errors in the Cloud Logging system.
- If no issues are found, you can restore the tables using BigQuery's time travel feature to ensure the data is returned to its state before the pipeline update.
Rejection: While this option could potentially fix the problem, the restore step might not be necessary if the issue is resolved in the ingestion pipeline itself. Time travel should be used only if you're unable to fix the root cause directly.
Option C:
- Step 1: Check for duplicate rows in the BigQuery tables.
- Step 2: Check the BigQuery Audit logs to find job IDs.
- Step 3: Use Cloud Monitoring to determine when the identified Dataflow jobs started and the pipeline code version.
- Step 4: When more than one pipeline ingests data into a table, stop all versions except the lates...
Author: Olivia Johnson · Last updated Jun 11, 2026
You have a BigQuery dataset named 'customers'. All tables will be tagged by using a Data Catalog tag template named 'gdpr'. The template contains one mandatory field, 'has_sensitive_data', with a boolean value. All employees must be able to do a simple search and find tables in the dataset that have either true or false in the 'has_sensitive_data' field. However, only the Human Resources (HR) group should be able to see the data inside the tables for which 'has_sen...
To solve the scenario where all employees can search and find tables based on the `has_sensitive_data` field while only allowing the Human Resources (HR) group to view the data in those tables with sensitive data, let's analyze the options carefully:
Option A:
- Action: Create the "gdpr" tag template with private visibility. Assign the `bigquery.dataViewer` role to the HR group on the tables that contain sensitive data.
Reasoning:
- If the tag template is private, the `all employees` group will not be able to search or view the tags on the tables, which goes against the requirement that all employees should be able to search and find tables based on the `has_sensitive_data` field.
- This option fails to meet the "searchable by all employees" requirement.
Rejection: This option is not valid because it does not allow all employees to search and find the tables based on the tag.
Option B:
- Action: Create the "gdpr" tag template with private visibility. Assign the `datacatalog.tagTemplateViewer` role to the all employees group on this tag. Assign the `bigquery.dataViewer` role to the HR group on the tables that contain sensitive data.
Reasoning:
- A private visibility setting for the tag template still restricts the ability of the `all employees` group to search and view the tags on the tables. While assigning `datacatalog.tagTemplateViewer` to the `all employees` group would allow them to view tags, it still violates the requirement to make the search functionality accessible to all employees without causing unnecessary complexity.
- Furthermore, since only HR should be able to see the data in sensitive tables, this configuration still overcomplicates the setup by restricting visibility in a way that is unnecessary.
Rejection: The private visibility for the tag template still impedes the requirement for the `all employees` group to search the tables. Additionally, the visibility ...
Author: Krishna · Last updated Jun 11, 2026
You are creating the CI/CD cycle for the code of the directed acyclic graphs (DAGs) running in Cloud Composer. Your team has two Cloud Composer instances: one instance for development and another instance for production. Your team is using a Git repository to maintain and develop the code of the DAGs. ...
To automate the deployment of DAGs to Cloud Composer based on a tag pushed to a Git repository, let’s break down each option and evaluate it based on the goal: smooth deployment to development and production environments, automated testing, and minimized overhead.
Option A:
- Step 1: Use Cloud Build to copy the code of the DAG to the Cloud Storage bucket of the development instance for DAG testing.
- Step 2: If the tests pass, use Cloud Build to copy the code to the bucket of the production instance.
Reasoning:
- This option uses Cloud Build to copy the code to Cloud Storage, which is the correct method for working with Cloud Composer as DAGs are typically stored in Cloud Storage buckets. It performs the deployment first to the development environment and then, if the tests are successful, to production.
- This is a fairly simple and streamlined approach for automating deployments in Cloud Composer, and it directly fits the use case of deploying the DAG code to Cloud Storage.
Rejection: While this solution works well and keeps the process simple, it doesn’t take full advantage of containerization or orchestration tools like Kubernetes, which could provide more flexibility and better handling of dependencies and testing environments. However, the simplicity of using Cloud Storage could be a benefit depending on your use case.
Option B:
- Step 1: Use Cloud Build to build a container with the code of the DAG and the KubernetesPodOperator to deploy the code to the Google Kubernetes Engine (GKE) cluster of the development instance for testing.
- Step 2: If the tests pass, use the KubernetesPodOperator to deploy the container to the GKE cluster of the production instance.
Reasoning:
- This option introduces containerization, which is an effective method for deploying applications in a controlled manner. Using Kubernetes to deploy the DAG to GKE clusters is a more advanced and flexible approach.
- However, Cloud Composer is built to work with Cloud Storage for storing DAGs, and this solution overcomplicates the deployment process by incorporating Kubernetes where it's not necessarily required for the task of DAG deployment.
Rejection: This option is more complex than needed, as Cloud Composer doesn’t require Kubernetes for this use case. It would introduce additional overhead in managing...
Author: Manish · Last updated Jun 11, 2026
You have a BigQuery table that ingests data directly from a Pub/Sub subscription. The ingested data is encrypted with a Google-managed encryption key. You need to meet a new organization policy that requires you to use keys from a cent...
To meet the new organization policy, you need to ensure that data is encrypted with keys from a centralized Cloud Key Management Service (Cloud KMS) project, using customer-managed encryption keys (CMEK), instead of the default Google-managed encryption key. Let’s break down each option:
Option A:
- Action: Use Cloud KMS encryption key with Dataflow to ingest the existing Pub/Sub subscription into the existing BigQuery table.
Reasoning:
- Dataflow can be used to stream data from Pub/Sub to BigQuery, and it can use customer-managed encryption keys (CMEK). However, the current BigQuery table is already in use, and using Dataflow as the middle layer would require modifying the existing data pipeline. The existing BigQuery table might not support switching encryption methods from Google-managed keys to CMEK easily, especially if it’s already in use and contains data encrypted by Google-managed keys.
- This option requires significant changes to the existing pipeline and does not directly address how to use CMEK for the existing table.
Rejection: This approach requires more infrastructure changes and doesn't directly apply to switching to CMEK for the existing BigQuery table. It’s not the most efficient solution.
Option B:
- Action: Create a new BigQuery table by using customer-managed encryption keys (CMEK), and migrate the data from the old BigQuery table.
Reasoning:
- This option involves creating a new BigQuery table with CMEK and migrating the data from the old table. BigQuery allows you to set CMEK for tables, and this would ensure that all new data is encrypted with keys from the centralized Cloud KMS project. However, this would require manual migration of existing data to the new table, which could be complex, especially if the dataset is large or frequently updated.
Rejection: While feasible, this option requires the creation of a ne...
Author: CrimsonViperX · Last updated Jun 11, 2026
You created an analytics environment on Google Cloud so that your data scientist team can explore data without impacting the on-premises Apache Hadoop solution. The data in the on-premises Hadoop Distributed File System (HDFS) cluster is in Optimized Row Columnar (ORC) formatted files with multiple columns of Hive partitioning. The data scientist team needs to be able to explore the data in a similar wa...
To meet the needs of your data scientist team in exploring data without impacting the on-premises Hadoop solution, and to ensure you provide a cost-effective storage and processing solution, let’s evaluate each option based on the requirements:
Option A:
- Action: Import the ORC files to Bigtable tables for the data scientist team.
Reasoning:
- Google Bigtable is designed for low-latency read and write operations on massive datasets and is ideal for workloads like time-series data or real-time analytics. However, Bigtable is not optimized for running SQL queries, especially on structured data like ORC files. It's more suited for NoSQL workloads rather than relational querying, which is what the data scientist team needs to explore the data.
- Bigtable lacks support for complex SQL-like queries (e.g., JOINs, GROUP BY) and doesn’t offer native support for columnar formats like ORC.
Rejection: This option is not ideal as it doesn't support the required SQL querying features and would impose a significant overhead for the data scientist team to work with.
Option B:
- Action: Import the ORC files to BigQuery tables for the data scientist team.
Reasoning:
- BigQuery is a fully-managed, serverless, and highly scalable data warehouse that supports SQL queries on structured data. However, importing ORC files directly into BigQuery requires a transformation or conversion to a format that BigQuery can efficiently process (like Parquet or Avro). This approach can be costly and time-consuming, especially when transforming large datasets.
- Additionally, BigQuery is a powerful tool for querying large datasets but may not be the most cost-effective when compared to other options, especially if your data scientists don’t need the full-scale data warehouse features for their exploration.
Rejection: While BigQuery offers SQL support, importing and transforming ORC files into a compatible format can be cumbersome and cost-inefficient for the exploratory use case. It also doesn't align well with the need for exploring ORC data as in the Hadoop/Hive environment.
Option C:
- Actio...
Author: James · Last updated Jun 11, 2026
You are designing a Dataflow pipeline for a batch processing job. You want to mitigate multiple zonal failu...
When designing a Dataflow pipeline for a batch processing job and aiming to mitigate multiple zonal failures at job submission time, you want to ensure that the job can recover from any failures in a specific zone without disrupting the pipeline's progress. Let’s evaluate each option:
A) Submit duplicate pipelines in two different zones by using the --zone flag.
- Pros: This approach might provide redundancy by submitting pipelines to different zones. If one zone experiences a failure, the other zone can still process the job.
- Cons: This method is inefficient and requires maintaining duplicate pipelines. There is no built-in mechanism for automatic recovery, and managing two separate pipelines would increase complexity and cost.
- Best Scenario: This option could be used in highly critical scenarios where job failure in one zone could result in significant downtime, but it is generally not the most efficient or scalable option.
B) Set the pipeline staging location as a regional Cloud Storage bucket.
- Pros: This method mitigates zonal failure by ensuring that the staging location (for storing files, etc.) is not tied to a single zone. If one zone experiences issues, the data is still available in the region, preventing downtime.
- Cons: This does not directly address failure in job execution or worker management in the event of zonal failure, as it only impacts the storage aspect.
- Best Scenario: This is ideal when the main concern is avoiding failure at the storage layer (i.e., in case the zone storing intermediate files or pipeline logs experiences issues), but it doesn't provide protection against job execution failures across zones.
C) Specify a worker region by using the --region flag.
- Pros: This ensures t...
Author: MoonlitPantherX · Last updated Jun 11, 2026
You are designing a real-time system for a ride hailing app that identifies areas with high demand for rides to effectively reroute available drivers to meet the demand. The system ingests data from multiple sources to Pub/Sub, processes the data, and stores the results for visualization and analysis in real-time dashboards. The data sources include driver location updates every 5 seconds and app-based booking events from riders. The data proc...
In designing a real-time system for a ride-hailing app, there are several important factors to consider, such as the need to handle both high-frequency driver location updates and app-based booking events, as well as performing aggregation every 2 seconds over a 30-second period. The goal is to produce low-latency, real-time insights that can be visualized in a dashboard. Let’s evaluate each option to determine the most suitable solution:
A) Group the data by using a tumbling window in a Dataflow pipeline, and write the aggregated data to Memorystore.
- Pros: Tumbling windows are useful when you want to process data in fixed, non-overlapping intervals. This is a good choice if the data stream needs to be processed in distinct chunks.
- Cons: However, in this case, the requirement is to aggregate the data for a 30-second period, with updates happening every 2 seconds. A tumbling window would create distinct, non-overlapping time chunks and wouldn't allow you to efficiently process a sliding window of data (which is necessary here). Additionally, Memorystore is an in-memory data store, and it may not be suitable for longer-term storage or aggregation that needs to be queried later for historical analysis or dashboards.
- Best Scenario: This approach could work if you wanted to process data in distinct, non-overlapping chunks and only cared about near real-time results, but it is not suitable for this case as it doesn't meet the sliding window requirement.
B) Group the data by using a hopping window in a Dataflow pipeline, and write the aggregated data to Memorystore.
- Pros: A hopping window is well-suited for cases where you need to calculate rolling or sliding aggregations over a time period (e.g., 30 seconds), with the window "hopping" forward at regular intervals (every 2 seconds in this case). This is exactly what is required for the 30-second aggregation with a 2-second update.
- Cons: Writing the aggregated data to Memorystore would only provide temporary storage and low-latency access, but it wouldn't be ideal for long-term storage, querying, or analysis, especially for visualization and dashboards that need historical or persistent data.
- Best Scenario: This approach could be useful for short-term caching or for systems that require quick in-memory processing, but it doesn't meet the need for long-term storage and analysis as well as BigQuery would.
C) Group the data by using...
Author: Ethan Smith · Last updated Jun 11, 2026
Your car factory is pushing machine measurements as messages into a Pub/Sub topic in your Google Cloud project. A Dataflow streaming job, that you wrote with the Apache Beam SDK, reads these messages, sends acknowledgment to Pub/Sub, applies some custom business logic in a DoFn instance, and writes the result to BigQuery. You want to ensure that if ...
To ensure that failed messages from your Dataflow streaming job are handled correctly and sent to a Pub/Sub topic for monitoring purposes, we need to explore the best method of isolating the failed messages and triggering alerting mechanisms.
Let's analyze each option:
A) Enable retaining of acknowledged messages in your Pub/Sub pull subscription. Use Cloud Monitoring to monitor the subscription/num_retained_acked_messages metric on this subscription.
- Pros: Retaining acknowledged messages would allow you to keep a record of successfully processed messages for later analysis or reprocessing.
- Cons: This option doesn’t address the scenario of failed messages; it focuses on retaining successfully acknowledged messages. Failed messages would not be sent to a separate topic, which is the desired outcome in this case. Furthermore, Cloud Monitoring of this metric won’t directly help you isolate and address failed messages that need attention.
- Best Scenario: This option could be useful in situations where you want to retain acknowledged messages for auditing, but it's not suitable for failed message handling.
B) Use an exception handling block in your Dataflow's DoFn code to push the messages that failed to be transformed through a side output and to a new Pub/Sub topic. Use Cloud Monitoring to monitor the topic/num_unacked_messages_by_region metric on this new topic.
- Pros: By using side outputs in Apache Beam, you can isolate failed messages and send them to a different Pub/Sub topic. This approach allows you to manage and monitor failed messages separately from successful ones. You can easily set up Cloud Monitoring to monitor the metric for the failed messages.
- Cons: While this is a good approach for isolating failed messages, it requires explicit handling of the failures within your DoFn code, which can be complex and error-prone depending on the logic. It may also result in additional overhead for managing the side outputs.
- Best Scenario: This option works well if you need fine-grained control over the failure handling within your DoFn code and are willing to manage the failure path explicitly.
C) Enable dead lettering in your Pub/Sub pull subscription, and specify a new Pub/Sub topic as the dead letter topic. Use Cloud Monitoring to monitor the subscription/dead_letter_message_count metric on your pull subscription.
- Pros: Dead-lettering...
Author: Kai99 · Last updated Jun 11, 2026
You want to store your team's shared tables in a single dataset to make data easily accessible to various analysts. You want to make this data readable but unmodifiable by analysts. At the same time, you want to provide the analysts with individual workspaces in the same project, where th...
When setting up a structure for shared data access while ensuring individual workspaces for analysts, the key considerations are:
1. Read-Only Access to Shared Data: Analysts need read-only access to a shared dataset with shared tables.
2. Isolation of Analysts' Data: Analysts should have their own private workspaces where they can create and store their own tables without sharing them with others.
Let’s analyze the options to identify the best approach:
A) Give analysts the BigQuery Data Viewer role at the project level. Create one other dataset, and give the analysts the BigQuery Data Editor role on that dataset.
- Pros: The BigQuery Data Viewer role at the project level ensures that analysts can read data across all datasets in the project. Giving them Data Editor role in another dataset allows them to write to a separate dataset.
- Cons: The analysts would have access to the entire project, which means they could potentially see all datasets (including private ones) within the project, violating the requirement that their data is not accessible by others. Also, giving them Data Editor access on a separate dataset doesn’t isolate their individual workspaces from each other.
- Best Scenario: This could be useful in cases where you want analysts to have write access to a shared dataset but is not ideal for ensuring complete isolation between analysts.
B) Give analysts the BigQuery Data Viewer role at the project level. Create a dataset for each analyst, and give each analyst the BigQuery Data Editor role at the project level.
- Pros: This ensures each analyst has a private workspace for storing their data.
- Cons: Granting BigQuery Data Editor at the project level would give analysts write access to all datasets in the project (including the shared dataset), which violates the requirement to prevent modification of the shared data. This is not desirable because analysts could modify or overwrite data in the shared dataset.
- Best Scenario: This could be used if you want all analysts to have broad write access across the enti...
Author: Elizabeth · Last updated Jun 11, 2026
You are running a streaming pipeline with Dataflow and are using hopping windows to group the data as the data arrives. You noticed that some data is arriving late but is not being marked as late data, which is resulting in inaccurate aggregations downstream. Yo...
In this case, you're experiencing an issue where late-arriving data is not being marked as late, leading to inaccurate aggregations. The goal is to handle late data correctly and ensure it is captured in the appropriate window for accurate processing. Let's analyze each option to determine the best solution.
A) Use watermarks to define the expected data arrival window. Allow late data as it arrives.
- Pros: Watermarks are a fundamental concept in stream processing that track the progress of time in the data stream. Watermarks help define when it's "safe" to process data and when to allow late data to be included in the appropriate windows. This approach allows you to handle late data explicitly by specifying a permissible lateness period (e.g., data that arrives within a certain time after the watermark is still processed).
- Cons: The main challenge would be ensuring that your watermarks are configured correctly to allow late data to be processed. However, this is a standard solution in streaming pipelines that supports efficient handling of late-arriving data.
- Best Scenario: This is an ideal solution for handling late data. Watermarks enable late data to be processed appropriately within defined lateness windows, ensuring that aggregations remain accurate.
B) Change your windowing function to tumbling windows to avoid overlapping window periods.
- Pros: Tumbling windows are non-overlapping fixed-length windows, and they can simplify some cases where you need strict window boundaries.
- Cons: Tumbling windows are not ideal for scenarios where data is arriving at different times with potential overlap, as they do not support capturing overlapping or sliding time periods. This would mean you lose the flexibility offered by hopping windows and would have trouble accommodating late-arriving data in the window as tumbling windows do not allow for "overlap" between the windows. This would worsen the problem you're facing, as it could increase the chances of data being dropped or missed entirely.
- Best Scenario: Tumbling windows could work in simpler cases where overlapping data isn't necessary, but they don’t provide the flexibility required in your situation where the data needs to be captured across overlapping windows with the possibility of late arrivals.
C) Change your ...
Author: Nia · Last updated Jun 11, 2026
You work for a large ecommerce company. You store your customer's order data in Bigtable. You have a garbage collection policy set to delete the data after 30 days and the number of versions is set to 1. When the data analysts run a query to report total customer spending, the analysts sometimes see customer data that is older than 3...
To address the requirement of ensuring that analysts do not see customer data older than 30 days while minimizing cost and overhead, let's evaluate each option based on key factors:
Option A: Set the expiring values of the column families to 29 days and keep the number of versions to 1.
- Explanation: By setting the expiration to 29 days, the data will be deleted one day earlier than the 30-day requirement. This could potentially avoid the situation where data is visible past the 30-day period.
- Rejected because: While this may reduce the risk of seeing data older than 30 days, it would also mean that data is deleted one day earlier than necessary. This could lead to the loss of valuable data that is still within the required 30-day retention period.
- Key Factor: Data retention is critical, and setting the expiration to 29 days could cause unintentional data loss.
Option B: Use a timestamp range filter in the query to fetch the customer's data for a specific range.
- Explanation: This approach ensures that queries return only data within the desired 30-day range, regardless of when it was inserted.
- Rejected because: While it guarantees that only data within the desired date range is retrieved, it introduces complexity to every query. Analysts will need to manually specify the time range in every query, which increases query complexity and overhead.
- Key Factor: The extra burden on analysts to filter data manually is inefficient and err...
Author: Aarav · Last updated Jun 11, 2026
You are using a Dataflow streaming job to read messages from a message bus that does not support exactly-once delivery. Your job then applies some transformations, and loads the result into BigQuery. You want to ensure that your data is being streamed into BigQuery with exactly-once...
To ensure exactly-once delivery semantics while ingesting streaming data from a message bus that does not support exactly-once delivery, you need a solution that can guarantee the integrity and consistency of the data, especially when using Dataflow in combination with BigQuery. Let's analyze each option to find the best solution:
Option A: Use the BigQuery Storage Write API and ensure that your target BigQuery table is regional.
- Explanation: The BigQuery Storage Write API allows for high throughput and lower latency when streaming data into BigQuery, which is ideal for ingesting large amounts of data (like 1.5 GB per second).
- Rejected because: While the Storage Write API is well-suited for high throughput, regional tables may not provide the optimal level of redundancy and scalability for a high-throughput use case. Additionally, ensuring exactly-once semantics depends on other factors, including how the streaming system manages deduplication and checkpointing.
Option B: Use the BigQuery Storage Write API and ensure that your target BigQuery table is multiregional.
- Explanation: The multiregional configuration provides better fault tolerance, redundancy, and scalability, which are essential for high-throughput streaming jobs. The BigQuery Storage Write API also supports high-throughput and exactly-once semantics, so combining this with a multiregional table will help ensure that data is delivered accurately and reliably.
- Selected because: This option supports both high throughput (suitable for 1.5 GB per second) and exactly-once semantics due to the combination of the BigQuery Storage Write API and a multiregional table. Multiregional tables also offer enhanced reliability and durability in the event of failure, which is ...
Author: Sophia · Last updated Jun 11, 2026
You have created an external table for Apache Hive partitioned data that resides in a Cloud Storage bucket, which contains a large number of files. You notice that queries against this table ...
To improve the performance of queries on an external table for Apache Hive partitioned data in a Cloud Storage bucket, we need to evaluate the options based on how they affect query performance, scalability, and overall system design. Here’s an analysis of each option:
Option A: Change the storage class of the Hive partitioned data objects from Coldline to Standard.
- Explanation: The storage class determines how data is stored in Google Cloud Storage (e.g., Standard, Nearline, Coldline). Coldline is optimized for long-term storage with infrequent access, while Standard is intended for frequently accessed data.
- Rejected because: Changing the storage class to Standard would primarily benefit latency and access times for frequent access but does not address the underlying query performance issue. For large-scale queries, the challenge is likely related to partitioning, metadata management, and access patterns, not just the storage class. Switching storage classes will not significantly improve the query performance if the underlying data structure (e.g., partitioning) is not optimized.
- Key Factor: This option won’t directly address slow query performance due to the large number of files or how metadata is handled.
Option B: Create an individual external table for each Hive partition by using a common table name prefix. Use wildcard table queries to reference the partitioned data.
- Explanation: By creating individual external tables for each partition, you can isolate each partition’s data, which may speed up query performance as each query only needs to scan relevant partitions. Using wildcard queries to reference the partitions helps in managing the data structure.
- Rejected because: While partitioning and isolating the data can reduce the amount of data scanned, creating many external tables for each partition increases management overhead. It also complicates the table structure, as you would need to maintain and query numerous individual tables instead of a single partitioned table. This solution could lead to scalability issues as the number of partitions grows, increasing the complexity of the metadata and query execution.
- Key Factor: This solution could become difficult to manage, especially as the number of partitions grows, and it may not provide the desired performance improvements without further optimization.
Op...
Author: Grace · Last updated Jun 11, 2026
You have a network of 1000 sensors. The sensors generate time series data: one metric per sensor per second, along with a timestamp. You already have 1 TB of data, and expect the data to grow by 1 GB every day. You need to access this data in two ways. The first access pattern requires retrieving the metric from one specific sensor stored at a specific timestamp, with a median single-d...
To determine the best storage solution for this time series data from 1000 sensors with two distinct access patterns, we need to carefully evaluate the performance, scalability, and ease of access for each option. Let's break down each option:
Option A: Store your data in BigQuery. Concatenate the sensor ID and timestamp, and use it as the primary key.
- Explanation: BigQuery is a powerful data warehouse for analytical workloads, designed for running complex queries on large datasets. By using a composite key of sensor ID and timestamp, you can efficiently query for a specific sensor’s data at a particular time.
- Rejected because: While BigQuery is excellent for analytical workloads, single-digit millisecond latency for retrieving a specific metric from a sensor at a specific timestamp is not its strength. BigQuery is optimized for batch processing and complex queries, not for low-latency lookups. For real-time access to time series data, BigQuery may not provide the performance you need for the first access pattern (specific sensor data at a specific timestamp).
- Key Factor: The low-latency requirement (single-digit milliseconds) for quick lookups does not align well with BigQuery’s strengths.
Option B: Store your data in Bigtable. Concatenate the sensor ID and timestamp and use it as the row key. Perform an export to BigQuery every day.
- Explanation: Bigtable is well-suited for storing time series data and provides fast access to individual rows, making it a good choice for low-latency reads. By using sensor ID and timestamp as the row key, Bigtable can provide very low latency for retrieving data at a specific time for a specific sensor.
- Selected because: Bigtable excels at providing single-digit millisecond latency for retrieving specific data points. Additionally, exporting to BigQuery once a day allows for running complex analytical queries on the entire dataset, taking advantage of BigQuery's processing power. This ensures that both access patterns are efficiently supported: fast individual queries via Bigtable and complex analytics via BigQuery.
- Key Factor: Bigtable handles high-frequency reads with low-latency access, and exporting to BigQuery allows for complex queries without sacrificing performan...
Author: Emily · Last updated Jun 11, 2026
You have 100 GB of data stored in a BigQuery table. This data is outdated and will only be accessed one or two times a year for analytics with SQL. For backup purposes, you want to store this data...
To determine the best option for storing outdated data that will be accessed infrequently but needs to be immutable for 3 years and minimize storage costs, let's evaluate each option based on cost, storage efficiency, and long-term immutability.
Option A:
1. Create a BigQuery table clone.
2. Query the clone when you need to perform analytics.
- Explanation: A BigQuery table clone provides a full copy of your existing data. Clones in BigQuery are typically used for managing concurrent operations but do not offer any substantial cost benefits for long-term storage. Clones are stored with the same pricing as regular tables.
- Rejected because: The storage costs for BigQuery tables, even for clones, are relatively high for long-term storage. This option does not help minimize storage costs, and the data would not be immutable in the same way as using a dedicated backup strategy with external storage.
Option B:
1. Create a BigQuery table snapshot.
2. Restore the snapshot when you need to perform analytics.
- Explanation: A BigQuery snapshot provides a point-in-time copy of the table, and you can restore it for analytics. However, snapshots are charged at the same rate as the original table, meaning this would not minimize costs for long-term storage.
- Rejected because: Snapshots are still stored in BigQuery and incur high storage costs. They are useful for short-term backups but not ideal for minimizing storage costs over several years. Additionally, they are not specifically designed to be immutable for 3 years.
Option C:
1. Perform a BigQuery export to a Cloud Storage bucket with archive storage class.
2. Enable versioning on the bucket.
3. Create a BigQuery external table on the exported files.
- Explanation: Exporting the data to Cloud Storage with the archive storage class...
Author: Emma · Last updated Jun 11, 2026
You have thousands of Apache Spark jobs running in your on-premises Apache Hadoop cluster. You want to migrate the jobs to Google Cloud. You want to use managed services to run your jobs instead of maintaining a long-lived Hadoop clust...
To decide the best approach for migrating Apache Spark jobs from an on-premises Hadoop cluster to Google Cloud with minimal changes, we need to evaluate the options based on key factors such as timeline, code changes, and the level of managed service. Here's the reasoning for each option:
A) Move your data to BigQuery. Convert your Spark scripts to a SQL-based processing approach.
- Reasoning: This option involves moving your data to BigQuery, a fully managed data warehouse, and converting Spark jobs into SQL-based approaches. Although BigQuery can handle large-scale data processing with SQL, this option would likely require significant changes to your existing Spark code since Spark is not SQL-native and may rely on more complex data transformations and processing. Converting the entire processing logic into SQL could be time-consuming and complex, especially with custom Spark transformations that don't map directly to SQL.
- Rejection: This option would require a lot of code changes and doesn't align with the goal of minimal changes to existing code.
B) Rewrite your jobs in Apache Beam. Run your jobs in Dataflow.
- Reasoning: Apache Beam is a powerful unified stream and batch processing model, and Dataflow is a fully managed service for running Beam pipelines. This option would require rewriting your existing Spark jobs into Apache Beam pipelines. Although Beam is highly scalable and flexible, the need to rewrite the jobs entirely is a major drawback, especially under a tight timeline.
- Rejection: This option would require significant code changes (rewriting Spark jobs into Beam), which conflicts w...
Author: Olivia Johnson · Last updated Jun 11, 2026
You are administering shared BigQuery datasets that contain views used by multiple teams in your organization. The marketing team is concerned about the variability of their monthly BigQuery analytics spend using the on-demand billing model. You nee...
To help the marketing team establish a consistent BigQuery analytics spend, it's crucial to select an option that provides predictability in terms of query costs while also addressing the need for flexibility and scalability based on the team's usage. Here's an evaluation of each option:
A) Create a BigQuery Enterprise reservation with a baseline of 250 slots and autoscaling set to 500 for the marketing team, and bill them back accordingly.
- Reasoning: This option creates a BigQuery Enterprise reservation, which is part of the BigQuery reservations model for sustained query workloads. A reservation with 250 slots and autoscaling to 500 provides a balance between predictable costs (due to the baseline reservation) and flexibility (due to autoscaling). This can effectively manage the marketing team's monthly analytics spend by locking in a portion of their capacity, ensuring they don't experience unpredictable spikes in costs. Billing back accordingly is also a good fit for tracking and allocating costs specifically to the marketing team.
- Selected Option: This option provides a predictable cost model for the marketing team while also ensuring that they can scale when needed, so it is a good choice.
B) Establish a BigQuery quota for the marketing team, and limit the maximum number of bytes scanned each day.
- Reasoning: A BigQuery quota can limit the number of bytes scanned, which helps prevent unexpected high costs from large queries. However, this doesn't directly address the variability in cost due to fluctuating query demands. While this can provide cost control, it's not as precise as a reservation in terms of achieving predictable costs. It might also restrict the marketing team's ability to run larger queries if they hit the daily quota.
- Rejection: Quotas are more about controlling data access and usage limits, but...
Author: StarlightBear · Last updated Jun 11, 2026
You are part of a healthcare organization where data is organized and managed by respective data owners in various storage services. As a result of this decentralized ecosystem, discovering and managing data has become difficult. You need to quickly identify and implement a cost-optimized solution to assist your organization wi...
To assist with data management, discovery, data lineage tracking, and data quality validation in a decentralized healthcare data ecosystem, we need to choose a solution that streamlines these tasks, integrates with existing services, and is scalable and cost-efficient. Here’s the evaluation of each option:
A) Use BigLake to convert the current solution into a data lake architecture.
- Reasoning: BigLake is a storage engine that enables you to unify your data across Google Cloud services, creating a consistent data lake experience. It simplifies managing and analyzing data from different storage systems. However, while BigLake can help organize data, it doesn't directly address the needs for data lineage tracking and data quality validation out of the box. Additionally, converting an existing system into a data lake could involve significant effort and may not solve the immediate problem of discovering and managing data.
- Rejection: This option doesn't fully meet all the requirements (data lineage and quality validation), and it may require considerable changes to your existing architecture, making it a more complex solution than necessary.
B) Build a new data discovery tool on Google Kubernetes Engine that helps with new source onboarding and data lineage tracking.
- Reasoning: Building a custom tool on Google Kubernetes Engine (GKE) gives full control over the system’s functionality. However, this solution would require a significant amount of development and maintenance effort. You'll need to create a custom solution for data discovery, lineage tracking, and data quality validation, which is resource-intensive and time-consuming, especially in a dynamic healthcare data ecosystem.
- Rejection: This approach is labor-intensive and may introduce delays in implementation. Building custom solutions may not be the most cost-effective or time-efficient way to address the organization's needs.
C) Use BigQuery to t...
Author: Mia · Last updated Jun 11, 2026
You have data located in BigQuery that is used to generate reports for your company. You have noticed some weekly executive report fields do not correspond to format according to company standards. For example, report errors include different telephone formats and different country code identifiers. This is a frequent ...
To solve the problem of normalizing data fields (e.g., telephone formats, country code identifiers) in BigQuery with a recurring job and no coding required, we need to choose a solution that is both simple to implement and doesn't require heavy coding or infrastructure management. Here's an evaluation of each option:
A) Use Cloud Data Fusion and Wrangler to normalize the data, and set up a recurring job.
- Reasoning: Cloud Data Fusion is a fully managed integration platform that allows for the creation of data pipelines with minimal coding, and Wrangler provides a user-friendly interface for data transformation, including normalization tasks like telephone number formatting. Using Cloud Data Fusion would allow you to visually design a pipeline that can transform the data and automate the process. The platform also supports scheduling, so it can run the job periodically without manual intervention.
- Selected Option: This is a strong candidate because it meets the requirement of normalizing data with no coding, leveraging the visual interface of Cloud Data Fusion and Wrangler to set up recurring jobs. It's easy to use and scalable for such tasks.
B) Use Dataflow SQL to create a job that normalizes the data, and that after the first run of the job, schedule the pipeline to execute recurrently.
- Reasoning: Dataflow SQL enables you to write SQL queries for stream and batch processing. While it can be used to normalize data, it requires some level of SQL expertise, and although it supports scheduling, Dataflow is generally better suited for more complex data processing pipelines. It also involves managing Apache Beam jobs and requires more infrastructure handling than a no...
Author: Liam123 · Last updated Jun 11, 2026
You are designing a messaging system by using Pub/Sub to process clickstream data with an event-driven consumer app that relies on a push subscription. You need to configure the messaging system that is reliable enough to handle temporary downtime of the consumer app. You also need the messaging system to store the input messages that cannot be consumed by the subscriber. The system needs to retry failed mess...
When designing a messaging system to process clickstream data in an event-driven app, the system needs to be reliable enough to handle temporary downtimes of the consumer app while retrying failed messages and avoiding overloading the consumer. We need to ensure that messages are retried gradually and that after a maximum of 10 retries, failed messages are moved to a dead-letter topic for further inspection.
Let's break down the options:
A) Increase the acknowledgement deadline to 10 minutes.
- Reasoning: Increasing the acknowledgement deadline ensures that the subscriber has more time to process a message before Pub/Sub considers it unacknowledged. While this can help in cases where message processing takes longer than usual, it doesn't address the retry mechanism or provide a way to handle failed messages after multiple retries. This option doesn't solve the issue of retrying messages gradually or handling dead-letter scenarios.
- Rejection: This option does not solve the key problem of retrying failed messages with an appropriate delay or storing them after a maximum number of retries.
B) Use immediate redelivery as the subscription retry policy, and configure dead lettering to a different topic with maximum delivery attempts set to 10.
- Reasoning: Immediate redelivery causes Pub/Sub to retry messages as soon as they fail. While this can retry failed messages, it doesn't provide a gradual backoff, and it might overload the consumer with retries happening too quickly. Additionally, configuring dead lettering to a different topic means that failed messages will be moved to a separate topic, which could be useful for logging and tracking.
- Rejection: Immediate redelivery does not support gradual retries (which are crucial to prevent overloading the consumer). Also, dead-lettering to a different topic is good, but...
Author: Harper · Last updated Jun 11, 2026
You designed a data warehouse in BigQuery to analyze sales data. You want a self-serving, low-maintenance, and cost- effective solution to share the sales data...
To determine the best option for sharing the sales dataset with other business units, let's analyze each choice based on the key factors of self-serving, low-maintenance, and cost-effectiveness:
Option A: Create an Analytics Hub private exchange, and publish the sales dataset.
- Pros:
- Analytics Hub is a centralized platform for data sharing in BigQuery, which allows organizations to securely share datasets across projects.
- The dataset is accessible through a shared exchange, making it easy for multiple teams to access and use it without managing individual access permissions.
- Ideal for cross-project data sharing and collaboration.
- Cons:
- Higher cost: This solution involves more overhead because of centralized management and potential data replication costs.
- Not the most self-serving or low-maintenance: Users still need to navigate the exchange and permissions management.
- Scenario for use: Useful when sharing large datasets across multiple business units with complex access control and auditing needs.
Option B: Enable the other business units' projects to access the authorized views of the sales dataset.
- Pros:
- Direct and simple: Allows users to query data without moving or duplicating it, and you can control access through authorized views.
- Low maintenance: Once set up, there’s minimal ongoing management.
- Cons:
- Not completely self-serving: Users depend on the initial configuration of authorized views to access the data.
- If the views are complex or need frequent updates, this might require more effort.
- Scenario for use: Ideal when data sharing is straightforward and minimal access control management is needed. It's a good option if your users don’t need to make changes to the dataset and mostly need read-only access.
Option C: Create and share views with the users in the other business units.
- Pros:
- Self-serving: Users can query the views directly and access the data in a structured form.
- Low maintenance: The data resides in one place, and users can access it as needed through the views.
- Cons:
- Complexity: If the dataset c...
Author: Emma Brown · Last updated Jun 11, 2026
You have terabytes of customer behavioral data streaming from Google Analytics into BigQuery daily. Your customers' information, such as their preferences, is hosted on a Cloud SQL for MySQL database. Your CRM database is hosted on a Cloud SQL for PostgreSQL instance. The marketing team wants to use your customers' information from the two databases and the customer behavioral data to create marketing campaigns for yearly active customers. You need to ensure that the ma...
Let's analyze each option to determine the most efficient, scalable, and cost-effective solution, focusing on minimizing the load on the Cloud SQL databases while supporting the marketing team's high query volume.
Option A: Create BigQuery connections to both Cloud SQL databases. Use BigQuery federated queries on the two databases and the Google Analytics data on BigQuery to run these queries.
- Pros:
- No data replication: Federated queries allow you to query the Cloud SQL databases without moving data, reducing storage costs.
- Integration with BigQuery: You can directly query the Cloud SQL data alongside your Google Analytics data in BigQuery, offering a unified analytics environment.
- Scalability: BigQuery is highly scalable and optimized for running complex queries with minimal management.
- Cons:
- Load on Cloud SQL: Federated queries can increase the load on Cloud SQL databases, especially when run frequently (100–300 times per day), which could impact their performance.
- Query performance: Federated queries can be slower than querying data stored natively in BigQuery, especially if the Cloud SQL databases are large or under heavy load.
- Scenario for use: This option is suitable when you need real-time access to external data with minimal replication overhead but might not be ideal for very high-frequency querying of Cloud SQL databases.
Option B: Create a job on Apache Spark with Dataproc Serverless to query both Cloud SQL databases and the Google Analytics data on BigQuery for these queries.
- Pros:
- Scalability and flexibility: Dataproc Serverless can automatically scale to meet high demand, handling hundreds of queries per day.
- Batch processing: Suitable for running periodic or complex jobs that require heavy data processing across multiple sources.
- Cons:
- Increased complexity: Managing a Spark job on Dataproc Serverless requires more configuration and maintenance compared to simpler solutions like BigQuery.
- Potential delays: Dataproc is not ideal for real-time, low-latency querying. Depending on how it's configured, it could introduce delays in marketing campaigns.
- Cost: Dataproc Serverless may incur additional costs due to the resource consumption of running Spark jobs, which could be more expensive compared to using BigQuery.
- Scenario for use: Useful for batch processing large datasets or running complex transformations, but it is less suitable for real-time, high-frequency querying of Cloud SQL.
Option C: Create streams in Datastream to replicate the required tables from both Cloud SQL databases to BigQuery for these queries.
- Pros:
- Real-time replication: Datastream replicates changes in Cloud SQL databases to BigQuery in near real-time, red...
Author: FlamePhoenix2025 · Last updated Jun 11, 2026
Your organization is modernizing their IT services and migrating to Google Cloud. You need to organize the data that will be stored in Cloud Storage and BigQuery. You need to enable a data mesh approach to sh...
To determine the most appropriate approach for implementing a data mesh architecture that enables sharing between the sales, product design, and marketing departments, let's evaluate the options in terms of scalability, decentralization, security, and ease of use.
Option A: Create a project for storage of the data for each of your departments. Enable each department to create Cloud Storage buckets and BigQuery datasets. Create user groups for authorized readers for each bucket and dataset. Enable the IT team to administer the user groups to add or remove users as the departments request.
- Pros:
- Departmental ownership: Each department controls its data storage, making it easier to implement a data mesh where departments can own their data.
- Fine-grained access control: The IT team can manage access rights to ensure the correct users have access to specific data.
- Simple setup: This approach is relatively simple to implement, and you can keep the projects isolated by department.
- Cons:
- Lack of centralized discovery: Without a centralized data sharing and discovery platform, it can be difficult for departments to find and access data from other departments.
- Potential inefficiencies: As departments manage their own data independently, it could lead to duplication of effort in managing access and data sharing.
- Scenario for use: This would work in cases where each department has entirely independent data needs and minimal cross-department collaboration.
Option B: Create multiple projects for storage of the data for each of your department applications. Enable each department to create Cloud Storage buckets and BigQuery datasets. Publish the data that each department shares in Analytics Hub. Enable all departments to discover and subscribe to the data they need in Analytics Hub.
- Pros:
- Decentralization: Each department has control over its own data, while still making data discoverable by other departments through the centralized Analytics Hub.
- Scalability: The data mesh approach is effectively implemented, with departments owning their data and allowing access to others in a controlled manner.
- Centralized discovery: Analytics Hub allows departments to easily find and subscribe to data from other departments, which is a key feature for a data mesh.
- Cons:
- Complexity: Setting up and managing data sharing through Analytics Hub adds complexity, especially when dealing with large volumes of data or many departments.
- Potential cost: There could be costs associated with setting up and maintaining the Analytics Hub, especially if large amounts of data are shared.
- Scenario for use: This is an ideal solution when collaboration between departments is essential, and data needs to be shared and discovered efficiently across different teams. It's well-suited for organizations adopting a more complex, cross-departmental data sharing approach.
Option C: Create a project for storage of the data for your organization. Create a central Cloud Storage bucket with three folders to store the files for each department. Create a central BigQuery dataset with tables prefixed with the department name. Give viewer rights for the storage...
Author: Elizabeth · Last updated Jun 11, 2026
You work for a large ecommerce company. You are using Pub/Sub to ingest the clickstream data to Google Cloud for analytics. You observe that when a new subscriber connects to an existing topic to analyze data, they are unable to subscribe to older data. For an upcoming yearly sale event in two months...
To ensure that new subscribers can access the last 30 days of data from Pub/Sub, we need to address the issue of data retention. Let's evaluate each option based on the key factors of data availability, ease of implementation, and scalability.
Option A: Create a new topic, and publish the last 30 days of data each time a new subscriber connects to an existing topic.
- Pros:
- It gives full control over the data that is available to new subscribers.
- You can guarantee the availability of the last 30 days of data for new subscribers.
- Cons:
- Manual effort: Continuously re-publishing the last 30 days of data is highly inefficient and requires ongoing effort to extract and publish historical data every time a new subscriber joins.
- Scalability issues: As the business grows and more subscribers join, this process will become cumbersome.
- Redundant data: This leads to duplication of effort and data, increasing operational overhead.
- Scenario for use: This might be used as a short-term workaround but is not ideal for long-term scalability or efficiency.
Option B: Set the topic retention policy to 30 days.
- Pros:
- Automated solution: With the retention policy set to 30 days, Pub/Sub will automatically retain data for 30 days, meaning that any new subscriber can access the data that was published within the last 30 days without manual intervention.
- Scalable: This option scales well since the retention policy is automatically managed by Pub/Sub.
- Cons:
- Limited control: The retention period applies to all data in the topic, not just the data for specific subscribers.
- No historical data beyond 30 days: If a subscriber misses data from the last 30 days due to connection issues, they will not be able to access it once the retention period expires.
- Scenario for use: This is an ideal solution for cases where a fixed retention period is acceptable, and new subscribers need access to recent data for analysis without manual data management.
Option C: Set the subscriber retention policy to 30 days.
- Pros:
- Flexible: The subscriber retention policy can control how long Pub/Sub retains acknowledged messages for a specific subscriber.
- Cons:
- Doesn’t address the issue for new subscri...
Author: Zara · Last updated Jun 11, 2026
You are designing the architecture to process your data from Cloud Storage to BigQuery by using Dataflow. The network team provided you with the Shared VPC network and subnetwork to be used by your pipelines. You ...
To enable the deployment of your Dataflow pipeline on the Shared VPC network, it’s crucial to configure the correct roles and permissions. Let's analyze each option based on the context of Shared VPC and the necessary network permissions for Dataflow.
Option A: Assign the compute.networkUser role to the Dataflow service agent.
- Pros:
- Correct permission for Shared VPC network usage: The `compute.networkUser` role grants permission to use network resources, such as Virtual Private Cloud (VPC) networks and subnets. It allows the Dataflow service agent to access the shared network, which is crucial for executing pipelines in a Shared VPC environment.
- Cons:
- Limited scope: The Dataflow service agent may require other permissions, such as permissions related to managing or executing pipelines, which this role does not cover.
- Scenario for use: This option is a good choice, as it grants the necessary network access to the Shared VPC network for Dataflow, but it may not address all the permissions required for a fully functional pipeline.
Option B: Assign the compute.networkUser role to the service account that executes the Dataflow pipeline.
- Pros:
- Correct role for pipeline execution: The `compute.networkUser` role can grant the necessary permissions for the service account to access the shared network, but only if the service account is the one that interacts with network resources directly.
- Cons:
- Not sufficient for overall pipeline deployment: While this provides network permissions, it does not grant the service account permissions to fully manage and execute the Dataflow pipeline itself. For the pipeline to function properly, additional roles would likely be needed to allow Dataflow operations.
- Scenario for use: This is useful if the service account needs network-level access to Shared VPC, but it’s not sufficient by itself for Dataflow deployment.
Option C: Assign the dataflow.admin role to the Dataflow service agent.
- Pros:
- Full Dataflow permissions: The `dataflow.admin` role gives the service agent full permissions...
Author: SolarFalcon11 · Last updated Jun 11, 2026
Your infrastructure team has set up an interconnect link between Google Cloud and the on-premises network. You are designing a high-throughput streaming pipeline to ingest data in streaming from an Apache Kafka cluster hosted on- premi...
To design a high-throughput streaming pipeline with minimal latency, we must consider several key factors such as performance, ease of integration, maintenance overhead, and scalability. Let's analyze the options:
Option A: Setup a Kafka Connect bridge between Kafka and Pub/Sub. Use a Google-provided Dataflow template to read the data from Pub/Sub, and write the data to BigQuery.
- Reasoning:
- This option uses Kafka Connect to bridge Kafka and Pub/Sub, which is a scalable and managed solution. Pub/Sub is designed for high-throughput and low-latency streaming data and integrates easily with Google Cloud services such as Dataflow and BigQuery.
- Advantages:
- Pub/Sub is a fully managed service designed for stream ingestion with minimal latency.
- Kafka Connect offers a simple way to bridge on-prem Kafka with cloud services.
- Dataflow provides robust, scalable stream processing and has a native BigQuery connector for efficient data loading.
- Disadvantages:
- Slight increase in complexity due to Kafka Connect setup.
Option B: Use a proxy host in the VPC in Google Cloud connecting to Kafka. Write a Dataflow pipeline, read data from the proxy host, and write the data to BigQuery.
- Reasoning:
- This option involves setting up a proxy in Google Cloud, which can introduce unnecessary latency, complexity, and maintenance overhead, as it requires managing a proxy host for data transfer.
- Advantages:
- Simple approach, using VPC to connect directly to Kafka.
- Dataflow can handle the stream processing.
- Disadvantages:
- Managing the proxy can become cumbersome and less scalable.
- Latency might be higher due to the indirect connection.
- Not optimal for real-time, high-throughput workloads because the proxy may create bottlenecks.
Option C: Use Dataflow, write a pipeline that reads the data from Kafka, and writes the data to BigQuery.
- Reasoning:
- This option directly connects Dataflow to Kafka and BigQuery. However, Data...
Author: Julian · Last updated Jun 11, 2026
You migrated your on-premises Apache Hadoop Distributed File System (HDFS) data lake to Cloud Storage. The data scientist team needs to process the data by using Apache Spark and SQL. Security policies need to be enforced at the c...
To determine the best option for your use case, let's consider the key requirements:
- Column-level security: Security must be enforced at the column level for the data.
- Scalability: The solution must be able to scale, ideally into a data mesh.
- Cost-effectiveness: The solution should be cost-effective.
- Apache Spark and SQL processing: The data scientist team needs to use Apache Spark and SQL.
- Transition from on-prem Hadoop HDFS: The solution should allow easy migration from an HDFS-based data lake.
Option A: Deploy a long-living Dataproc cluster with Apache Hive and Ranger enabled. Configure Ranger for column-level security. Process with Dataproc Spark or Hive SQL.
- Reasoning:
- Advantages:
- Dataproc can handle Apache Spark, and Apache Hive provides SQL capabilities.
- Apache Ranger enables fine-grained access control (including column-level security).
- Dataproc is scalable and compatible with Hadoop workloads, providing a familiar environment for your data science team.
- Disadvantages:
- Cost: Running a long-living Dataproc cluster can be expensive, especially with the need to manage and scale the infrastructure.
- Complexity: Managing Dataproc clusters and configuring Apache Ranger for column-level security requires significant setup and ongoing maintenance.
- Limited integration for a data mesh: While Dataproc can scale, it is not as well suited to integrate into a broader cloud-native data mesh architecture.
Option B: Define a BigLake table. Create a taxonomy of policy tags in Data Catalog. Add policy tags to columns. Process with the Spark-BigQuery connector or BigQuery SQL.
- Reasoning:
- Advantages:
- BigLake tables provide a unified access layer across data in Google Cloud Storage and BigQuery.
- Policy tags in Data Catalog allow for column-level security by tagging columns and managing access based on these tags.
- BigQuery integrates well with Spark through the Spark-BigQuery connector, providing scalability and SQL-based processing.
- This solution leverages cloud-native services, reducing the complexity of managing infrastructure.
- BigLake tables are designed for high scalability and integration into a data mesh.
- Disadvantages:
- The initial setup for BigLake and policy tagging might require some effort, but once set up, it provides excellent scalability and flexibility.
Option C: Load the data to BigQuery tables. Create a taxonomy of policy tags in Data Catalog. Add policy tags to columns. Process with the Spark-BigQuery connector or BigQuery SQL.
- Reasoni...
Author: ElectricLionX · Last updated Jun 11, 2026
One of your encryption keys stored in Cloud Key Management Service (Cloud KMS) was exposed. You need to re- encrypt all of your CMEK-protected Cloud Storage data that used that key, and then delete the compromised key. You also want to reduce the risk of obje...
To address the scenario effectively, let's evaluate the options based on the following key requirements:
- Re-encrypt data: The data that was encrypted with the compromised key needs to be re-encrypted.
- Delete the compromised key: After re-encrypting the data, the compromised key should be deleted.
- Prevent future unprotected writes: Ensure that future data written to Cloud Storage is properly encrypted with a customer-managed encryption key (CMEK).
Option A: Rotate the Cloud KMS key version. Continue to use the same Cloud Storage bucket.
- Reasoning:
- Rotation of the KMS key is a good practice to manage encryption key lifecycle. However, rotating the key version does not re-encrypt the data that was already encrypted with the compromised key. Therefore, while it may prevent new data from being encrypted with the compromised key, it doesn't address the need to re-encrypt the existing data.
- Disadvantages:
- The existing data remains encrypted with the compromised key, which doesn’t resolve the issue of the exposed key.
- This option doesn’t fully meet the requirement of re-encrypting existing data with a new key.
Option B: Create a new Cloud KMS key. Set the default CMEK key on the existing Cloud Storage bucket to the new one.
- Reasoning:
- Changing the default CMEK key on the bucket ensures that any new data written to the bucket is encrypted with the new key. However, it doesn't address the re-encryption of the existing data that is already stored in the bucket.
- Disadvantages:
- This option doesn’t help with re-encrypting the existing data. The compromised key may still be used for accessing older objects in the bucket.
- The need to re-encrypt all existing objects with the new key is not addressed in this option.
Option C: Create a new Cloud KMS key. Create a new Cloud Storage bucket. Copy all objects from the old bucket to the new one bucket while specifying the new Cloud KMS key in the copy command.
- Reasoning:
- This option involves creating a new KMS key, and then migrating the objects to a new bucket configured to use this new key. When copying the obje...
Author: FlamePhoenix2025 · Last updated Jun 11, 2026
You have an upstream process that writes data to Cloud Storage. This data is then read by an Apache Spark job that runs on Dataproc. These jobs are run in the us-central1 region, but the data could be stored anywhere in the United States. You need to have a recovery process in place in case of a catastrophic single region failure. You need an approa...
To determine the best solution, let’s consider the following key requirements:
- Recovery Point Objective (RPO) of 15 minutes: The data loss in the event of a regional failure should be minimal, specifically no more than 15 minutes.
- Minimal latency: The system should be optimized for low-latency data reads when the Dataproc job accesses the data.
- Fault tolerance: The system should be resilient to a catastrophic failure in a single region.
- Seamless data replication and recovery: In case of a regional failure, there should be a smooth recovery process, with minimal disruption to the Dataproc jobs and data access.
Option A: Create two regional Cloud Storage buckets, one in the us-central1 region and one in the us-south1 region. Use the Storage Transfer Service to copy data hourly from the us-central1 bucket to the us-south1 bucket. Run the Dataproc cluster in a zone in the us-central1 region, reading from the bucket in that region. In case of regional failure, redeploy your Dataproc clusters to the us-south1 region and read from the bucket in that region instead.
- Reasoning:
- Advantages: The Storage Transfer Service allows for automated, scheduled data replication. You can set the replication to hourly, ensuring that data from us-central1 is backed up in us-south1.
- Disadvantages:
- 15-minute RPO: Hourly replication doesn’t meet the 15-minute RPO requirement because data loss in the event of a failure could be up to an hour, not 15 minutes.
- Latency: Data stored in different regions will introduce some latency when reading from the backup bucket in a failure scenario.
- Manual intervention: Redeploying Dataproc clusters to a different region would require manual intervention and may introduce delays.
Option B: Create a Cloud Storage bucket in the US multi-region. Run the Dataproc cluster in a zone in the us-central1 region, reading data from the US multi-region bucket. In case of a regional failure, redeploy the Dataproc cluster to the us-central2 region and continue reading from the same bucket.
- Reasoning:
- Advantages:
- A US multi-region bucket automatically replicates data across multiple regions within the United States, providing high availability and redundancy. This ensures that if one region fails, the data is still available.
- Dataproc can read from the multi-region bucket, providing minimal latency as the data is served from the nearest available region.
- Disadvantages:
- RPO: Although multi-region buckets ensure availability, the data loss (RPO) might still be affected by the propagation delay and replication lag. However, it generally supports a lower RPO compared to hourly replication.
- Latency: While the latency is minimized, in case of regional failure, redeploying Dataproc in another region might introduce some overhead in recovery time, but this is typically faster than manually switching between buckets.
Option C: Create a dual-region Cloud Storage bucket in the us-central1 and us-south1 regions. Enable ...
Author: Lucas · Last updated Jun 11, 2026
You currently have transactional data stored on-premises in a PostgreSQL database. To modernize your data environment, you want to run transactional workloads and support analytics needs with a single database. You need to move to Google Cl...
To determine the best option for your use case, let’s evaluate the key requirements:
- Transactional workloads: The solution should support transactional workloads, which are typically characterized by a high volume of reads and writes, and data integrity.
- Analytics: The solution should also support analytics on the same database.
- Minimal cost and complexity: The solution should minimize the cost and complexity, ensuring that you don’t need to make significant changes to your current database management system (DBMS).
Option A: Migrate and modernize your database with Cloud Spanner.
- Reasoning:
- Cloud Spanner is a globally distributed, horizontally scalable database, designed for high availability, with support for both transactional and analytical workloads.
- Advantages:
- Scalable and suitable for high-throughput transactional workloads with global consistency.
- Cloud Spanner supports SQL, making it easier to use for workloads that require both transactional processing and analytics.
- Disadvantages:
- Cost and complexity: Cloud Spanner is typically used for large-scale, distributed workloads. For smaller applications or those that don't require global distribution, Cloud Spanner can be more expensive than other alternatives like Cloud SQL.
- It would also require significant changes in the database structure and application code if you’re coming from a PostgreSQL database, which increases complexity.
- It’s an over-engineered solution for your scenario if you don’t need global distribution or the level of scalability that Cloud Spanner offers.
Option B: Migrate your workloads to AlloyDB for PostgreSQL.
- Reasoning:
- AlloyDB is a fully managed PostgreSQL-compatible database optimized for both transactional and analytical workloads. It offers a PostgreSQL-compatible interface but adds Google Cloud optimizations, such as high availability and horizontal scalability.
- Advantages:
- It’s PostgreSQL-compatible, so you don’t need to make major changes to your existing applications.
- Supports both transactional workloads and analytics (OLTP and OLAP), providing the ability to handle both types of operations efficiently.
- AlloyDB is designed for performance, offering better performance for analytic workloads compared to regular PostgreSQL.
- Disadvantages:
- Cost: AlloyDB may be more expensive than simpler solutions like Cloud SQL due to its advanced features.
- Still a newer offering, which means it might have fewer community resources and best practices compared to Cloud SQL or BigQuery.
Option C: Migrate to BigQuery to optimize analytics.
- Reasoning:
- BigQuery is a fully managed, serverless data warehouse that’s optimized for analytics and m...
Author: Emma Brown · Last updated Jun 11, 2026
You are architecting a data transformation solution for BigQuery. Your developers are proficient with SQL and want to use the ELT development technique. In addition, your developers need an intuitive coding environment and the ability to manage SQL...
Let's break down each of the options and their relevance for the given requirements:
A) Use Dataform to build, manage, and schedule SQL pipelines.
- Why it works:
- SQL-centric: Dataform is specifically built for managing and transforming data using SQL, which aligns with the fact that your developers are proficient with SQL and want to use the ELT development technique.
- Intuitive coding environment: Dataform provides an intuitive coding environment that supports version control and collaboration, allowing SQL scripts to be managed as code.
- Scheduling and orchestration: It also includes the ability to schedule and orchestrate SQL-based transformations directly in the platform, which fits your need to manage SQL pipelines efficiently.
- ELT support: As an ELT platform, Dataform focuses on transforming data once it's loaded into BigQuery, fitting the workflow the developers want.
- Why other options are less suited:
B) Use Dataflow jobs to read data from Pub/Sub, transform the data, and load the data to BigQuery.
- Why it doesn't work as well:
- Programming complexity: Dataflow is more geared towards advanced data transformations, often requiring Java or Python coding. It’s not as SQL-centric as the developers may need and could introduce unnecessary complexity if the team prefers to work in SQL.
- Not focused on ELT: While Dataflow can perform ETL, the focus is typically on stream processing or complex transformations. It isn't tailored for simple ELT processes where SQL-based transformations are preferred.
C) Use Data Fusion to build and execute ETL pipelines.
- Why...
Author: CrimsonViperX · Last updated Jun 11, 2026
You work for a farming company. You have one BigQuery table named sensors, which is about 500 MB and contains the list of your 5000 sensors, with columns for id, name, and location. This table is updated every hour. Each sensor generates one metric every 30 seconds along with a timestamp, which you want to store in BigQuery. You wa...
Let's analyze each option in terms of the data model requirements:
A) Create a metrics column in the sensors table. Set RECORD type and REPEATED mode for the metrics column. Use an UPDATE statement every 30 seconds to add new metrics.
- Why it doesn't work:
- Scalability: Adding a column to an existing table and using a REPEATED RECORD type for metrics could cause the sensors table to grow substantially, especially if the number of metrics per sensor increases. With 5000 sensors, each generating a metric every 30 seconds, this approach would likely lead to a large number of repeated records in the `sensors` table, which could make queries slower and more expensive.
- Performance impact: Using `UPDATE` statements every 30 seconds can cause locking and performance bottlenecks, especially for large tables. BigQuery is optimized for append-only operations (using `INSERT`), not frequent updates. This could lead to increased costs as well.
- Not optimal for cost: The growing size of the `sensors` table, combined with the `UPDATE` operations, could result in high storage and query costs over time.
B) Create a metrics column in the sensors table. Set RECORD type and REPEATED mode for the metrics column. Use an INSERT statement every 30 seconds to add new metrics.
- Why it doesn't work:
- Scalability issue: As with Option A, the `sensors` table could become very large due to the high frequency of metrics being appended every 30 seconds. Over time, this would not scale well for analytics or performance.
- Expensive queries: Since every metric is stored in the `sensors` table itself, analytical queries could become expensive, especially if you're querying data from many sensors or for a large time range. The need to unnest and process all the metrics stored in a single column could result in high query costs.
- Data management complexity: Storing metrics directly in the `sensors` table, which is meant to hold metadata, could lead to data management issues over time.
C) Create a metrics table partitioned by timestamp. Create a sensorId column in the metrics table, which points to the id column in the sensors table. Use an INSERT statement every 30 seconds to append new metrics to the metrics table. Join the two tables, if needed, when running the analytical query.
- Why it works:
- Optimized for scalability: Storing metrics in a separate table allows the `sensors` table to remain small, only...
Author: Abigail · Last updated Jun 11, 2026
You are managing a Dataplex environment with raw and curated zones. A data engineering team is uploading JSON and CSV files to a bucket asset in the curated zone but the files are not being automatically disco...
Let's evaluate each option for this scenario:
A) Move the JSON and CSV files to the raw zone.
- Why it doesn't work:
- The files are already in the curated zone, which is the zone intended for transformed, cleaned, and processed data. Moving them to the raw zone would defeat the purpose of the curated zone and violate the typical data lifecycle structure in Dataplex.
- Dataplex Discovery: Dataplex should be able to discover files in the curated zone as well, as long as they are properly configured for discovery.
- Not relevant to the issue: The problem is related to auto-discovery settings, not the location of the files. Moving files to the raw zone wouldn't address the underlying issue of Dataplex not automatically discovering them.
B) Enable auto-discovery of files for the curated zone.
- Why it works:
- Correct solution: Dataplex requires auto-discovery settings to be enabled in the zone where data is stored in order to automatically detect files. If the files are in the curated zone and Dataplex is not automatically discovering them, it suggests that auto-discovery has not been enabled for that zone.
- Discovery configuration: Enabling auto-discovery for the curated zone will allow Dataplex to scan and automatically discover the uploaded JSON and CSV files in that zone, making them available for further processing.
- Best practice: This is the most direct solution and aligns with how Dataplex works.
C) Use the `bg` command-line tool to load the JSON and CSV files into BigQuery tables.
- Why it doesn't work:
- The issue is related to Dataplex file discovery, not directly about loading files into BigQuery. The `bg` tool can ...
Author: MysticJaguar44 · Last updated Jun 11, 2026
You have a table that contains millions of rows of sales data, partitioned by date. Various applications and users query this data many times a minute. The query requires aggregating values by using AVG, MAX, and SUM, and does not require joining to other tables. The required aggregations are only computed over the past year of data, though you need to retain full historical data in the base tables. You wan...
Let's break down each option and evaluate it in terms of the requirements:
A) Create a materialized view to aggregate the base table data. Include a filter clause to specify the last one year of partitions.
- Why it works:
- Materialized views: Materialized views are precomputed and stored, which means the results of the query (including the aggregations) are already calculated and readily available, significantly reducing query time and computational costs.
- Partition filter: By specifying the last year of partitions in the filter, you're restricting the scope of the materialized view to just the most recent data, improving query performance since only the necessary data is included.
- Automatic refresh: Materialized views in BigQuery are automatically refreshed, ensuring that the query always includes the latest data without requiring manual intervention.
- Cost and performance efficient: Since the materialized view stores precomputed aggregations, it reduces both the computation cost and query duration for frequently queried data.
- Why other options are less suited:
B) Create a materialized view to aggregate the base table data. Configure a partition expiration on the base table to retain only the last year of partitions.
- Why it doesn't work as well:
- Partition expiration is not ideal: While setting a partition expiration on the base table ensures data retention for the last year, it removes historical data from the base table beyond a year, which is against the requirement to retain full historical data. The query only needs the last year, but historical data must still be kept.
- Data loss: This solution could lead to loss of data older than one year, which contradicts the requirement to keep full historical data.
C) Create a view to aggregate the base table data. Include a filter clause to specify the last year of partitions.
- Why it doesn't work as well:
- Non-materialized view: Views do not store precomputed results. Each time the view is queried, ...
Author: James · Last updated Jun 11, 2026
Your organization uses a multi-cloud data storage strategy, storing data in Cloud Storage, and data in Amazon Web Services' (AWS) S3 storage buckets. All data resides in US regions. You want to query up-to-date data by using BigQuery, regardless of which cloud the data is stored in. You need to al...
Let's break down each option based on the requirements:
A) Set up a BigQuery Omni connection to the AWS S3 bucket data. Create BigLake tables over the Cloud Storage and S3 data and query the data using BigQuery directly.
- Why it doesn't work as well:
- BigLake tables: BigLake tables are designed to provide a unified management layer over various data sources (including Cloud Storage and S3). However, BigLake is not focused on querying external data directly without copying it into BigQuery.
- Direct querying limitation: BigLake tables do not support querying data directly from external sources (like S3) in the same way external tables do. This means that you can't query data in S3 directly without it being first loaded into BigQuery, which goes against the requirement of querying the data without direct access to the storage buckets.
B) Set up a BigQuery Omni connection to the AWS S3 bucket data. Create external tables over the Cloud Storage and S3 data and query the data using BigQuery directly.
- Why it works:
- BigQuery Omni: BigQuery Omni allows you to query data stored in multiple clouds (Google Cloud and AWS) by using a unified interface. It enables you to connect to AWS S3 and query data directly in S3 without having to move it to Cloud Storage.
- External tables: By creating external tables over both Cloud Storage and S3 data, you can query the data from both clouds directly in BigQuery without needing to load it into BigQuery's internal tables. This allows users to query up-to-date data without giving them direct access to the storage buckets.
- Meets the requirement: This option allows you to query the data in both Cloud Storage and AWS S3 without moving it and without granting direct access to the storage buckets.
C) Use the Storage Transfer Service to copy data from the AWS S3 buckets to Cloud Storage buckets. Create BigLake tables over the Cloud Storage data and query the data using BigQuery directly.
- Wh...
Author: Isabella1 · Last updated Jun 11, 2026
You are preparing an organization-wide dataset. You need to preprocess customer data stored in a restricted bucket in Cloud Storage. The data will be used to create consumer analy...
When evaluating these options, the key factors to consider are:
1. Data Privacy Requirements: Ensuring that sensitive data is either masked, removed, or encrypted appropriately to comply with data privacy regulations.
2. Preprocessing Data for Analysis: The need to preprocess data for meaningful analysis without exposing sensitive information.
3. Data Encryption and Access Management: Ensuring proper encryption of sensitive data at rest and that only authorized entities have access to it.
Option Analysis:
A) Use Dataflow and the Cloud Data Loss Prevention API to mask sensitive data. Write the processed data in BigQuery.
- Reasoning: This option directly uses the Cloud Data Loss Prevention (DLP) API to identify and mask sensitive data. Masking is useful in ensuring that sensitive data (e.g., PII, financial info) is either obfuscated or removed for analytics purposes, allowing data processing without violating privacy laws.
- Pros: The DLP API specifically focuses on detecting and masking sensitive data. It works well for compliance requirements by ensuring privacy.
- Cons: While it masks sensitive data, the original data is processed and may still be at risk in intermediate stages.
B) Use customer-managed encryption keys (CMEK) to directly encrypt the data in Cloud Storage. Use federated queries from BigQuery. Share the encryption key by following the principle of least privilege.
- Reasoning: This option focuses on encryption of data at rest using customer-managed encryption keys (CMEK). Federated queries allow analysis without copying data into BigQuery, but encryption is key.
- Pros: Using CMEK ensures that only authorized users with the right key can decrypt the data. It also minimizes risk because the data is encrypted and kept secure.
- Cons: Federated queries may not be ideal for all types of analysis, as they might introduce latency and require complex access management for the encryption keys. It doesn't directly address the issue of detecting and masking sensitive data as in Option A or C.
C) Use the Cloud Data Loss Prevention API and Dataflow to detect and r...
Author: VenomousSerpent42 · Last updated Jun 11, 2026
You need to connect multiple applications with dynamic public IP addresses to a Cloud SQL instance. You configured users with strong passwords and enforced the SSL connection to your Cloud SQL instance. You want ...
When connecting multiple applications to a Cloud SQL instance using a public IP, there are several factors to consider, such as security, access control, network configuration, and ensuring encrypted connections.
Option Analysis:
A) Add CIDR 0.0.0.0/0 network to Authorized Network. Use Identity and Access Management (IAM) to add users.
- Reasoning: This option suggests adding the CIDR `0.0.0.0/0` network to the Authorized Network for the Cloud SQL instance. This would allow any external IP address to connect to the instance.
- Pros: This would make the Cloud SQL instance accessible from anywhere, as it doesn't restrict the origin of the requests.
- Cons: This is highly insecure, as allowing access from `0.0.0.0/0` means that any IP address can connect to your Cloud SQL instance, which exposes your database to potential attackers, even if IAM and strong passwords are used. This goes against security best practices.
B) Add all application networks to Authorized Network and regularly update them.
- Reasoning: This option allows specific application networks (with known IP addresses) to connect to the Cloud SQL instance by adding them to the Authorized Network list. You also need to regularly update these IP addresses as they change.
- Pros: This option is more secure than Option A, as only the specified networks can access the Cloud SQL instance. Regular updates are necessary because the applications have dynamic IP addresses.
- Cons: Maintaining a list of authorized IPs for dynamic IP addresses is cumbersome and prone to errors, especially as applications can change their public IPs frequently. It may be more difficult to manage if you have many applications with changing IPs.
C) Leave the Authorized Network empty. Use Cloud SQL Auth proxy on all applications.
- Reasoning: This option suggests leaving the Authorized Net...
Author: Aarav2020 · Last updated Jun 11, 2026
You are migrating a large number of files from a public HTTPS endpoint to Cloud Storage. The files are protected from unauthorized access using signed URLs. You created a TSV file that contains the list of object URLs and started a transfer job by using Storage Transfer Service. You notice that the job has run for a long time and eventually failed. Checking the logs of the transfer job reveals that the job was running fine until one point, and then it ...
To address the HTTP 403 errors encountered during the transfer job, we need to identify the core issue and choose the best method to fix it while considering the requirements for resuming the migration process.
Key Factors to Consider:
1. HTTP 403 Errors: This status code generally means that the request was denied due to authorization issues. In the context of signed URLs, this likely indicates that the URLs expired, were revoked, or are invalid due to a short validity period.
2. Signed URL Validity Period: If the validity period of the signed URLs was too short, the transfer job could run into 403 errors as the URLs expire during the job's execution.
3. Resuming Migration: We need to fix the issue without having to redo the entire migration, particularly avoiding the need to re-download already completed files.
Option Analysis:
A) Set up Cloud Storage FUSE, and mount the Cloud Storage bucket on a Compute Engine instance. Remove the completed files from the TSV file. Use a shell script to iterate through the TSV file and download the remaining URLs to the FUSE mount point.
- Reasoning: This approach involves using Cloud Storage FUSE to mount the Cloud Storage bucket and then manually transferring the remaining files using a shell script.
- Pros: This would allow you to manually download the remaining files without relying on the Storage Transfer Service.
- Cons: This method introduces complexity by requiring manual scripting and wouldn't solve the core issue of expired signed URLs. It also lacks scalability for large migrations, making it impractical for large-scale transfers. This approach doesn’t directly address the expiration problem and adds extra steps in the process.
B) Renew the TLS certificate of the HTTPS endpoint. Remove the completed files from the TSV file and rerun the Storage Transfer Service job.
- Reasoning: Renewing the TLS certificate is unrelated to the issue at hand. HTTP 403 errors are typically caused by expired or invalid signed URLs, not by issues with the TLS certificate of the HTTPS endpoint.
- Pros: This might resolve issues with expired certificates, but it...
Author: ThunderBear · Last updated Jun 11, 2026
You work for an airline and you need to store weather data in a BigQuery table. Weather data will be used as input to a machine learning model. The model only uses the last 30 days of weather data...
Key Factors to Consider:
1. Data Retention: We only need to keep the last 30 days of weather data, and unnecessary data should be automatically removed to minimize storage costs.
2. Data Expiration: Expiring or deleting data older than 30 days without requiring manual intervention.
3. Automation: The process should be automated to avoid unnecessary overhead in managing the data retention process.
Option Analysis:
A) Create a BigQuery table where each record has an ingestion timestamp. Run a scheduled query to delete all the rows with an ingestion timestamp older than 30 days.
- Reasoning: This option involves adding an ingestion timestamp to each record and manually running a scheduled query to delete records older than 30 days.
- Pros: This method can work, but it requires manual effort to set up and maintain the scheduled query. It also doesn't automatically expire data based on its age; instead, it relies on deleting rows explicitly.
- Cons: This solution adds overhead in managing the scheduled query. You also need to remember to handle the deletion, which could potentially lead to data being retained longer than necessary if the scheduled query is missed or fails.
B) Create a BigQuery table partitioned by datetime value of the weather date. Set up partition expiration to 30 days.
- Reasoning: Partitioning the table by the datetime value of the weather data and setting up partition expiration to 30 days ensures that data older than 30 days is automatically removed when the partition expires.
- Pros: This is a fully automated solution. Once set up, BigQuery will automatically manage the retention of data based on the partition expiration, avoiding any manual intervention.
- Cons: This solution works well if you want to retain data based on the date the weather data refers to, but there may be situations where data is inserted late or in bulk, and it might not align with the weather date partition.
C) Cre...
Author: Maya · Last updated Jun 11, 2026
You need to look at BigQuery data from a specific table multiple times a day. The underlying table you are querying is several petabytes in size, but you want to filter your data and provide simple aggregations to downstrea...
Let's analyze each option, considering the need to query data from a large table (several petabytes in size), apply filters, and run simple aggregations while getting faster query performance and up-to-date insights.
Key Requirements:
- Multiple queries per day: You need to run queries several times a day.
- Large underlying dataset: The table is several petabytes in size.
- Faster results and up-to-date insights: You need to provide fast query performance and real-time (or near real-time) insights.
- Simple aggregations and filtering: The queries will involve filtering data and performing aggregations, not complex joins or transformations.
---
Option A) Run a scheduled query to pull the necessary data at specific intervals daily.
- Explanation: A scheduled query will automatically run at fixed intervals (e.g., daily), but it doesn't address the need for real-time or near real-time insights. The data pulled will be static and only updated at the next scheduled interval, which could lead to outdated information between runs.
- Rejected: Since you need faster insights and up-to-date results multiple times a day, a scheduled query is not the best solution for your requirements, as it may introduce delays.
Option B) Use a cached query to accelerate time to results.
- Explanation: BigQuery provides a cache for previous query results. If a query is identical to one that was run recently and the underlying data has not changed, BigQuery will return cached results, improving performance. However, this only works if the query has been run before and the data hasn't changed. Since you're working with large datasets and require real-time data, cached queries are unlikely to provide the most up-to-date insights.
- Rejected: Cached queries are useful for repeated queries but do not address the need for fresh data, which is required in this case. Cached data wo...
Author: David · Last updated Jun 11, 2026
Your chemical company needs to manually check documentation for customer order. You use a pull subscription in Pub/Sub so that sales agents get details from the order. You must ensure that you do not process orders twice with di...
Let's evaluate each of the given options based on the scenario:
Option A: Use a Deduplicate PTransform in Dataflow before sending the messages to the sales agents.
- Pros: This would allow you to process messages in Dataflow, ensuring that duplicates are handled before the sales agents get them.
- Cons: While deduplication can be a useful strategy, this adds additional complexity to the workflow. It requires setting up a Dataflow job, which may increase operational overhead and latency.
- When to use: If the system's primary goal was to handle large-scale data processing with sophisticated transformations, a Deduplicate PTransform in Dataflow would be useful. However, in this case, the requirement is to avoid processing orders twice and avoid adding more complexity.
Option B: Create a transactional database that monitors the pending messages.
- Pros: A transactional database could store the status of each order and help track whether it's been processed or not. This could give you strong consistency.
- Cons: This requires additional infrastructure for database management and introduces complexity. It could also potentially create a bottleneck, especially with high-frequency orders, and requires maintaining the database's consistency and health.
- When to use: This option is viable if you want full transactional integrity with the ability to handle other complex operations around order processing. However, given the requirement to minimize complexity, this is not ideal.
Option C: Use Pub/Sub exactly-once delivery in your pull subscription.
- Pros: Pub/Sub with exactly-once delivery guarantees that each message is delivered only once to the subscriber, ...
Author: StarryEagle42 · Last updated Jun 11, 2026
You are migrating your on-premises data warehouse to BigQuery. As part of the migration, you want to facilitate cross-team collaboration to get the most value out of the organization's data. You need to design an architecture that would allow teams within the organization to securely publish, discover, and subs...
Let’s analyze each option to determine the best approach for facilitating secure, self-service cross-team collaboration while minimizing costs and ensuring data freshness:
Option A: Use Analytics Hub to facilitate data sharing.
- Pros: Analytics Hub is specifically designed for securely sharing datasets across teams and organizations. It allows you to publish datasets that can be accessed by other teams, provides a self-service model for data discovery and subscription, and supports features like data governance, access controls, and audit logging.
- Cons: It may have additional costs associated with its use, depending on the scale of data sharing and the number of teams involved. However, these costs are usually outweighed by the ease of use and security features.
- When to use: This option is ideal when the primary goal is secure, scalable, and efficient cross-team collaboration with minimal operational overhead. Analytics Hub simplifies data access and sharing without requiring manual dataset management.
Option B: Create authorized datasets to publish shared data in the subscribing team's project.
- Pros: Authorized datasets in BigQuery enable a user or team to access data in another project without having to copy or replicate the data. This approach supports data security and minimizes the cost of data duplication. Teams can access shared data while keeping their own data isolated.
- Cons: This option requires careful management of permissions and dataset access across projects. It is also more manual and less scalable than using Analytics Hub, especially if the number of teams or datasets increases.
- When to use: This approach works well in environments where teams already have well-established workflows around dataset management. It is useful when you need to grant access on a case-by-case basis, but may become cumbersome with large-scale collaboration.
Option C: Create a new dataset for sharing in each individual team's project. Grant the subscribing team the BigQuery.dataViewer role on the d...
Author: Andrew · Last updated Jun 11, 2026
You want to migrate an Apache Spark 3 batch job from on-premises to Google Cloud. You need to minimally change the job so that the job reads from Cloud Storage and writes the result to BigQuery. Your job is optimized for Spark, where each executor has 8 vCPU and 16 GB memory, and you want to b...
Let's evaluate each of the options based on the requirements of minimal change to the job, the need to read from Cloud Storage, write to BigQuery, and the need to minimize installation and management effort:
Option A: Execute the job as part of a deployment in a new Google Kubernetes Engine (GKE) cluster.
- Pros: GKE provides a scalable and managed environment for running containerized applications, which is ideal for highly customizable workloads. Kubernetes offers flexibility for running Spark jobs in containers with resource management, scaling, and fault tolerance.
- Cons: Running Apache Spark on GKE requires significant configuration and management. You’d need to containerize your Spark job, configure Kubernetes resources like pods, deployments, and services, and set up proper integration with Cloud Storage and BigQuery. This adds complexity and doesn't meet the goal of minimizing installation and management effort. Additionally, it requires expertise in managing Kubernetes clusters and scaling workloads.
- When to use: This approach is ideal for highly customized workloads and when you need to orchestrate multiple services within a containerized environment. However, it’s not the best option if your goal is simplicity and minimal management.
Option B: Execute the job from a new Compute Engine VM.
- Pros: This option gives you full control over the environment, which means you can configure the exact resources (vCPU, memory) and install Apache Spark. It’s a more customizable solution.
- Cons: Although you have flexibility in choosing resources, managing a VM for a Spark job involves manually installing and configuring Spark, Hadoop, and other necessary components. It also requires setting up dependencies for reading from Cloud Storage and writing to BigQuery. This introduces a high level of management effort, and scaling is more manual compared to other managed services.
- When to use: This approach can be used if you require full control over the environment, but it increases operational overhead compared to managed solutions, and doesn't align with the goal of minimizing management effort.
Option C: Execute the job in a new Dataproc cluster.
- Pros: Dataproc is a fully managed Apache Spark and H...
Author: Zara · Last updated Jun 11, 2026
You are configuring networking for a Dataflow job. The data pipeline uses custom container images with the libraries that are required for the transformation logic preinstalled. The data pipeline reads the data from Cloud Storage and writes the data to BigQuery. You need to...
Let's evaluate each option based on the need for cost-effective and secure communication between a Dataflow job and Google APIs and services, considering the requirement to read from Cloud Storage and write to BigQuery:
Option A: Disable external IP addresses from worker VMs and enable Private Google Access.
- Pros: This option ensures that the worker VMs do not have external IP addresses, which significantly improves security by preventing direct access from the internet. Enabling Private Google Access allows the Dataflow workers to communicate securely with Google Cloud services like Cloud Storage and BigQuery over the internal Google Cloud network without needing an external IP.
- Cons: There are minimal downsides to this setup, as it reduces the attack surface of the worker VMs and allows the data pipeline to communicate securely with Google services. There is no need for outbound internet connectivity, so this setup is cost-effective as well.
- When to use: This option is ideal for ensuring secure and cost-effective communication between the pipeline and Google services, as it eliminates the need for external IPs while allowing access to necessary services over the internal network. This setup reduces both security risks and costs associated with external IPs.
Option B: Leave external IP addresses assigned to worker VMs while enforcing firewall rules.
- Pros: Assigning external IPs allows the worker VMs to access the internet and Google services directly. Firewall rules can control which external traffic is allowed.
- Cons: Leaving external IPs on worker VMs exposes them to the internet, which increases security risks. Managing firewall rules introduces complexity, and while it can be done securely, it is more difficult to ensure all traffic is appropriately controlled. Additionally, maintaining external IPs incurs costs.
- When to use: This approach might be used if the pipeline requires direct internet access outside of Google Cloud, but in this case, the primary goal is to have secure and cost-effective communication with Google services. Therefore, this option introduces unnecessary complexity and security concerns.
Option C: Disable external IP addresses and establish a Private Service Connect endpoint IP address.
- Pros: Disabling external ...
Author: Emma · Last updated Jun 11, 2026
You are using Workflows to call an API that returns a 1KB JSON response, apply some complex business logic on this response, wait for the logic to complete, and then perform a load from a Cloud Storage file to BigQuery. The Workflows standard library does not have sufficient capabilities to perform your complex logic, and you...
Let's evaluate each of the options based on the requirements of applying complex Python logic in a workflow that is optimized for simplicity and speed of execution:
Option A: Create a Cloud Composer environment and run the logic in Cloud Composer.
- Pros: Cloud Composer is a fully managed workflow orchestration service based on Apache Airflow, and it can run Python logic. It integrates with many Google Cloud services, so it could manage dependencies and orchestrate the full pipeline.
- Cons: Cloud Composer is relatively heavyweight and typically used for long-running, complex workflows that involve many tasks and dependencies. Setting up Cloud Composer, even though it can run Python code, introduces more operational overhead, complexity, and cost compared to lighter alternatives like Cloud Functions. It may be overkill for your specific use case, as the task of running a small 1KB JSON logic doesn’t require an entire workflow orchestration service.
- When to use: Cloud Composer is suited for large-scale, multi-step workflows or when you need complex dependencies, but it's not ideal for small, straightforward logic or tasks that need to be quick and simple.
Option B: Create a Dataproc cluster, and use PySpark to apply the logic on your JSON file.
- Pros: Dataproc can provide distributed processing power, and PySpark is excellent for large-scale data processing, especially when the workload is distributed.
- Cons: Dataproc is generally used for big data processing tasks and typically involves significant overhead in terms of setup, cluster management, and resource allocation. For a simple task involving a 1KB JSON response and some business logic, setting up and managing a Dataproc cluster is inefficient and unnecessary. The operational complexity and associated costs make this an impractical solution for the scenario.
- When to use: Dataproc is ideal when you're dealing with large volumes of data and require parallel computation. However, it is over-engineered for small, simple tasks, such as applying logic to a small JSON response.
Option C: Invoke a Cloud Function instance that uses Python to apply the logic on your JSON file.
- Pros: Cloud Functions are serverless, meaning they are lightweight and scalabl...
Author: Liam123 · Last updated Jun 11, 2026
You are administering a BigQuery on-demand environment. Your business intelligence tool is submitting hundreds of queries each day that aggregate a large (50 TB) sales history fact table at the day and month levels. These queries have a slow response time and are exceeding c...
In this scenario, you are dealing with a large 50 TB sales history fact table, and you are looking to improve response time, lower query costs, and minimize maintenance.
Let's analyze the given options:
A) Build authorized views on top of the sales table to aggregate data at the day and month level.
- Reasoning: Authorized views are a way to control access to specific data, not a performance optimization technique. Building views does not solve performance or cost issues directly since they are essentially just queries with no materialization or caching. This would still result in querying the large sales table each time, leading to slow response times and high costs.
- Rejected: This option doesn't address performance or cost effectively.
B) Enable BI Engine and add your sales table as a preferred table.
- Reasoning: BI Engine is a high-performance in-memory analysis service for Google BigQuery. It accelerates queries by caching the data in memory, improving response times. However, BI Engine is best suited for smaller datasets or high-frequency queries over relatively small amounts of data. It can work well if the aggregates are already relatively small (such as summary tables) but may not be as effective for very large datasets (like 50 TB) because the memory-based cache could get overwhelmed. The cost-benefit could diminish with such a large table.
- Rejected: Although BI Engine can help with query speed, it might not be ideal for a 50 TB dataset and the associated costs might not scale well.
C) Build materialized views on top of the sales table to aggregate data at the day and month level.
- Reasoning: Materialized views are precomputed and stored query results, which significantly improve performance because they avoid recalculat...
Author: Kunal · Last updated Jun 11, 2026
You have several different unstructured data sources, within your on-premises data center as well as in the cloud. The data is in various formats, such as Apache Parquet and CSV. You want to centralize this data in Cloud Storage. You need to set up an object sink for ...
To centralize your unstructured data (in various formats like Apache Parquet and CSV) in Cloud Storage with the ability to use your own encryption keys and leverage a GUI-based solution, let's analyze the options:
A) Use BigQuery Data Transfer Service to move files into BigQuery
- Reasoning: BigQuery Data Transfer Service is typically used for moving data from external sources (such as Google Ads, YouTube, or cloud storage) into BigQuery for analytics. It’s not designed for handling unstructured data directly in Cloud Storage or for managing encryption keys. BigQuery is a data warehouse, and this option does not fit well with the goal of centralizing various file formats in Cloud Storage.
- Rejected: This is not suitable for unstructured data or for the need to use your own encryption keys for Cloud Storage.
B) Use Storage Transfer Service to move files into Cloud Storage
- Reasoning: Storage Transfer Service is specifically designed for transferring data from various sources (including on-premises data centers, other cloud storage, or even external HTTP locations) into Cloud Storage. It supports handling large volumes of data and can leverage encryption keys managed by the user. Additionally, it provides a GUI for easy management of transfers. This fits the requirement of centralizing unstructured data into Cloud Storage with custom encryption.
- Selected: This is the most suitable option because it meets the requirement to move various data formats into Cloud Storage while using your own encryption keys and offers a GUI-based interface.
C) Use Dataflow to move files into Cloud Storage
- ...
