From Dirty Data to Defensible History: Compliance Analytics Using T-SQL and SCD Type 2 in Microsoft Fabric Warehouse
Project Overview
This project focuses on modernizing compliance analytics for Veritas Global, a legal and compliance organization struggling with unreliable reporting and limited visibility into its incident investigation workflows.
The objective was to transform inconsistent operational data into reliable, analytics-ready datasets using advanced T-SQL capabilities in Microsoft Fabric Warehouse. The solution addresses data quality challenges, complex organizational hierarchies, analyst workload monitoring, and historical tracking of employee assignments to support accurate compliance reporting and executive decision-making.
The Challenge
-
Unreliable dates: The incident created date was stored as text, mixing formats and values like “Error”, so ordinary queries failed.
-
Missing assignments: Many incidents had no assigned analyst, and a careless join would silently drop those rows.
-
A five-level reporting line: Analyst, Lead, Manager, VP and Director were held in one table, so finding an analyst’s Director needed more than a standard join.
-
No history: The roster changed weekly and was overwritten, so leadership could not tell who was actually responsible when an incident occurred.
Solution Overview
I implemented a T-SQL analytics solution in Microsoft Fabric Warehouse to improve compliance reporting. The solution works as follows:
-
Data Cleansing: Standardized incident dates, handled NULL values, and classified cases by ageing risk.
-
Window Functions: Calculated running case totals and ranked analysts by performance.
-
Hierarchy Reconstruction: Used CTEs and self-joins to map the five-level organizational hierarchy.
-
SCD Type 2: Developed a stored procedure to maintain historical analyst roster changes.
-
Historical Accountability: Matched incidents with the analyst and management structure responsible at the time.
-
Executive Reporting: Identified critical case backlogs and workload distribution across Directors.
This solution enables reliable compliance reporting, performance monitoring, and historical accountability.
Implementation
Data Standardization & NULL Handling
Unknown, Error Values – Legacy_Created_Date
Handling Null values – Assigned_AnalystID
Added New column as Analyst_Key_Display
How can we clean messy, inconsistent incident data so it becomes reliable for analytics?
Organizational Hierarchy
II - Tracking Analyst Workloads and Performance Using SQL Window Functions
The objective is to monitor cumulative case workloads over time and identify top-performing analysts through role-based rankings.
1. Cumulative Incident Tracking: Calculated running totals of incidents for each analyst using COUNT() OVER(), ordered chronologically by incident creation date.
Implemented COUNT() OVER() with PARTITION BY and ORDER BY to calculate the running total of incidents assigned to each analyst in chronological order. Used a CTE to prepare cleaned incident dates and excluded invalid default dates (1900-01-01) to maintain accurate timeline analysis.
2. Role-Based Performance Leaderboard: Used DENSE_RANK() to rank analysts based on closed incident counts, partitioning the results by job title to ensure fair comparisons.
Used DENSE_RANK() to rank analysts based on their total number of closed incidents. Partitioned the ranking by job Title to compare analysts within the same professional role. A LEFT JOIN was used to combine incident records with analyst reference data.
SCD Type II Implementation
Implemented an SCD Type 2 historical tracking mechanism using T-SQL Stored Procedures to maintain multiple versions of analyst records.
Business Impact
-
Improves compliance visibility through accurate incident tracking and ageing analysis.
-
Strengthens accountability by identifying the management structure at the time of each incident.
-
Reduces audit risks through reliable historical data and traceable reporting.
-
Enables leadership to identify case bottlenecks and make informed operational decisions.
Closing Note
Project Watchdog transforms fragmented compliance data into reliable, audit-ready insights. By combining advanced T-SQL, hierarchy modelling, and SCD Type 2, the solution strengthens historical accountability, improves operational transparency, and enables faster, data-driven compliance decisions for Veritas Global.