A load can finish on time and still publish the wrong data. Data quality rules make the definition of acceptable rows visible and executable in T-SQL.
Write Data Quality Rules as Questions
Start with data quality rules the business can explain. Are required keys present? Are dates valid? Does each order point to a known customer? Are amounts within an agreed range? Each rule should have a name, severity, owner, and query that identifies violating rows.
I avoid a single Boolean called IsValid when a row can break several rules. Save the rule name and source row identifier for every violation. That gives the source owner a concrete correction list. It also lets the load distinguish a warning from a condition that blocks publication.
Ask the reader of a report what would make its numbers untrustworthy. That answer is usually more useful than a generic catalog of checks. A quality rule exists to protect a decision, not to decorate a pipeline dashboard.
Check Keys and Required Values First
Missing business keys make deduplication and replay unsafe. Find null or blank keys before applying target changes. Check uniqueness at the intended grain, not at the stage row identity. A unique identity value can coexist with duplicate customer IDs.
Use constraints in the final table as the last defense. Stage queries provide better diagnostics because they can list every offending row. A constraint error alone tells you where the transaction stopped, not the full correction set.
I count violations and inspect examples before a new rule becomes a hard gate. The count is measured by the query, not invented in the blog post. The result tells you whether the source has a current problem or whether the rule itself needs a clearer definition.
SELECT SourceRowId, CustomerCode
FROM dbo.StageCustomer
WHERE NULLIF(TRIM(CustomerCode), N'') IS NULL;
SELECT CustomerCode, COUNT(*) AS duplicate_count
FROM dbo.StageCustomer
GROUP BY CustomerCode
HAVING COUNT(*) > 1;Validate Types Before Conversion
TRY_CONVERT is useful when a source sends dates and numbers as text. A failed conversion returns NULL instead of aborting the whole query. But a source NULL and an invalid value also need different handling. Check the original text before classifying the result.
Do not depend on ambiguous date formats. Ask for ISO style input, or use a documented conversion style for the source format. A date like 03/04/2025 means different things to different systems. SQL Server should not have to guess which calendar the feed intended.
Keep rejected source values intact. Trimming and parsing into clean columns is fine, but do not overwrite the original text before review. I want the operator to see the exact value that arrived. It makes the conversation with the source owner much shorter.
SELECT SourceRowId, RawOrderDate
FROM dbo.StageOrder
WHERE NULLIF(TRIM(RawOrderDate), N'') IS NOT NULL
AND TRY_CONVERT(date, RawOrderDate, 23) IS NULL;
Store Data Quality Rules Carefully
A rule table can hold RuleId, name, severity, active status, owner, and a description. It should not automatically execute arbitrary SQL text supplied through an editable field. That design turns a data quality catalog into an unrestricted code runner. Keep executable checks in reviewed procedures or views and link them to rule metadata.
Version the definition when a rule changes. A load from last month should remain explainable under the rule version that ran then. Record RuleId and version with each violation. If thresholds are configurable, store the value and effective dates so the result can be reproduced.
A rule called BadData is not a rule. A name such as MissingCustomerCode or InvalidOrderDate tells the next person what to fix. Plain names are part of operational quality. They keep alerts useful when somebody is reading them before coffee.
Keep a Reject Table With Evidence
The reject table should retain RunId, source row ID, rule ID, original value or raw payload reference, detected time, and disposition. A row can violate more than one rule. Store one violation per row and rule if you need complete diagnosis, then decide whether the row itself can be corrected and replayed.
Protect sensitive values in the reject table. Retain only what support needs, and apply the same access and retention controls as the source data. A reject table is still a data store. It can become a second ungoverned copy if nobody owns it.
I keep accepted and rejected counts separate. Their sum should reconcile to the staged count after accounting for intentional filters. If it does not, investigate the missing category. A quality process that loses rows between trays has become a quality problem itself.
Fail a Load When a Gate Is Broken
A severe rule should stop publication. Run validation after staging and before the target transaction. If the violation count exceeds the agreed threshold, log the rule failures and THROW an error. SQL Agent then reports failure, and the previous published data stays in place.
Some rules are warnings. A missing optional phone number does not necessarily block a financial report. Record the warning and show it in the run summary. The gate decision should be explicit per rule, not inferred from a vague severity label alone.
What happens when the source owner disputes a rule? Keep the rule definition, sample offending rows, and run ID available. That makes the decision reviewable. I would rather have a short argument over a clear rule than a long investigation of a silent correction.
DECLARE @bad_rows bigint;
SELECT @bad_rows = COUNT_BIG(*)
FROM dbo.StageOrder
WHERE OrderAmount < 0;
IF @bad_rows > 0
THROW 50001, 'Negative order amounts block this load.', 1;Measure Drift in Data Quality Rules Without Inventing Success
Track violations by rule and source over time. A sudden change can reveal a source release, a format change, or a broken extraction. Do not call a load healthy solely because it crossed a threshold. Compare against the business meaning of the data and the report it feeds.
Review false positives and stale data quality rules. A rule that nobody owns will eventually be bypassed. Retire it deliberately or give it an owner. Keep a test dataset with valid, invalid, and borderline rows for each important rule. A new rule should prove that it catches the bad case without rejecting the good one.
The aim is to fail clearly before bad rows become trusted numbers. T-SQL provides the checks, but ownership provides the judgment. Together they give the next load a reason to stop and a path to resume.
Related reading on this blog: Where Should a Data Quality Check Live? Gates, Controls, and Quarantine and "Clean Data" Is Not a Requirement: Writing Rules People Can Act On.
A data quality rule is not a scorecard ornament, it is an executable promise to readers.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
