Microsoft Purview Data Quality: A Comprehensive Technical Guide

Data Source Layer

Registered data sources including relational databases, data lakes, file systems, and SaaS applications. Each source requires appropriate credentials and network connectivity through Azure Integration Runtime or Self-Hosted Integration Runtime (SHIR).

Scan Configuration Layer

Defines scan scope (databases, schemas, tables), rule sets to apply, scan schedules (one-time, recurring), and resource allocation. Scans can be full or incremental, with support for partition-level scanning for performance optimisation.

Rule Engine Layer

Executes built-in and custom quality rules against scanned data. Rules are organized into rule sets and can validate completeness, uniqueness, validity, consistency, and custom business logic. The engine supports SQL-based expressions and statistical functions.

Assessment & Reporting Layer

Aggregates scan results into quality scores, generates alerts for threshold violations, and provides interactive dashboards. Supports data quality dimensions scoring, trend analysis, and exportable reports in CSV and PDF formats.

The architecture follows a distributed processing model where scan workloads execute on Integration Runtime nodes, whilst metadata and assessment results store in Purview's managed service. This separation ensures minimal impact on source systems whilst enabling parallel processing of multiple tables. The platform uses Azure Active Directory for authentication and supports role-based access control (RBAC) with distinct permissions for data readers, quality managers, and administrators.

Azure Subscription Prerequisites

• Active Azure subscription with appropriate permissions (Contributor or Owner role at resource group level)
• Microsoft Purview account provisioned through Azure Portal or ARM templates
• Azure SQL Database or alternative data source for initial testing environment
• Resource quotas verified for additional Purview capacity units if processing large data estates

Network and Connectivity

• Azure Integration Runtime (automatic for Azure sources) or Self-Hosted Integration Runtime for on-premises/private network sources
• Firewall rules configured to allow Purview service tags or specific IP ranges
• Private endpoint configuration if connecting to VNet-integrated data sources
• DNS resolution verified for all target data sources from Integration Runtime location

Authentication and Permissions

• Service principal or managed identity with read access to target data sources
• Data Source Administrator role in Purview for registering sources
• Data Reader role minimum for scanning databases and tables
• Azure RBAC permissions: Microsoft.Purview/accounts/scan/read and write permissions

Development Tools

• Purview Studio web interface (primary management portal)
• Azure PowerShell module (Az.Purview) for automation scenarios
• SQL Server Management Studio or Azure Data Studio for validating rule syntax
• Python SDK (azure-purview-scanning) for programmatic scan management

Advantages of Built-in Rules

• Immediate Deployment: Activated with simple checkboxes during scan configuration, no syntax knowledge required
• Optimised Performance: Microsoft-engineered for efficient execution across various source types and data volumes
• Automatic Updates: Enhanced by Microsoft as patterns evolve and new quality checks are identified
• Cross-Source Consistency: Apply identical logic across SQL, NoSQL, and file-based sources

Common Built-in Rules Include

• Null value detection across all columns
• Data type conformance validation
• Length constraint verification
• Duplicate key identification
• Statistical outlier detection
• Pattern matching for common formats (email, phone, postal codes)

Row-Level Validation

-- Check single record constraint
{Age} >= 18 AND {Age} <= 120

-- Multi-condition validation
{Status} IN ('Active', 'Pending', 'Closed')
AND {CreatedDate} <= GETDATE()

-- Pattern conformance
{PostalCode} LIKE '[0-9][0-9][0-9][0-9]'

Aggregate Validation

-- Completeness threshold
(COUNT(*) WHERE {Email} IS NOT NULL) 
/ COUNT(*) >= 0.95

-- Uniqueness check
COUNT(DISTINCT {CustomerID}) = COUNT(*)

-- Range validation
MAX({TransactionDate}) <= GETDATE()

Rule 4A: Numeric Range Validation

-- Rule Name: SensibleNumericBoundaries
-- Dimension: Validity
-- Severity: High
-- Description: Validates numeric fields fall within 
--              realistic business boundaries

-- Age validation (assuming human subjects)
{Age} IS NOT NULL
AND {Age} >= 0
AND {Age} <= 120

-- Percentage validation
AND {DiscountPercentage} >= 0
AND {DiscountPercentage} <= 100

-- Currency amount validation (no negative revenues)
AND {TransactionAmount} >= 0
AND {TransactionAmount} <= 1000000

-- Temperature sensor validation (Celsius)
AND {Temperature} >= -50
AND {Temperature} <= 60

-- Threshold: Green ≥ 99%, Yellow ≥ 97%, Red < 97%

Sector Application: Universal across industries. Manufacturing uses range validation for sensor readings; retail for pricing and discounts; healthcare for vital signs; financial services for transaction amounts and credit limits.

Rule 4B: Date Range and Temporal Validity

-- Rule Name: TemporalBoundaryCompliance
-- Dimension: Validity
-- Severity: High
-- Description: Validates dates fall within logical 
--              temporal boundaries

-- No future dates for historical transactions
{TransactionDate} IS NOT NULL
AND {TransactionDate} <= GETDATE()

-- No dates before business inception
AND {TransactionDate} >= '2010-01-01'

-- Reasonable data retention window (7 years)
AND {TransactionDate} >= DATEADD(year, -7, GETDATE())

-- Birth dates for adults (18+ requirement)
AND {DateOfBirth} IS NOT NULL
AND DATEDIFF(year, {DateOfBirth}, GETDATE()) >= 18
AND DATEDIFF(year, {DateOfBirth}, GETDATE()) <= 100

-- Threshold: Green ≥ 99.5%, Yellow ≥ 98%, Red < 98%

Sector Application: Critical for insurance policy dates, education enrolment periods, utility service activation dates, and energy contract effective dates. Prevents temporal anomalies in trend analysis.

Rule 4C: Logical Sequence and Ordering

-- Rule Name: ChronologicalSequenceIntegrity
-- Dimension: Consistency
-- Severity: High
-- Description: Validates logical ordering between 
--              related date fields

-- Start date precedes end date
{PolicyStartDate} IS NOT NULL
AND {PolicyEndDate} IS NOT NULL
AND {PolicyStartDate} < {PolicyEndDate}

-- Reasonable duration constraints (1-5 year policies)
AND DATEDIFF(day, {PolicyStartDate}, {PolicyEndDate}) >= 365
AND DATEDIFF(day, {PolicyStartDate}, {PolicyEndDate}) <= 1825

-- Created date precedes modified date
AND {CreatedDate} <= {LastModifiedDate}

-- Application date before approval date
AND ({ApprovalDate} IS NULL 
     OR {ApplicationDate} <= {ApprovalDate})

-- Threshold: Green = 100%, Yellow ≥ 99%, Red < 99%

Sector Application: Insurance policies, energy contracts, educational enrolments, utility service periods. Ensures business process integrity and supports accurate duration calculations for billing and analytics.

Rule 4D: Categorical Domain Validation

-- Rule Name: AllowedCategoricalValues
-- Dimension: Validity
-- Severity: Medium
-- Description: Validates categorical fields contain 
--              only approved domain values

-- Status field validation
{AccountStatus} IS NOT NULL
AND {AccountStatus} IN (
  'Active', 'Inactive', 'Suspended', 
  'PendingActivation', 'Closed', 'Dormant'
)

-- Priority level validation
AND {PriorityLevel} IN ('Low', 'Medium', 'High', 'Critical')

-- Country code validation (ISO 3166-1 alpha-2)
AND {CountryCode} IN (
  'GB', 'US', 'DE', 'FR', 'ES', 'IT', 
  'NL', 'BE', 'IE', 'SE', 'NO', 'DK'
  -- Include all valid ISO codes
)

-- Product category validation
AND {ProductCategory} IN (
  SELECT DISTINCT {CategoryCode} 
  FROM [dbo].[ApprovedCategories]
  WHERE {IsActive} = 1
)

-- Threshold: Green ≥ 98%, Yellow ≥ 95%, Red < 95%

Sector Application: Standardises dropdown values, classification codes, and status indicators across systems. Prevents free-text variations and supports reliable aggregation and filtering in reporting.

Rule 5A: Primary Key Uniqueness

-- Rule Name: PrimaryKeyUniquenessConstraint
-- Dimension: Uniqueness
-- Severity: Critical
-- Description: Validates no duplicate primary key 
--              values exist in the dataset

-- Aggregate uniqueness check
COUNT(DISTINCT {CustomerID}) = COUNT(*)

-- Alternative expression checking for duplicates:
-- (SELECT COUNT(*) FROM [dbo].[Customers] c2
--  WHERE c2.{CustomerID} = PARENT.{CustomerID}) = 1

-- Row-level duplicate identification:
-- {CustomerID} NOT IN (
--   SELECT {CustomerID}
--   FROM [dbo].[Customers]
--   GROUP BY {CustomerID}
--   HAVING COUNT(*) > 1
-- )

-- Threshold Settings:
-- Green: 100% unique (no duplicates permitted)
-- Yellow: ≥ 99.9%
-- Red: < 99.9%

Critical Impact: Primary key duplicates cause unpredictable query results, corrupted joins, and analytical inaccuracies. Immediate investigation required when detected.

Rule 5B: Business Key Uniqueness

-- Rule Name: EmailAddressUniquenessCheck
-- Dimension: Uniqueness
-- Severity: High
-- Description: Validates business identifiers remain 
--              unique across active records

-- Email uniqueness for active customers
{EmailAddress} NOT IN (
  SELECT {EmailAddress}
  FROM [dbo].[Customers]
  WHERE {AccountStatus} = 'Active'
    AND {EmailAddress} IS NOT NULL
  GROUP BY {EmailAddress}
  HAVING COUNT(*) > 1
)

-- Alternative aggregate approach:
-- COUNT(DISTINCT {EmailAddress}) = 
-- COUNT(*) WHERE {EmailAddress} IS NOT NULL

-- Threshold: Green ≥ 99%, Yellow ≥ 97%, Red < 97%

Business Context: Email, national insurance numbers, passport numbers, and other business identifiers should be unique within active record populations. Duplicates indicate data entry errors or fraudulent accounts.

Rule 5C: Composite Key Duplicate Detection

-- Rule Name: CompositePrimaryKeyUniqueness
-- Dimension: Uniqueness
-- Severity: Critical
-- Description: Validates uniqueness across multiple 
--              column combinations

-- Check ProductID + EffectiveDate combination uniqueness
CONCAT({ProductID}, '|', 
       CAST({EffectiveDate} AS VARCHAR)) NOT IN (
  SELECT CONCAT({ProductID}, '|', 
                CAST({EffectiveDate} AS VARCHAR))
  FROM [dbo].[ProductPrices]
  GROUP BY {ProductID}, {EffectiveDate}
  HAVING COUNT(*) > 1
)

-- Alternative multi-column check:
-- NOT EXISTS (
--   SELECT 1 FROM [dbo].[ProductPrices] p2
--   WHERE p2.{ProductID} = PARENT.{ProductID}
--     AND p2.{EffectiveDate} = PARENT.{EffectiveDate}
--     AND p2.{RecordID} != PARENT.{RecordID}
-- )

-- Threshold: Green = 100%, Yellow ≥ 99.5%, Red < 99.5%

Application Scenarios: Time-series data with entity+date keys, multi-regional pricing with product+region keys, grade records with student+course keys.

Rule 5D: Fuzzy Duplicate Identification

-- Rule Name: FuzzyNameMatchDetection
-- Dimension: Uniqueness
-- Severity: Medium
-- Description: Identifies potential duplicates through 
--              similarity matching

-- Detect similar names with same postal code
{CustomerID} NOT IN (
  SELECT c1.{CustomerID}
  FROM [dbo].[Customers] c1
  INNER JOIN [dbo].[Customers] c2
    ON c1.{PostalCode} = c2.{PostalCode}
    AND c1.{CustomerID} < c2.{CustomerID}
    AND (
      -- Same surname, similar first name
      c1.{Surname} = c2.{Surname}
      AND LEFT(c1.{FirstName}, 3) = LEFT(c2.{FirstName}, 3)
      OR
      -- Transposed first/last names
      c1.{FirstName} = c2.{Surname}
      AND c1.{Surname} = c2.{FirstName}
    )
)

-- Threshold: Green ≥ 95%, Yellow ≥ 90%, Red < 90%
-- Note: Lower thresholds acceptable as fuzzy matching
--       may flag legitimate records for manual review

Investigation Workflow: Fuzzy matches require data steward review. Configure alerts to route potential duplicates to deduplication queues for manual or ML-assisted resolution.

Statistical Validation Patterns

Implement z-score analysis to detect outliers: (ABS({Value} - AVG({Value})) / NULLIF(STDEV({Value}), 0)) <= 3. This identifies values more than three standard deviations from mean, adapting automatically to dataset characteristics. Useful for sensor data, transaction amounts, and performance metrics where acceptable ranges aren't predetermined but emerge from historical patterns. Extend with interquartile range (IQR) methods: {Value} >= PERCENTILE_CONT(0.25) - 1.5 * (PERCENTILE_CONT(0.75) - PERCENTILE_CONT(0.25)) for distributions with extreme outliers.

Time-Series Continuity Validation

Verify temporal sequence completeness and detect gaps: EXISTS (SELECT 1 FROM Readings r2 WHERE r2.DeviceID = PARENT.DeviceID AND r2.Timestamp = DATEADD(hour, -1, PARENT.Timestamp)). Identifies missing time-series data points critical for continuous monitoring applications. Implement moving window comparisons: ABS({CurrentValue} - LAG({CurrentValue}) OVER (PARTITION BY {DeviceID} ORDER BY {Timestamp})) <= {CurrentValue} * 0.2 to flag sudden changes exceeding 20% from prior reading, indicating sensor malfunction or data transmission error.

Graph Relationship Validation

Validate network topology constraints in hierarchical or graph structures: ensure no circular references in org charts, verify maximum hierarchy depth, validate degree constraints (nodes can't exceed N connections). Example preventing circular reporting lines: WITH RecursiveCTE AS (SELECT {EmployeeID}, {ManagerID}, 1 AS Level FROM Employees WHERE {EmployeeID} = PARENT.{EmployeeID} UNION ALL SELECT e.{EmployeeID}, e.{ManagerID}, Level + 1 FROM Employees e INNER JOIN RecursiveCTE r ON e.{ManagerID} = r.{EmployeeID} WHERE Level < 10) SELECT COUNT(*) FROM RecursiveCTE WHERE {EmployeeID} = {ManagerID}) = 0.

Checksum and Hash Validation

Verify data integrity through calculated checksums: RIGHT({AccountNumber}, 1) = CAST((SUM(CAST(SUBSTRING({AccountNumber}, n, 1) AS INT)) % 10) AS VARCHAR) implementing Luhn algorithm for account numbers. Validate hash columns match source data: HASHBYTES('SHA2_256', CONCAT({Field1}, {Field2}, {Field3})) = {StoredHash} detecting unauthorised modifications or transmission corruption. Essential for financial identifiers, sensitive data integrity, and change detection mechanisms.

Stage 1: Structural Validation

First pass validates basic structure: non-null required fields, data type conformance, length constraints. Fast-executing rules that quickly eliminate fundamentally invalid records before expensive validation.

Stage 2: Format and Pattern Validation

Second pass applies format rules: email patterns, postcode formats, phone number structure. Moderate complexity rules executed only on structurally valid records.

Stage 3: Business Logic Validation

Third pass implements complex business rules: cross-field validation, date sequencing, conditional requirements. Computationally intensive rules applied to reduced dataset.

Stage 4: Referential and Statistical Validation

Final pass performs expensive operations: referential integrity checks, duplicate detection, statistical outlier analysis. Applied only to records passing all prior stages.

Critical Data Quality Dimensions

• Completeness: Missing meter reads prevent accurate billing and create estimated consumption issues
• Accuracy: Erroneous readings from faulty meters or transmission errors lead to customer complaints
• Timeliness: Delayed data prevents real-time grid balancing and demand response
• Consistency: Consumption patterns must align with historical profiles and seasonal expectations
• Validity: Readings must fall within physically possible ranges for meter type and customer segment

Data Sources in Scope

• Smart meter readings (half-hourly electricity, daily gas)
• Substation monitoring sensors (voltage, current, temperature)
• Customer master data (supply point references, meter types)
• Tariff and pricing tables (time-of-use rates)
• Network topology configuration (transformer connections)
• Outage and incident logs (service interruptions)

Meter Reading Plausibility Validation

-- Rule Name: SmartMeterReadingPlausibility
-- Dimension: Validity
-- Severity: Critical
-- Description: Validates electricity consumption falls 
--              within physically possible ranges by customer type

CASE
  -- Domestic customer validation (kWh per half-hour)
  WHEN {CustomerSegment} = 'Domestic'
  THEN {ConsumptionKWH} >= 0
       AND {ConsumptionKWH} <= 10
       AND ({ConsumptionKWH} = 0 
            OR {ConsumptionKWH} >= 0.01)
  
  -- Small commercial validation  
  WHEN {CustomerSegment} = 'SmallCommercial'
  THEN {ConsumptionKWH} >= 0
       AND {ConsumptionKWH} <= 50
  
  -- Industrial customer validation
  WHEN {CustomerSegment} = 'Industrial'
  THEN {ConsumptionKWH} >= 0
       AND {ConsumptionKWH} <= 500
  
  ELSE TRUE
END

-- Additional checks for physically impossible values
AND {ConsumptionKWH} IS NOT NULL
AND {ReadingTimestamp} IS NOT NULL
AND {ReadingTimestamp} <= GETDATE()

-- Threshold: Green ≥ 99%, Yellow ≥ 97%, Red < 97%

Business Impact: Prevents billing of physically impossible consumption values that generate customer disputes. Flags meter faults requiring field engineer dispatch before inaccurate bills are issued.

Time-Series Continuity and Gap Detection

-- Rule Name: MeterReadingContinuityCheck
-- Dimension: Completeness
-- Severity: High
-- Description: Validates continuous half-hourly readings 
--              with no unexpected gaps

-- Check previous reading exists 30 minutes prior
EXISTS (
  SELECT 1 
  FROM [dbo].[SmartMeterReadings] prev
  WHERE prev.{MeterPointID} = PARENT.{MeterPointID}
    AND prev.{ReadingTimestamp} = 
        DATEADD(minute, -30, PARENT.{ReadingTimestamp})
)

-- Alternative aggregate approach for daily completeness:
-- (COUNT(*) WHERE {MeterPointID} = PARENT.{MeterPointID}
--  AND CAST({ReadingTimestamp} AS DATE) = 
--      CAST(PARENT.{ReadingTimestamp} AS DATE)) >= 48

-- Exclude expected gaps during meter installation
AND NOT EXISTS (
  SELECT 1 FROM [dbo].[MeterEvents]
  WHERE {MeterPointID} = PARENT.{MeterPointID}
    AND {EventType} = 'Installation'
    AND {EventTimestamp} BETWEEN 
        DATEADD(hour, -24, PARENT.{ReadingTimestamp})
        AND PARENT.{ReadingTimestamp}
)

-- Threshold: Green ≥ 98%, Yellow ≥ 95%, Red < 95%

Business Impact: Identifies communication failures, meter faults, or data collection system issues requiring investigation. Gaps trigger estimated reads with associated revenue risk and settlement imbalance charges.

Consumption Pattern Anomaly Detection

-- Rule Name: ConsumptionProfileAnomalyDetection
-- Dimension: Consistency
-- Severity: Medium
-- Description: Detects consumption deviating significantly 
--              from customer's historical profile

-- Calculate customer's typical consumption for this time
DECLARE @HistoricalAvg FLOAT = (
  SELECT AVG({ConsumptionKWH})
  FROM [dbo].[SmartMeterReadings]
  WHERE {MeterPointID} = PARENT.{MeterPointID}
    AND DATEPART(weekday, {ReadingTimestamp}) = 
        DATEPART(weekday, PARENT.{ReadingTimestamp})
    AND DATEPART(hour, {ReadingTimestamp}) = 
        DATEPART(hour, PARENT.{ReadingTimestamp})
    AND {ReadingTimestamp} >= DATEADD(day, -90, GETDATE())
    AND {ReadingTimestamp} < GETDATE()
);

DECLARE @HistoricalStdDev FLOAT = (
  SELECT STDEV({ConsumptionKWH})
  FROM [dbo].[SmartMeterReadings]
  WHERE {MeterPointID} = PARENT.{MeterPointID}
    AND DATEPART(weekday, {ReadingTimestamp}) = 
        DATEPART(weekday, PARENT.{ReadingTimestamp})
    AND DATEPART(hour, {ReadingTimestamp}) = 
        DATEPART(hour, PARENT.{ReadingTimestamp})
    AND {ReadingTimestamp} >= DATEADD(day, -90, GETDATE())
);

-- Current reading within 3 standard deviations of historical
{ConsumptionKWH} >= @HistoricalAvg - (3 * @HistoricalStdDev)
AND {ConsumptionKWH} <= @HistoricalAvg + (3 * @HistoricalStdDev)

-- Threshold: Green ≥ 90%, Yellow ≥ 85%, Red < 85%
-- Note: Lower thresholds acceptable as legitimate usage 
--       changes (new appliances, lifestyle changes) trigger flags

Business Impact: Identifies meter tampering, theft, meter faults, or unusual consumption requiring investigation. Supports proactive customer contact before billing surprise and complaint escalation.

Supply Point Reference Integrity

-- Rule Name: MPANReferenceValidation
-- Dimension: Consistency
-- Severity: Critical
-- Description: Validates Meter Point Administration Number 
--              (MPAN) references exist and are correctly structured

-- MPAN format validation (13 digits)
{MPAN} IS NOT NULL
AND LEN({MPAN}) = 13
AND {MPAN} LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

-- Check digit validation (industry standard algorithm)
AND CAST(RIGHT({MPAN}, 1) AS INT) = (
  (CAST(SUBSTRING({MPAN}, 1, 1) AS INT) * 3) +
  (CAST(SUBSTRING({MPAN}, 2, 1) AS INT) * 5) +
  (CAST(SUBSTRING({MPAN}, 3, 1) AS INT) * 7) +
  (CAST(SUBSTRING({MPAN}, 4, 1) AS INT) * 9) +
  (CAST(SUBSTRING({MPAN}, 5, 1) AS INT) * 1) +
  (CAST(SUBSTRING({MPAN}, 6, 1) AS INT) * 3) +
  (CAST(SUBSTRING({MPAN}, 7, 1) AS INT) * 5) +
  (CAST(SUBSTRING({MPAN}, 8, 1) AS INT) * 7) +
  (CAST(SUBSTRING({MPAN}, 9, 1) AS INT) * 9) +
  (CAST(SUBSTRING({MPAN}, 10, 1) AS INT) * 1) +
  (CAST(SUBSTRING({MPAN}, 11, 1) AS INT) * 3) +
  (CAST(SUBSTRING({MPAN}, 12, 1) AS INT) * 5)
) % 10

-- Verify MPAN exists in industry registration database
AND {MPAN} IN (
  SELECT {MPAN} 
  FROM [dbo].[MPANRegister]
  WHERE {Status} = 'Active'
)

-- Threshold: Green = 100%, Yellow ≥ 99.5%, Red < 99.5%

Utilities Data Landscape

• Meter Read Data: Manual reads from field operatives, AMR (Automated Meter Reading) systems, and customer self-reads
• Network Monitoring: Pressure sensors, flow meters, and SCADA systems monitoring distribution networks
• Asset Registers: Meter inventories, pipe networks, valve locations, and maintenance schedules
• Customer Accounts: Property addresses, supply point references (SPID), tariff assignments
• Work Orders: Meter exchanges, leak repairs, connection activities

Regulatory Quality Requirements

UK water companies must achieve minimum performance commitments for meter reading success rates (target: 98% valid reads annually), billing accuracy (maximum 2% error rate), and void property identification. Ofwat applies financial penalties for quality failures impacting customer experience. Gas distribution networks face similar Ofgem requirements for consumption data accuracy supporting network charging and carbon reporting.

Read Progression Validation

-- Rule Name: MeterReadProgressionLogic
-- Dimension: Consistency
-- Severity: High
-- Description: Validates meter reads show forward 
--              progression accounting for meter rollovers

-- Current read >= previous read (normal progression)
-- OR read has rolled over (current + register capacity >= previous)
(
  {CurrentRead} >= (
    SELECT TOP 1 {ReadValue}
    FROM [dbo].[MeterReadings] prev
    WHERE prev.{MeterSerialNumber} = PARENT.{MeterSerialNumber}
      AND prev.{ReadDate} < PARENT.{ReadDate}
    ORDER BY prev.{ReadDate} DESC
  )
)
OR
(
  -- Meter rollover scenario (6-digit meter: 999999 -> 000001)
  {CurrentRead} + {MeterRegisterCapacity} >= (
    SELECT TOP 1 {ReadValue}
    FROM [dbo].[MeterReadings] prev
    WHERE prev.{MeterSerialNumber} = PARENT.{MeterSerialNumber}
      AND prev.{ReadDate} < PARENT.{ReadDate}
    ORDER BY prev.{ReadDate} DESC
  )
  AND {CurrentRead} < 100000  -- Low reading after rollover
)

-- Threshold: Green ≥ 97%, Yellow ≥ 94%, Red < 94%

Consumption Reasonability Check

-- Rule Name: WaterConsumptionReasonability
-- Dimension: Validity
-- Severity: Medium
-- Description: Validates calculated consumption falls 
--              within expected ranges for property type

-- Calculate daily average consumption
DECLARE @DaysInPeriod INT = DATEDIFF(
  day,
  (SELECT TOP 1 {ReadDate} FROM [dbo].[MeterReadings] prev
   WHERE prev.{MeterSerialNumber} = PARENT.{MeterSerialNumber}
     AND prev.{ReadDate} < PARENT.{ReadDate}
   ORDER BY prev.{ReadDate} DESC),
  {ReadDate}
);

DECLARE @DailyConsumption FLOAT = 
  {CalculatedConsumption} / NULLIF(@DaysInPeriod, 0);

-- Apply property-type specific thresholds (litres/day)
CASE
  WHEN {PropertyType} = 'Residential'
  THEN @DailyConsumption >= 0 
       AND @DailyConsumption <= 1000
  WHEN {PropertyType} = 'Commercial'
  THEN @DailyConsumption >= 0 
       AND @DailyConsumption <= 5000
  WHEN {PropertyType} = 'Industrial'
  THEN @DailyConsumption >= 0 
       AND @DailyConsumption <= 50000
  ELSE TRUE
END

-- Threshold: Green ≥ 93%, Yellow ≥ 88%, Red < 88%

Read Method and Source Validation

-- Rule Name: ReadMethodValidation
-- Dimension: Completeness
-- Severity: Medium
-- Description: Ensures read method recorded and 
--              appropriate for meter type

-- Read method must be populated
{ReadMethod} IS NOT NULL
AND {ReadMethod} IN (
  'Actual', 'Customer', 'Estimated', 
  'AMR', 'Smart', 'InspectionRead'
)

-- Smart meters must have AMR/Smart read method
AND (
  {MeterType} != 'Smart' 
  OR {ReadMethod} IN ('AMR', 'Smart')
)

-- Manual meters cannot have smart read methods
AND (
  {MeterType} != 'Manual'
  OR {ReadMethod} IN ('Actual', 'Customer', 'Estimated', 'InspectionRead')
)

-- Customer reads require validation flag
AND (
  {ReadMethod} != 'Customer'
  OR {ValidationStatus} IN ('Accepted', 'Pending', 'Rejected')
)

-- Threshold: Green ≥ 99%, Yellow ≥ 96%, Red < 96%

Zero Consumption Investigation

-- Rule Name: ZeroConsumptionFlags
-- Dimension: Validity
-- Severity: Low
-- Description: Flags suspicious zero consumption 
--              for occupied properties

-- Flag zero consumption if property occupied
NOT (
  {CalculatedConsumption} = 0
  AND {PropertyOccupancyStatus} = 'Occupied'
  AND NOT EXISTS (
    SELECT 1 FROM [dbo].[WorkOrders]
    WHERE {MeterSerialNumber} = PARENT.{MeterSerialNumber}
      AND {WorkType} IN ('MeterExchange', 'MeterRemoval', 'SupplyDisconnection')
      AND {CompletionDate} BETWEEN 
          DATEADD(day, -90, PARENT.{ReadDate})
          AND PARENT.{ReadDate}
  )
)

-- Threshold: Green ≥ 85%, Yellow ≥ 75%, Red < 75%
-- Note: Lower threshold acceptable as legitimate zero 
--       consumption occurs (holidays, seasonal properties)

Claim Temporal Consistency Validation

-- Rule Name: ClaimDateLogicalSequence
-- Dimension: Consistency
-- Severity: Critical
-- Description: Validates chronological integrity of 
--              claim event dates

-- Incident date must precede notification date
{IncidentDate} IS NOT NULL
AND {NotificationDate} IS NOT NULL
AND {IncidentDate} <= {NotificationDate}

-- Notification within reasonable timeframe (90 days typical)
AND DATEDIFF(day, {IncidentDate}, {NotificationDate}) <= 90

-- Incident cannot be future-dated
AND {IncidentDate} <= GETDATE()

-- Policy must be active at incident date
AND {IncidentDate} >= {PolicyStartDate}
AND ({PolicyEndDate} IS NULL 
     OR {IncidentDate} <= {PolicyEndDate})

-- Settlement date (if present) must follow assessment
AND ({SettlementDate} IS NULL
     OR {SettlementDate} >= {NotificationDate})

-- Threshold: Green = 100%, Yellow ≥ 99%, Red < 99%

Impact: Temporal inconsistencies indicate data entry errors, fraudulent backdating, or system integration failures. Critical for accurate loss ratios and reserve calculations.

Claim Amount Reasonability by Peril Type

-- Rule Name: PerilSpecificAmountValidation
-- Dimension: Validity
-- Severity: High
-- Description: Validates claim amounts align with 
--              typical ranges for specific peril types

CASE
  -- Motor claims (vehicle damage)
  WHEN {PerilCode} IN ('MOT_COLLISION', 'MOT_THEFT', 'MOT_FIRE')
  THEN {EstimatedLoss} >= 100 
       AND {EstimatedLoss} <= 100000
       AND ({EstimatedLoss} <= {VehicleValue} * 1.2)
  
  -- Property claims (building damage)
  WHEN {PerilCode} IN ('PROP_FIRE', 'PROP_FLOOD', 'PROP_STORM')
  THEN {EstimatedLoss} >= 500
       AND {EstimatedLoss} <= 1000000
       AND ({EstimatedLoss} <= {SumInsured} * 1.1)
  
  -- Contents claims
  WHEN {PerilCode} IN ('CONT_THEFT', 'CONT_DAMAGE')
  THEN {EstimatedLoss} >= 50
       AND {EstimatedLoss} <= 100000
       AND ({EstimatedLoss} <= {ContentsSumInsured} * 1.1)
  
  -- Personal injury claims
  WHEN {PerilCode} IN ('PI_INJURY', 'PI_MEDICAL')
  THEN {EstimatedLoss} >= 100
       AND {EstimatedLoss} <= 5000000
  
  ELSE {EstimatedLoss} >= 0 
       AND {EstimatedLoss} <= 10000000
END

-- Reserved amount should align with estimate
AND ({ReservedAmount} IS NULL
     OR ABS({ReservedAmount} - {EstimatedLoss}) / 
        NULLIF({EstimatedLoss}, 0) <= 0.25)

-- Threshold: Green ≥ 96%, Yellow ≥ 92%, Red < 92%

Impact: Identifies claims requiring immediate review for potential fraud, assessment errors, or data entry mistakes before payment authorisation.

Policy Coverage Alignment Validation

-- Rule Name: ClaimCoverageEligibility
-- Dimension: Consistency
-- Severity: Critical
-- Description: Validates claimed peril covered under 
--              active policy at time of incident

-- Policy reference must exist and be active
{PolicyNumber} IN (
  SELECT {PolicyNumber}
  FROM [dbo].[Policies]
  WHERE {Status} = 'Active'
    OR ({Status} = 'Expired' 
        AND {ExpiryDate} >= PARENT.{IncidentDate})
)

-- Claimed peril must be covered by policy
AND EXISTS (
  SELECT 1 
  FROM [dbo].[PolicyCoverages] pc
  WHERE pc.{PolicyNumber} = PARENT.{PolicyNumber}
    AND pc.{PerilCode} = PARENT.{PerilCode}
    AND pc.{EffectiveDate} <= PARENT.{IncidentDate}
    AND (pc.{EndDate} IS NULL 
         OR pc.{EndDate} >= PARENT.{IncidentDate})
)

-- No relevant exclusions apply
AND NOT EXISTS (
  SELECT 1 
  FROM [dbo].[PolicyExclusions] pe
  WHERE pe.{PolicyNumber} = PARENT.{PolicyNumber}
    AND pe.{ExclusionType} = PARENT.{PerilCode}
    AND pe.{EffectiveDate} <= PARENT.{IncidentDate}
)

-- Claim amount within policy limits
AND {EstimatedLoss} <= (
  SELECT {CoverageLimit}
  FROM [dbo].[PolicyCoverages]
  WHERE {PolicyNumber} = PARENT.{PolicyNumber}
    AND {PerilCode} = PARENT.{PerilCode}
)

-- Threshold: Green = 100%, Yellow ≥ 99.5%, Red < 99.5%

Impact: Prevents processing of ineligible claims, reducing unnecessary assessment costs and potential repudiation disputes. Critical for accurate claims handling expenses.

Claimant Information Completeness

-- Rule Name: ClaimantDataCompleteness
-- Dimension: Completeness
-- Severity: High
-- Description: Ensures sufficient claimant information 
--              for processing and regulatory reporting

-- Core claimant details present
{ClaimantName} IS NOT NULL
AND LEN(TRIM({ClaimantName})) >= 3
AND {ClaimantDateOfBirth} IS NOT NULL
AND {ClaimantAddress} IS NOT NULL
AND {ClaimantPostcode} IS NOT NULL

-- Contact method available
AND ({ClaimantPhone} IS NOT NULL 
     OR {ClaimantEmail} IS NOT NULL)

-- Date of birth reasonable (age 16-120)
AND DATEDIFF(year, {ClaimantDateOfBirth}, GETDATE()) >= 16
AND DATEDIFF(year, {ClaimantDateOfBirth}, GETDATE()) <= 120

-- Bank details present if payment required
AND ({ClaimStatus} != 'Approved'
     OR ({BankAccountNumber} IS NOT NULL
         AND {BankSortCode} IS NOT NULL
         AND {AccountHolderName} IS NOT NULL))

-- AML checks documented for high-value claims
AND ({EstimatedLoss} < 10000
     OR {AMLCheckStatus} IN ('Passed', 'Pending'))

-- Threshold: Green ≥ 98%, Yellow ≥ 95%, Red < 95%

Impact: Incomplete claimant data causes payment delays, regulatory reporting failures, and anti-money laundering compliance risks. Flags records requiring data enrichment before processing.

Anomaly Scoring Integration

Combine Purview quality scores with fraud detection algorithms: claims failing multiple validation rules receive elevated fraud scores for investigator review. Example composite scoring: temporal inconsistencies (+15 points), amount outliers (+20 points), duplicate claimant patterns (+25 points), policy coverage mismatches (+30 points). Scores above 40 trigger mandatory Special Investigation Unit (SIU) referral before settlement authorisation.

Cross-Claim Pattern Analysis

-- Detect multiple claims from same claimant
COUNT(*) OVER (
  PARTITION BY {ClaimantName}, {ClaimantDateOfBirth}
  ORDER BY {NotificationDate}
  ROWS BETWEEN UNBOUNDED PRECEDING 
           AND CURRENT ROW
) <= 3  -- Max 3 claims in 365 days

-- Identify workshop/repairer patterns
AND {PreferredRepairer} NOT IN (
  SELECT {RepairerID}
  FROM [dbo].[HighRiskRepairers]
)

Critical Data Domains

• Student Demographics: Personal details, contact information, nationality, disability declarations
• Programme Enrolments: Course selections, mode of study, attendance patterns, progression status
• Academic Records: Module registrations, assessment grades, credit accumulation, degree classifications
• Financial Accounts: Fee assessments, payment records, bursary awards, student loan notifications
• Immigration Compliance: Visa details, attendance monitoring, engagement reporting for international students

Regulatory Consequences

Universities face serious sanctions for data quality failures: HESA data quality flags delay funding allocations; OfS access and participation reporting errors jeopardise regulatory compliance; UKVI sponsor licence revocations for immigration monitoring failures cost institutions their ability to recruit international students (40% of revenue for some Russell Group universities). A single immigration reporting error can trigger institutional audits investigating thousands of student records.

Student Identity and Demographics Validation

-- Rule Name: StudentCoreIdentityCompleteness
-- Dimension: Completeness
-- Severity: Critical
-- Description: Validates essential student identity 
--              data for HESA and OfS reporting

-- Core identity fields present
{StudentID} IS NOT NULL
AND LEN({StudentID}) = 8  -- Institutional standard
AND {Surname} IS NOT NULL
AND LEN(TRIM({Surname})) >= 2
AND {FirstName} IS NOT NULL
AND LEN(TRIM({FirstName})) >= 2
AND {DateOfBirth} IS NOT NULL

-- Date of birth realistic (age 16-100 at enrolment)
AND DATEDIFF(year, {DateOfBirth}, {EnrolmentDate}) >= 16
AND DATEDIFF(year, {DateOfBirth}, {EnrolmentDate}) <= 100

-- Contact details present
AND {EmailAddress} IS NOT NULL
AND {EmailAddress} LIKE '%@%'
AND ({MobilePhone} IS NOT NULL 
     OR {HomePhone} IS NOT NULL)
AND {TermTimeAddress} IS NOT NULL
AND {TermTimePostcode} IS NOT NULL

-- Nationality and residency for fee assessment
AND {Nationality} IS NOT NULL
AND {Nationality} IN (
  SELECT {CountryCode} FROM [dbo].[CountryCodes]
)
AND {FeeStatus} IN ('Home', 'Overseas', 'Islands')
AND {ResidencyCategory} IS NOT NULL

-- Threshold: Green = 100%, Yellow ≥ 99.5%, Red < 99.5%

Impact: Incomplete student records prevent HESA submission, delay student loan notifications, and block degree certificate production. Critical foundation for all student administration processes.

Programme Enrolment Integrity Validation

-- Rule Name: EnrolmentDataIntegrity
-- Dimension: Consistency
-- Severity: High
-- Description: Validates programme enrolment data 
--              consistency and business rule compliance

-- Programme reference valid and active
{ProgrammeCode} IN (
  SELECT {ProgrammeCode}
  FROM [dbo].[Programmes]
  WHERE {Status} = 'Active'
    AND {AcademicYear} = PARENT.{AcademicYear}
)

-- Enrolment status appropriate
AND {EnrolmentStatus} IN (
  'Enrolled', 'Suspended', 'Withdrawn', 
  'Completed', 'Intermitted'
)

-- Mode of study consistent with programme
AND {ModeOfStudy} IN (
  SELECT {AllowedMode}
  FROM [dbo].[ProgrammeDeliveryModes]
  WHERE {ProgrammeCode} = PARENT.{ProgrammeCode}
)

-- Study intensity within valid ranges
AND {StudyIntensity} >= 0
AND {StudyIntensity} <= 100
AND ({ModeOfStudy} != 'Full-time' 
     OR {StudyIntensity} >= 75)
AND ({ModeOfStudy} != 'Part-time' 
     OR {StudyIntensity} < 75)

-- Academic year format validation (e.g., '2023/24')
AND {AcademicYear} LIKE '[0-9][0-9][0-9][0-9]/[0-9][0-9]'
AND CAST(LEFT({AcademicYear}, 4) AS INT) + 1 = 
    2000 + CAST(RIGHT({AcademicYear}, 2) AS INT)

-- Expected end date logical given start and duration
AND {ExpectedEndDate} >= {EnrolmentDate}
AND DATEDIFF(month, {EnrolmentDate}, {ExpectedEndDate}) 
    BETWEEN {ProgrammeDurationMin} AND {ProgrammeDurationMax}

-- Threshold: Green ≥ 99%, Yellow ≥ 97%, Red < 97%

Impact: Enrolment inconsistencies corrupt timetabling, resource planning, and student loan entitlement calculations. Errors propagate to HESA return causing institutional data quality flags.

Academic Assessment and Progression Rules

-- Rule Name: AssessmentDataValidation
-- Dimension: Validity
-- Severity: High
-- Description: Validates assessment marks and academic 
--              progression data integrity

-- Mark within valid range (0-100 scale or Pass/Fail)
({MarkScheme} = 'Percentage'
 AND {Mark} IS NOT NULL
 AND {Mark} >= 0
 AND {Mark} <= 100)
OR
({MarkScheme} = 'PassFail'
 AND {Grade} IN ('Pass', 'Fail', 'Deferred'))

-- Credit value matches module definition
AND {CreditsAwarded} = (
  SELECT {CreditValue}
  FROM [dbo].[Modules]
  WHERE {ModuleCode} = PARENT.{ModuleCode}
)

-- Pass/fail consistent with mark
AND ({Mark} IS NULL 
     OR {MarkScheme} != 'Percentage'
     OR (({Mark} >= 40 AND {Result} = 'Pass')
         OR ({Mark} < 40 AND {Result} = 'Fail')))

-- Assessment board date after module end
AND {AssessmentBoardDate} IS NOT NULL
AND {AssessmentBoardDate} >= {ModuleEndDate}
AND {AssessmentBoardDate} <= GETDATE()

-- Reassessment flag consistent with result
AND ({Result} != 'Fail' 
     OR {ReassessmentRequired} IN ('Yes', 'No'))

-- Cumulative credits within programme limits
AND (
  SELECT SUM({CreditsAwarded})
  FROM [dbo].[StudentAssessments]
  WHERE {StudentID} = PARENT.{StudentID}
    AND {AcademicYear} <= PARENT.{AcademicYear}
    AND {Result} = 'Pass'
) <= {ProgrammeTotalCredits} * 1.2

-- Threshold: Green ≥ 98%, Yellow ≥ 95%, Red < 95%

Impact: Assessment data errors corrupt degree classification calculations, transcript accuracy, and professional body accreditation evidence. Critical for institutional reputation and student employment prospects.

Immigration Compliance Monitoring

-- Rule Name: Tier4VisaSponsorshipCompliance
-- Dimension: Consistency
-- Severity: Critical
-- Description: Validates international student data 
--              meets UKVI reporting requirements

-- International students must have visa details
({FeeStatus} != 'Overseas'
 OR (
   {VisaType} IS NOT NULL
   AND {VisaNumber} IS NOT NULL
   AND {VisaExpiryDate} IS NOT NULL
   AND {VisaExpiryDate} > GETDATE()
 ))

-- Visa type appropriate for study level
AND ({VisaType} != 'Student'
     OR {StudyLevel} IN ('UG', 'PGT', 'PGR'))

-- Right to study dates cover enrolment period
AND ({FeeStatus} != 'Overseas'
     OR {VisaExpiryDate} >= {ExpectedEndDate})

-- Attendance monitoring present for visa holders
AND ({VisaType} IS NULL
     OR EXISTS (
       SELECT 1 FROM [dbo].[AttendanceRecords]
       WHERE {StudentID} = PARENT.{StudentID}
         AND {RecordDate} >= DATEADD(day, -30, GETDATE())
     ))

-- Engagement status recorded (10-point scale)
AND ({VisaType} IS NULL
     OR {EngagementScore} IS NOT NULL
     AND {EngagementScore} BETWEEN 0 AND 10)

-- Contact details current (updated within 90 days)
AND ({FeeStatus} != 'Overseas'
     OR {ContactDetailsLastVerified} >= 
        DATEADD(day, -90, GETDATE()))

-- Threshold: Green = 100%, Yellow ≥ 99%, Red < 99%

Impact: UKVI compliance failures risk sponsor licence suspension or revocation, preventing international student recruitment. Regular quality monitoring demonstrates institutional compliance culture and prevents regulatory intervention.

Month 1: Foundation

Implement core identity and enrolment validation rules. Establish baseline quality metrics across student records system. Configure daily scans for newly registered students and recent enrolment changes.

Month 2: Assessment Data

Deploy academic assessment validation rules. Integrate quality checks into examination board workflows, preventing progression decisions on incomplete data. Train faculty on quality report interpretation.

Month 3: Compliance Monitoring

Activate immigration and financial data validation rules. Configure automated alerts for visa expiry, missing attendance records, and fee assessment errors. Establish remediation SLAs with registry teams.

Month 4: HESA Preparation

Implement HESA-specific validation rules mirroring statutory return edit checks. Execute pre-submission quality sweeps identifying records requiring correction before annual data collection deadline.

Frequency-Based Scheduling

Continuous (Hourly): Critical operational data requiring immediate quality feedback—smart meter readings, transaction processing, IoT sensor streams. Use incremental scans covering recent data only (last hour) to minimise overhead whilst maintaining rapid detection capabilities.

Daily: Master data and transactional systems with overnight ETL processes. Schedule scans to execute post-ETL completion (e.g., ​0200-0400​) ensuring validation occurs against complete daily loads before business users access data.

Weekly: Large historical datasets, infrequently changing reference data, or computationally expensive validation rules. Execute during weekend maintenance windows when system utilisation is lowest and extended scan durations are acceptable.

Monthly/Quarterly: Regulatory reporting datasets, archival systems, or data requiring extensive sampling rather than comprehensive validation. Align with reporting cycle deadlines (e.g., HESA submission preparation, quarterly financial closes).

Event-Driven Scheduling

Post-ETL Triggers: Configure scans to initiate automatically following Azure Data Factory pipeline completions, using pipeline success/failure events as scan triggers. Ensures quality validation occurs immediately after data loads when remediation is most actionable.

Data Change Detection: Implement change data capture (CDC) tracking triggering scans when monitored tables receive modifications. Particularly valuable for master data tables where quality checks should execute only when records are actually updated rather than on fixed schedules.

Business Process Integration: Trigger scans from operational workflows—e.g., execute claims data validation when claim status changes to 'Submitted for Approval', validate student records upon enrolment confirmation, check meter reads when billing cycle initiates.

Incremental vs Full Scan Strategies

Incremental Scans: Validate only recently modified records identified through timestamp columns (WHERE ModifiedDate >= DATEADD(hour, -1, GETDATE())) or CDC mechanisms. Dramatically reduce scan duration and resource consumption for large tables with low change rates. Configure through scan scope filters in Purview Studio.

Full Scans: Validate entire table contents detecting quality degradation in historical records or cross-record pattern anomalies requiring complete dataset visibility. Schedule less frequently (weekly/monthly) and during low-activity periods. Essential for duplicate detection and statistical validation requiring complete population analysis.

Hybrid Approach: Combine frequent incremental scans (daily for new/modified records) with periodic full scans (monthly for comprehensive assessment). Balances timely detection with thorough quality oversight whilst managing computational costs.

Resource Allocation and Performance

Integration Runtime Sizing: Allocate appropriate compute resources based on scan complexity and duration requirements. Azure IR auto-scales but consider dedicated SHIR nodes for intensive scanning workloads on large on-premises databases.

Parallel Execution: Purview supports concurrent table scanning within a scan run. Configure parallelism degree based on source database capacity—avoid overwhelming transactional systems with excessive concurrent queries during business hours.

Partition-Level Scanning: For partitioned tables (date ranges, geographic regions), configure partition-aware scans validating recent partitions incrementally whilst scanning older partitions comprehensively on extended schedules. Reduces scan duration whilst maintaining coverage.

Alert Configuration

Navigate to Data Map → Sources → [Your Source] → Scans → [Scan Name] → Alerts. Configure notification rules based on:

• Quality Score Thresholds: Alert when overall quality score drops below defined threshold (e.g., <90%)
• Rule Failure Rates: Notify when specific critical rules exceed acceptable failure rates
• Scan Execution Failures: Immediate notification of scan completion failures, timeout errors, or connectivity issues
• Trend Degradation: Alert on quality score declines exceeding specified percentage over defined periods (e.g., >5% drop week-over-week)

Notification Channels

Purview supports multiple alert delivery mechanisms:

• Email Notifications: Direct emails to data stewards, quality managers, or operational teams with scan result summaries and failure details
• Azure Monitor Integration: Push quality metrics to Azure Monitor for integration with enterprise monitoring dashboards and ITSM ticketing systems
• Logic Apps Workflows: Trigger automated remediation workflows, ServiceNow ticket creation, or Microsoft Teams channel notifications via Azure Logic Apps connectors
• API Webhooks: POST scan results to custom endpoints for integration with proprietary monitoring platforms or data quality dashboards

Historical Baseline Approach

Establish thresholds based on current data quality state measured through initial assessment scans. Calculate baseline pass rates for each rule across representative data samples (e.g., last 30 days of data). Set initial thresholds slightly below baseline (e.g., baseline 94% → threshold 92%) allowing operational headroom whilst flagging significant degradation. Appropriate for mature data environments with established quality levels requiring maintenance rather than transformation.

Example: If customer email completeness currently achieves 96%, set Green ≥95%, Yellow ≥92%, Red <92%. Alerts trigger only when quality deteriorates beyond normal operational variation.

Target-Based Approach

Define thresholds based on business requirements, regulatory mandates, or industry best practices regardless of current state. Establishes aspirational quality targets driving improvement initiatives. Appropriate for transformation programmes, new system implementations, or addressing known quality deficits.

Example: Regulatory requirement for 98% billing accuracy sets threshold Green ≥98%, Yellow ≥96%, Red <96% even if current accuracy is 92%. Gap drives focused remediation efforts and investment justification.

Risk-Weighted Approach

Vary threshold strictness based on data criticality and downstream impact. Apply stringent thresholds (≥99%) to critical data elements affecting revenue, safety, or compliance. Use permissive thresholds (≥85%) for non-critical optional attributes with limited business impact. Focuses remediation resources on highest-value quality improvements.

Example: Customer billing address (critical for payment processing): Green ≥99%, vs. marketing preferences (optional attribute): Green ≥85%.

Progressive Tightening Approach

Implement gradually increasing threshold stringency as organisational quality maturity improves. Begin with achievable thresholds ensuring early wins and stakeholder confidence, then progressively tighten every 3-6 months as remediation processes mature. Supports cultural change management and sustainable improvement.

Example: Quarter 1: Green ≥85%, Quarter 2: Green ≥88%, Quarter 3: Green ≥92%, Quarter 4: Green ≥95%. Incremental progress with consistent improvement trajectory.

Role-Based Alert Distribution

• Data Stewards: All alerts for owned data domains, detailed failure records, remediation assignment notifications
• System Administrators: Scan execution failures, connectivity issues, performance degradation alerts
• Business Process Owners: Quality failures impacting specific workflows (billing, claims processing, enrolment)
• Data Governance Lead: Executive summaries, trend analysis, threshold breach escalations
• Operations Teams: Real-time critical alerts requiring immediate intervention (meter reading failures, transaction validation errors)

Escalation Workflows

# Example: Tiered alert escalation
Initial Alert (Tier 2):
  → Data Steward notification
  → 24-hour response SLA

No Resolution After 24h:
  → Escalate to Data Domain Lead
  → 48-hour resolution SLA

No Resolution After 48h:
  → Escalate to Data Governance Board
  → Executive visibility
  → 72-hour resolution SLA

Critical Alert (Tier 3):
  → Immediate Operations Team page
  → Concurrent Data Governance Lead notification
  → 4-hour response SLA
  → Hourly status updates until resolution

Overall Quality Score

Aggregate percentage of records passing all configured rules within a scan. Calculation: (Total Passing Records / Total Scanned Records) × 100. Provides headline metric for executive reporting and trend tracking. Interpretation Guidance: Scores ≥95% indicate healthy data; 90-94% suggests targeted remediation needed; <90% signals systemic quality issues requiring comprehensive intervention. Monitor score trends—gradual decline indicates process drift or system degradation requiring root cause analysis.

Rule Pass Rate

Percentage of records passing each individual rule. Reveals specific quality dimensions requiring attention. Interpretation Guidance: Rules with <80% pass rates indicate either overly strict thresholds requiring adjustment or fundamental data collection problems. Rules showing sudden pass rate drops signal process changes, system failures, or new data sources requiring investigation. Compare pass rates across similar rules to identify pattern anomalies.

Failure Volume and Distribution

Absolute count of failing records and distribution across rules. Distinguishes between widespread issues (many rules failing) vs. isolated problems (single rule failures). Interpretation Guidance: High failure volumes with concentrated distribution (80% failures on 1-2 rules) suggest specific fixable issues. High volumes with dispersed distribution (failures across many rules) indicate fundamental data collection or integration problems requiring architectural remediation rather than tactical fixes.

Temporal Trends and Velocity

Quality score changes over time revealing improvement trajectories or degradation patterns. Interpretation Guidance: Positive trends (improving scores) validate remediation effectiveness and should be communicated to stakeholders reinforcing investment value. Negative trends require urgency assessment—gradual decline (1-2% monthly) suggests process drift requiring attention; sudden drops (>5% week-over-week) indicate incidents demanding immediate investigation and resolution.

Executive Quality Dashboard

Purpose: Provide senior leadership with strategic quality oversight and investment justification evidence.

Key Components: Overall quality scores by business domain, trend charts showing improvement trajectories, cost of quality estimates (estimated revenue impact of quality issues), comparison to industry benchmarks or regulatory requirements, remediation project status summaries.

Frequency: Monthly or quarterly governance board reporting. Focus on business outcomes rather than technical metrics—translate quality scores into customer satisfaction impacts, revenue assurance, compliance risk mitigation.

Operational Quality Reports

Purpose: Support day-to-day data stewardship activities and tactical remediation prioritisation.

Key Components: Rule-level pass/fail breakdowns, detailed failure record lists with drill-down capabilities, historical comparison showing week-over-week changes, remediation action tracking, data profiling statistics identifying common failure patterns.

Frequency: Daily or weekly distribution to data stewards and domain owners. Include actionable intelligence—specific records requiring correction, suggested remediation approaches based on failure patterns, integration with work management systems for task assignment.

Regulatory Compliance Reports

Purpose: Demonstrate data quality governance to regulators and auditors as evidence of systematic control frameworks.

Key Components: Quality scores for regulatory-relevant data elements (e.g., HESA fields, Solvency II data items, GDPR subject rights data), audit trails showing quality monitoring cadence and coverage, documented threshold rationale and risk assessments, remediation response times and effectiveness metrics, control testing evidence for critical quality rules.

Frequency: Quarterly or annually aligned with regulatory reporting cycles. Maintain archived copies as compliance evidence supporting regulatory submissions or audit requests.

Data Product Quality Scorecards

Purpose: Assess fitness-for-purpose of specific data products (datasets, APIs, analytical models) for consumer decision-making.

Key Components: Quality dimensions scoring (completeness, accuracy, timeliness, consistency, validity), data product SLA compliance (against contractual commitments), consumer satisfaction ratings, quality-impacted use case identification, improvement roadmaps.

Frequency: Monthly review with data product managers and consumers. Frame quality as enabling capability—high quality enables advanced analytics, automation, and strategic initiatives; poor quality constrains organisational data-driven maturity.

Common Failure Patterns

• Temporal Clustering: Failures concentrated in specific time periods suggest batch process failures, system outages, or seasonal data collection issues
• Source System Correlation: Failures predominantly from single source systems indicate integration problems, data extraction issues, or upstream quality degradation
• Geographic Concentration: Failures in specific regions suggest localised process variations, regional system differences, or data collection challenges
• New Record Bias: Recently created records showing higher failure rates indicate data entry issues, incomplete migration processes, or insufficient user training

Diagnostic Questions

• Did quality suddenly degrade or gradually decline? (Incident vs drift)
• Are failures consistent across all rules or isolated? (Systemic vs specific)
• Do failures correlate with any recent changes? (System deployments, process modifications)
• Are specific user groups or departments associated with failures? (Training needs, process compliance)
• Do failures affect all record types or specific segments? (Conditional logic issues, data type problems)

Quantify Business Impact

Translate quality metrics into business terms, for example (illustrative figures): "Email completeness of 92% means 3,200 customers unreachable for critical service notifications" or "Claims data quality score of 88% extends average settlement time by 4.2 days, costing £580K annually in additional handling expenses".

Visualise Trends and Comparisons

Use charts showing improvement trajectories, benchmark comparisons (internal teams, industry standards), and threshold zone indicators (green/yellow/red) for intuitive comprehension. Avoid overwhelming with technical detail—focus on "so what" narrative.

Provide Actionable Recommendations

Every quality report should conclude with prioritised remediation recommendations, resource requirements, expected timelines, and projected business benefit. Enable decision-makers to approve investments with clear value propositions.

Celebrate Improvements

Recognise teams achieving quality improvements, publicise success stories demonstrating governance programme value, and reinforce positive behaviours. Quality culture requires both accountability for failures and recognition for achievements.

Issue: Scan Fails with "Unable to Connect to Data Source"

Symptoms: Scan configuration validates successfully but execution fails immediately with connectivity errors. Appears in scan history as "Failed" with error message referencing connection timeouts or authentication failures.

Common Causes:

• Firewall rules blocking Integration Runtime IP addresses from reaching data source
• Service principal or managed identity lacking read permissions on target database
• Incorrect connection string (wrong server name, database name, or port)
• Self-Hosted Integration Runtime offline or unable to resolve data source DNS
• Private endpoint configuration incomplete or DNS resolution failing

Resolution Steps:

1. Test connectivity using Azure Data Studio or SQL Server Management Studio from Integration Runtime machine using same credentials configured in Purview
2. Verify firewall rules: navigate to data source → Networking → Add Integration Runtime IP ranges to allowlist
3. Validate service principal permissions: grant db_datareader role or specific SELECT permissions on target schemas
4. Check SHIR status: open Integration Runtime Configuration Manager, verify node shows "Running" status with green indicator
5. Review private endpoint DNS: execute nslookup against data source FQDN from IR machine confirming private IP resolution

Issue: Scan Succeeds but Returns Zero Tables

Symptoms: Scan completes with "Success" status but discovers no tables or assets despite database containing multiple objects.

Common Causes:

• Authentication credentials valid but lacking schema-level enumeration permissions
• Scan scope filters excluding all tables (incorrect wildcard patterns or schema selections)
• Data source connection pointing to wrong database within server

Resolution Steps:

1. Grant VIEW DEFINITION permission to service principal: GRANT VIEW DEFINITION TO [service-principal-name]
2. Review scan scope configuration: verify schema filters don't inadvertently exclude target tables
3. Confirm database name in connection string matches intended target
4. Test with simplified scope (single schema, no filters) to isolate configuration issues

Issue: Rule Validation Fails with Syntax Errors

Symptoms: "Validate Syntax" button returns error messages when saving custom rules. Rule expressions using valid SQL syntax locally but failing in Purview context.

Common Causes:

• Using unsupported SQL functions or syntax extensions specific to source database engine
• Column references missing curly brace notation: ColumnName instead of {ColumnName}
• Attempting subqueries without proper PARENT reference context
• Using single quotes incorrectly in string literals within expressions

Resolution Approaches:

• Reference Purview-supported function list (standard SQL:2016 functions generally safe)
• Systematically ensure all column references use {ColumnName} format
• Test complex expressions incrementally—start with simple validation, progressively add conditions
• Use database-specific functions sparingly; prefer portable SQL constructs

Example Syntax Corrections

-- INCORRECT: Missing curly braces
EmailAddress LIKE '%@%'

-- CORRECT: Proper column reference
{EmailAddress} LIKE '%@%'

---

-- INCORRECT: Unsupported function
CHARINDEX('@', EmailAddress) > 0

-- CORRECT: Standard LIKE operator
{EmailAddress} LIKE '%@%'

---

-- INCORRECT: Subquery without context
{Amount} > (SELECT AVG(Amount) 
            FROM Transactions)

-- CORRECT: Correlated subquery
{Amount} > (SELECT AVG({Amount})
            FROM Transactions t
            WHERE t.{Type} = PARENT.{Type})