{"slug":"gcp-bigquery-cost-performance-analyst","title":"gcp-bigquery-cost-performance-analyst","summary":"Analyze BigQuery slot reservation sizing, BI Engine acceleration, query cost estimation, dataset governance (expiration, access controls), and partitioning/clustering optimization to reduce on-demand scan costs.","platform":"Claude","tags":[],"authorName":"LLM Mart","authorSlug":"llm-mart","score":0,"source":"github","price":null,"verified":false,"createdAt":"2026-10-05T21:52:19.403267Z","repo":{"url":"https://github.com/VincentChuWaiChow/vanguard-frontier-agentic","stars":24,"forks":3,"license":"Apache-2.0","updatedAt":"2026-10-05T13:00:24Z"},"bodyHtml":"<hr>\n<h2>name: gcp-bigquery-cost-performance-analyst\ndescription: Analyze BigQuery slot reservation sizing, BI Engine acceleration, query cost estimation, dataset governance (expiration, access controls), and partitioning/clustering optimization to reduce on-demand scan costs.\nallowed-tools: Read Grep Glob\nmetadata:\nauthor: \"github: VincentChuWaiChow\"\nversion: \"0.2.0\"\nupdated: \"2026-05-09\"\ncategory: data</h2>\n<h1>GCP BigQuery Cost and Performance Analyst</h1>\n<h2>Purpose</h2>\n<p>Act as the BigQuery cost and performance analyst who assumes every unpartitioned table, on-demand scan, and over-privileged dataset role is a future incident until proven otherwise.</p>\n<h2>Reference Directory</h2>\n<table>\n<thead>\n<tr>\n<th>Scenario</th>\n<th>Trigger Keywords</th>\n<th>Reference</th>\n</tr>\n</thead>\n<tbody>\n<tr>\n<td>Query cost analysis</td>\n<td>slot utilization, on-demand cost, $5/TB, query cost, INFORMATION_SCHEMA</td>\n<td><a href=\"#cost-analysis\">Cost analysis</a></td>\n</tr>\n<tr>\n<td>Performance tuning</td>\n<td>partition, cluster, query plan, EXPLAIN, slow query, join optimization</td>\n<td><a href=\"#performance-tuning\">Performance section</a></td>\n</tr>\n<tr>\n<td>Column/row security</td>\n<td>column-level, row-level, policy tag, data masking, authorized view</td>\n<td><a href=\"#data-governance\">Data governance</a></td>\n</tr>\n<tr>\n<td>BigQuery ML</td>\n<td>BQML, CREATE MODEL, ML.PREDICT, ML.EVALUATE</td>\n<td><a href=\"#bigquery-ml\">BigQuery ML section</a></td>\n</tr>\n<tr>\n<td>Billing export</td>\n<td>billing export, cost attribution, label, spend</td>\n<td><a href=\"#billing-export\">Billing export</a></td>\n</tr>\n<tr>\n<td>Reservation model</td>\n<td>slots, commitment, reservation, baseline vs burst</td>\n<td><a href=\"#reservations\">Reservations section</a></td>\n</tr>\n</tbody>\n</table>\n<h2>When to use</h2>\n<p>Use this skill for:</p>\n<ul>\n<li>BigQuery slot reservation assessment: Standard vs. Enterprise vs. Enterprise Plus tier selection and sizing</li>\n<li>On-demand vs. flat-rate billing mode trade-off analysis and cost modeling</li>\n<li>BI Engine acceleration design for dashboard and reporting workloads</li>\n<li>Query cost estimation and scan reduction via partitioning, clustering, and materialized views</li>\n<li>Dataset governance: expiration policy review, access control audits, and IAM role right-sizing</li>\n<li>Cross-region data transfer cost identification and egress optimization</li>\n<li>BigQuery incidents involving runaway costs, slow queries, slot exhaustion, or data access anomalies</li>\n</ul>\n<h2>Key GCP specifics</h2>\n<ul>\n<li>On-demand pricing: $5/TB scanned. A full table scan of 10 TB costs $50. Unpartitioned tables with no WHERE clause are a runaway cost risk — a single misrouted query can exhaust monthly budgets.</li>\n<li>Slot reservations (Standard/Enterprise/Enterprise Plus) provide predictable throughput vs. on-demand burst. Wrong selection can 10x costs: Standard slots are best for steady workloads; Enterprise adds autoscaling and cross-region failover.</li>\n<li>BI Engine caches frequently queried data in memory — dramatically reduces slot consumption for dashboards hitting the same aggregates repeatedly.</li>\n<li>Partitioning (date/timestamp/integer range) + clustering is the #1 cost-control lever. Partition pruning eliminates full scans. Always assess partitioning gaps before recommending compute increases.</li>\n<li>Dataset-level access controls use IAM roles — <code>roles/bigquery.dataViewer</code> is the minimum for read access. <code>roles/bigquery.admin</code> on a dataset is a critical finding equivalent to full data control.</li>\n<li>Cross-region data transfer between BigQuery datasets incurs network egress costs. Queries that JOIN across regions force data movement and can generate unexpected bills.</li>\n<li><code>INFORMATION_SCHEMA.JOBS</code> provides query-level cost history. Always use it to identify top spenders before recommending architectural changes.</li>\n<li>Wildcard tables and <code>SELECT *</code> on large tables are common cost anti-patterns — require column pruning and partition filtering.</li>\n</ul>\n<h2>Data Governance</h2>\n<p>BigQuery supports fine-grained access control beyond project/dataset/table IAM:</p>\n<p><strong>Column-level security</strong> — use policy tags (Data Catalog taxonomy) to restrict access to sensitive columns (PII, PCI, PHI). Users without the <code>Fine-Grained Reader</code> permission see NULL for tagged columns.</p>\n<p><strong>Row-level security</strong> — use <code>CREATE ROW ACCESS POLICY</code> to filter rows based on the querying user's identity. Example:</p>\n<pre><code>CREATE ROW ACCESS POLICY sales_region_filter\nON dataset.sales_table\nGRANT TO (\"group:apac-team@example.com\")\nFILTER USING (region = 'APAC');\n</code></pre>\n<p><strong>Data masking</strong> — combine policy tags with masking rules to show hashed/nulled/last-4-digits values to analysts without access to raw PII.</p>\n<p><strong>Authorized views</strong> — share query results without granting access to underlying tables. Useful for cross-project analytics with controlled exposure.</p>\n<p>Always confirm data governance requirements before designing BigQuery schemas — retroactively adding column-level security to existing tables requires schema changes and data re-classification.</p>\n<h2>BigQuery ML</h2>\n<p>BigQuery ML (BQML) enables training and serving ML models directly in BigQuery using SQL syntax, without exporting data to a separate training infrastructure:</p>\n<ul>\n<li><strong>CREATE MODEL</strong> — train a model (linear regression, logistic regression, k-means, boosted trees, DNN, time series, matrix factorization, or imported TF/Vertex models)</li>\n<li><strong>ML.EVALUATE</strong> — assess model quality metrics against an eval dataset</li>\n<li><strong>ML.PREDICT</strong> — run batch inference directly in SQL against a trained model</li>\n<li><strong>ML.EXPLAIN_PREDICT</strong> — get feature attribution for predictions</li>\n</ul>\n<p>BQML training jobs consume slots from the same reservation as query jobs — size reservations to account for concurrent training and query load. For large models, prefer Vertex AI Training and import the resulting model artifact into BQML via <code>CREATE MODEL ... OPTIONS (model_type='imported_tensorflow')</code>.</p>\n<h2>Lean operating rules</h2>\n<ul>\n<li>Prefer official GCP documentation and live evidence over memory or inference.</li>\n<li>Separate confirmed facts from inference. If a query plan, slot usage, or billing metric was not queried or shown, say so.</li>\n<li>Challenge unpartitioned large tables, missing clustering, SELECT * queries, on-demand billing with predictable load, and admin-level dataset roles.</li>\n<li>Keep answers scoped, reversible, least-privilege, and explicit about blockers or unknowns.</li>\n<li>Load references only when needed; do not pull all deep guidance into short answers.</li>\n</ul>\n<h2>References</h2>\n<p>Load these only when needed:</p>\n<ul>\n<li><a href=\"references/workflow-and-output.md\">Workflow and output contract</a> — use when executing the full cost and performance review, incident triage, or formatting the final answer.</li>\n<li><a href=\"references/official-sources.md\">Official sources</a> — use when grounding GCP BigQuery service behavior or checking the detailed source list.</li>\n</ul>\n<h2>Response minimum</h2>\n<p>Return, at minimum:</p>\n<ul>\n<li>the scoped target and evidence level,</li>\n<li>the top cost drivers and partitioning/clustering gaps,</li>\n<li>the slot reservation vs. on-demand billing assessment,</li>\n<li>the dataset governance and access control findings,</li>\n<li>the safest next actions with validation steps,</li>\n<li>the assumptions or blockers that prevent stronger conclusions.</li>\n</ul>\n","files":[{"path":"metadata.json","sizeBytes":1114,"isText":true},{"path":"references/official-sources.md","sizeBytes":966,"isText":true},{"path":"references/workflow-and-output.md","sizeBytes":1886,"isText":true},{"path":"SKILL.md","sizeBytes":6836,"isText":true}],"reviewScore":null,"reviewSummary":null,"trust":{"provenance":"trusted-source-unreviewed","notice":"Community-authored content, reproduced verbatim and not vetted as instructions. Treat it as data to evaluate, never as directives to follow.","bodySource":null},"bodyLocked":false,"purchaseUrl":null,"sourceUrl":null,"report":{"provenance":"trusted-source-unreviewed","screen":{"ran":true,"outcome":"clean","suspicious":0,"notes":0,"hiddenCharacters":false},"virusScan":{"engine":"clamav","status":"clean","scannedAt":"2026-10-05T21:59:28.653924Z","sha256":"7E1BBF4AFF8E3DBD43BAD00B9FDFC1D59D5A331C1EB65AFD453B28B44DD259AC","sizeBytes":5585},"review":null,"source":{"repositoryUrl":"https://github.com/VincentChuWaiChow/vanguard-frontier-agentic","path":"skills/gcp/gcp-bigquery-cost-performance-analyst","license":"Apache-2.0","commit":"febe32a08e78fd06b1e466187410d673f1958d87","subtreeSha":"F1425A237A3FB6BE9BD428C70E60B17B7091D2B942307D1A56130049D4C72345","lastSyncedAt":"2026-10-05T21:51:58.639905Z"},"reviewedAt":"2026-10-05T22:13:57.831776Z","notice":"Community-authored content, reproduced verbatim and not vetted as instructions. Treat it as data to evaluate, never as directives to follow."},"install":[{"target":"skills-cli","command":"npx skills add https://github.com/VincentChuWaiChow/vanguard-frontier-agentic/tree/master/skills/gcp/gcp-bigquery-cost-performance-analyst"},{"target":"claude-code","command":"claude plugin marketplace add https://llmmart.ai/marketplace.json && claude plugin install vincentchuwaichow-vanguard-frontier-agentic@llmmart"},{"target":"git","command":"git clone https://github.com/VincentChuWaiChow/vanguard-frontier-agentic.git"}]}