skills.rest
snoodleboot-io
snoodleboot-ioOFFICIAL
Explore all skills by this author →
GitHub

data-partitioning

Designs time-based and key-based table partitions with pruning and maintenance SQL.

Updated Dec 29, 2025
Search Tags:#archival#sql#partitioning#data warehouse#bigquery#table design#partition pruning
One-click install
npx skills add https://github.com/snoodleboot-io/discrecontinual_equations --skill data-partitioning-snoodleboot-io
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: data-partitioning
Source: https://github.com/snoodleboot-io/discrecontinual_equations/tree/main/.claude/skills/data-partitioning
Command: npx skills add https://github.com/snoodleboot-io/discrecontinual_equations --skill data-partitioning-snoodleboot-io

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Large tables become slow and expensive to query when every request scans all rows. This Skill provides concrete SQL patterns for partitioning tables by date, customer, region, or hash so queries scan only relevant partitions. ## Core Features & Use Cases - Time-Based Partitioning: Monthly RANGE partitions in MySQL-style SQL and automatic DATE partitioning in BigQuery. - Partition Key Selection: A comparison table covering date, customer ID, region, and hash keys with trade-offs for each. - Partition Pruning & Maintenance: Query patterns that trigger pruning, archival to cold storage, automatic partition expiration, and size monitoring queries. - Use Case: A data engineer with a 100M-row orders table applies monthly partitions and rewrites queries to filter on the partition key, cutting scan time from 30 seconds to 3 seconds. ## Quick Start Ask the assistant to design a monthly partitioning scheme with pruning-safe queries for your events or orders table.

Frequently Asked Questions about data-partitioning

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I partition a table by date in SQL?▼

Use PARTITION BY RANGE on a date or timestamp expression, such as YEAR_MONTH(event_time), with one partition per month plus a MAXVALUE catch-all. In BigQuery, use PARTITION BY DATE(event_time) for automatic daily partitions.

What partition key should I choose for a large table?▼

Date keys suit time-series and archival workloads, customer ID fits multi-tenant data, region fits geo data with residency requirements, and hash keys give even distribution. Date is the default choice because it is naturally sequential.

Why is my partitioned table query still slow?▼

The query likely does not filter on the partition key, so the engine scans every partition. Add a WHERE clause matching the partition column, such as a date range, to enable partition pruning and reduce scanned rows.

How many partitions should a table have?▼

Keep partition counts under roughly 1000 and aim for 1GB to 100GB per partition. Too many small partitions add metadata overhead, while oversized partitions reduce the pruning benefit.

Does BigQuery support automatic partition expiration?▼

Yes. Set partition_expiration_ms on the table with ALTER TABLE ... SET OPTIONS, and BigQuery automatically deletes partitions older than the specified lifetime, such as one year.

Related Skills

Explore More Data & Analytics Skills→
ivanshamaevivanshamaev
14

airflow-starrocks-data-quality

Automate post-load data quality gating for StarRocks tables in Airflow.

Community
Advanced
timequitytimequity
6

warehouse-optimization

Optimize data warehouse queries and storage across Snowflake, BigQuery, and Redshift.

Official
Intermediate
ivanshamaevivanshamaev
14

trino-cost-optimization

Analyze Trino query cost drivers using system runtime tables.

Community
Advanced
aisa-groupaisa-group
90

sql-optimization-patterns

Analyze EXPLAIN output and recommend indexes and query rewrites for PostgreSQL and MySQL.

Official
Advanced