Detailed Analysis

Understanding the Scenario

• Table1 has 1 billion existing rows with a clustered columnstore index
• The table is hash-distributed on Product Key across 60 distributions (default in Azure Synapse Analytics)
• 30 million new rows are added monthly
• Partitioning is required on Sales Date column to optimize query performance and data loading

Key Considerations for Partitioning in Azure Synapse Analytics

1. Minimum Row Requirements for Clustered Columnstore Index

• Microsoft recommends at least 1 million rows per distribution and partition for optimal compression and performance
• With 60 distributions, this translates to 60 million rows per partition for optimal performance

2. Data Distribution Analysis

• Monthly partitioning (Option A): 30 million rows ÷ 60 distributions = 500,000 rows per distribution

  • This falls below the recommended 1 million rows per distribution
  • Results in poor columnstore compression and suboptimal query performance

• Yearly partitioning (Option B): 360 million rows ÷ 60 distributions = 6 million rows per distribution

  • Exceeds the minimum requirement significantly
  • Provides excellent compression and query performance

• Daily partitioning (Option C): ~1 million rows ÷ 60 distributions = ~16,667 rows per distribution

  • Far below minimum requirements
  • Would create excessive partitions with poor performance

• Weekly partitioning (Option D): ~7.5 million rows ÷ 60 distributions = ~125,000 rows per distribution

  • Still below the 1 million row threshold

3. Query Performance Considerations

• Yearly partitions (Option B) provide:
  • Better columnstore compression ratios
  • More efficient segment elimination during queries
  • Reduced metadata overhead
  • Faster query execution for time-based filtering

4. Data Loading Optimization

• While monthly partitioning might seem intuitive for monthly data loads, the performance penalty from insufficient rows per distribution outweighs any loading benefits
• Azure Synapse Analytics can efficiently handle large partition loads
• The existing 1 billion rows already provide a solid foundation for yearly partitioning

Why Option B is Optimal

• Meets minimum row requirements: 6 million rows per distribution exceeds the 1 million threshold
• Optimal compression: Larger row counts per partition enable better columnstore compression
• Query performance: Fewer, larger partitions reduce metadata overhead and improve segment elimination
• Future scalability: Accommodates growth while maintaining performance standards

Why Other Options Are Less Suitable

• Option A (Monthly): Insufficient rows per distribution (500K < 1M minimum)
• Option C (Daily): Extremely granular partitioning with very poor performance
• Option D (Weekly): Still below minimum row requirements (125K < 1M)

Best Practice Alignment

Microsoft's documentation clearly states that for clustered columnstore tables in dedicated SQL pools, partitions should contain sufficient data to achieve at least 1 million rows per distribution. Yearly partitioning with 360 million total rows (6 million per distribution) aligns perfectly with this guidance while optimizing both query performance and data loading efficiency.