Create the quality-gate procedure

CREATE OR ALTER PROCEDURE dbo.usp_RunSalesQualityGate
    @BatchId uniqueidentifier,
    @MaxAgeMinutes int = 180,
    @AmountTolerance decimal(19,4) = 0.01
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE
        @RunId bigint,
        @ExpectedRows bigint,
        @ActualRows bigint,
        @ExpectedAmount decimal(19,4),
        @ActualAmount decimal(19,4),
        @ExtractedAtUtc datetime2(0),
        @MissingMandatory bigint,
        @DuplicateRows bigint,
        @OrphanCustomers bigint,
        @BlockingFailures int;

    DECLARE @Results TABLE
    (
        RuleCode varchar(50),
        RuleName nvarchar(200),
        Severity varchar(10),
        Passed bit,
        FailedRowCount bigint,
        ObservedValue nvarchar(200)
    );

    BEGIN TRY
        INSERT dbo.QualityRun (BatchId, GateStatus)
        VALUES (@BatchId, 'Running');

        SET @RunId = SCOPE_IDENTITY();

        SELECT
            @ExpectedRows = ExpectedRowCount,
            @ExpectedAmount = ExpectedAmount,
            @ExtractedAtUtc = ExtractedAtUtc
        FROM dbo.SalesBatchControl
        WHERE BatchId = @BatchId;

        IF @ExpectedRows IS NULL
            THROW 51000, 'No trusted control record exists for this batch.', 1;

        SELECT
            @ActualRows = COUNT_BIG(*),
            @ActualAmount = COALESCE(SUM(NetAmount), 0)
        FROM dbo.SalesStage
        WHERE BatchId = @BatchId;

        SELECT @MissingMandatory = COUNT_BIG(*)
        FROM dbo.SalesStage
        WHERE BatchId = @BatchId
          AND (
              NULLIF(LTRIM(RTRIM(InvoiceNumber)), '') IS NULL
              OR CustomerId IS NULL
              OR InvoiceDate IS NULL
              OR NetAmount IS NULL
          );

        SELECT @DuplicateRows = COALESCE(SUM(ExcessRows), 0)
        FROM
        (
            SELECT COUNT_BIG(*) - 1 AS ExcessRows
            FROM dbo.SalesStage
            WHERE BatchId = @BatchId
              AND InvoiceNumber IS NOT NULL
            GROUP BY InvoiceNumber
            HAVING COUNT_BIG(*) > 1
        ) AS Duplicates;

        SELECT @OrphanCustomers = COUNT_BIG(*)
        FROM dbo.SalesStage AS S
        LEFT JOIN dbo.Customer AS C
          ON C.CustomerId = S.CustomerId
        WHERE S.BatchId = @BatchId
          AND S.CustomerId IS NOT NULL
          AND C.CustomerId IS NULL;

        INSERT @Results VALUES
        (
            'FRESHNESS', 'Source extract is within the agreed age', 'Blocking',
            IIF(
                DATEDIFF(minute, @ExtractedAtUtc, SYSUTCDATETIME())
                    BETWEEN 0 AND @MaxAgeMinutes,
                1, 0
            ),
            IIF(
                DATEDIFF(minute, @ExtractedAtUtc, SYSUTCDATETIME())
                    BETWEEN 0 AND @MaxAgeMinutes,
                0, 1
            ),
            CONCAT(DATEDIFF(minute, @ExtractedAtUtc, SYSUTCDATETIME()), ' minutes old')
        ),
        (
            'COMPLETENESS', 'Mandatory sales fields are populated', 'Blocking',
            IIF(@MissingMandatory = 0, 1, 0), @MissingMandatory,
            CONCAT(@MissingMandatory, ' incomplete rows')
        ),
        (
            'UNIQUENESS', 'Invoice number is unique within the batch', 'Blocking',
            IIF(@DuplicateRows = 0, 1, 0), @DuplicateRows,
            CONCAT(@DuplicateRows, ' excess duplicate rows')
        ),
        (
            'CUSTOMER_KEY', 'Every customer exists in the master table', 'Blocking',
            IIF(@OrphanCustomers = 0, 1, 0), @OrphanCustomers,
            CONCAT(@OrphanCustomers, ' orphan customer rows')
        ),
        (
            'CONTROL_TOTAL', 'Row count and amount agree to source controls', 'Blocking',
            IIF(
                @ActualRows = @ExpectedRows
                AND ABS(@ActualAmount - @ExpectedAmount) <= @AmountTolerance,
                1, 0
            ),
            IIF(@ActualRows = @ExpectedRows, 0, ABS(@ActualRows - @ExpectedRows)),
            CONCAT(
                'Rows ', @ActualRows, '/', @ExpectedRows,
                '; amount variance ',
                CONVERT(varchar(50), @ActualAmount - @ExpectedAmount)
            )
        );

        INSERT dbo.QualityResult
            (RunId, RuleCode, RuleName, Severity, Passed, FailedRowCount, ObservedValue)
        SELECT
            @RunId, RuleCode, RuleName, Severity, Passed, FailedRowCount, ObservedValue
        FROM @Results;

        SELECT @BlockingFailures = COUNT(*)
        FROM @Results
        WHERE Severity = 'Blocking'
          AND Passed = 0;

        UPDATE dbo.QualityRun
        SET CompletedAtUtc = SYSUTCDATETIME(),
            GateStatus = IIF(@BlockingFailures = 0, 'Passed', 'Blocked'),
            BlockingFailureCount = @BlockingFailures
        WHERE RunId = @RunId;

        IF @BlockingFailures > 0
            THROW 51001, 'Data quality gate blocked the reporting refresh.', 1;

        SELECT RunId, BatchId, GateStatus, BlockingFailureCount
        FROM dbo.QualityRun
        WHERE RunId = @RunId;
    END TRY
    BEGIN CATCH
        IF @RunId IS NOT NULL
        BEGIN
            UPDATE dbo.QualityRun
            SET CompletedAtUtc = COALESCE(CompletedAtUtc, SYSUTCDATETIME()),
                GateStatus = CASE WHEN GateStatus = 'Blocked' THEN GateStatus ELSE 'Error' END,
                ErrorMessage = ERROR_MESSAGE()
            WHERE RunId = @RunId;
        END;

        THROW;
    END CATCH;
END;