top of page
Screenshot 2026-02-02 134929.jpg

Veritas Global - T-SQL Analytics

Case Study

About

This case study demonstrates the design of a robust compliance analytics solution using Microsoft Fabric Warehouse and advanced T-SQL. The implementation focuses on cleansing unreliable legacy data, flattening multi-level organizational hierarchies, and maintaining full historical accountability using SCD Type 2 patterns. The solution enables accurate compliance reporting, workload analysis, and executive-level visibility through reliable, audit-ready data models.

Challenge

  • Unreliable Compliance Reporting – Leadership lacked confidence in compliance reports due to poor data quality, leading to inaccurate aging metrics and delayed decision-making.

​

  • Inconsistent Legacy Source Data – Critical fields such as incident dates were stored as free-text with mixed formats and invalid values, causing T-SQL queries to fail and pipelines to break.

​

  • Complex Multi-Level Hierarchies – Analyst reporting lines spanned up to five levels, making it difficult to associate incidents with the correct leadership chain using standard SQL joins.

​

  • Missing Historical Ownership – Frequent analyst and team changes required tracking historical assignments over time to ensure accountability for past incidents.

​

  • Advanced Analytics Under Constraints – The solution needed to support running case loads, role-based rankings, and aging risk analysis using T-SQL while remaining performant and production-ready

Solution

  • Compliance Data Foundation – A defensive T-SQL data layer was created to standardize unreliable legacy fields, ensuring compliance reports remain accurate and do not fail due to inconsistent source data.

​

  • Actionable Aging Risk Insights – Business-defined aging risk categories (Fresh, Warning, Critical) were implemented directly in T-SQL, enabling leadership to quickly identify overdue and high-risk compliance incidents.

​

  • Clear Accountability Through Hierarchies – Multi-level analyst reporting structures were flattened using fixed-depth hierarchy logic, allowing incidents to be traced across the full management chain for better oversight and escalation.

​​

  • Accurate Historical Ownership – Analyst and team changes were tracked using SCD Type 2 patterns, ensuring each incident is attributed to the correct owner based on when it occurred.

​

  • Scalable, Audit-Ready Analytics – The final solution delivered trusted, query-ready views and procedures in Microsoft Fabric Warehouse, supporting operational analysis, executive reporting, and audit requirements at scale.

Business impact

  • Trusted Reporting – Standardized and validated data improved confidence in compliance reports and dashboards.

​

  • Clear Ownership – Historical tracking and hierarchy visibility ensured incidents were attributed to the correct owners.

​

  • Faster Risk Detection – Aging risk categories helped teams quickly identify and prioritize high-risk cases.

​

  • Executive Visibility – Leadership gained clear insight into workloads, performance, and escalation paths.

​

  • Audit Readiness – A scalable, production-ready data model supported audits and ongoing compliance needs.

carbon (31).png

The Gold.Clean_Incidents view applies  T-SQL logic to standardize compliance incident data by safely converting legacy text-based date fields using TRY_CAST and defaulting invalid values to 1900-01-01 to prevent query failures. Missing analyst assignments are handled using null-safe logic, ensuring incidents remain available for downstream analysis and joins. Business-driven aging logic is implemented using DATEDIFF and CASE expressions to classify incidents into Fresh, Warning, and Critical risk categories. This view provides a reliable Gold-layer foundation for analytics and reporting in Microsoft Fabric Warehouse.

This query flattens a fixed-depth organizational hierarchy using a Common Table Expression (Unique_Analysts) to first remove duplicate analyst records. Multiple self-joins are then applied to resolve up to five levels of reporting relationships, mapping each analyst to their full management chain. A CASE expression determines the hierarchy depth based on the highest populated manager level, while CONCAT_WS constructs a readable command chain path from analyst to director.

This MERGE statement implements Slowly Changing Dimension (SCD) Type 2 logic to track historical changes in analyst attributes such as title and manager. The source data is prepared using a UNION ALL to differentiate existing records from new versions by assigning a MergeKey only to current records. When a match is found on AnalystID and the target record is marked as current, any change in title or manager triggers an update that expires the existing record by setting its End_Date to yesterday and marking it as non-current. New or changed analyst records are then inserted with a fresh Start_Date, a NULL End_Date, and IS_Current = 1, preserving a full history of analyst assignments over time without overwriting prior records.

This output validates the SCD Type 2 implementation in the Analyst_Roaster_History table. When an analyst’s attributes change, the existing record is expired by setting the End_Date and IS_Current = 0, and a new record is inserted with an updated Start_Date, NULL End_Date, and IS_Current = 1. As shown, multiple rows per analyst represent different time slices, preserving full history while clearly identifying the active record

bottom of page