This is the first in a four-part series of blog posts explaining my journey using SQL Server’s partition switching functionality to load a very large Fact tables.
Partition switching is a Microsoft best practice data loading technique as described in Microsoft’s Data Loading performance guide for large data warehouse loads. Microsoft also claimed the world record for the fastest data load time for a Data Warehouse a few years back and the Partition Switching formed part of this project.
I am involved in a project that requires we load +- 25 Million records each day into a Fact table. As a result we identified Partition Switching as a must use technique for the Fact table data load.
When implementing a partition switching strategy a Staging Fact table with exactly the same structure as the actual Fact table must exist on the same filegroup. The data inserts into a Staging Fact Table where a simple Alter Table …. Switch statement switches the data into the Fact table. This makes for a very fast loading technique because only the Metadata in the filegroup updates and no actual data rows inserted into the potentially very large Fact table.
Before doing the actual Alter Table … Switch a number of steps has to be followed to prepare the database schema and data for the Partition Switch and this requires some work. The following TechNet article describes all the schema requirements to enable partition switching.
The next blog will explain the first section of technical work we need to do for our Partition Switch strategy.
Please comment and share your learning’s around partition switching using SQL Server.