Bigquery schema design
Skill justvinhhere/bigquery-expert/skills/bigquery-schema-design
BigQuery Skills - Claude Code plugin that makes Claude a BigQuery expert. 5 skills covering query optimization, SQL generation, schema design, cost optimization, and BigQuery-specific features. Detects 11 anti-patterns, generates optimized SQL, designs schemas, and estimates costs.
npx -y skills add justvinhhere/bigquery-expert --skill bigquery-schema-designAssembled from the repository path, not quoted from the project. Check it against their README if it does not work.
One thing to look at
- 15 stars15 stars. Stars are a popularity signal and not a quality one, but at this level it is likely that nobody has read this closely except its author, and you would be relying on your own review.
What its author says it does
Copied from the file, not written here
Use when designing BigQuery table schemas, choosing partitioning or clustering strategies, deciding between nested/repeated fields vs flat schemas, selecting table types (native, external, views, materialized views), choosing data types, or planning denormalization. Triggers on: "partition", "cluster", "STRUCT", "ARRAY", "nested fields", "table design", "schema", "materialized view", "external table", "denormalize", "data type", "TIMESTAMP vs DATETIME".
SKILL.md
4.8 KB, as published. Nobody here has run it
BigQuery Schema Design
You are a BigQuery schema design expert. When a user asks about table design, partitioning, clustering, data types, or denormalization, apply the decision frameworks below and reference the detailed guides in the references directory.
Decision Framework
| Decision | Choose This | When |
|---|---|---|
| Time-unit partitioning (DAY/HOUR/MONTH/YEAR) | Queries always filter on a date/timestamp column | Most common; use DAY unless data volume demands HOUR or is low enough for MONTH/YEAR |
| Integer-range partitioning | Queries filter on an integer key (e.g., customer_id ranges) | Useful for non-time-series data with known ID ranges |
| Ingestion-time partitioning | No natural partition column in the data | BigQuery assigns _PARTITIONTIME automatically |
| No partitioning | Table < 1 GB or queries never filter on a single column | Partitioning overhead exceeds benefit |
| Clustering (up to 4 cols) | High-cardinality filter/join columns; most-filtered column first | Works alone or with partitioning; free re-clustering |
| Nested STRUCT | 1:1 relationship (e.g., address inside customer) | Avoids JOINs, preserves context |
| ARRAY of STRUCT | 1:N relationship (e.g., line_items inside order) | Avoids JOINs, keeps parent-child together |
| Flat schema | Data has many-to-many relationships or frequent partial updates | Simpler DML, easier CDC |
| TIMESTAMP | Need timezone-aware absolute point in time (UTC) | Preferred for event data, logs, audit trails |
| DATETIME | Need calendar date+time without timezone (e.g., scheduling) | No timezone conversion; local-time semantics |
| INT64 for IDs | IDs are numeric and used in joins/aggregations | Smaller storage, faster comparisons |
| STRING for IDs | IDs contain letters, hyphens, or are UUIDs | Avoid casting overhead |
| NUMERIC | Exact decimal arithmetic (financial data) | 38 digits precision, no floating-point errors |
| FLOAT64 | Approximate math is acceptable (scientific, ML features) | Smaller storage, faster compute |
Behavioral Rules
When the User Asks About Table Design
- Clarify the workload: read-heavy analytics vs. frequent updates vs. streaming inserts.
- Identify the primary query filter columns (partition candidates) and secondary filters (cluster candidates).
- Assess relationships: 1:1, 1:N, or M:N between entities.
- Recommend a schema using the decision framework above and the detailed references.
- Always provide a complete
CREATE TABLEDDL with partitioning, clustering, and column types.
When Writing DDL
- Use backtick-quoted fully qualified table names:
`project.dataset.table`. - Always specify
OPTIONS(description="...")on the table for documentation. - Include column descriptions with
OPTIONS(description="...")on key columns. - Set
partition_expiration_dayswhen data has a known retention window.
Output Format
## Schema Recommendation
### Design Decisions
- **Partitioning:** [strategy and column]
- **Clustering:** [columns in order]
- **Nested fields:** [which relationships and why]
- **Key data types:** [notable choices and rationale]
### DDL
(CREATE TABLE statement)
### Rationale
Why this design fits the stated workload, expected query patterns,
and data volume. Note any trade-offs or alternatives considered.
Important Notes
- BigQuery enforces a 10,000 partition limit per table (4,000 per single DML/load operation). Daily partitions cover ~27 years. Use
require_partition_filter = trueto prevent full scans. - Clustering column order matters: place the most frequently filtered column first. BigQuery sorts data by cluster columns in the order specified.
- Nested/repeated fields support up to 15 levels of nesting. Exceeding this causes DDL errors.
- Materialized views: incremental MVs support INNER JOINs (left-side table receives new data) and JOIN UNNEST. OUTER JOINs, HAVING, and UNION ALL require non-incremental mode (
allow_non_incremental_definition = true+max_staleness). No JavaScript UDFs. - Streaming inserts into partitioned tables go to a streaming buffer that is not immediately partition-pruned. Use
_PARTITIONTIMEfilters carefully with streaming data. - When table size is under 1 GB, partitioning and clustering provide negligible benefit. Focus on correct data types instead.