Loading even very large fact tables is not a problem if you can perform incremental loads. However, this means that data in the source should never be updated or deleted; data should only be inserted. This is rarely the case with LOB applications. In addition, even if you have the possibility of performing an incremental load, you should have a parameterized Extract- Transform-Load (ETL) procedure in place so you can reload portions of data loaded already in earlier loads. There is always the possibility that something might go wrong in the source system, which means that you will have to reload historical data. This reloading will require you to delete part of the data from your DW.
Deleting large portions of fact tables might consume too much time unless you perform a minimally logged deletion. A minimally logged deletion operation can be done using the TRUNCATE TABLE command; however, in previous versions of SQL Server, this command deleted all the data from a table, and deleting all the data is usually not acceptable. More commonly, you need to delete only portions of the data. With SQL Server 2016 and 2017, truncation is somewhat easier, because you can truncate one or more partitions.
Inserting huge amounts of data can consume too much time as well. You can do a minimally logged insert, but as you already know, minimally logged inserts have some limitations. Among other limitations, a table must either be empty, have no indexes, or use a clustered index only on an ever-increasing (or ever-decreasing) key so that all inserts occur on one end of the index.
You can resolve all of these problems by partitioning a table. You can achieve even better query performance using a partitioned table because you can create partitions in different filegroups on different drives, thus parallelizing reads. In addition, the SQL Server query optimizer can do early partition elimination, so SQL Server does not even touch a partition with data excluded from the results set of a query. You can also perform maintenance procedures on a subset of filegroups, and thus on a subset of partitions. That way, you can also speed up regular maintenance tasks. Partitions have many benefits.
Although you can partition a table on any attribute, partitioning over dates is most common in data warehousing scenarios. You can use any time interval for a partition. Depending on your needs, the interval could be a day, a month, a year, or any other interval.
In addition to partitioning tables, you can also partition indexes. If indexes are partitioned in the same way as the base tables, they are called aligned indexes. Partitioned table and index concepts include the following:
- Partition function: This is an object that maps rows to partitions by using values from specific columns. The columns used for the function are called partitioning columns. A partition function performs logical mapping.
- Partition scheme: A partition scheme maps partitions to filegroups. A partition scheme performs physical mapping.
- Aligned index: This is an index built on the same partition scheme as its base table. If all indexes are aligned with their base table, switching a partition is a metadata operation only, so it is very fast. Columnstore indexes have to be aligned with their base tables. Nonaligned indexes are, of course, indexes that are partitioned differently from their base tables.
- Partition switching: This is a process that switches a block of data from one table or partition to another table or partition. You switch the data by using the ALTER TABLE T-SQL command. You can perform the following types of switching:
- Reassign all data from a nonpartitioned table to an empty existing partition of a partitioned table
- Switch a partition of a one-partitioned table to a partition of another partitioned table
- Reassign all data from a partition of a partitioned table to an existing empty nonpartitioned table
- Partition elimination: This is a query optimizer process in which SQL Server accesses only those partitions needed to satisfy query filters.
For more information about table and index partitioning, refer to the MSDN Partitioned Tables and Indexes article at https://msdn.microsoft.com/en-us/library/ms190787.aspx.