Troubleshooting Duplicate Ingestion in BigQuery Pipelines

PDE By islever Editorial Last updated

Short answer

C Investigate duplicate rows, correlate BigQuery Audit logs and Cloud Monitoring data to identify concurrent pipeline versions, then stop older versions to fix the data increase.

Question

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 some tables had their daily partition data size doubled. You need to investigate and fix the cause of the data increase. What should you do?
    1. Check for duplicate rows in the BigQuery tables that have the daily partition data size doubled. 2. Schedule daily SQL jobs to deduplicate the affected tables. 3. Share the deduplication script with the other operational teams to reuse if this occurs to other tables.
    1. Check for code errors in the deployed pipelines. 2. Check for multiple writing to pipeline BigQuery sink. 3. Check for errors in Cloud Logging during the day of the release of the new pipelines. 4. If no errors, restore the BigQuery tables to their content before the last release by using time travel.
    1. Check for duplicate rows in the BigQuery tables that have the daily partition data size doubled. 2. Check the BigQuery Audit logs to find job IDs. 3. Use Cloud Monitoring to determine when the identified Dataflow jobs started and the pipeline code version. 4. When more than one pipeline ingests data into a table, stop all versions except the latest one.
    Correct Answer
    1. Roll back the last deployment. 2. Restore the BigQuery tables to their content before the last release by using time travel. 3. Restart the Dataflow jobs and replay the messages by seeking the subscription to the timestamp of the release.

Key Takeaways

When data volume doubles post-deployment despite unchanged input, suspect concurrent old/new pipeline instances; use audit logs to verify and stop duplicates.

Learn how to diagnose and fix data duplication in BigQuery caused by ghost pipelines during deployment updates using Cloud Monitoring and Audit Logs.

Common mistake: Choosing rollback (D) or immediate deduplication (A) ignores the need to identify the root cause via logs, leading to recurring issues or wasted resources.

Expert Analysis

The scenario describes a classic "ghost pipeline" or duplicate ingestion issue following a deployment. The key constraints are that Pub/Sub volume remained constant, yet BigQuery partition sizes doubled for specific tables immediately after a new pipeline version was deployed. This strongly implies that the old pipeline instance was not successfully terminated or drained before the new one started, resulting in both versions writing to the same destination simultaneously.

Option C is the correct approach because it prioritizes investigation and root cause identification before remediation. Step 1 checks for the symptom (duplicate rows). Step 2 uses BigQuery Audit logs to identify the specific job IDs responsible for the writes. Step 3 correlates these jobs with Cloud Monitoring data to determine their start times and code versions, confirming if multiple versions ran concurrently. Step 4 provides the fix: stopping all but the latest valid version to prevent further duplication. This aligns with best practices for operationalizing pipelines, where understanding the lifecycle of jobs is critical.

Option A suggests deduplication as a primary fix, which treats the symptom rather than the cause and adds unnecessary compute cost. Option B skips the investigative correlation steps and jumps to restoration, which doesn't address the active bug causing double writes. Option D rolls back without investigating, potentially masking the deployment flaw, and replaying messages can lead to further complexity if the underlying logic error persists.

This question tests your ability to troubleshoot pipeline deployments using GCP observability tools (Audit Logs, Cloud Monitoring) and understand the operational consequences of improper pipeline version management.

Exam Strategy

Always correlate resource usage spikes with deployment timelines. When facing data anomalies post-deployment, check for concurrent job executions using BigQuery Audit Logs and Cloud Monitoring before applying fixes like deduplication or rollback.

Frequently Asked Questions

Why is checking BigQuery Audit logs important in this scenario?

Audit logs provide a record of all jobs executed, including their IDs and timestamps. This allows you to correlate increased data volume with specific job executions and identify if multiple pipeline versions were running simultaneously.

What is a 'ghost pipeline' in GCP data engineering?

A ghost pipeline refers to an old instance of a data pipeline that continues running after a new version has been deployed, often due to failure to cancel or drain the previous instance. This leads to duplicate data ingestion.

Why shouldn't I just deduplicate the data immediately?

Deduplication addresses the symptom, not the cause. If the ghost pipeline remains active, you will continue to incur storage costs and processing overhead for duplicate data until the root cause is resolved.

More PDE FAQ →

Community Votes

C
60%
B
40%

60% of anonymous learners picked answer C. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 9 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 9

  1. raaad 👍 12 Answered C
    - Detailed Investigation of Logs and Jobs Checking for duplicate rows targets the potential immediate cause of the issue. - Checking the BigQuery Audit logs helps identify which jobs might be contributing to the increased data volume. - Using Cloud Monitoring to correlate job starts with pipeline versions helps identify if a specific version of the pipeline is responsible. - Managing multiple versions of pipelines ensures that only the intended version is active, addressing any versioning errors that might have occurred during deployment. ======= Why not B While it addresses the symptom (excess data), it doesn't necessarily stop the problem from recurring. (The questions asked to investigate and fix)
  2. RenePetersen 👍 3
    This does not fix the error, it basically assumes that the error is not really there.
  3. task_7 👍 2 Answered B
    B. Check for code errors in the deployed pipelines, multiple writing to pipeline BigQuery sink, errors in Cloud Logging, and if necessary, restore tables using time travel. Check for code errors Check for multiple writes Check Cloud Logging Restore tables if necessary:
  4. SamuelTsch 👍 2 Answered B
    No idea which one to choose. Option C miss a step - to restore the tables.
  5. carbonscave 👍 1 Answered C
    Why C is the right moveThe "Ghost Pipeline" Anti-Pattern: The symptoms in the prompt are classic indicators of a botched deployment. If the incoming Pub/Sub volume hasn't changed, but the BigQuery partition size exactly doubled on the day of a release, it almost guarantees that the deployment failed to drain or cancel the old pipeline. Both the old version and the new version of the pipeline are currently running concurrently, reading the same messages, and writing them twice. The Investigation Playbook: Option C follows the exact Google Cloud incident response path for rogue writers. You confirm the duplicates exist $\rightarrow$ you check BigQuery Audit Logs to capture the specific principalEmail or jobId writing the data $\rightarrow$ you trace that identity back to the active Dataflow jobs. The Root Cause Fix: Once you visually confirm that multiple versions of the pipeline are running simultaneously and targeting the same tables, stopping the older, orphaned versions immediately stops the duplication at the source.
Show 4 more comments
  1. Ryannn23 👍 1
    " You need to investigate and fix the cause of the data increase. " - fixing the target tables was not required.
  2. judy_data 👍 1
    Because option D doesn't investigate the root cause of the issue and doesn't even check the code.
  3. Matt_108 👍 1 Answered C
    Option C - agree with Raaad on the reasons
  4. mi_yulai 👍 1
    Why not D?

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

← Back to PDE Study Guide