Data Quality Checks Worth Running After Every Load

Useful data quality checks tell you whether a load produced data the business can trust. A successful INSERT only proves that SQL Server accepted the operation under the rules currently enforced.

Define What One Row Represents

Start with the grain of the incoming data. One row might represent an order, an order line, or a daily account balance. A duplicate check is meaningless until the expected business key is clear.

Assign a load identifier and retain a source row identifier where possible. These let you connect a failed check to the original input. A message saying invalid data without a row reference is an invitation to repeat the entire investigation.

CREATE TABLE #LoadCheck
(
    SourceRowId int NOT NULL,
    BusinessKey nvarchar(30) NULL,
    Quantity int NULL,
    EventDate date NULL
);
INSERT #LoadCheck VALUES
 (1, N'A100', 3, '20260101'),
 (2, N'A100', 2, '20260102'),
 (3, NULL, -1, NULL);

This temporary table intentionally includes invalid examples. Run the blocks in the same query window. The rows demonstrate checks rather than represent observations from a real load.

Reconcile Counts at a Stable Boundary

Compare the extracted source count with the landed count for the same batch or source snapshot. Counting a changing source later can produce a difference unrelated to the load. Record when and how the source count was established.

SELECT COUNT_BIG(*) AS landed_rows
FROM #LoadCheck;
SELECT COUNT_BIG(*) AS distinct_business_keys
FROM (SELECT BusinessKey FROM #LoadCheck GROUP BY BusinessKey) AS k;

The second result counts groups, including a possible NULL group. It is not automatically the number of valid business entities. Report accepted, rejected, and deliberately excluded rows separately.

Equal totals do not prove equal data. One missing row and one duplicate can cancel in the count. Add key-level reconciliation when the consequence of a mismatch justifies it.

Reject Missing Required Values

Identify which columns are required by the business contract. An optional comment and a missing order identifier deserve different treatment. For text, also decide whether empty strings or whitespace count as missing.

SELECT SourceRowId, BusinessKey, Quantity, EventDate
FROM #LoadCheck
WHERE NULLIF(LTRIM(RTRIM(BusinessKey)), N'') IS NULL
   OR Quantity IS NULL
   OR EventDate IS NULL;

Keep that rule aligned with the target schema and downstream consumers. A nullable staging column can be useful for capturing invalid source data. It should not silently relax a required target rule.

Check Uniqueness and Valid Ranges

Group by the complete business key to find duplicate candidates. Do not arbitrarily keep one row until you know whether the duplicates represent retries or conflicting values. The resolution policy should be explicit.

SELECT BusinessKey, COUNT_BIG(*) AS occurrences
FROM #LoadCheck
WHERE BusinessKey IS NOT NULL
GROUP BY BusinessKey
HAVING COUNT_BIG(*) > 1;
SELECT SourceRowId, Quantity, EventDate
FROM #LoadCheck
WHERE Quantity < 0
   OR EventDate < CONVERT(date, '20000101', 112);

The date floor is an illustrative rule for this example, not a universal data-quality limit. Real ranges come from the source contract and business meaning. A negative quantity could be invalid inventory or a legitimate return.

Check relationships too, such as a customer key that has no approved dimension member. Keep lookup misses visible rather than dropping them through an inner join. A clean-looking target can hide discarded source rows.

Make Failed Validation Stop Promotion

A validation query is useful only if the pipeline reacts to its result. Decide which failures block the batch and which become reported exceptions. Do not mark the batch complete before those decisions are applied.

IF EXISTS
(
    SELECT 1 FROM #LoadCheck
    WHERE NULLIF(LTRIM(RTRIM(BusinessKey)), N'') IS NULL
       OR Quantity IS NULL OR Quantity < 0 OR EventDate IS NULL
)
    THROW 50010, 'Load validation failed. Review the rejected source rows.', 1;

This example deliberately raises an error for the supplied invalid data. A production pipeline should persist its rejection details before losing the working session. Include the load identifier, rule, source row, and an appropriately protected value sample.

Keep the Evidence With the Batch

Store the source count, landed count, accepted count, rejection count, and validation outcome together. Distinguish a pipeline failure from a business-rule rejection. They require different responses and often different owners.

Review the checks when the source contract changes. A rule that was sensible last year can reject legitimate new activity. Good validation is a maintained agreement about acceptable data, not a collection of permanent guesses.

Load validation is not a green execution status, it is evidence that the data meets its contract.

This post was rewritten from scratch in September 2026. The original, published on 2011-06-08, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.