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 = trueon 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.JOBSby 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
- Dataset/project layout with locations and access roles.
- Bytes-scanned report for the top 20 queries, with fixes identified.
- Partition filter enforced on the largest tables, proven by a failed query.
- Pricing model decision with the break-even calculation.
- Cost alerting on daily scan volume per project.
04 同仓库更多 Skills
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.
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.
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.
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.
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.
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.