Bigquery Pipeline Audit
Review Python BigQuery jobs for runaway cost, weak idempotency, and silent failures before production runs.
Overview
BigQuery Pipeline Audit is an agent skill most often used in Ship (also Build, Operate) that reviews Python + BigQuery pipelines for cost safety, idempotency, and production readiness with a structured A–F report and exa
Install
npx skills add https://github.com/github/awesome-copilot --skill bigquery-pipeline-auditWhat is this skill?
- Structured audit sections A–F plus Final verdict on cost, safety, and readiness
- Maps every `client.query`, load, extract, copy, and external API/LLM call to billing exposure
- Flags loop-per-date or per-entity queries, missing `maximum_bytes_billed`, and >20 worst-case BQ jobs
- Idempotency and failure-visibility checks with minimal fixes tied to function names and line locations
- Senior data-engineer framing: no full rewrites—exact patch locations only
- Audit structure: sections A through F plus Final
- Immediate flag if worst-case BigQuery job count exceeds 20
- Checks `QueryJobConfig.maximum_bytes_billed` on every `client.query`
Adoption & trust: 8.5k installs on skills.sh; 34.6k GitHub stars; 3/3 security scanners passed (skills.sh audits).
What problem does it solve?
Your Python pipeline might scan terabytes in loops or rerun loads without idempotency, and you will not know until the invoice or corrupted partitions show up.
Who is it for?
Pre-production reviews of batch or incremental BigQuery scripts, post-incident cost forensics, and rerunnable ETL you cannot afford to duplicate.
Skip if: Greenfield pipeline design from scratch, non-BigQuery warehouses, or repos with no Python BigQuery client usage.
When should I use this skill?
When reviewing a Python + BigQuery pipeline script for production readiness, cost exposure, or rerun safety.
What do I get? / Deliverables
You receive a sectioned audit report naming risky jobs and line-level minimal fixes so you can harden the pipeline before production schedules or reruns.
- Structured audit report (sections A–F + Final)
- Flagged cost and idempotency risks
- Minimal patch suggestions at named functions/lines
Recommended Skills
Journey fit
Spans multiple journey phases - primary shelf plus alternate fits below.
Primary shelf is ship/review because the skill is a structured production-readiness audit with patch-level findings, even though pipeline authors use it during build and operate. Review fits cost-safety and idempotency checks on existing scripts—the same rigor as code review, applied to data pipelines.
Where it fits
Review a new incremental load script before wiring it into Cloud Functions.
Gate a PR that adds async gather around dozens of `client.query` calls.
After a billing alert, trace which loops and retries multiplied job count.
How it compares
Use as a focused data-pipeline reviewer instead of a generic lint pass that ignores bytes billed and job counts.
Common Questions / FAQ
Who is bigquery-pipeline-audit for?
Indie builders and small teams running Python jobs against BigQuery who need a cost-and-safety review before cron, Airflow, or Cloud Run schedules.
When should I use bigquery-pipeline-audit?
During build when finishing a pipeline module, at ship before enabling production schedules, or at operate after spend anomalies—whenever BQ jobs run in loops or retries.
Is bigquery-pipeline-audit safe to install?
It is read-only analysis guidance; confirm what your agent can read in the repo and check the Security Audits panel on this page before granting repository access.
SKILL.md
READMESKILL.md - Bigquery Pipeline Audit
# BigQuery Pipeline Audit: Cost, Safety and Production Readiness You are a senior data engineer reviewing a Python + BigQuery pipeline script. Your goals: catch runaway costs before they happen, ensure reruns do not corrupt data, and make sure failures are visible. Analyze the codebase and respond in the structure below (A to F + Final). Reference exact function names and line locations. Suggest minimal fixes, not rewrites. --- ## A) COST EXPOSURE: What will actually get billed? Locate every BigQuery job trigger (`client.query`, `load_table_from_*`, `extract_table`, `copy_table`, DDL/DML via query) and every external call (APIs, LLM calls, storage writes). For each, answer: - Is this inside a loop, retry block, or async gather? - What is the realistic worst-case call count? - For each `client.query`, is `QueryJobConfig.maximum_bytes_billed` set? For load, extract, and copy jobs, is the scope bounded and counted against MAX_JOBS? - Is the same SQL and params being executed more than once in a single run? Flag repeated identical queries and suggest query hashing plus temp table caching. **Flag immediately if:** - Any BQ query runs once per date or once per entity in a loop - Worst-case BQ job count exceeds 20 - `maximum_bytes_billed` is missing on any `client.query` call --- ## B) DRY RUN AND EXECUTION MODES Verify a `--mode` flag exists with at least `dry_run` and `execute` options. - `dry_run` must print the plan and estimated scope with zero billed BQ execution (BigQuery dry-run estimation via job config is allowed) and zero external API or LLM calls - `execute` requires explicit confirmation for prod (`--env=prod --confirm`) - Prod must not be the default environment If missing, propose a minimal `argparse` patch with safe defaults. --- ## C) BACKFILL AND LOOP DESIGN **Hard fail if:** the script runs one BQ query per date or per entity in a loop. Check that date-range backfills use one of: 1. A single set-based query with `GENERATE_DATE_ARRAY` 2. A staging table loaded with all dates then one join query 3. Explicit chunks with a hard `MAX_CHUNKS` cap Also check: - Is the date range bounded by default (suggest 14 days max without `--override`)? - If the script crashes mid-run, is it safe to re-run without double-writing? - For backdated simulations, verify data is read from time-consistent snapshots (`FOR SYSTEM_TIME AS OF`, partitioned as-of tables, or dated snapshot tables). Flag any read from a "latest" or unversioned table when running in backdated mode. Suggest a concrete rewrite if the current approach is row-by-row. --- ## D) QUERY SAFETY AND SCAN SIZE For each query, check: - **Partition filter** is on the raw column, not `DATE(ts)`, `CAST(...)`, or any function that prevents pruning - **No `SELECT *`**: only columns actually used downstream - **Joins will not explode**: verify join keys are unique or appropriately scoped and flag any potential many-to-many - **Expensive operations** (`REGEXP`, `JSON_EXTRACT`, UDFs) only run after partition filtering, not on full table scans Provide a specific SQL fix for any query that fails these checks. --- ## E) SAFE WRITES AND IDEMPOTENCY Identify every write operation. Flag plain `INSERT`/append with no dedup logic. Each write should use one of: 1. `MERGE` on a deterministic key (e.g., `entity_id + date + model_version`) 2. Write to a staging table scoped to the run, then swap or merge into final 3. Append-only with a dedupe view: `QUALIFY ROW_NUMBER() OVER (PARTITION BY <key>) = 1` Also check: - Will a re-run create duplicate rows? - Is the write disposition (`WRITE_TRUNCATE` vs `WRITE_APPEND`) intentional and documented? - Is `run_id` being used as part of the merge or dedupe key? If so, flag it. `run_id` should be stored as a met