top of page

Background

Veritas Global, a $42 billion institutional asset manager operating across London, New York, and Singapore, faced a structural gap in its regulatory reporting: when the FCA asked who was responsible for a flagged position on a specific date, the firm couldn't answer, because its Relationship Manager roster overwrote every historical record on personnel change. This case study covers the T-SQL architecture built to close that gap — a defensive gold-layer view that absorbs dirty source data without dropping rows, a CTE that resolves a five-level accountability hierarchy from Analyst to Managing Partner, and an SCD Type 2 MERGE pattern that preserves every future roster change as permanent history instead of erasing it.

Problem

  • ​​FCA (Financial Conduct Authority) review requested point-in-time attribution for flagged positions, and the firm couldn't identify which Relationship Manager was responsible on a specific historical date.

  • The RM (Relationship Management) roster table overwrote records on every personnel change, permanently destroying prior states with no way to reconstruct history.

  • Source position data arrived dirty — notional values stored as text with embedded symbols, dates as inconsistently formatted strings — and direct casting caused entire batches to fail silently.

  • The accountability chain ran five levels deep (Analyst to Managing Partner), and standard joins against a flat roster couldn't resolve it, making executive-level attribution impossible.

  • Three separate regulators (FCA, SEC - Securities and Exchange Commission and MAS - Monetary Authority of Singapore) each required historical attribution, compounding the exposure from a single unresolved gap.

  • Manual reconstruction of accountability took roughly 15 hours per regulatory cycle and depended on memory and email trails rather than reliable data.

Solution

Data Pipeline

  • Built a Fabric Data Pipeline with two Copy Data activities to bring the source tables — Relationship Manager roster and flagged positions — from an Azure SQL Database into the warehouse

  • Configured the RM_Data copy activity to pull from VeritasGlobal.ref_Relationship_Managers and land it as ref.Relationship_Managers in the warehouse, using auto-create table with Insert as the write behavior

  • ​Set up the second Copy Data activity to pull the raw flagged positions table, VeritasGlobal.stg_Flagged_Positions, from the same Azure SQL Database source.

  • Landed it in the warehouse as stg.Flagged_Positions using auto-create table with Insert, keeping it in staging since this is the "dirty" data that still needs cleansing before it's ready for reporting.

Warehouse Environment (SQL codes)

Created 3 schemas in the warehouse — stg, ref, and gold — to separate raw staging data, reference tables, and the final governed output, matching the three-layer structure defined in the engineering mandate

  • Built the gold.Clean_Positions view using TRY_CAST on the settlement date, flag date, and notional value, so a badly formatted source value returns NULL instead of crashing the entire query.

  • Left-joined positions to the RM roster with ISNULL marking unmatched RMs as "Unassigned" instead of dropping the row, and added an Exposure_Category using a CASE statement that buckets notional value into Low, Medium, High, or Out of Range — with Unclassified for values that couldn't be parsed at all.

  • Built the Hierarchy Flattener by joining the RM roster table to itself five times (L1 through L5), walking up the ManagerID chain from each RM to the Managing Partner at the top.

  • Used a CASE statement to calculate Hierarchy_Depth based on how far the chain goes, and concatenated all five names together with CONCAT_WS to produce a single readable Accountability Chain string for each RM

Built the RM Performance Leaderboard by counting resolved, regulator-signed-off positions per RM in a CTE, then ranking them globally by resolved count using DENSE_RANK so tied RMs share the same rank without skipping a number

SQL DDL (Data Definition Language) script

  • Evolved the flat ref.Relationship_Managers table into an SCD Type 2 structure by adding three tracking columns: start_date, end_date, and is_active

  • Backfilled all existing records as the current active state, setting start_date to 7 days ago, end_date to NULL, and is_active to 1

  • Created a source table stg.New_RM_Roster to hold incoming roster changes, matching the same structure as the main roster (RMID, Name, Title, ManagerID, Jurisdiction, Desk)

  • Inserted 3 sample roster changes into the source table — a promotion, a desk move, and a jurisdiction transfer — to simulate incoming updates that the SCD Type 2 MERGE logic will need to process next.

Implementing SCD Type 2 Logic using MERGE INTO USING approach

  • Built the SCD Type 2 MERGE, the final piece that closes the accountability gap for good, using a source query that routes each incoming roster change into one of two branches: a "matched" branch (RM already exists) and a "not matched" branch (brand new RM)

  • For an RM whose details changed (name, title, manager, desk, or jurisdiction), the MERGE expires their existing record by setting end_date to yesterday and is_active to 0, rather than overwriting it

  • For a brand-new RM, the MERGE inserts a fresh row with today's start_date, a NULL end_date, and is_active set to 1.

  • This means an RM's promotion or move never erases history — the old record stays on file as "expired," and a new "active" record takes over, so any point-in-time question about who was responsible can now be answered accurately.

  • Ran the MERGE against the incoming roster changes and confirmed the SCD Type 2 logic worked exactly as designed: Natalie Rousseau's promotion from Analyst to Associate closed her old record (end_date 27-08-2026, is_active 0) and created a new active one, rather than simply overwriting her title.

  • Wei Lang's move from the SG jurisdiction to UK produced the same pattern — the prior record was preserved as expired history while a new active record captured the change, and a matching update correctly fixed a name spelling mismatch ("Wei Liang" to "Wei Lang") along the way.

  • Amir Hassan, a brand-new RM not previously in the table, was correctly routed to the "insert" branch of the MERGE, landing as a fresh active record with no prior history to preserve — confirming the logic handles both existing RM updates and new RM additions correctly

Outcome

  • ​Closes the accountability gap permanently — every roster change from go-live forward is preserved as history, giving Veritas Global a defensible answer to any regulator's point-in-time attribution question.

  • Eliminates pipeline failures from dirty source data — defensive casting means a single malformed date or notional value no longer takes down the entire reporting view.

  • Reduces regulatory exposure — a resolved gap that previously risked $2M to $5M in penalties per audit cycle across FCA, SEC (Securities and Exchange Commission), and MAS (Monetary Authority of Singapore) reviews.

  • Cuts manual reconstruction effort — replaces roughly fifteen hours of memory- and email-dependent manual work per regulatory cycle with a queryable, automated source of truth.

  • Delivers full accountability visibility — every flagged position now carries a clear exposure classification and a traceable chain from Analyst to Managing Partner, ready for direct submission to regulatory reviewers.

bottom of page