How to validate data in SQL Server

Q: Does data quality validation need write access to my database?

No. Every check in this guide is a `SELECT` that returns a single number. A `db_datareader` login plus `VIEW DEFINITION` is sufficient. Catalyst connects read-only and stores only metadata and check results — the rows themselves stay in your server.

A practical guide to validating data quality in Microsoft SQL Server — the six checks every table needs, the T-SQL to write them, and how to turn them into a versioned data contract that runs on a schedule.

2026-08-26 · 7 min read

The fastest way to validate data in SQL Server is to express each expectation as a single aggregate query that returns a violation count, run all of them against the table in one pass, and fail the batch when a count crosses its threshold. Six checks cover most real breakage: nulls in required columns, duplicate keys, values outside an allowed set, numbers outside a plausible range, malformed strings, and stale rows. This guide shows the T-SQL for each, the SQL Server quirks that will bite you, and how to move from ad-hoc scripts to a contract that runs on a schedule.

Why SQL Server needs its own approach

Most data quality advice is written for Postgres and quietly assumes things T-SQL does not have. Three differences matter:

There is no regular expression operator. LIKE is the only pattern matching in the box. It supports [0-9] character classes and %/_ wildcards, which is enough for anchored, fixed-shape patterns, and nothing more.
Row limiting is TOP n, not LIMIT n, and it goes before the select list rather than at the end of the statement. Any tool that builds sample queries has to know that.
Collation decides equality. A default SQL_Latin1_General_CP1_CI_AS column treats 'ACTIVE' and 'active' as the same value. A case-sensitive collation does not. Your "allowed values" check silently means different things on two servers.

Everything below is written with those in mind.

The six checks every table needs

1. Completeness — nulls in required columns

SELECT COUNT_BIG(*) AS violations
FROM [sales].[orders]
WHERE [order_id] IS NULL;

Use COUNT_BIG rather than COUNT on large fact tables: COUNT returns int and overflows past 2.1 billion rows. Note also that COUNT(column) already skips nulls, which is why the check is written as a filtered COUNT(*) — the two are easy to confuse and the wrong one always returns zero violations.

2. Uniqueness — duplicate business keys

SELECT COUNT_BIG(*) AS violations
FROM (
    SELECT [order_id]
    FROM [sales].[orders]
    WHERE [order_id] IS NOT NULL
    GROUP BY [order_id]
    HAVING COUNT(*) > 1
) AS dupes;

A primary key constraint would prevent this, but analytics tables are usually loaded by an ELT job into a heap with no constraints at all. The duplicate check is what a missing constraint costs you.

3. Conformity — values outside an allowed set

SELECT COUNT_BIG(*) AS violations
FROM [sales].[orders]
WHERE [status] IS NOT NULL
  AND [status] NOT IN ('pending', 'paid', 'shipped', 'refunded');

Leave the IS NOT NULL guard in. NOT IN with a null on the left evaluates to UNKNOWN, the row is dropped, and a column that is 40% null looks perfectly conformant.

4. Accuracy — numbers outside a plausible range

SELECT COUNT_BIG(*) AS violations
FROM [sales].[orders]
WHERE [total_amount] IS NOT NULL
  AND ([total_amount] < 0 OR [total_amount] > 100000);

Range checks catch unit errors — cents loaded as euros, a decimal shifted by a currency conversion — that no schema constraint will ever see.

5. Conformity — malformed identifiers

Here is where T-SQL diverges. There is no ~ operator, so an anchored pattern has to be expressed as LIKE:

-- ^ORD-[0-9]{6}$  becomes:
SELECT COUNT_BIG(*) AS violations
FROM [sales].[orders]
WHERE [reference] IS NOT NULL
  AND [reference] NOT LIKE 'ORD-[0-9][0-9][0-9][0-9][0-9][0-9]';

LIKE is implicitly anchored at both ends when the pattern has no leading or trailing %, so this is a true equivalent of the anchored regex. What you cannot express is alternation, optional groups, backreferences or unbounded repetition. For those, fall back to an explicit allowed-value list or a hand-written customSql check rather than pretending LIKE is a regex engine.

Catalyst does this translation for you: a regex rule on a SQL Server connection is converted to the equivalent LIKE pattern when it is expressible, and raises an explicit "unsupported on this dialect" error when it is not — so the check fails loudly instead of silently passing.

6. Timeliness — freshness

SELECT COUNT_BIG(*) AS violations
FROM [sales].[orders]
WHERE [created_at] < DATEADD(HOUR, -24, SYSUTCDATETIME());

Use SYSUTCDATETIME(), not GETDATE(). GETDATE() returns the server's local time, so the same check drifts by an hour twice a year and by whole hours across regions. Store timestamps as datetime2 and compare in UTC.

Validation should never need write access. A minimal login looks like this:

CREATE LOGIN catalyst_ro WITH PASSWORD = '<generated>';
CREATE USER catalyst_ro FOR LOGIN catalyst_ro;
ALTER ROLE db_datareader ADD MEMBER catalyst_ro;
GRANT VIEW DEFINITION TO catalyst_ro;   -- needed to read INFORMATION_SCHEMA

db_datareader gives SELECT on every table; VIEW DEFINITION is what lets the account enumerate columns and types from INFORMATION_SCHEMA.COLUMNS. Point it at a readable secondary if you have an availability group — validation queries are aggregate scans and belong off the primary.

From ad-hoc SQL to a data contract

Scripts like the ones above rot. They live in someone's repo, nobody knows which ones still run, and the thresholds are invisible to the analysts who depend on them. The fix is to express the expectations declaratively, in a format both humans and tools can read.

Catalyst uses the Open Data Contract Standard (ODCS) for this. The six checks above become:

apiVersion: v3.0.0
kind: DataContract
info:
  title: orders
  version: 1.2.0
  owner: data-platform
schema:
  - name: orders
    physicalName: orders
    physicalType: table
    properties:
      - name: order_id
        logicalType: string
        physicalType: uniqueidentifier
        required: true
        primaryKey: true
        quality:
          - rule: nullCount
            dimension: completeness
            severity: error
            mustBe: "0"
          - rule: duplicateCount
            dimension: uniqueness
            severity: error
            mustBe: "0"
      - name: status
        logicalType: string
        quality:
          - rule: validValues
            dimension: conformity
            severity: error
            mustBe: "['pending', 'paid', 'shipped', 'refunded']"
      - name: total_amount
        logicalType: number
        quality:
          - rule: between
            dimension: accuracy
            severity: error
            mustBe: "[0, 100000]"
      - name: reference
        logicalType: string
        quality:
          - rule: regex
            dimension: conformity
            severity: warning
            mustBe: "'^ORD-[0-9]{6}#39;"
      - name: created_at
        logicalType: timestamp
        quality:
          - rule: freshness
            dimension: timeliness
            severity: error
            mustBe: "<= 24h"

The YAML is the source of truth. Catalyst compiles each quality entry into the dialect-correct T-SQL shown earlier, runs the batch against your server, and records pass/warn/fail per check. Editing a rule in the visual builder rewrites the same YAML, comments and ordering intact, so a contract reviewed in a pull request is the contract that runs.

Note the severity field. error fails the run; warning records the violation and keeps going. Pattern checks on human-entered fields are usually warnings — you want the trend, not a 3 a.m. page.

Scheduling and alerting

A validation you run by hand is a validation you run once. Point a schedule at the dataset — hourly for freshness on a streaming table, daily after the ELT window for everything else — and alert on the transition, not on the state. What matters is "orders started failing", not "orders is still failing", which is the thing that trains people to ignore the channel.

Two operational notes specific to SQL Server:

Set a query timeout. A full-table COUNT_BIG(*) on an unindexed billion-row heap will happily run for an hour. Catalyst applies a per-check timeout and reports the check as error (distinct from fail) so an infrastructure problem never looks like a data problem.
Watch the plan cache. Parameterless aggregate scans are cheap to compile but expensive to run. On very large tables, validate a partition rather than the whole table, or run against a filtered indexed view.

SQL Server gotchas worth knowing

Trap	What happens	What to do
`COUNT(*)` overflow	Fails past 2.1 bn rows	Use `COUNT_BIG(*)`
`NOT IN` with nulls	Rows silently excluded	Add `IS NOT NULL`
`GETDATE()`	Local server time	Use `SYSUTCDATETIME()`
Case-insensitive collation	`'PAID'` passes a `'paid'` check	Pin the collation, or normalise with `COLLATE`
Implicit conversion	`WHERE varchar_col = 123` scans the whole table	Compare like types
`float` equality	Range bounds off by rounding	Use `decimal` for money

Frequently asked questions

Can I validate data in SQL Server without writing SQL?

Yes. Define the expectation declaratively — required, unique, allowed values, numeric range, pattern, maximum age — and let the tool compile it to T-SQL. Catalyst imports the columns and types from INFORMATION_SCHEMA, suggests a baseline set of rules from the schema, and generates the queries. You only write SQL for genuinely bespoke logic, through a customSql rule.

Does data quality validation need write access to my database?

No. Every check in this guide is a SELECT that returns a single number. A db_datareader login plus VIEW DEFINITION is sufficient. Catalyst connects read-only and stores only metadata and check results — the rows themselves stay in your server.

How do I check regular expressions in T-SQL?

You cannot, directly — SQL Server has no regex operator. Anchored, fixed-length patterns translate to LIKE with [0-9]-style character classes. Anything using alternation, optional groups or unbounded repetition has to become an allowed-value list, a CLR function, or a custom SQL check.

How often should validations run?

Match the schedule to the data's own cadence, and run the check just after the load that produces it. Freshness rules on streaming tables want hourly runs; batch dimension tables want one run after the nightly ELT completes. Running more often than the data changes only produces noise.

Does this work with Azure SQL and Microsoft Fabric?

Azure SQL Database uses the same T-SQL surface, so everything here applies unchanged. Microsoft Fabric's warehouse and SQL analytics endpoint also speak T-SQL with the same lack of regex — see the Microsoft Fabric guide for the differences around Entra ID authentication and OneLake metadata sync.