Microsoft Fabric
T-SQL

The Accountability Gap

A Microsoft Fabric warehouse exercise in defensive T-SQL, a five-level hierarchy chain, and an SCD Type 2 MERGE that closes a record instead of overwriting it.

Role: Independent case study
Year: 2026
Stack: Microsoft Fabric · Data Factory · Fabric Warehouse · T-SQL · SCD Type 2

Veritas Global is a forty-two-billion-dollar asset manager reporting to three regulators, each of which can ask who was accountable for a flagged position on a specific date in the past. Its Relationship Manager roster was a flat table that got overwritten every time someone changed desk or title, so the honest answer to that question was often “we no longer know.” The brief was a Microsoft Fabric warehouse layer that stops trusting dirty source data blindly, resolves a five-level reporting chain in a single query, and never erases a personnel record again.

The problem

Three regulators (the FCA, the SEC, and MAS) each require point-in-time attribution: not who manages an account today, but who held it when a flagged position was opened. Veritas Global’s roster table couldn’t answer that, because every promotion, desk move, or jurisdiction transfer updated the existing row in place. The firm’s Global Head of Risk put it plainly after an FCA review asked for the record on a specific date: “We could not answer it. Not because the data was complex. Because we had overwritten the record when the team restructured in November.”

Three compounding failures sat behind that gap. Source telemetry arrived dirty, notional values as text with embedded symbols, dates in inconsistent formats, and a direct CAST on any of it could fail an entire batch silently. The reporting line ran five levels deep, from Analyst to Managing Partner, deeper than a standard join could resolve. And the roster itself preserved zero historical states: every update destroyed the version before it.

The approach

Three structural changes close those three failures. A governed gold view absorbs dirty source data with defensive casting, so a bad row gets classified rather than dropped. A self-join query walks the five-level hierarchy in one pass instead of failing on it. And the flat roster becomes an SCD Type 2 table, where a personnel change closes the old record and opens a new one rather than overwriting anything. One honest limit sits underneath all three: the structure protects every roster change from the day it goes live, but it cannot reconstruct a state that was already overwritten before that.

Land the source tables

Three schemas separate the layers: ref for the roster, stg for raw positions, gold for the governed output. A Data Factory Copy Data activity then pulls both source tables from the upstream Azure SQL database into the Fabric warehouse, one pipeline task per table.

T-SQL script creating three schemas: CREATE SCHEMA ref, CREATE SCHEMA gold, CREATE SCHEMA stg. — The three-layer split, declared before anything else is built.

A Data Factory Copy Data activity's Source tab, connected to an Azure SQL Database named fabric-project-srv-2027 on the msfabricbootcamp database, reading the table VeritasGlobal.ref_Relationship_Managers. — The roster's source connection: table mode, no query logic yet, straight off the upstream Azure SQL database.

The same Copy Data activity's Destination tab, targeting the AnjaliWarehouse connection with auto-create table enabled, writing to ref.Relationship_Managers with Insert as the write behaviour. — Landed as ref.Relationship_Managers, auto-created rather than hand-scripted, since this step is pure ingestion.

A second Copy Data activity's Source tab, reading VeritasGlobal.stg_Flagged_Positions from the same Azure SQL Database connection. — The flagged positions feed, pulled by a second task in the same pipeline.

The second Copy Data activity's Destination tab, writing to stg.Flagged_Positions in the AnjaliWarehouse connection, auto-create table, Insert write behaviour. — Landed as stg.Flagged_Positions, deliberately still in the staging schema: nothing here has been validated yet.

The governed gold view

gold.clean_positions is where dirty data gets absorbed rather than allowed to fail a pipeline. Every numeric and date column from staging goes through TRY_CAST, which returns NULL on a bad value instead of raising an error, and a missing RM is resolved with ISNULL(r.Name, 'Unassigned') rather than dropped by the join.

T-SQL CREATE OR ALTER VIEW statement for gold.clean_positions, selecting PositionID, RMID, an ISNULL-wrapped RMID_Name defaulting to Unassigned, Asset_Class, Currency, Flag_Type, Status, Regulatory_Signoff, TRY_CAST columns for Clean_Settlement_Date and Clean_Flag_Date, TRY_CAST for Clean_Notional_Value, and a CASE expression deriving Exposure_Category from four notional thresholds, left joining stg.Flagged_Positions to ref.Relationship_Managers on a TRY_CAST'd RMID. — The whole defensive contract in one view: nothing here can take the pipeline down, whatever shape the source data arrives in.

Query results from gold.clean_positions showing PositionID, RMID, RMID_Name, Asset_Class, Currency, Flag_Type, Status, a 0 or 1 Regulatory_Signoff column, Clean_Settlement_Date, Clean_Flag_Date, Clean_Notional_Value and Exposure_Category, for nine rows all attributed to Natalie Rousseau or Wei Liang. — Regulatory_Signoff came out as a bit column, 0 or 1, rather than the Y/N varchar the brief specified. It still answers the same question, just typed differently than asked.

Fourteen further rows from gold.clean_positions, including two rows for PositionID POS-0033 and POS-0029 where RMID is NULL and RMID_Name reads Unassigned, and rows with a NULL Clean_Notional_Value classified as Unclassified. — Unassigned positions and unparseable notionals both stay visible, with an explicit label rather than a silent drop.

Look closely at the Exposure_Category logic and one row exposes a real gap in it. The Medium_Exposure branch reads >= 10000000 AND < 9999999, a range that can never be true since ten million is already past nine million, nine hundred and ninety-nine thousand. POS-0008, with a notional of 52,339,391, falls through every branch and lands on Out_of_Range in the first results screenshot above, when it should sit in a mid-tier band. The defensive casting works exactly as designed. The threshold above it has a typo that quietly reclassifies most of the mid-range portfolio.

The RM performance leaderboard

Ranking Relationship Managers by resolved, regulator-signed-off positions, partitioned so RMs only compete against peers, uses DENSE_RANK rather than RANK: two RMs tied on the same count get the same rank, and the RM below them gets the next rank down rather than one skipped to make room.

T-SQL query with a CTE named RM_Performance counting resolved, signed-off positions per RM from gold.clean_positions, then selecting RMID, RMID_Name, Position_Resolved and DENSE_RANK() OVER (ORDER BY Position_Resolved DESC) AS Rank. — Only Status = 'Resolved' and Regulatory_Signoff = 1 rows count. An RM with a pile of still-open positions doesn't get credit for volume alone.

Leaderboard result: James Okafor, Sophie Brennan and Natalie Rousseau each with 2 resolved positions and Rank 1, and Rahul Mehta with 1 resolved position and Rank 2, with no rank 2 or 3 skipped ahead of him. — Three RMs share rank 1 on identical counts, and the next RM down gets rank 2, not rank 4. That's the DENSE_RANK behaviour the brief asked for.

Flatten the five-level hierarchy

ref.Relationship_Managers self-joins to itself four times, once per step up the ManagerID chain from Analyst to Managing Partner, so the complete reporting line resolves in a single query rather than a recursive loop.

T-SQL query selecting RMID, Name and Title from the base alias L5, with a CASE expression deriving Hierarchy_Depth from which of four LEFT JOINed aliases L1 through L4 successfully resolved, and a CONCAT_WS('->', ...) expression building an Accountability_Chain string from all five levels, joining ref.Relationship_Managers to itself four times on ManagerID. — Four LEFT JOINs, walking L5 (the RM in question) up through L4, L3, L2 to L1. A Managing Partner's chain stops at L5 itself; an Analyst's chain resolves all the way to L1.

Hierarchy query result: Natalie Rousseau, Analyst, Hierarchy_Depth L5, chain Natalie Rousseau to James Okafor to Wei Liang to Sophie Brennan to Rahul Mehta. James Okafor, Associate, L4. Wei Liang, Director, L3. Sophie Brennan, Managing Director, L2. Rahul Mehta, Managing Partner, L1, chain is just his own name. — Every RM appears exactly once, with the full chain spelled out as a single readable string rather than five separate columns.

Hierarchy_Depth here counts how many people are in the chain from that RM up to the top, five for the Analyst at the bottom, one for the Managing Partner who has no manager above them. It’s a different number from the “regulatory significance level” in the brief, which ran the other way, one for Analyst and five for Managing Partner. Both describe the same five-step ladder; they just count from opposite ends.

Make roster history survive change

This is the part the whole case study is named for. The roster gets three new columns, start_date, end_date, and is_active, and every existing row is backfilled to an active state before any real change ever reaches it.

T-SQL ALTER TABLE statement adding start_date DATE, end_date DATE and is_active BIT to ref.Relationship_Managers, followed by an UPDATE setting start_date to seven days before GETDATE, end_date to NULL and is_active to 1 for every existing row. — The one-time backfill: every current RM becomes an active row with no end date, the baseline the SCD logic builds on from here.

ref.Relationship_Managers after the backfill, showing five RMs, all with is_active 1, start_date 2026-08-15 and end_date NULL. — Five people, five active rows, nothing yet to distinguish this from a plain roster table. The next step is what makes it SCD Type 2.

Incoming changes land in stg.New_RM_Roster, a feed of only the RMs who were promoted, moved, or newly hired, not a full resync of the roster. A staging query tags each incoming row twice: once with its real RMID as MergedKey, but only if that RM already exists as an active record, and once with MergedKey forced to NULL, unconditionally.

T-SQL query selecting from stg.New_RM_Roster with an INNER JOIN to ref.Relationship_Managers, tagging matched rows with S.RMID AS MergedKey, UNION ALL with a second SELECT from the same staging table tagging every row with NULL AS MergedKey. — Every incoming change becomes two candidate rows: one that can only match an existing active record, one that never can.

Staging diff result: Natalie Rousseau and Wei Liang each appear twice, once with MergedKey set to their RMID and once with MergedKey NULL, while Amir Hassan, a new hire, appears once with MergedKey NULL. — Natalie and Wei Liang were both changed, so each produced a matched copy and a NULL copy. Amir Hassan is brand new, so only the NULL copy exists for him, there's no existing row for the INNER JOIN to match.

Challenge 01

Make one change produce two rows, not zero or four

The MERGE has to close the old version of a changed RM's record and open a new one, in a single atomic statement, without double-processing an unchanged RM and without letting a changed RM's own two staging rows both fire the same outcome.

The MergedKey trick from the staging query does the routing. The ON clause is S.MergedKey = T.RMID AND T.is_active = 1. The matched-copy row (real MergedKey) finds the existing active record and enters WHEN MATCHED, where an AND block checks whether name, title, manager, jurisdiction, or desk actually differ, if none of them do, no action fires for that pairing at all. The NULL-copy row can never satisfy S.MergedKey = T.RMID, because SQL never treats NULL as equal to anything, so it always falls to WHEN NOT MATCHED THEN INSERT.

For a genuinely changed RM, that produces exactly the pair the acceptance criteria asked for: the matched copy closes the old row (end_date set, is_active = 0), and the NULL copy inserts the new one. The two outcomes can’t collide, because they’re never the same row hitting the same clause, they’re two different staging rows built for two different jobs.

It’s worth naming the edge this doesn’t fully cover: the insert side has no equivalent “only if something actually changed” guard. If stg.New_RM_Roster ever carried a resubmission of an unchanged record, its NULL-copy row would still insert a new active version, because WHEN NOT MATCHED alone doesn’t check whether anything is different. The design leans on the upstream feed only ever containing genuine changes, which held for this run, but isn’t enforced by the MERGE itself.

The MERGE statement went through two passes. The first wired up the routing logic with a placeholder where the staging query would go. The second substituted the real subquery in and labelled which side was which.

T-SQL MERGE INTO ref.Relationship_Managers T USING a placeholder comment reading 'here we insert staging table query' AS S, ON S.MergedKey = T.RMID AND T.is_active = 1, WHEN MATCHED AND a block of five inequality checks THEN UPDATE closing the row, WHEN NOT MATCHED THEN INSERT a new active row. — The MERGE shell, written before the staging query was dropped in: the routing logic first, the data source second.

The completed MERGE statement, with comments reading Source = Incoming Records and Target = Existing Records, the full UNION ALL staging subquery inlined in the USING clause, then the same ON, WHEN MATCHED AND, and WHEN NOT MATCHED logic as the skeleton version. — The finished statement: one atomic MERGE, source and target labelled, ready to run against stg.New_RM_Roster on a schedule.

ref.Relationship_Managers after the MERGE. Natalie Rousseau appears twice, Associate active from 2026-08-22 and Analyst closed with end_date 2026-08-21. Wei Liang appears twice, Director in UK active from 2026-08-22 and Director in SG closed with end_date 2026-08-21. James Okafor, Sophie Brennan and Rahul Mehta each appear once, untouched. Amir Hassan appears once, active from 2026-08-22. — The proof: Natalie's promotion and Wei Liang's jurisdiction move both closed their old row and opened a new one. The three RMs with no incoming change never got touched, and the new hire got exactly one row.

What this proves

A view can absorb source-data failures without a pipeline going down, a five-level hierarchy can resolve in one query instead of a recursive loop, and a personnel change can become history instead of erasing it, all inside plain T-SQL. But the honest answer to the question this case study is named for is still no. The SCD Type 2 structure protects every roster change from the day it goes live forward. It cannot reconstruct the state of a record that was already overwritten before that date. Telling a regulator what a system can and cannot prove, in the same breath as showing them the fix, is part of the engineering, not an afterthought to it.