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.

How to validate data quality in Microsoft SQL Server: the six checks every table needs, the T-SQL for each, and how to run them on a schedule as a versioned data contract.

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. For the parts that are the same on every engine — where to run checks, how to schedule them, which tables to prioritise — start with the complete guide to validating data.

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. Also note that COUNT(column) skips nulls, which is why the check uses 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. Without the constraint, you need this check.

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

T-SQL has no ~ operator, so an anchored pattern has to be written 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 visibly 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 these tend to rot. They sit in someone's repo, nobody knows which ones still run, and the analysts who depend on them can't see the thresholds. Writing the expectations declaratively, in a format people and tools can both read, fixes that.

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"

Catalyst treats the YAML as the master copy. 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, because you want to see the trend without getting paged at 3 a.m.

Scheduling and alerting

Validations run by hand tend to get 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 when a check changes state rather than on every run. You want to hear "orders started failing"; "orders is still failing" repeated every hour 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?

Not directly, because 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.