Week 4 · Load to the warehouse › Day 26

Day 26. T-SQL for people who run warehouses

1. Why this matters

SQL was your stated biggest gap. Writing SELECT is the start; a five-year BI developer also knows why a query is slow, why a load blocked a report, and which index fixes it. This is the day that separates "knows SQL" from "runs a SQL warehouse".

2. Indexes in one table

create table mart.fct_qsr_lines (
  business_date date not null, restaurant_key int not null, menu_item_key int not null,
  channel varchar(12) not null, qty smallint not null, revenue decimal(12,2) not null, order_id varchar(20) not null
);
create clustered columnstore index cci_fct_qsr_lines on mart.fct_qsr_lines;

Load columnstore in batches of at least ~102,400 rows so data goes straight into compressed rowgroups; tiny trickle inserts sit in the delta store.

3. Reading a plan

In SSMS (or VS Code with the MSSQL extension), show the actual execution plan (Ctrl+M). Read right to left. Look for:

• Table/Index Scan on a big table where you expected a seek.
• Key Lookup repeated thousands of times: add include columns.
• Estimated vs actual rows far apart: stale statistics or a non-sargable predicate.
• Spills to tempdb on sorts and hashes: memory grant too small.

Non-sargable means the predicate hides the column from the index: where year(business_date) = 2026 can't seek; where business_date >= '2026-01-01' and business_date < '2027-01-01' can.

set statistics io, time on;
select restaurant_key, sum(revenue) from mart.fct_qsr_lines
where business_date >= '2026-09-01' and business_date < '2026-10-01' group by restaurant_key;

4. Locks, blocking and RCSI

By default, SQL Server's READ COMMITTED uses locks: a big DELETE in a load can block Power BI's refresh queries until it commits. Read Committed Snapshot Isolation (RCSI) makes readers see the last committed version instead of waiting. It's on by default in Azure SQL Database; on SQL Server you enable it with alter database ... set read_committed_snapshot on.

5. Partitioning and temporal tables

Table partitioning (by month on business_date) gives partition elimination and lets you switch a fully loaded staging table into place instantly. It's worth it on facts with hundreds of millions of rows; below that, columnstore rowgroup elimination usually suffices.

System-versioned temporal tables keep history automatically: every update writes the old row to a history table with validity times. Great for "what did the campaign table say on 1 Sept?" and as a cheap SCD for small dimensions.

create table mart.dim_campaign (
  campaign_key int identity primary key, campaign_id varchar(10) not null unique,
  client varchar(50), channel varchar(20), objective varchar(20),
  valid_from datetime2 generated always as row start, valid_to datetime2 generated always as row end,
  period for system_time (valid_from, valid_to)
) with (system_versioning = on (history_table = mart.dim_campaign_history));

select * from mart.dim_campaign for system_time as of '2026-09-01';