LLI Wiki — My Digital Knowledge Base

Data warehouse design principles on SQL Server

Star schema, surrogate keys, columnstore indexes, compression, SSIS practices, CDC and temporal tables.

This article gathers general design principles for building a data warehouse on SQL Server: dimensional modelling, indexing, compression, ETL with SSIS and change tracking.

Star schema: facts and dimensions

A dimensional model separates facts from dimensions.

In a typical star schema, the fact table holds the measures (quantity, total amount) and a foreign key to each dimension. Each dimension has its own key and attributes, including a date dimension with many calendar and fiscal attributes and a time dimension.

Always use surrogate keys. A surrogate key is a meaningless integer generated by the warehouse, used instead of the business key of the source system. It isolates the warehouse from source key changes, keeps joins narrow and fast, and allows tracking history. The business key is kept in the dimension as an alternate key.

Star schema: one fact table linked to date, product, customer, store, salesperson and time dimensions

Indexing and compression

Table type Recommended index
Dimension regular (rowstore, B-tree) index
Fact columnstore index

Index maintenance

For rowstore indexes, the usual rule based on fragmentation is: between 5 % and 30 %, reorganise the index; above 30 %, rebuild it.

Sizing and test environment

Local Group Policy Editor, System section

ETL with SSIS

Properties of an OLE DB connection manager in SSIS

SSIS task execution properties: FailParentOnFailure and MaximumErrorCount

Change tracking: CDC and temporal tables

Mastodon LLIMastodon SocialFosstodon