Partitioning Strategy AI Prompt

Design the optimal partitioning and clustering strategy for this data warehouse table.

Table: {{table_name}}
Approximate size: {{table_size}}
Query patterns: {{query_patterns}}
Warehouse platform: {{platform}} (BigQuery / Snowflake / Redshift / Databricks / Trino)

1. Partitioning:
   - Partition by the column most frequently used in WHERE filters
   - For time-series data: partition by date (daily partitions for tables < 1TB, monthly for larger)
   - For non-time data: partition by a low-cardinality column (region, status, product_category)
   - Avoid over-partitioning: partitions should be > 100MB each to avoid small-file problems
   - Avoid under-partitioning: each partition should be a meaningful data subset to skip files effectively

2. Clustering / sort keys:
   - After partitioning, cluster by the next most common filter column (e.g. customer_id, product_id)
   - Cluster by columns used in JOIN conditions to collocate related rows
   - For Snowflake: choose cluster keys with high cardinality and low correlation with insert order
   - For BigQuery: cluster up to 4 columns in order of filter frequency
   - For Redshift: SORTKEY on the main time column, DISTKEY on the most common join key

3. Partition pruning validation:
   - Write a test query using EXPLAIN to confirm partition pruning is occurring
   - Alert if a query scans > {{max_scan_ratio}}% of partitions (indicates missing partition filter)

4. Maintenance:
   - For Delta/Iceberg: OPTIMIZE (compaction) and VACUUM (remove deleted files) on a schedule
   - For Redshift: VACUUM and ANALYZE after large loads
   - Monitor partition statistics: flag partitions with unusually high or low row counts

Return: partitioning and clustering DDL, partition pruning test query, and maintenance schedule.