Case study · P—10
Columnar Data Warehouse with SQL Server 2017
- Role
- Redesign and high availability · Data Architect
- Period
- January 2017 – August 2017
- Organisation
- LC Waikiki
- Stack
- SQL Server 2017, Columnstore indexes, Always On, SSIS
- Status
- Completed
In short
Enterprise data warehouse redesigned with columnstore indexes and Always On high availability on SQL Server 2017: up to 18x faster performance.
Context
LC Waikiki’s enterprise data warehouse ran on SQL Server and was loaded by SSIS jobs. Columnstore indexes were already part of the Data Architect role’s work (2014–2017).
Role: redesigned the warehouse with columnstore indexes and implemented Always On high availability, as Data Architect.
Approach
- Columnstore redesign: the enterprise data warehouse was redesigned around columnstore indexes on SQL Server 2017.
- High availability: an Always On high-availability warehouse.
- Loads (same role, 2014–2017): 80+ SSIS jobs were refactored to incremental, metadata-driven loads.
Architecture and technology
SQL Server 2017 with columnstore indexes and Always On; SSIS jobs with incremental, metadata-driven loading.
Outcome
- Up to 18x faster performance.
- An Always On high-availability data warehouse.
- In the same Data Architect role (2014–2017): 35x compression with columnstore indexes, and the batch window cut from 7 hours to under 2.
Lessons
- Storage layout and load design were both levers: columnstore indexes on the storage side, incremental and metadata-driven loads on the batch side.