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.
- A fact table stores the measures (quantities, amounts) of business events, at the centre of the model. Many fact rows point to one dimension row.
- A dimension table stores the descriptive attributes used to filter and group the measures (date, product, customer, store, salesperson, time of day).
- A bridge table (also called a factless fact table) holds only keys and has no measure and no primary key. It is used to model many-to-many relationships between a fact and a dimension.
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.
Indexing and compression
| Table type | Recommended index |
|---|---|
| Dimension | regular (rowstore, B-tree) index |
| Fact | columnstore index |
- A columnstore index stores data by column in compressed segments. It suits large scans and aggregations typical of fact tables. Its metadata is visible through
sys.column_store_row_groupsandsys.column_store_segments. - Batch mode execution (processing rows in batches instead of one by one) applies to queries on columnstore indexes.
- Compression is most efficient on read-only or read-mostly data. It is a good fit for dimensions.
- An indexed view materialises the result of a view. It is mainly used through the view; automatic use by the optimizer without referencing the view requires Enterprise edition.
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
- Think about throughput from the start. A typical design question is how to load 1 TB of data in less than 30 minutes; measure the storage with a random-read test tool such as SQLIO before choosing the architecture.
- SqlLocalDB is a lightweight SQL Server Express execution mode that is convenient for local development.
ETL with SSIS
- Install the BI Developer Extensions (Business Intelligence projects for Visual Studio): bideveloperextensions.github.io.
- A connection manager can use several providers: ADO.NET, ODBC or OLE DB. It exposes properties such as the server name, the initial catalog and
RetainSameConnection. - A package error is not the same as a failed job: a task can fail and the package still be handled, depending on the properties of the tasks.
MaximumErrorCountsets how many errors are tolerated before the container fails (set to 1 in the example).FailParentOnFailureset toTruemakes the parent container fail when the task fails. - Checkpoints allow a failed package to restart from the failed task instead of from the beginning. They require the tasks to fail the package on failure (
FailPackageOnFailure). - To test mail notifications without a real mail server, a local test SMTP server such as Papercut SMTP can be used.
Change tracking: CDC and temporal tables
- Change Data Capture (CDC) records inserts, updates and deletes from the transaction log. Two functions are useful to read it:
sys.fn_cdc_map_time_to_lsnmaps a start and end time to log sequence numbers (LSN);- the
cdc.fn_cdc_get_all_changes_<capture_instance>function returns the exact changes between two LSNs.
- Temporal tables (system-versioned) keep the full history of rows automatically. They require two additional period columns (start and end of validity) in the table.