Answer: clustered columnstore

## Analysis of the Scenario Given a large fact table in Azure Synapse Analytics dedicated SQL pool with: - 5 billion rows - 50 columns - Currently stored as a heap - Queries aggregating values from approximately 100 million rows - Queries returning only two columns - Poor query performance ## Recommended Solution: Clustered Columnstore Index **Why Clustered Columnstore Index (CCI) is the optimal choice:** ### 1. **Columnar Storage for Analytical Workloads** - Clustered Columnstore Index stores data in columnar format, which is ideal for analytical queries that typically scan large volumes of data but only access a subset of columns - Since queries return only two columns out of 50, columnar storage provides significant performance benefits by reading only the required columns ### 2. **Excellent Compression** - CCI provides superior data compression (typically 10x compression ratio) - Reduced I/O operations when scanning 100 million rows - Better memory utilization during query execution ### 3. **Batch Mode Processing** - Columnstore indexes enable batch mode processing, which processes multiple rows simultaneously - This is particularly effective for aggregation operations on large datasets ### 4. **Large Table Optimization** - Microsoft recommends clustered columnstore indexes for tables with more than 60-100 million rows - With 5 billion rows, this table clearly falls into the category where CCI provides maximum benefit ### 5. **Heap Conversion** - Since the table is currently a heap, converting to clustered columnstore index will organize the data optimally for analytical queries - The conversion process will restructure the entire table into columnar format ## Why Other Options Are Less Suitable ### **Nonclustered Columnstore Index (Option A)** - Not supported in Azure Synapse Analytics dedicated SQL pool - Only available in traditional SQL Server environments - Would not be a valid option for this Azure service ### **Nonclustered Index (Option C)** - Row-based storage doesn't provide the same compression benefits - Less efficient for scanning large volumes of data (100 million rows) - Would require creating multiple indexes for different query patterns - Not optimized for aggregation operations on large datasets ### **Clustered Index (Option D)** - Row-based storage limits compression benefits - Less efficient for analytical queries that access only a few columns - Better suited for OLTP workloads with point lookups rather than large-scale aggregations - Microsoft recommends clustered indexes for tables up to 100 million rows, not 5 billion ## Performance Impact Implementing a clustered columnstore index on this table would: - Reduce I/O by reading only the two required columns - Leverage high compression to minimize data movement - Enable batch mode processing for faster aggregations - Provide the optimal storage format for the described analytical workload pattern This solution aligns with Microsoft's best practices for large fact tables in data warehousing scenarios using Azure Synapse Analytics.