Bigquery Pipeline Audit

BigQuery pipeline audit

Review a Python + BigQuery pipeline, inventory every billable or external side effect, and return a risk-ranked production-readiness report with exact functions, line locations, and minimal fixes.

When to invoke

• "Audit this BigQuery pipeline before it runs in production."
• "Check this Python script for BigQuery cost and rerun safety."
• "Review these client.query and load_table_from_* calls."
• "Find runaway backfill, DDL/DML, extract_table, or copy_table risks."
• "Is this BigQuery job loop safe and observable?"

Cost exposure

Locate every BigQuery job trigger and every external side effect. Treat client.query, load_table_from_*, extract_table, copy_table, DDL/DML via query, external APIs, LLM calls, and storage writes as billable or mutating operations.

Flag immediately when any BigQuery query runs once per date or once per entity in a loop, worst-case BigQuery job count exceeds 20, or maximum_bytes_billed is missing on any client.query call.

Execution modes

Verify a --mode flag exists with at least dry_run and execute options.

If the flags are missing, propose a minimal argparse patch with safe defaults instead of redesigning the CLI.

Backfill and loop design

Hard fail when the script runs one BigQuery query per date or per entity in a loop. Date-range backfills must use one of these patterns:

Default date ranges should be bounded, usually no more than 14 days without an --override. A crash in the middle of a run must be safe to rerun without double-writing. Backdated simulations must read time-consistent data using FOR SYSTEM_TIME AS OF, partitioned as-of tables, or dated snapshot tables; flag any read from a latest or unversioned table in backdated mode.

Query safety and scan size

For each query, check the SQL shape before reviewing style.

Provide a concrete SQL fix for every failing query.

Safe writes and idempotency

Identify every write operation and flag plain INSERT or append with no deduplication logic.

Check whether reruns create duplicate rows, whether WRITE_TRUNCATE or WRITE_APPEND is intentional and documented, and whether run_id is incorrectly part of the uniqueness key. Store run_id as metadata unless multi-run history is explicitly required.

Observability

Failures must raise and abort; no silent except: pass and no warn-only failure path for a mutating operation. Each BigQuery job log should include job ID, bytes processed or billed when available, slot milliseconds, and duration. The run summary must include run_id, env, mode, date_range, tables written, total BQ jobs, total bytes, and run_id must be present on every log line.

If run_id is missing, propose this one-line fix:

run_id = run_id or datetime.utcnow().strftime('%Y%m%dT%H%M%S')

Criteria

Blocking failures

• Any client.query call lacks QueryJobConfig.maximum_bytes_billed.
• Worst-case BigQuery job count is greater than 20 or cannot be calculated.
• A backfill runs one query per date or one query per entity.
• Production can execute without --env=prod --confirm.
• A rerun can double-write records or corrupt final tables.

Review completeness

• Every BigQuery job trigger and external call is listed with function and line evidence.
• Every query has partition, projection, join, and expensive-operation checks.
• Every write operation has an idempotency strategy and dedup key.
• Every failure path and end-of-run summary is observable through logs or metrics.

Audit vocabulary

Preserve these review labels from the source checklist when reporting: COST EXPOSURE, DRY RUN AND EXECUTION MODES, BACKFILL AND LOOP DESIGN, QUERY SAFETY AND SCAN SIZE, SAFE WRITES AND IDEMPOTENCY, and OBSERVABILITY. Use the terms date-range, set-based, mid-run, re-run, and many-to-many exactly when they describe the risk. Include SQL evidence such as CAST(...) when a partition filter blocks pruning.

Output template

## BigQuery pipeline audit - <file or module>

**Verdict:** PASS | FAIL
**Scope reviewed:** <files, functions, and entry points>

### A. Cost exposure
| Trigger | Location | Loop/retry context | Worst-case count | Cap present | Finding |
| --- | --- | --- | --- | --- | --- |
| `<client.query or job>` | `<function:line>` | `<context>` | `<count>` | `<yes/no>` | `<risk>` |

### B. Dry run and execution modes
<pass/fail with evidence and minimal argparse patch if needed>

### C. Backfill and loop design
<pass/fail with backfill pattern, MAX_CHUNKS/MAX_JOBS evidence, and rerun safety>

### D. Query safety and scan size
| Query | Evidence | Risk | SQL fix |
| --- | --- | --- | --- |

### E. Safe writes and idempotency
| Write | Disposition | Dedup key | Rerun outcome | Recommendation |
| --- | --- | --- | --- | --- |

### F. Observability
<job ID, bytes, slot milliseconds, duration, run_id, and run summary findings>

### Patch list
1. `<highest-risk minimal patch with exact function>`
2. `<next patch>`

### Top 3 cost risks
1. `<rough estimate, for example 90 dates x 3 retries = 270 BigQuery jobs>`

Quality gate

• The report states PASS or FAIL with section-specific reasons for A through F.
• Every billable BigQuery operation and external call has exact location evidence.
• The worst-case job count calculation is explicit and compares against 20, MAX_JOBS, and MAX_CHUNKS where present.
• Missing maximum_bytes_billed, unsafe production defaults, and row-by-row backfills are treated as blocking failures.
• Every SQL recommendation preserves partition pruning and avoids SELECT *.
• Every write recommendation names a deterministic deduplication key and explains run_id usage.
• The patch list is ordered by production risk and avoids broad rewrites.