Day 30: Real-world scenarios, part 2 & final assessment

A. Lesson overview

Activity breakdown

Business scenario. The retailer is building a small sales mart. The CRM sends a daily feed of customer changes. Analysts need to report sales by the customer segment at the time of the sale as well as by today’s segment; the DBA needs to see what the load is doing and whether it blocks anyone. This is your final project.

What this course has and hasn’t done. Thirty days of structured practice give you a solid, broad working knowledge of T-SQL and a portfolio of exercises. Expertise comes from applying these techniques to real systems over time — keep practising on your own data.

B. Theory explained clearly

Part 1 — A dependable load

Two qualities matter most:

• Idempotency — running the same batch twice leaves the target exactly as after the first run. Change detection (“only update if a value differs”) is what makes this work.
• Atomicity — either the whole batch applies or none of it does (XACT_ABORT ON, TRY…CATCH, one transaction). The run log row is written before the transaction, so a failed run is still recorded.

Part 2 — Slowly changing dimensions

A dimension describes the “who/what/where” of facts (customer, product). When a descriptive attribute changes, you choose per attribute how history is kept:

A Type 2 dimension has:

• a surrogate key (CustomerKey, IDENTITY) — one per version; facts point to it;
• the business key (CustomerID) — the same for all versions of a customer;
• a validity interval, here half-open: ValidFrom <= date < ValidTo, with ValidTo = '9999-12-31' for the current version (as temporal tables do, Day 12) — consecutive versions meet exactly, with no gaps or overlaps;
• an IsCurrent flag, enforced by a filtered unique index: one current row per customer.

Order of operations for a batch dated D: apply Type 1 changes to all versions; close changed current versions (ValidTo = D, IsCurrent = 0), capturing which customers changed (OUTPUT … INTO); insert their new versions (ValidFrom = D); insert brand-new customers. MERGE can do parts of this, but with the caveats from Day 15 — separate statements in one transaction are easier to read and test.

Part 3 — Point-in-time lookups

To attach the right version to a fact, join on the business key and the date:

LEFT JOIN Lab.DimCustomer AS d
       ON d.CustomerID = o.CustomerID
      AND o.OrderDate >= d.ValidFrom
      AND o.OrderDate <  d.ValidTo

With non-overlapping half-open intervals, each order matches exactly one version. Use LEFT JOIN and map a missing match to an “unknown” key (−1) rather than losing the fact row; then check that there are none.

Reports can then show either as it was (join the fact’s CustomerKey) or as it is now (join on CustomerID to the current version) — often a deliberate business choice.

Part 4 — Monitoring with DMVs

Dynamic management views expose the engine’s current state. The essentials:

Server-scoped DMVs need VIEW SERVER STATE (or, in SQL Server 2022+, the narrower VIEW SERVER PERFORMANCE STATE); database-scoped ones need VIEW DATABASE STATE. Grant these to monitoring logins rather than sysadmin. DMV data is volatile: plan-cache statistics disappear when plans are evicted or the server restarts.

C. Dataset and starting state

Setup. Run day30_final_project_setup.sql (download it). It is destructive for the Day 30 Lab objects only and can be re-run to start again.

The feed, row by row:

Validation. The script ends with this query. Expected: 42, 40, 2, 11, 0.

SELECT  (SELECT COUNT(*) FROM Lab.DimCustomer)                     AS DimRows,
        (SELECT COUNT(*) FROM Lab.DimCustomer WHERE IsCurrent = 1) AS CurrentRows,
        (SELECT COUNT(*) FROM Lab.DimCustomer WHERE IsCurrent = 0) AS HistoryRows,
        (SELECT COUNT(*) FROM Lab.CustomerFeed)                    AS FeedRows,
        (SELECT COUNT(*) FROM Lab.EtlRunLog)                       AS Runs;

D. Worked examples

Worked example 1 — Classify the feed before loading anything

WITH Classified AS
(
    SELECT  f.FeedRowID, f.CustomerID, f.CustomerSegment, f.ChangedAt,
            CASE WHEN f.CustomerID IS NULL THEN N'Missing CustomerID'
                 WHEN f.CustomerSegment IS NULL
                   OR f.CustomerSegment NOT IN ('Consumer', 'Business', 'VIP') THEN N'Invalid segment'
                 WHEN NULLIF(TRIM(f.FirstName), N'') IS NULL
                   OR NULLIF(TRIM(f.LastName),  N'') IS NULL THEN N'Missing name'
                 WHEN f.City IS NULL OR f.Country IS NULL     THEN N'Missing city or country'
            END AS RejectReason
    FROM    Lab.CustomerFeed AS f
)
SELECT  FeedRowID, CustomerID, RejectReason,
        CASE WHEN RejectReason IS NOT NULL THEN 'Rejected'
             WHEN ROW_NUMBER() OVER (PARTITION BY CustomerID, CASE WHEN RejectReason IS NULL THEN 0 ELSE 1 END
                                     ORDER BY ChangedAt DESC, FeedRowID DESC) > 1 THEN 'Superseded'
             ELSE 'Accepted' END AS Outcome
FROM    Classified
ORDER BY FeedRowID;

Expected result: 11 rows — rows 1, 3–8 and 11 Accepted (8); row 2 Superseded; row 9 Rejected — Missing CustomerID; row 10 Rejected — Invalid segment. 8 + 1 + 2 = 11: every row accounted for. Rejections are decided first, and only valid rows compete for “latest” — a design choice worth writing down.

Worked example 2 — A Type 2 change by hand (in a transaction you roll back)

BEGIN TRANSACTION;

    DECLARE @Changed TABLE (CustomerID int PRIMARY KEY);

    UPDATE Lab.DimCustomer
    SET    ValidTo = '2026-01-15', IsCurrent = 0
    OUTPUT inserted.CustomerID INTO @Changed
    WHERE  CustomerID = 4 AND IsCurrent = 1 AND City <> N'York';

    INSERT INTO Lab.DimCustomer (CustomerID, FirstName, LastName, Email, Phone, City, Country, CustomerSegment, ValidFrom)
    SELECT d.CustomerID, d.FirstName, d.LastName, d.Email, d.Phone, N'York', d.Country, d.CustomerSegment, '2026-01-15'
    FROM   Lab.DimCustomer AS d
    JOIN   @Changed        AS c ON c.CustomerID = d.CustomerID
    WHERE  d.ValidTo = '2026-01-15';

    SELECT CustomerKey, CustomerID, City, ValidFrom, ValidTo, IsCurrent
    FROM   Lab.DimCustomer WHERE CustomerID = 4 ORDER BY ValidFrom;

ROLLBACK TRANSACTION;

Expected result: two rows for customer 4: Leeds 2021-11-05 2026-01-15 0 and York 2026-01-15 9999-12-31 1 (the new CustomerKey is 43 — the next identity value — but it is rolled back, and identity values aren’t reused, so your later keys will start higher). The old version’s ValidTo equals the new version’s ValidFrom: no gap, no overlap. Running the UPDATE again would change nothing — City <> N'York' is false for the new current row.

Worked example 3 — Which version applied on a date?

SELECT  o.OrderID, o.OrderDate, d.CustomerKey, d.CustomerSegment, d.ValidFrom, d.ValidTo
FROM    Sales.SalesOrder AS o
JOIN    Lab.DimCustomer  AS d
        ON  d.CustomerID = o.CustomerID
        AND o.OrderDate >= d.ValidFrom AND o.OrderDate < d.ValidTo
WHERE   o.CustomerID = 18 AND o.OrderDate >= '2025-03-01' AND o.OrderDate < '2025-06-01'
ORDER BY o.OrderDate, o.OrderID;

Expected result: 4 rows — 1207 2025-03-14 19 Consumer 2023-03-03 2025-04-01, 1211 2025-03-19 19 Consumer …, 1243 2025-05-20 20 VIP 2025-04-01 9999-12-31, 1249 2025-05-27 20 VIP …. Orders before 1 April use the Consumer version (key 19), later ones the VIP version (key 20). The keys come from the setup’s load order (customers 1–12 get keys 1–12, customer 13’s two versions 13–14, and so on) and are the same every time you run the setup script. Run it for the whole of 2025 to see that every order matches exactly one version.

Worked example 4 — Watching a blocked session

Open three query windows on RetailLab.

SELECT  r.session_id, r.blocking_session_id, r.status, r.command,
        r.wait_type, r.wait_time AS WaitMs, r.wait_resource,
        DB_NAME(r.database_id) AS DatabaseName,
        SUBSTRING(t.text, r.statement_start_offset / 2 + 1,
                  (CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
                        ELSE r.statement_end_offset END - r.statement_start_offset) / 2 + 1) AS RunningStatement,
        s.login_name, s.host_name, s.program_name
FROM    sys.dm_exec_requests AS r
JOIN    sys.dm_exec_sessions AS s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE   r.blocking_session_id <> 0;

-- The blocker may be idle (not running anything) — find it through sessions
SELECT  s.session_id, s.status, s.open_transaction_count, s.last_request_end_time, s.login_name
FROM    sys.dm_exec_sessions AS s
WHERE   s.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0);

Expected result: while window B waits — one row: B’s session ID, blocking_session_id = A’s session ID, wait_type LCK_M_S (waiting for a shared lock), the SELECT text. The second query shows A as sleeping with open_transaction_count 1 — the classic “someone left a transaction open” picture. Session IDs are your own. (If you enabled read committed snapshot on Day 16 and left it on, B won’t block — it reads the last committed version.)

E. Guided practical lab — The SCD Type 2 load procedure

You’ll build and run Lab.usp_LoadDimCustomer, which part 1 of the final project then relies on.

Step 1 — Create the procedure

1. Do: Read it section by section — every section maps to Part 1 and Part 2 of the theory.
2. Run:

   USE RetailLab;
   GO
   CREATE OR ALTER PROCEDURE Lab.usp_LoadDimCustomer
       @BatchDate date
   AS
   BEGIN
       SET NOCOUNT ON;
       SET XACT_ABORT ON;
   
       DECLARE @RunID int, @Read int, @Rejected int, @Superseded int,
               @Inserted int = 0, @Expired int = 0, @Type1 int = 0, @Err nvarchar(2048);
       DECLARE @Changed TABLE (CustomerID int PRIMARY KEY);
       DECLARE @New     TABLE (CustomerID int PRIMARY KEY);
   
       -- Log the start OUTSIDE the transaction, so a failed run is still recorded
       INSERT INTO Lab.EtlRunLog (ProcessName, BatchDate) VALUES (N'usp_LoadDimCustomer', @BatchDate);
       SET @RunID = SCOPE_IDENTITY();
   
       BEGIN TRY
           IF @BatchDate IS NULL
               THROW 50700, N'@BatchDate is required.', 1;
           IF EXISTS (SELECT 1 FROM Lab.DimCustomer WHERE IsCurrent = 1 AND ValidFrom > @BatchDate)
           BEGIN
               SET @Err = FORMATMESSAGE(N'Batch date %s is earlier than an existing version.',
                                        CONVERT(nvarchar(10), @BatchDate, 23));
               THROW 50701, @Err, 1;
           END;
   
           -- 1. Validate
           DROP TABLE IF EXISTS #Feed;
           SELECT  f.*,
                   CASE WHEN f.CustomerID IS NULL THEN N'Missing CustomerID'
                        WHEN f.CustomerSegment IS NULL
                          OR f.CustomerSegment NOT IN ('Consumer', 'Business', 'VIP') THEN N'Invalid segment'
                        WHEN NULLIF(TRIM(f.FirstName), N'') IS NULL
                          OR NULLIF(TRIM(f.LastName),  N'') IS NULL THEN N'Missing name'
                        WHEN f.City IS NULL OR f.Country IS NULL     THEN N'Missing city or country'
                   END AS RejectReason
           INTO    #Feed
           FROM    Lab.CustomerFeed AS f;
   
           -- 2. De-duplicate: latest valid row per customer
           DROP TABLE IF EXISTS #Valid;
           SELECT  CustomerID, TRIM(FirstName) AS FirstName, TRIM(LastName) AS LastName,
                   NULLIF(LOWER(TRIM(Email)), N'') AS Email, Phone, City, Country, CustomerSegment
           INTO    #Valid
           FROM   (SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID
                                                ORDER BY ChangedAt DESC, FeedRowID DESC) AS rn
                   FROM   #Feed WHERE RejectReason IS NULL) AS x
           WHERE   rn = 1;
   
           SELECT @Read = COUNT(*), @Rejected = COUNT(RejectReason) FROM #Feed;
           SET @Superseded = @Read - @Rejected - (SELECT COUNT(*) FROM #Valid);
   
           BEGIN TRANSACTION;
   
           INSERT INTO Lab.EtlReject (RunID, FeedRowID, Reason)
           SELECT @RunID, FeedRowID, RejectReason FROM #Feed WHERE RejectReason IS NOT NULL;
   
           -- 3. Type 1: overwrite on every version, only where something differs
           UPDATE d
           SET    FirstName = v.FirstName, LastName = v.LastName, Email = v.Email, Phone = v.Phone,
                  UpdatedRunID = @RunID
           FROM   Lab.DimCustomer AS d
           JOIN   #Valid          AS v ON v.CustomerID = d.CustomerID
           WHERE  d.FirstName IS DISTINCT FROM v.FirstName OR d.LastName IS DISTINCT FROM v.LastName
              OR  d.Email     IS DISTINCT FROM v.Email     OR d.Phone    IS DISTINCT FROM v.Phone;
           SET @Type1 = @@ROWCOUNT;
   
           -- 4. Type 2: close changed current versions…
           UPDATE d
           SET    ValidTo = @BatchDate, IsCurrent = 0, UpdatedRunID = @RunID
           OUTPUT inserted.CustomerID INTO @Changed (CustomerID)
           FROM   Lab.DimCustomer AS d
           JOIN   #Valid          AS v ON v.CustomerID = d.CustomerID
           WHERE  d.IsCurrent = 1
             AND (d.City            IS DISTINCT FROM v.City
               OR d.Country         IS DISTINCT FROM v.Country
               OR d.CustomerSegment IS DISTINCT FROM v.CustomerSegment);
           SET @Expired = @@ROWCOUNT;
   
           -- …and insert their new versions
           INSERT INTO Lab.DimCustomer
                  (CustomerID, FirstName, LastName, Email, Phone, City, Country, CustomerSegment, ValidFrom, InsertedRunID)
           SELECT  v.CustomerID, v.FirstName, v.LastName, v.Email, v.Phone, v.City, v.Country, v.CustomerSegment,
                   @BatchDate, @RunID
           FROM    #Valid AS v
           JOIN    @Changed AS c ON c.CustomerID = v.CustomerID;
   
           -- 5. New customers
           INSERT INTO Lab.DimCustomer
                  (CustomerID, FirstName, LastName, Email, Phone, City, Country, CustomerSegment, ValidFrom, InsertedRunID)
           OUTPUT inserted.CustomerID INTO @New (CustomerID)
           SELECT  v.CustomerID, v.FirstName, v.LastName, v.Email, v.Phone, v.City, v.Country, v.CustomerSegment,
                   @BatchDate, @RunID
           FROM    #Valid AS v
           WHERE   NOT EXISTS (SELECT 1 FROM Lab.DimCustomer AS d WHERE d.CustomerID = v.CustomerID);
           SET @Inserted = @@ROWCOUNT;
   
           -- 6. Flag possible duplicates among the new customers
           INSERT INTO Lab.CustomerMatchReview (RunID, NewCustomerID, ExistingCustomerID, MatchReason)
           SELECT  @RunID, n.CustomerID, e.CustomerID,
                   CASE WHEN n.Phone = e.Phone THEN N'Same phone' ELSE N'Same name and city' END
           FROM    Lab.DimCustomer AS n
           JOIN    @New            AS nw ON nw.CustomerID = n.CustomerID
           JOIN    Lab.DimCustomer AS e
                   ON  e.IsCurrent = 1
                   AND e.CustomerID NOT IN (SELECT CustomerID FROM @New)
                   AND (e.Phone = n.Phone
                        OR (e.FirstName = n.FirstName AND e.LastName = n.LastName AND e.City = n.City))
           WHERE   n.IsCurrent = 1;
   
           UPDATE Lab.EtlRunLog
           SET    Status = 'Succeeded', FinishedAt = SYSDATETIME(),
                  RowsRead = @Read, RowsRejected = @Rejected, RowsSuperseded = @Superseded,
                  RowsInserted = @Inserted, RowsExpired = @Expired, RowsType1 = @Type1
           WHERE  RunID = @RunID;
   
           COMMIT TRANSACTION;
       END TRY
       BEGIN CATCH
           IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
           UPDATE Lab.EtlRunLog
           SET    Status = 'Failed', FinishedAt = SYSDATETIME(), ErrorMessage = ERROR_MESSAGE()
           WHERE  RunID = @RunID;
           THROW;
       END CATCH;
   END;
   GO

3. Observe: Commands completed successfully.
4. Why it matters: Each statement is set-based and guarded by “only if different”, which is what makes the procedure idempotent. Type 1 runs before Type 2 so the new version copies already-corrected values either way.
5. Confirm: SELECT OBJECT_ID(N'Lab.usp_LoadDimCustomer'); is not NULL.

Step 2 — Run it for 2026-01-15

1. Do: Load the batch and read the log.
2. Run:

   EXEC Lab.usp_LoadDimCustomer @BatchDate = '2026-01-15';
   
   SELECT RunID, Status, RowsRead, RowsRejected, RowsSuperseded, RowsInserted, RowsExpired, RowsType1
   FROM   Lab.EtlRunLog ORDER BY RunID;
   SELECT FeedRowID, Reason FROM Lab.EtlReject ORDER BY RunID, FeedRowID;
   SELECT NewCustomerID, ExistingCustomerID, MatchReason FROM Lab.CustomerMatchReview;

3. Observe: run 1 Succeeded: read 11, rejected 2, superseded 1, inserted 3, expired 3, Type 1 rows 3. Rejects: 9 Missing CustomerID, 10 Invalid segment. Review: 44 → 5, Same phone.
4. Why it matters: 11 = 8 accepted + 2 rejected + 1 superseded; the 8 accepted rows produced 3 new customers, 3 Type 2 changes (customers 4, 7, 18), Type 1 changes for customers 2 and 18 (3 rows — customer 18 had two versions) and one unchanged customer (9).
5. Confirm: SELECT COUNT(*), SUM(CAST(IsCurrent AS int)) FROM Lab.DimCustomer; → 48, 43.

Step 3 — Inspect the history

1. Do: Look at the customers who changed.
2. Run:

   SELECT CustomerID, CustomerKey, Email, City, CustomerSegment, ValidFrom, ValidTo, IsCurrent
   FROM   Lab.DimCustomer
   WHERE  CustomerID IN (2, 4, 7, 18)
   ORDER BY CustomerID, ValidFrom;

3. Observe: customer 2: 1 row, email ben.carter@example.org. Customer 4: Leeds (to 2026-01-15) → York (current). Customer 7: Consumer (to 2026-01-15) → VIP. Customer 18: three versions — Milan Consumer (to 2025-04-01), Milan VIP (2025-04-01 to 2026-01-15), Rome VIP (current) — all with the new .com email.
4. Why it matters: Type 1 corrected the email across history; Type 2 kept the city history. Customer 7’s 09:00 change to Business never appears — it was superseded within the batch.
5. Confirm: for every customer, each version’s ValidTo equals the next version’s ValidFrom.

Step 4 — Prove idempotency

1. Do: Run the same batch again.
2. Run:

   EXEC Lab.usp_LoadDimCustomer @BatchDate = '2026-01-15';
   SELECT TOP (1) RunID, Status, RowsInserted, RowsExpired, RowsType1, RowsRejected
   FROM   Lab.EtlRunLog ORDER BY RunID DESC;
   SELECT COUNT(*) AS DimRows FROM Lab.DimCustomer;

3. Observe: run 2 Succeeded with 0 inserted, 0 expired, 0 Type 1 rows (still 2 rejected — they’re logged per run); DimRows still 48.
4. Why it matters: Schedulers retry; operators re-run. A load that isn’t idempotent turns every retry into a data problem.
5. Confirm: no new review rows for run 2.

Step 5 — A failure is logged and changes nothing

1. Do: Run with an impossible batch date.
2. Run:

   BEGIN TRY
       EXEC Lab.usp_LoadDimCustomer @BatchDate = '2025-01-01';
   END TRY
   BEGIN CATCH
       SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS Msg;
   END CATCH;
   SELECT TOP (1) RunID, Status, ErrorMessage FROM Lab.EtlRunLog ORDER BY RunID DESC;
   SELECT COUNT(*) AS DimRows FROM Lab.DimCustomer;

3. Observe: 50701 Batch date 2025-01-01 is earlier than an existing version.; run 3 Failed with that message; DimRows 48.
4. Why it matters: Loading an older batch after a newer one would create overlapping versions. The guard refuses it, and the log row survives because it was written outside the transaction.
5. Confirm: three runs in the log: Succeeded, Succeeded, Failed.

F. Final project — Customer sales mart

Build on the lab’s dimension. Work in the Lab schema; use the names given (the validation script relies on them). Allow 3–4 hours.

Part 1 — Fact table and load (30 points)

Create Lab.FactSales:

Write Lab.usp_LoadFactSales @FromDate date, @ToDate date (half-open range) that loads non-cancelled order lines for the range, using a point-in-time lookup. It must be idempotent (re-running a range replaces it, never duplicates it), atomic, follow the Day 20 error pattern, and log a row in Lab.EtlRunLog (ProcessName = 'usp_LoadFactSales', RowsInserted = lines loaded). Load 2025 ('2025-01-01', '2026-01-01'), twice.

Success criteria: 360 lines, 198 orders, SUM(NetAmount) 63741.9445, no row with key −1 — after both runs.

Part 2 — Reporting view (20 points)

Create Lab.vSalesBySegment2025 returning one row per segment with NetSalesAsAtOrder (segment of the version linked to each fact) and NetSalesCurrentSegment (customer’s current segment), both decimal(12,2).

Success criteria:

Both columns total 63741.94. In two or three sentences, explain the differences (which customers move, and why).

Part 3 — Data-quality checks (15 points)

Write one query (or a procedure) returning a row per check with CheckName and Failures: (a) customers without exactly one current version; (b) gaps or overlaps between consecutive versions; (c) facts whose key doesn’t match the business key and date; (d) facts with key −1; (e) the last successful dimension run did not read every feed row (RowsRead ≠ rows in Lab.CustomerFeed).

Success criteria: 5 rows, all Failures = 0.

Part 4 — Monitoring (15 points)

(a) Write a blocking report (blocked session, blocker, wait type, wait time, both sessions’ SQL, the blocker’s open transaction count) and demonstrate it with two sessions, one of which runs Lab.usp_LoadFactSales while the other holds a lock on Lab.FactSales. (b) Write a query returning the five cached statements with the highest total logical reads whose text mentions FactSales or DimCustomer, with execution count and average elapsed time in milliseconds.

Success criteria: (a) the load appears as blocked with a lock wait (LCK_M_…) while the other session’s transaction is open, and completes after it ends; (b) up to 5 rows, including the fact load’s INSERT.

Part 5 — Design note (20 points)

Write about one page (in a comment block or a separate document) covering: the grain of the fact; which attributes are Type 1 and Type 2 and why; how idempotency and atomicity are achieved; what happens with late-arriving customers; indexes you added and why; two risks or limitations and what you’d do next.

Rubric

Validation

Run scripts/lessons/day30_validate_final_project.sql (download it). It checks the dimension (after the lab), the run log, rejects, the duplicate review, idempotency (by re-running the dimension load inside a transaction it rolls back), and parts 1 and 2. All 18 checks should show PASS.

Stretch challenges (optional)

1. Late-arriving customer. Insert an order (in a Lab copy of the order tables, not Sales) for customer 45, who isn’t in the dimension yet. Change the fact load to create an inferred member — a minimal dimension row flagged IsInferred = 1 — instead of using −1, and make the dimension load complete the row (Type 1 overwrite, no new version) when the customer arrives.
2. Monitoring the Perf workload. If you still have the Week 4 data, run the Day 22 baseline queries and use sys.dm_exec_query_stats and sys.dm_os_wait_stats (difference between two snapshots) to describe the workload in five sentences.

G. Reference solution and explanations

This is one good solution; others can score full marks.

CREATE TABLE Lab.FactSales
(
    OrderLineID int            NOT NULL CONSTRAINT PK_FactSales PRIMARY KEY,
    OrderID     int            NOT NULL,
    OrderDate   date           NOT NULL,
    CustomerKey int            NOT NULL,
    CustomerID  int            NOT NULL,
    ProductID   int            NOT NULL,
    Quantity    int            NOT NULL,
    NetAmount   decimal(19,4)  NOT NULL
);
CREATE INDEX IX_FactSales_OrderDate   ON Lab.FactSales (OrderDate) INCLUDE (CustomerKey, NetAmount);
CREATE INDEX IX_FactSales_CustomerKey ON Lab.FactSales (CustomerKey) INCLUDE (NetAmount);
GO

CREATE OR ALTER PROCEDURE Lab.usp_LoadFactSales
    @FromDate date,
    @ToDate   date
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @RunID int, @Rows int;

    INSERT INTO Lab.EtlRunLog (ProcessName, BatchDate) VALUES (N'usp_LoadFactSales', @FromDate);
    SET @RunID = SCOPE_IDENTITY();

    BEGIN TRY
        IF @FromDate IS NULL OR @ToDate IS NULL OR @FromDate >= @ToDate
            THROW 50710, N'Supply @FromDate < @ToDate (half-open range).', 1;

        BEGIN TRANSACTION;

        DELETE FROM Lab.FactSales
        WHERE  OrderDate >= @FromDate AND OrderDate < @ToDate;

        INSERT INTO Lab.FactSales (OrderLineID, OrderID, OrderDate, CustomerKey, CustomerID, ProductID, Quantity, NetAmount)
        SELECT  l.OrderLineID, o.OrderID, o.OrderDate,
                COALESCE(d.CustomerKey, -1),
                o.CustomerID, l.ProductID, l.Quantity,
                l.Quantity * l.UnitPrice * (1 - l.DiscountPct)
        FROM    Sales.SalesOrder     AS o
        JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
        LEFT JOIN Lab.DimCustomer    AS d
               ON  d.CustomerID = o.CustomerID
               AND o.OrderDate >= d.ValidFrom AND o.OrderDate < d.ValidTo
        WHERE   o.OrderStatus <> 'Cancelled'
          AND   o.OrderDate >= @FromDate AND o.OrderDate < @ToDate;
        SET @Rows = @@ROWCOUNT;

        UPDATE Lab.EtlRunLog
        SET    Status = 'Succeeded', FinishedAt = SYSDATETIME(), RowsRead = @Rows, RowsInserted = @Rows
        WHERE  RunID = @RunID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        UPDATE Lab.EtlRunLog
        SET    Status = 'Failed', FinishedAt = SYSDATETIME(), ErrorMessage = ERROR_MESSAGE()
        WHERE  RunID = @RunID;
        THROW;
    END CATCH;
END;
GO

EXEC Lab.usp_LoadFactSales '2025-01-01', '2026-01-01';
EXEC Lab.usp_LoadFactSales '2025-01-01', '2026-01-01';

SELECT COUNT(*) AS Lines, COUNT(DISTINCT OrderID) AS Orders, SUM(NetAmount) AS Net,
       SUM(CASE WHEN CustomerKey = -1 THEN 1 ELSE 0 END) AS UnknownKeys
FROM   Lab.FactSales;

CREATE OR ALTER VIEW Lab.vSalesBySegment2025
AS
WITH AsAt AS
(
    SELECT d.CustomerSegment, SUM(f.NetAmount) AS Net
    FROM   Lab.FactSales   AS f
    JOIN   Lab.DimCustomer AS d ON d.CustomerKey = f.CustomerKey
    WHERE  f.OrderDate >= '2025-01-01' AND f.OrderDate < '2026-01-01'
    GROUP BY d.CustomerSegment
),
CurrentSeg AS
(
    SELECT d.CustomerSegment, SUM(f.NetAmount) AS Net
    FROM   Lab.FactSales   AS f
    JOIN   Lab.DimCustomer AS d ON d.CustomerID = f.CustomerID AND d.IsCurrent = 1
    WHERE  f.OrderDate >= '2025-01-01' AND f.OrderDate < '2026-01-01'
    GROUP BY d.CustomerSegment
)
SELECT  COALESCE(a.CustomerSegment, c.CustomerSegment) AS CustomerSegment,
        CAST(COALESCE(a.Net, 0) AS decimal(12,2))       AS NetSalesAsAtOrder,
        CAST(COALESCE(c.Net, 0) AS decimal(12,2))       AS NetSalesCurrentSegment
FROM    AsAt AS a
FULL OUTER JOIN CurrentSeg AS c ON c.CustomerSegment = a.CustomerSegment;
GO

SELECT * FROM Lab.vSalesBySegment2025 ORDER BY CustomerSegment;

WITH LastDimRun AS
(
    SELECT TOP (1) RowsRead, RunID
    FROM   Lab.EtlRunLog
    WHERE  ProcessName = N'usp_LoadDimCustomer' AND Status = 'Succeeded'
    ORDER BY RunID DESC
)
SELECT N'Customers without exactly one current version' AS CheckName, COUNT(*) AS Failures
FROM  (SELECT CustomerID FROM Lab.DimCustomer GROUP BY CustomerID
       HAVING SUM(CASE WHEN IsCurrent = 1 THEN 1 ELSE 0 END) <> 1) AS x
UNION ALL
SELECT N'Gaps or overlaps between versions', COUNT(*)
FROM  (SELECT ValidTo, LEAD(ValidFrom) OVER (PARTITION BY CustomerID ORDER BY ValidFrom) AS NextFrom
       FROM Lab.DimCustomer) AS x
WHERE  NextFrom IS NOT NULL AND NextFrom <> ValidTo
UNION ALL
SELECT N'Facts whose key does not match business key and date', COUNT(*)
FROM   Lab.FactSales AS f
LEFT JOIN Lab.DimCustomer AS d
       ON d.CustomerKey = f.CustomerKey AND d.CustomerID = f.CustomerID
      AND f.OrderDate >= d.ValidFrom AND f.OrderDate < d.ValidTo
WHERE  f.CustomerKey <> -1 AND d.CustomerKey IS NULL
UNION ALL
SELECT N'Facts with unknown customer (-1)', COUNT(*) FROM Lab.FactSales WHERE CustomerKey = -1
UNION ALL
SELECT N'Feed rows not read by the last dimension run',
       ABS(r.RowsRead - (SELECT COUNT(*) FROM Lab.CustomerFeed))
FROM   LastDimRun AS r;

-- (a) Blocking report
SELECT  r.session_id            AS BlockedSession,
        r.blocking_session_id   AS BlockingSession,
        r.wait_type, r.wait_time AS WaitMs, r.wait_resource,
        SUBSTRING(bt.text, r.statement_start_offset / 2 + 1,
                  (CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(bt.text)
                        ELSE r.statement_end_offset END - r.statement_start_offset) / 2 + 1) AS BlockedStatement,
        bs.open_transaction_count AS BlockerOpenTransactions,
        lt.text                 AS BlockerLastBatch
FROM    sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS bt
JOIN    sys.dm_exec_sessions AS bs ON bs.session_id = r.blocking_session_id
LEFT JOIN sys.dm_exec_connections AS c ON c.session_id = r.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS lt
WHERE   r.blocking_session_id <> 0;

-- Demonstration
--   Window A: BEGIN TRANSACTION; UPDATE Lab.FactSales SET Quantity = Quantity WHERE OrderLineID = (SELECT MIN(OrderLineID) FROM Lab.FactSales);
--   Window B: EXEC Lab.usp_LoadFactSales '2025-01-01', '2026-01-01';   -- waits
--   Window C: run the blocking report; then in A: ROLLBACK TRANSACTION;

-- (b) Expensive cached statements touching the mart
SELECT TOP (5)
        qs.execution_count,
        qs.total_logical_reads,
        CAST(qs.total_elapsed_time / 1000.0 / qs.execution_count AS decimal(12,1)) AS AvgElapsedMs,
        SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
                  (CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
                        ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS StatementText
FROM    sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE   st.text LIKE N'%FactSales%' OR st.text LIKE N'%DimCustomer%'
ORDER BY qs.total_logical_reads DESC;

H. Knowledge check

1. Understanding. Why do Type 2 dimensions use a surrogate key rather than the business key as the primary key?

2. Debugging. After a re-run of the customer load, three customers have two current rows. What two mechanisms in today’s design prevent this?

3. Query interpretation. With half-open intervals, a version is ValidFrom = 2025-04-01, ValidTo = 2026-01-15. Which version applies to an order on 2026-01-15?

4. Understanding. Why is the run-log row inserted before BEGIN TRANSACTION?

5. Debugging. A session shows blocking_session_id = 72, but session 72 has no row in sys.dm_exec_requests. What does that tell you, and how do you see what 72 did?

I. Troubleshooting and cleanup

Cleanup. Keep your project for your portfolio. To remove it, re-run the setup script (which drops and recreates the Day 30 objects) or:

DROP VIEW      IF EXISTS Lab.vSalesBySegment2025;
DROP PROCEDURE IF EXISTS Lab.usp_LoadDimCustomer, Lab.usp_LoadFactSales;
DROP TABLE     IF EXISTS Lab.FactSales, Lab.CustomerMatchReview, Lab.EtlReject, Lab.EtlRunLog,
                         Lab.CustomerFeed, Lab.DimCustomer;

To return the whole database to its starting point, run scripts/setup/04_reset_training_data.sql (destructive: it drops and recreates RetailLab, including every Lab and Perf object).

J. Completion checklist, recap and references

Completion checklist

• The lab load reported 11 / 2 / 1 / 3 / 3 / 3, and the re-run 0 / 0 / 0.
• Customer 18 has three versions; customer 7’s 09:00 change never appears.
• You watched a blocked session (LCK_M_S) and found its idle blocker.
• Final project: 360 fact lines, 63741.9445, segment report as specified.
• The validation script shows 18 of 18 PASS; your design note is written.

Recap

• A dependable load validates, de-duplicates, rejects with reasons, runs atomically, logs, reconciles — and is idempotent.
• Type 1 overwrites; Type 2 closes and inserts versions with half-open intervals and surrogate keys; one current row per business key, enforced by an index.
• Point-in-time joins attach the version valid on the fact’s date; reports must say whether they show “as it was” or “as it is”.
• DMVs show what’s running, who blocks whom, what we wait on and what costs most; Query Store keeps the history.

Course recap: where to go next

Over 30 days you have covered querying (Week 1), joins, sets, dates, strings and window-based analysis (Week 2), safe data modification and programmability (Week 3), and performance, advanced features and real-world patterns (Week 4). Next steps that build directly on this course:

• Re-do the Week 4 lessons on a copy of real data from your work (with permission), recording baselines and improvements.
• Practise reading execution plans for your own slowest reports.
• Explore areas this course only touched: Always Encrypted and row-level security, columnstore design, In-Memory OLTP, Service Broker, and Azure SQL / Fabric warehouse differences.
• Consider the Microsoft Learn learning paths and certifications for SQL Server and Azure data roles as a structured way to keep going.

References (Microsoft Learn)

• Dynamic management views and functions
• sys.dm_exec_requests
• sys.dm_exec_query_stats
• sys.dm_os_wait_stats
• Understand and resolve SQL Server blocking problems
• MERGE (Transact-SQL)
• IS [NOT] DISTINCT FROM (Transact-SQL)
• Temporal tables