Partitioning a Slow Ingest Pipeline in Azure Synapse
A dedicated SQL pool in Azure Synapse holds a 4 TB fact table FactSales loaded nightly from 400 source CSV files. The table was created with a hash-distributed SaleId and no partition. Nightly loads take 5 hours and fail with TempDB pressure. Analysts query the last 90 days 95% of the time. The business needs the load under 90 minutes and wants to drop data older than 3 years. The team proposes switching to round-robin distribution and adding a partition on SaleDate by day.
1. Evaluate the proposed round-robin plus daily partition design. What should be changed and why?
Read the worked answer
Round-robin is the wrong choice here. FactSales is a large fact table joined to dimensions on SaleId or DateKey; round-robin forces data movement on every join. A hash distribution on the most-used join column (often DateKey or SaleId) keeps joins co-located. The daily partition is also too granular: 3 years of daily partitions means ~1,095 partitions, which inflates metadata and switch operations. Partition by month (36 partitions) or by the retention boundary. Partitioning helps only when queries filter on the partition column; the 90-day query pattern supports a date partition, but the load bottleneck is more likely file count and staging. Recommend staging 400 files into a heap, then CREATE TABLE AS SELECT into the partitioned table, and use partition switching to drop old months.
2. The load still exceeds 90 minutes after partitioning. What three tuning changes would you test first?
Read the worked answer
First, reduce file count: 400 small files cause scheduler overhead; combine into fewer larger files or use PolyBase/COPY into a staging table. Second, ensure statistics are updated after load on join columns; stale stats cause bad shuffle plans. Third, use CTAS with MAXDOP and a resource class that gives enough memory, and consider REORGANIZE/REBUILD only if fragmentation is proven. Also verify the distribution column avoids skew; check DBCC PDW_SHOWSPACEUSED or sys.pdw_nodes_column_store_segments. Measure each change against the 90-minute SLA.
3. How would you implement the 3-year retention with minimal impact on running queries?
Read the worked answer
Use partition switching rather than DELETE. Keep a staging table with the same schema and filegroup, switch the oldest month's partition into it, then truncate the staging table. This is a metadata operation and avoids transaction log growth. Schedule it outside the load window and after statistics refresh. Verify that queries filtering on SaleDate benefit from partition elimination; if most queries do not filter by date, the retention gain is operational only.