AGENT SKILL

bigquery-pipeline-audit

Audits Python + BigQuery pipelines for cost safety, idempotency, and production readiness. Returns a structured report with exact patch locations.

License 许可
MIT
最近更新
2026-08-19
Stars
★ 2
Forks
0

01 前言 Front Matter

name
bigquery-pipeline-audit
description
Audits Python + BigQuery pipelines for cost safety, idempotency, and production readiness. Returns a structured report with exact patch locations.

03 SKILL.md 正文

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 metadata column, not as part of the uniqueness key, unless you explicitly want multi-run history.

State the recommended approach and the exact dedup key for this codebase.


F) OBSERVABILITY: Can you debug a failure?

Verify:

  • Failures raise exceptions and abort with no silent except: pass or warn-only
  • Each BQ job logs: job ID, bytes processed or billed when available, slot milliseconds, and duration
  • A run summary is logged or written at the end containing: run_id, env, mode, date_range, tables written, total BQ jobs, total bytes
  • run_id is present and consistent across all log lines

If run_id is missing, propose a one-line fix: run_id = run_id or datetime.utcnow().strftime('%Y%m%dT%H%M%S')


Final

1. PASS / FAIL with specific reasons per section (A to F). 2. Patch list ordered by risk, referencing exact functions to change. 3. If FAIL: Top 3 cost risks with a rough worst-case estimate (e.g., "loop over 90 dates x 3 retries = 270 BQ jobs").

04 同仓库更多 Skills

Reihantt6
Reihantt6/sns-myagent

youtube-transcript

Extract transcripts from YouTube videos. Use when the user asks for a transcript, subtitles, or captions of a YouTube video and provides a YouTube URL (youtube.com/watch?v=, youtu.be/, or similar). Supports output with or without timestamps.

★ 2
Reihantt6
Reihantt6/sns-myagent

youtube-thumbnail-design

YouTube thumbnail design with specific dimensions, contrast rules, and mobile preview optimization. Covers safe zones, text placement, face expression psychology, and A/B testing. Use for: YouTube thumbnails, video cover images, click-through optimization. Triggers: youtube thumbnail, thumbnail design, video thumbnail, click through rate, ctr optimization, youtube cover, video cover image, thumbnail maker, thumbnail tips, youtube design, video preview image

★ 2
Reihantt6
Reihantt6/sns-myagent

youtube-short-maker

Make YouTube Shorts that hold attention with Pexo — vertical 9:16, fast-paced, hook in the first 3 seconds. Describe your topic and Pexo writes the script, generates the shots, picks the models, and assembles a finished Short with music and captions. Use for YouTube Shorts: "youtube short", "make a youtube short", "shorts video", "short-form video". NOT for long-form or landscape video.

★ 2
Reihantt6
Reihantt6/sns-myagent

xcode-project-setup

Safely modifies Xcode projects (.pbxproj) to add Swift Packages and link files. Use this skill whenever an iOS project needs dependencies installed (e.g. Firebase, Alamofire).

★ 2
Reihantt6
Reihantt6/sns-myagent

x402

Set up Browser Use Cloud payments with x402 — pay per request from a crypto wallet (USDC on Base mainnet), no signup or API key. Two setups it works out up front — "just use it" (set up a wallet so you or Claude Code can run cloud browser tasks paid from the wallet — Claude writes and runs throwaway scripts, nothing touches your codebase) or "build it in" (install the SDK and write the key + code into your project). Walks through wallet setup, funding, .env, and a ~$1 test run. Use when the user asks about x402, pay-per-use, USDC payments, or wants Browser Use Cloud without an API key. For the free-tier signup (reverse-CAPTCHA → API key), use `browser-use cloud signup` or the `cloud` skill instead.

★ 2
Reihantt6
Reihantt6/sns-myagent

x-twitter-scraper

Build GitHub Copilot workflows with Xquik X API SDKs, REST endpoints, hosted Apify Actor runs, MCP tools, TweetClaw OpenClaw plugin installs, signed webhooks, tweet search, user lookup, follower exports, media actions, and agent automation.

★ 2