·

Real World Workflow Data Pipeline Debugging And Reporting

Walk through a real production workflow that uses Google Cloud MCP so your AI agent can manage cloud resources and investigate infrastructure end to end.

Workflow Overview: From Data Discrepancy Report to Verified Fix

The scenario: a finance stakeholder reports that the daily_revenue_summary BigQuery table shows numbers roughly 8% lower than the source system for the last five days, and asks for both a root-cause explanation and a corrected report. This is a realistic composite pipeline-debugging workflow — the kind of thing that normally eats half a day between manually querying BigQuery, digging through Cloud Run logs for the ETL job, checking GCS for intermediate files, and finally writing a fix.

Using Claude Code with a GCP MCP server (BigQuery, Cloud Run, Cloud Logging, and GCS all connected), the workflow breaks into three phases that map directly to the next three sections of this topic:

  1. Profile the dataset — confirm the discrepancy is real, quantify it precisely, and narrow down when it started.
  2. Trace the pipeline — find the Cloud Run ETL job responsible, read its logs for the affected run window, and check the GCS staging bucket for the intermediate files it processed.
  3. Fix and verify — identify the specific transform bug, apply a corrected backfill, and verify the fix with a query that would have caught the original discrepancy.
Session-opening prompt:
"Finance says daily_revenue_summary in analytics-project-prod
looks about 8% low for the last 5 days compared to the source
system. Let's investigate. Start by confirming the discrepancy
in BigQuery — don't touch the pipeline yet."

Framing the opening prompt this way — confirm before touching anything — is deliberate. The single most common mistake in AI-assisted incident response is jumping straight to "fix it" before the actual scope and cause are established, which risks a fix aimed at the wrong problem. Every tool call in this workflow that isn't a pure read (the final backfill) gets an explicit human confirmation checkpoint, which the walkthrough below marks clearly.

Tips
- Open an investigation by confirming the reported problem quantitatively before proposing any fix — a stakeholder's "about 8% low" needs a precise number before you can validate a fix against it.
- Structure the prompt sequence as profile → trace → fix, and don't let the agent skip ahead — each phase's output is the input the next phase needs to be well-scoped.
- Keep read-only investigation (BigQuery queries, log reads) unrestricted, but require explicit confirmation before anything that writes data (the backfill in phase 3).

Step 1: Profiling the Dataset and Locating the Anomaly with BigQuery

First, quantify the discrepancy precisely rather than trusting the stakeholder's rough "8% low" estimate — it's often close but rarely exact, and the exact number matters for validating the eventual fix.

Prompt:
"Check the schema of analytics-project-prod.reporting.
daily_revenue_summary and analytics-project-prod.raw_events.orders.
Then compare daily_revenue_summary's revenue_total against a
fresh SUM from raw_events.orders for the last 7 days, so we have
2 clean days as a baseline plus the 5 affected days."

-- Dry-run first, as always
SELECT
  s.summary_date,
  s.revenue_total AS summarized_revenue,
  ROUND(SUM(o.total_amount), 2) AS actual_revenue,
  ROUND(s.revenue_total - SUM(o.total_amount), 2) AS diff,
  ROUND(SAFE_DIVIDE(s.revenue_total - SUM(o.total_amount), SUM(o.total_amount)) * 100, 2) AS diff_pct
FROM `analytics-project-prod.reporting.daily_revenue_summary` AS s
JOIN `analytics-project-prod.raw_events.orders` AS o
  ON s.summary_date = DATE(o.order_date)
WHERE o.status = 'completed'
  AND s.summary_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
GROUP BY s.summary_date, s.revenue_total
ORDER BY s.summary_date;

Result:
summary_date  summarized_revenue  actual_revenue  diff       diff_pct
2026-08-14    142,830.00          142,830.00      0.00       0.0
2026-08-15    138,910.00          138,910.00      0.00       0.0
2026-08-16    121,455.00          131,982.50      -10,527.50 -7.98
2026-08-17    118,204.00          128,660.00      -10,456.00 -8.13
2026-08-18    125,077.00          135,930.00      -10,853.00 -7.98
2026-08-19    119,882.00          130,190.50      -9,908.50  -7.61
2026-08-20    122,301.00          132,780.00      -10,479.00 -7.83

That's a clean signal: exactly zero discrepancy through 2026-08-15, then a consistent ~8% shortfall starting 2026-08-16, holding steady since. A consistent percentage rather than a growing or erratic one usually points to a systematic transform bug (something excluding a fixed category of rows) rather than a partial-data or timing issue, which would typically show a shrinking or irregular gap.

Prompt:
"The gap is a consistent ~8% starting exactly 2026-08-16. Break
down actual_revenue by order status and by a few dimensions
(region, payment_method) for 2026-08-16 to see if one segment
accounts for the missing revenue."

SELECT
  payment_method,
  region,
  COUNT(*) AS order_count,
  ROUND(SUM(total_amount), 2) AS revenue
FROM `analytics-project-prod.raw_events.orders`
WHERE order_date = '2026-08-16' AND status = 'completed'
GROUP BY payment_method, region
ORDER BY revenue DESC;

The breakdown shows payment_method = 'store_credit' orders account for almost exactly the missing 8% — a plausible-looking segment that's easy to overlook in a transform that filters payment methods by an explicit allowlist instead of an exclusion list.

Tips
- Always establish a clean baseline period (days with zero discrepancy) before the affected window — it pinpoints the exact start date, which is essential for the log-tracing step next.
- A consistent percentage gap across days points toward a systematic transform bug; an irregular or growing gap points toward a data-freshness or partial-load issue — let the pattern shape where you look next.
- Segment-break the discrepancy by every dimension the table has (status, region, payment method, channel) before assuming you know the cause — the actual segment responsible is often not the first guess.

Step 2: Tracing the Pipeline Through Cloud Run Logs and GCS Artifacts

With 2026-08-16 as the exact start date and store_credit orders as the likely excluded segment, the next step is finding what deployed or changed around that date.

Prompt:
"What Cloud Run service handles the ETL for daily_revenue_summary?
List its revisions from the last 10 days and flag any deployed
on or just before 2026-08-16."

gcloud run revisions list --service=revenue-summary-etl \
  --region=us-central1 --format=json --limit=10 | \
  jq '[.[] | {name: .metadata.name, created: .metadata.creationTimestamp}]'

Result:
[
  { "name": "revenue-summary-etl-00034-old", "created": "2026-07-28T09:00:00Z" },
  { "name": "revenue-summary-etl-00035-new", "created": "2026-08-15T22:14:00Z" }
]

A revision deployed the night before the discrepancy started is exactly the lead we needed. Next, pull the diff between revisions and the logs from its first production run.

Prompt:
"revenue-summary-etl-00035-new deployed 2026-08-15 at 22:14 UTC,
right before the gap started. Show me the deploy logs and any
warnings from its first run after deploy, around 2026-08-16
02:00 UTC (assume the ETL runs nightly at 2 AM)."

gcloud logging read \
  'resource.type="cloud_run_revision"
   AND resource.labels.service_name="revenue-summary-etl"
   AND resource.labels.revision_name="revenue-summary-etl-00035-new"
   AND timestamp>="2026-08-16T01:55:00Z"
   AND timestamp<="2026-08-16T02:30:00Z"' \
  --format=json --limit=100

Relevant log line found:
{
  "severity": "WARNING",
  "textPayload": "Skipping 214 rows with payment_method not in
    allowed set ['credit_card', 'debit_card', 'paypal', 'bank_transfer']"
}

There's the bug, in a log line the pipeline was emitting the whole time but nobody was watching. The transform's payment method allowlist wasn't updated when store_credit was added as a valid payment method upstream — a classic allowlist-drift bug, and exactly the kind of thing a targeted log-tracing pass finds fast that a general dashboard alert wouldn't have caught (the ETL "succeeded," it just silently dropped rows).

Prompt:
"Check gs://analytics-etl-staging/revenue-summary/2026-08-16/
for the intermediate staging file this job produced, and
confirm it's missing store_credit rows too — that'll tell us
whether the bug is in the extraction step or the transform step."

gsutil cat gs://analytics-etl-staging/revenue-summary/2026-08-16/staged_orders.jsonl | \
  jq -c 'select(.payment_method == "store_credit")' | wc -l

Zero store_credit rows in the staged file confirms the drop happens during extraction/staging, not in a later transform step — narrowing exactly where the fix needs to go, in the extraction query or the allowlist config it reads from.

Tips
- Correlate the exact discrepancy start date against revision deploy timestamps first — a deploy the night before is a strong lead, but confirm it against logs rather than assuming correlation is causation.
- Search logs for WARNING-level messages, not just ERROR — a pipeline that "succeeds" while silently dropping rows logs a warning, if it logs anything at all, and dashboards rarely alert on warning volume.
- Check the intermediate GCS artifact to localize the bug to a specific pipeline stage (extraction vs. transform vs. load) before writing a fix — guessing which stage is wrong wastes a fix-and-redeploy cycle.

Step 3: Fixing the Transform and Backfilling with Verified Queries

With the root cause localized — a hardcoded payment method allowlist in the extraction step missing store_credit — the fix itself is small, but the backfill needs care because you're correcting five days of already-materialized summary data that finance may already be looking at.

Prompt:
"Show me the extraction query or config in the ETL service that
has the payment_method allowlist. I want to see the actual source,
not just infer it from the logs."

Since MCP GCP tools generally don't expose source code access, this step pulls the Cloud Run service's container image config and, more usefully, has the agent check if the allowlist is externalized to a config file readable via GCS or is baked into the image (requiring you to point it at the actual repo, which a coding agent handles the same as any other codebase task once located):

Prompt:
"Found it — the allowlist is in
etl/revenue_summary/config/payment_methods.yaml in our repo.
Update it to include store_credit, matching the format of the
existing entries."

allowed_payment_methods:
  - credit_card
  - debit_card
  - paypal
  - bank_transfer
  - store_credit  # added 2026-08-21 — was silently dropping ~8% of orders since 2026-08-16

With the fix identified and committed (through your normal PR process — this part is regular code review, not something to skip), the backfill is the data-side remediation:

Prompt:
"Before running any backfill, dry-run a query that recomputes
daily_revenue_summary for 2026-08-16 through 2026-08-20 using
the corrected logic (including store_credit), and show me the
bytes scanned and the corrected totals compared to what's
currently in the table."

-- Dry-run the corrected recomputation first
SELECT
  DATE(order_date) AS summary_date,
  ROUND(SUM(total_amount), 2) AS corrected_revenue
FROM `analytics-project-prod.raw_events.orders`
WHERE status = 'completed'
  AND order_date BETWEEN '2026-08-16' AND '2026-08-20'
GROUP BY summary_date
ORDER BY summary_date;

bq query --use_legacy_sql=false --dry_run \
  "$(cat ./scratch/corrected_revenue_backfill.sql)"

892 MB is trivial cost-wise; the dry run here is mostly a correctness gate, not a cost gate — confirming the query only touches the intended 5-day window before it writes anything.

Prompt:
"That matches our earlier actual_revenue numbers from step 1.
Now write and dry-run a MERGE statement that updates
daily_revenue_summary for those 5 dates with the corrected
totals, without touching any other rows."

MERGE `analytics-project-prod.reporting.daily_revenue_summary` AS target
USING (
  SELECT
    DATE(order_date) AS summary_date,
    ROUND(SUM(total_amount), 2) AS corrected_revenue
  FROM `analytics-project-prod.raw_events.orders`
  WHERE status = 'completed'
    AND order_date BETWEEN '2026-08-16' AND '2026-08-20'
  GROUP BY summary_date
) AS corrected
ON target.summary_date = corrected.summary_date
WHEN MATCHED THEN
  UPDATE SET
    target.revenue_total = corrected.corrected_revenue,
    target.last_corrected_at = CURRENT_TIMESTAMP();

bq query --use_legacy_sql=false --dry_run \
  --maximum_bytes_billed=2000000000 \
  "$(cat ./scratch/revenue_summary_merge.sql)"

Only after the dry run confirms the MERGE scopes correctly (touching exactly 5 rows, matched on summary_date) does it run for real — and immediately after, re-run the original discrepancy-detection query from Step 1 as verification, because a fix that isn't checked against the original symptom isn't actually verified.

Prompt:
"Run the MERGE for real now. Then re-run the exact comparison
query from the start of this investigation to confirm the
diff_pct is 0 across all 7 days."

Verification result:
summary_date  diff_pct
2026-08-14    0.0
2026-08-15    0.0
2026-08-16    0.0
2026-08-17    0.0
2026-08-18    0.0
2026-08-19    0.0
2026-08-20    0.0

Closing the loop with the exact query that first surfaced the problem, rather than a new spot-check, is what makes this a verified fix rather than a plausible one. Report back to finance with the root cause, the fix (allowlist config + a code comment marking when and why), and the corrected numbers — the whole investigation-to-verification cycle logged in one session transcript that anyone can audit later if the same category of bug recurs.

Tips
- Dry-run backfill queries for correctness (does it touch only the intended rows/dates) even when the byte cost is trivial — a MERGE with a wrong ON clause is a data-integrity risk regardless of how cheap it is to run.
- Route the actual code fix (the allowlist config change) through your normal PR review process — an AI-assisted investigation ending in an unreviewed direct commit skips the safety net that catches an equally-plausible-but-wrong fix.
- Always close a debugging session by re-running the original symptom-detection query against the fixed data — a fix you haven't re-verified against the original complaint is a hypothesis, not a resolution.

Tips

Tips
- Structure any AI-assisted pipeline debugging session as profile → trace → fix → verify, with an explicit human checkpoint before every state-changing step — this workflow's discipline generalizes well beyond this specific revenue bug.
- Keep the full session transcript (or a written summary of the tool calls and key findings) as the incident record — it's more precise than a manually written postmortem and took no extra time to produce.
- When a bug traces back to a hardcoded allowlist or similar brittle pattern, flag it as a follow-up even after the immediate fix ships — an allowlist that silently drops unrecognized values is a recurring bug shape, not a one-off.