What does a green load status mean if invalid rows reached the destination? Use data quality asserts to make broken rules stop the load.
Make Data Quality Asserts Executable
A load needs more than a successful INSERT statement. Define the accepted data before touching the destination. Turn each rule into a query that returns violations.
I prefer checks that can run independently during troubleshooting. I also keep their definitions beside the loading procedure. A separate spreadsheet of rules drifts away from executable behavior.
This example checks required keys, duplicate keys, allowed ranges, and missing parent rows. Each violation query must return zero rows. Passing one rule never compensates for failing another.
Use an isolated test database for the complete example. The object names are unique to this demonstration. The script creates persistent evidence tables as well as staging and destination tables.
CREATE TABLE dbo.QualityCustomer
(
CustomerId int NOT NULL PRIMARY KEY
);
CREATE TABLE dbo.QualityStage
(
StageRowId int IDENTITY PRIMARY KEY,
BatchId int NOT NULL,
SourceKey int NULL,
CustomerId int NULL,
Quantity int NULL,
Amount decimal(12,2) NULL
);
CREATE TABLE dbo.QualityTarget
(
SourceKey int NOT NULL PRIMARY KEY,
CustomerId int NOT NULL
REFERENCES dbo.QualityCustomer(CustomerId),
Quantity int NOT NULL CHECK (Quantity BETWEEN 1 AND 1000),
Amount decimal(12,2) NOT NULL CHECK (Amount BETWEEN 0 AND 1000000)
);
CREATE TABLE dbo.QualityRun
(
RunId uniqueidentifier NOT NULL PRIMARY KEY,
BatchId int NOT NULL,
StartedUtc datetime2(3) NOT NULL,
FinishedUtc datetime2(3) NULL,
Status varchar(12) NOT NULL,
ErrorNumber int NULL,
ErrorMessage nvarchar(2048) NULL
);
CREATE TABLE dbo.QualityResult
(
RunId uniqueidentifier NOT NULL
REFERENCES dbo.QualityRun(RunId),
RuleName varchar(30) NOT NULL,
ViolationCount bigint NOT NULL,
PRIMARY KEY (RunId, RuleName)
);
INSERT dbo.QualityCustomer VALUES (1), (2);
INSERT dbo.QualityStage
(BatchId, SourceKey, CustomerId, Quantity, Amount)
VALUES (1, 100, 1, 2, 10),
(1, 100, 99, -1, 20),
(1, NULL, NULL, NULL, NULL),
(2, 200, 1, 3, 30),
(2, 201, 2, 4, 40);
GOFreeze the Input Being Checked
A validation pass followed by reading changed staging rows creates a gap. Seal a batch before calling its loader. The producer must stop editing that batch under an enforced operational contract.
The procedure copies that sealed batch into a temporary table. Both checks and insertion use that same copy. This prevents later staging changes from replacing the validated input.
A temporary copy alone does not establish producer completeness. A producer writing during capture can still create an incomplete batch. Use batch ownership, completion status, and appropriate isolation to close that gap.
Parent data remains independently changeable after validation. Destination foreign keys still protect the actual insertion. Keep destination constraints even when staging checks appear comprehensive.
Record All Data Quality Asserts before Throwing
These data quality asserts record all four outcomes before rejecting a batch. That gives the operator a useful starting point. Fixing one error should not require another run to discover the next.
The duplicate check counts repeated key groups. The other checks count violating rows. Name the field ViolationCount so those different meanings remain honest.
NULL quantities and amounts need explicit conditions. Comparisons alone do not classify NULL as an invalid range. Required source and customer keys receive their own rule.
CREATE OR ALTER PROCEDURE dbo.QualityLoad
@BatchId int,
@RunId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
THROW 50000, 'Run this loader outside an existing transaction.', 1;
INSERT dbo.QualityRun
(RunId, BatchId, StartedUtc, Status)
VALUES (@RunId, @BatchId, SYSUTCDATETIME(), 'Started');
BEGIN TRY
SELECT SourceKey, CustomerId, Quantity, Amount
INTO #Stage
FROM dbo.QualityStage
WHERE BatchId = @BatchId;
INSERT dbo.QualityResult
(RunId, RuleName, ViolationCount)
SELECT @RunId, 'Null keys', COUNT_BIG(*)
FROM #Stage
WHERE SourceKey IS NULL OR CustomerId IS NULL;
INSERT dbo.QualityResult
SELECT @RunId, 'Duplicate keys', COUNT_BIG(*)
FROM
(
SELECT SourceKey
FROM #Stage
WHERE SourceKey IS NOT NULL
GROUP BY SourceKey
HAVING COUNT_BIG(*) > 1
) AS D;
INSERT dbo.QualityResult
SELECT @RunId, 'Invalid ranges', COUNT_BIG(*)
FROM #Stage
WHERE Quantity IS NULL OR Quantity NOT BETWEEN 1 AND 1000
OR Amount IS NULL OR Amount NOT BETWEEN 0 AND 1000000;
INSERT dbo.QualityResult
SELECT @RunId, 'Orphan customers', COUNT_BIG(*)
FROM #Stage AS S
WHERE S.CustomerId IS NOT NULL
AND NOT EXISTS
(
SELECT 1 FROM dbo.QualityCustomer AS C
WHERE C.CustomerId = S.CustomerId
);
IF EXISTS
(
SELECT 1 FROM dbo.QualityResult
WHERE RunId = @RunId AND ViolationCount > 0
)
THROW 50001, 'Staging rules failed. Read the check results.', 1;
IF NOT EXISTS (SELECT 1 FROM #Stage)
THROW 50002, 'This loader requires a nonempty batch.', 1;
BEGIN TRANSACTION;
INSERT dbo.QualityTarget
(SourceKey, CustomerId, Quantity, Amount)
SELECT SourceKey, CustomerId, Quantity, Amount FROM #Stage;
COMMIT TRANSACTION;
UPDATE dbo.QualityRun
SET Status = 'Succeeded', FinishedUtc = SYSUTCDATETIME()
WHERE RunId = @RunId;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
UPDATE dbo.QualityRun
SET Status = 'Failed', FinishedUtc = SYSUTCDATETIME(),
ErrorNumber = ERROR_NUMBER(), ErrorMessage = ERROR_MESSAGE()
WHERE RunId = @RunId;
THROW;
END CATCH;
END;
GO
Keep Failure Evidence Outside the Rollback
The initial run record and check results use autocommit statements. They precede the destination transaction. Rolling back a rejected destination insert therefore preserves those records.
The loader rejects an existing caller transaction deliberately. Otherwise an outer rollback could remove the supposedly persistent evidence. That restriction is part of the procedure contract.
The CATCH block rolls back before updating the run record. An uncommittable transaction cannot write failure details safely. Error handling must restore a usable transaction state first.
Logging and destination commit are separate operations here. A failure after commit can leave destination rows with an incomplete run status. Production reconciliation must compare durable batch identity with destination evidence.
Exercise Both Paths of the Data Quality Asserts
Batch one deliberately contains invalid values. Batch two contains different source keys and valid customers. The caller supplies each run identifier. An OUTPUT parameter doesn't reach the caller when the procedure ends with an error. The first call is meant to fail with error 50001.
DECLARE @RunId uniqueidentifier = NEWID();
BEGIN TRY
EXEC dbo.QualityLoad @BatchId = 1, @RunId = @RunId;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
SELECT * FROM dbo.QualityRun WHERE RunId = @RunId;
SELECT * FROM dbo.QualityResult WHERE RunId = @RunId;
SET @RunId = NEWID();
EXEC dbo.QualityLoad @BatchId = 2, @RunId = @RunId;
SELECT * FROM dbo.QualityRun WHERE RunId = @RunId;
SELECT * FROM dbo.QualityResult WHERE RunId = @RunId;
SELECT * FROM dbo.QualityTarget ORDER BY SourceKey;The second batch is intended for a fresh destination. Repeating its insert encounters existing primary keys. Decide separately whether real batches append, replace, or apply idempotent updates.
The failed run records one null key, one duplicate key group, two invalid ranges, and one orphan customer. The second run succeeds with zero violations for every rule. The destination then holds source keys 200 and 201.
Make the Operational Contract Visible
A Started record does not prove the loader is still running. Connection loss and cancellation can bypass this CATCH block. Reconcile abandoned records using the scheduler and destination batch evidence.
Retain enough details to identify the producer and source version. Protect logged values according to their sensitivity. A log table should not become an unrestricted copy of customer data.
What should an operator do when the same batch fails twice? Provide a correction and resubmission process with a fresh run identifier. Quietly deleting evidence makes the next investigation harder.
I treat data quality asserts as executable acceptance criteria. I review their boundaries whenever the destination contract changes. Garbage does not become valuable because its INSERT finished politely.
Diagnose Failures without Weakening Rules
For a failed batch, rerun the specific violation query against its retained input. Return the source key and staging identifier with the failing fields. Those identifiers let the producer correct the original record rather than guessing from a count.
A count alone does not explain which duplicate record should survive. Define the producer's identity contract before choosing any deduplication strategy. Arbitrarily keeping the first staging row can hide conflicting quantities or customer assignments.
Range boundaries also need business ownership. This demonstration accepts one through one thousand units, including both endpoints. Change those limits only after agreeing on the actual destination meaning and its supporting constraints.
Decimal conversion happens before these checks because staging already contains typed columns. A text landing table needs separate conversion checks with TRY_CONVERT. Preserve the original text when a conversion fails so the source issue remains explainable.
An orphan check should use the intended parent key and relevant effective period. A customer that existed last year does not automatically satisfy today's contract. Add temporal qualification when the load represents time-dependent customer relationships.
Record rule revisions when changing acceptance behavior. Otherwise two runs with the same batch can produce different outcomes without an explanation. A simple rule-version column connects the evidence to the procedure definition used for that attempt.
Alerting should identify the batch, failing rules, and responsible operator. Avoid placing complete sensitive rows into notification text. Link the operational process to controlled database evidence through a permitted internal identifier.
A scheduler should treat the propagated THROW as a failed step. Catching the exception and reporting success defeats the loader's acceptance contract. Confirm that the caller preserves failure status through its own error handling.
An empty input is deliberately rejected by this example. Some real feeds legitimately deliver zero rows on quiet days. Specify that decision explicitly instead of assuming zero violations always means a complete input.
Retention needs a practical boundary too. Keep failed input long enough for diagnosis and source correction. Remove expired evidence through an approved retention process that preserves the required audit trail.
Related reading on this blog: Where Should a Data Quality Check Live? Gates, Controls, and Quarantine and Convert Old Syntax of RAISEERROR to THROW.
A successful load is not a completed INSERT, it is accepted data with traceable checks.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
