bigquery-schema-design

Name: bigquery-schema-design
Author: justvinhhere

15stars1forks

How to install bigquery-schema-design?

justvinhhere/bigquery-expert/bigquery-schema-design

$npx -y skills add justvinhhere/bigquery-expert --skill bigquery-schema-design

Installs into the current project.

Summary

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:

Files · 1

View on GitHub

SKILL.md

1	# BigQuery Schema Design
2
3	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.
4
5	## Decision Framework
6
7	\| Decision \| Choose This \| When \|
8	\|----------\|------------\|------\|
9	\| 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 \|
10	\| Integer-range partitioning \| Queries filter on an integer key (e.g., customer_id ranges) \| Useful for non-time-series data with known ID ranges \|
11	\| Ingestion-time partitioning \| No natural partition column in the data \| BigQuery assigns `_PARTITIONTIME` automatically \|
12	\| No partitioning \| Table < 1 GB or queries never filter on a single column \| Partitioning overhead exceeds benefit \|
13	\| Clustering (up to 4 cols) \| High-cardinality filter/join columns; most-filtered column first \| Works alone or with partitioning; free re-clustering \|
14	\| Nested STRUCT \| 1:1 relationship (e.g., `address` inside `customer`) \| Avoids JOINs, preserves context \|
15	\| ARRAY of STRUCT \| 1:N relationship (e.g., `line_items` inside `order`) \| Avoids JOINs, keeps parent-child together \|
16	\| Flat schema \| Data has many-to-many relationships or frequent partial updates \| Simpler DML, easier CDC \|
17	\| TIMESTAMP \| Need timezone-aware absolute point in time (UTC) \| Preferred for event data, logs, audit trails \|
18	\| DATETIME \| Need calendar date+time without timezone (e.g., scheduling) \| No timezone conversion; local-time semantics \|
19	\| INT64 for IDs \| IDs are numeric and used in joins/aggregations \| Smaller storage, faster comparisons \|
20	\| STRING for IDs \| IDs contain letters, hyphens, or are UUIDs \| Avoid casting overhead \|
21	\| NUMERIC \| Exact decimal arithmetic (financial data) \| 38 digits precision, no floating-point errors \|
22	\| FLOAT64 \| Approximate math is acceptable (scientific, ML features) \| Smaller storage, faster compute \|
23
24	## Behavioral Rules
25
26	### When the User Asks About Table Design
27	1. Clarify the workload: read-heavy analytics vs. frequent updates vs. streaming inserts.
28	2. Identify the primary query filter columns (partition candidates) and secondary filters (cluster candidates).
29	3. Assess relationships: 1:1, 1:N, or M:N between entities.
30	4. Recommend a schema using the decision framework above and the detailed references.
31	5. Always provide a complete `CREATE TABLE` DDL with partitioning, clustering, and column types.
32
33	### When Writing DDL
34	- Use backtick-quoted fully qualified table names: `` `project.dataset.table` ``.
35	- Always specify `OPTIONS(description="...")` on the table for documentation.
36	- Include column descriptions with `OPTIONS(description="...")` on key columns.
37	- Set `partition_expiration_days` when data has a known retention window.
38
39	## Output Format
40
41	```
42	## Schema Recommendation
43
44	### Design Decisions
45	- Partitioning: [strategy and column]
46	- Clustering: [columns in order]
47	- Nested fields: [which relationships and why]
48	- Key data types: [notable choices and rationale]
49
50	### DDL
51
52	(CREATE TABLE statement)
53
54	### Rationale
55	Why this design fits the stated workload, expected query patterns,
56	and data volume. Note any trade-offs or alternatives considered.
57	```
58
59	## Important Notes
60
61	- 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 = true` to prevent full scans.
62	- Clustering column order matters: place the most frequently filtered column first. BigQuery sorts data by cluster columns in the order specified.
63	- Nested/repeated fields support up to 15 levels of nesting. Exceeding this causes DDL errors.
64	- 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.
65	- Streaming inserts into partitioned tables go to a streaming buffer that is not immediately partition-pruned. Use `_PARTITIONTIME` filters carefully with streaming data.
66	- When table size is under 1 GB, partitioning and clustering provide negligible benefit. Focus on correct data types instead.

People who liked this also viewed

6 picks

Type

bigquery-schema-design

15stars1forks

View on GitHub

How to install bigquery-schema-design?

justvinhhere/bigquery-expert/bigquery-schema-design

$npx -y skills add justvinhhere/bigquery-expert --skill bigquery-schema-design

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# BigQuery Schema Design
2
3	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.
4
5	## Decision Framework
6
7	\| Decision \| Choose This \| When \|
8	\|----------\|------------\|------\|
9	\| 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 \|
10	\| Integer-range partitioning \| Queries filter on an integer key (e.g., customer_id ranges) \| Useful for non-time-series data with known ID ranges \|
11	\| Ingestion-time partitioning \| No natural partition column in the data \| BigQuery assigns `_PARTITIONTIME` automatically \|
12	\| No partitioning \| Table < 1 GB or queries never filter on a single column \| Partitioning overhead exceeds benefit \|
13	\| Clustering (up to 4 cols) \| High-cardinality filter/join columns; most-filtered column first \| Works alone or with partitioning; free re-clustering \|
14	\| Nested STRUCT \| 1:1 relationship (e.g., `address` inside `customer`) \| Avoids JOINs, preserves context \|
15	\| ARRAY of STRUCT \| 1:N relationship (e.g., `line_items` inside `order`) \| Avoids JOINs, keeps parent-child together \|
16	\| Flat schema \| Data has many-to-many relationships or frequent partial updates \| Simpler DML, easier CDC \|
17	\| TIMESTAMP \| Need timezone-aware absolute point in time (UTC) \| Preferred for event data, logs, audit trails \|
18	\| DATETIME \| Need calendar date+time without timezone (e.g., scheduling) \| No timezone conversion; local-time semantics \|
19	\| INT64 for IDs \| IDs are numeric and used in joins/aggregations \| Smaller storage, faster comparisons \|
20	\| STRING for IDs \| IDs contain letters, hyphens, or are UUIDs \| Avoid casting overhead \|
21	\| NUMERIC \| Exact decimal arithmetic (financial data) \| 38 digits precision, no floating-point errors \|
22	\| FLOAT64 \| Approximate math is acceptable (scientific, ML features) \| Smaller storage, faster compute \|
23
24	## Behavioral Rules
25
26	### When the User Asks About Table Design
27	1. Clarify the workload: read-heavy analytics vs. frequent updates vs. streaming inserts.
28	2. Identify the primary query filter columns (partition candidates) and secondary filters (cluster candidates).
29	3. Assess relationships: 1:1, 1:N, or M:N between entities.
30	4. Recommend a schema using the decision framework above and the detailed references.
31	5. Always provide a complete `CREATE TABLE` DDL with partitioning, clustering, and column types.
32
33	### When Writing DDL
34	- Use backtick-quoted fully qualified table names: `` `project.dataset.table` ``.
35	- Always specify `OPTIONS(description="...")` on the table for documentation.
36	- Include column descriptions with `OPTIONS(description="...")` on key columns.
37	- Set `partition_expiration_days` when data has a known retention window.
38
39	## Output Format
40
41	```
42	## Schema Recommendation
43
44	### Design Decisions
45	- Partitioning: [strategy and column]
46	- Clustering: [columns in order]
47	- Nested fields: [which relationships and why]
48	- Key data types: [notable choices and rationale]
49
50	### DDL
51
52	(CREATE TABLE statement)
53
54	### Rationale
55	Why this design fits the stated workload, expected query patterns,
56	and data volume. Note any trade-offs or alternatives considered.
57	```
58
59	## Important Notes
60
61	- 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 = true` to prevent full scans.
62	- Clustering column order matters: place the most frequently filtered column first. BigQuery sorts data by cluster columns in the order specified.
63	- Nested/repeated fields support up to 15 levels of nesting. Exceeding this causes DDL errors.
64	- 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.
65	- Streaming inserts into partitioned tables go to a streaming buffer that is not immediately partition-pruned. Use `_PARTITIONTIME` filters carefully with streaming data.
66	- When table size is under 1 GB, partitioning and clustering provide negligible benefit. Focus on correct data types instead.

People who liked this also viewed

6 picks

Type