AGENT SKILL

bigquery-architect

Architect-level BigQuery design — datasets and project layout, partitioning and clustering that actually prune, slots versus on-demand pricing, materialised views and BI Engine, streaming inserts versus the Storage Write API, and the query patterns that quietly scan terabytes. Use when the warehouse is BigQuery.

License 许可
MIT
最近更新
2026-11-13
Stars
★ 0
Forks
0

01 前言 Front Matter

name
bigquery-architect
license
MIT
metadata
{ "crusor-path": "data-engineering-architect/storage-architect/bigquery-architect", "crusor-domain": "data-engineering-architect", "crusor-origin": "original", "crusor-sources": "cloud.google.com/bigquery/docs/partitioned-tables; cloud.google.com/bigquery/docs/clustered-tables; cloud.google.com/bigquery/pricing", "crusor-triggers": "bigquery, bq slots, partitioned table, clustering bigquery, on demand pricing, storage write api, bi engine, google warehouse", "crusor-verified-on": "2026-10-30" }
description
Architect-level BigQuery design — datasets and project layout, partitioning and clustering that actually prune, slots versus on-demand pricing, materialised views and BI Engine, streaming inserts versus the Storage Write API, and the query patterns that quietly scan terabytes. Use when the warehouse is BigQuery.
compatibility
Google BigQuery

03 SKILL.md 正文

BigQuery Architect

BigQuery charges per byte scanned on-demand, or per slot-hour with capacity. Both models make table design the primary cost control.

When to use

  • The analytical store is BigQuery.
  • Query costs are surprising or unpredictable.
  • Choosing between on-demand and slot reservations.

When NOT to use

  • Cross-cloud analytical design → warehouse-architect.
  • Transactional workloads — BigQuery is not an OLTP store.

Decision 1 — Layout

organisation
  project per environment / business domain
    dataset per layer: raw_<source>, silver, gold_marts
      tables partitioned by date, clustered by high-selectivity column
  • Datasets are the access-control and location boundary; choose region once (multi-region costs more, is not always needed).
  • Separate billing projects for ad-hoc analysis so exploratory spend is visible.
  • Table naming: <layer>_<domain>_<entity>; undocumented tables become landfill.

Decision 2 — Physical design

Technique Effect
partition by DATE/TIMESTAMP column prunes whole days
partition by ingestion time useful only if you filter on it
clustering (up to 4 cols) prunes within partitions
require_partition_filter blocks accidental full scans
column types (no wide STRING blobs) scans are columnar
  • Set require_partition_filter = true on large tables — the cheapest guardrail available.
  • Clustering order matters: most selective first.
  • Nested/repeated fields reduce joins but can hide scan cost; measure.

Decision 3 — Pricing model

  • On-demand: simple, per-TB scanned, good for spiky or small workloads.
  • Slots (autoscaling reservations): better for predictable, high-volume BI.
  • Break-even is roughly total scanned TB/day versus slot cost; compute it with real numbers rather than intuition.
  • Reservations are per project/region; unused committed slots are still billed.

Decision 4 — Serving patterns

  • Materialised views for repeated aggregations; refresh cost is real.
  • BI Engine for dashboard-heavy concurrency.
  • Streaming inserts are for small volumes; Storage Write API for high throughput with exactly-once semantics.
  • Scheduled queries for transforms; dbt or Dataform for managed pipelines.

Decision 5 — Cost control

  • INFORMATION_SCHEMA.JOBS by user, project, and bytes billed — review weekly.
  • Guardrails: custom quotas, per-query byte limits, and partitioned table requirements.
  • Kill patterns: SELECT * on wide tables, cross joins, unpartitioned self-joins, and dashboards scanning raw events.
  • Cache hits are free; identical queries within 24 h reuse results — design dashboards to hit the cache.

Failure modes at scale

  • No partition filter required; one dashboard scans two years of events hourly.
  • Clustering on a low-cardinality column; no pruning benefit.
  • On-demand pricing with 200 dashboards refreshing every five minutes.
  • Streaming inserts for event data at scale; quota limits and duplicate rows.
  • Ad-hoc exploration billed to the production project, invisible in reports.

Verification receipts

  1. Dataset/project layout with locations and access roles.
  2. Bytes-scanned report for the top 20 queries, with fixes identified.
  3. Partition filter enforced on the largest tables, proven by a failed query.
  4. Pricing model decision with the break-even calculation.
  5. Cost alerting on daily scan volume per project.

04 同仓库更多 Skills

SriAkhilSJ
SriAkhilSJ/PulseAIRepo

interaction-patterns-architect

Architect-level interaction design in code — choosing established patterns over invented ones, feedback and latency behaviour, destructive-action safeguards, navigation and overlay conventions, and the state model behind every interactive surface. Use when a screen has to behave, not just look.

★ 0
SriAkhilSJ
SriAkhilSJ/PulseAIRepo

design-system-ops-architect

Architect-level operation of a design system in code — token pipelines and versioning, component API and variant policy, the catalogue as the contract, adoption metrics, and the governance that stops a system from forking into three. Use when a component library must survive several teams.

★ 0
SriAkhilSJ
SriAkhilSJ/PulseAIRepo

design-handoff-architect

Architect-level design handoff — what must be specified before implementation starts, tokens over raw values, responsive and content-limit contracts, state matrices, and the review loop that catches drift between design and code. Use when a design is about to become code.

★ 0
SriAkhilSJ
SriAkhilSJ/PulseAIRepo

content-microcopy-architect

Architect-level UI content — where strings live, voice and terminology consistency, error and empty-state copy as part of the interface, translation-ready authoring, and the content model a product needs before it multiplies screens. Use when the words in the interface matter as much as the pixels.

★ 0
SriAkhilSJ
SriAkhilSJ/PulseAIRepo

ui-ux-implementation-architect

Architect-level direction for turning a design into working UI — the handoff contract, component ownership versus screen-level layout, interaction and state conventions, design QA gates, and the review habits that keep an implementation faithful without frozen pixels. Use before any specific UI implementation skill.

★ 0
SriAkhilSJ
SriAkhilSJ/PulseAIRepo

test-strategy-architect

Architect-level test strategy — allocating assertions across unit, integration, contract and end-to-end layers, designing for testability through dependency boundaries, fixture and data strategy, and the flake and coverage policies that keep a suite trustworthy. Use when defining how a codebase is tested.

★ 0