How to validate data in Microsoft Fabric

Q: Why do duplicates appear when my Fabric table has a primary key?

Because Fabric constraints are declared `NOT ENFORCED` — they inform the query optimiser but do not reject rows. A rerun pipeline will happily write duplicate keys. Validate uniqueness explicitly with a `duplicateCount` rule; it is the only enforcement present.

Q: What permissions does a validation service principal need?

Workspace **Viewer** is normally enough to read from the SQL analytics endpoint, plus the tenant setting permitting service principals to use Fabric APIs. Grant `SELECT` on specific schemas if you want tighter scope. No write permission is required — every check is an aggregate `SELECT`.

A practical guide to validating data quality in Microsoft Fabric — Warehouse and Lakehouse SQL analytics endpoint, the T-SQL to write the six essential checks, the metadata sync trap, and how to run it all as a versioned data contract.

2026-08-30 · 8 min read

To validate data in Microsoft Fabric, connect to the Warehouse or the Lakehouse SQL analytics endpoint over the standard T-SQL protocol, express each expectation as an aggregate query returning a violation count, and run the batch after each pipeline. The six checks that matter are the same everywhere — nulls, duplicate keys, disallowed values, out-of-range numbers, malformed strings, stale rows — but Fabric adds two failure modes the checks have to account for: the SQL endpoint's metadata can lag behind what OneLake actually contains, and the T-SQL surface is deliberately narrower than SQL Server's.

What you are actually connecting to

Fabric exposes three SQL-speaking things, and the differences matter for validation:

Warehouse — read/write T-SQL over Delta tables in OneLake. Full DML.
Lakehouse SQL analytics endpoint — a read-only T-SQL view over the Lakehouse's Delta tables, kept in sync automatically.
Mirrored databases — replicated sources, surfaced through the same read-only endpoint.

All three speak the TDS protocol, so any SQL Server client connects to them. All three are read-only or read-mostly from a validation tool's point of view, which is exactly what you want. Catalyst treats Fabric as its own connection type rather than reusing the SQL Server one, because the authentication model and the dialect limits differ.

The metadata sync trap

This is the Fabric-specific thing to understand. When a Spark job or a pipeline writes a Delta table into a Lakehouse, the SQL analytics endpoint discovers the change asynchronously. For a short window the endpoint can report the previous state of the table — old row counts, occasionally a table that does not exist yet.

The consequence for validation: a check that fires immediately at the end of a notebook can read pre-write metadata and report a false pass, which is worse than a false fail. Two mitigations:

Trigger validation from the pipeline's completion activity with a short delay, rather than from the notebook itself.
Include a row-level freshness rule alongside any metadata-based one, so a stale endpoint shows up as a timeliness failure rather than as silence.

The six checks every table needs

Fabric's T-SQL has the same fundamental limits as SQL Server — no regular expression operator, TOP n rather than LIMIT n, bracket quoting — plus a few of its own: no MERGE on the read endpoint, and a narrower set of built-in functions than a full SQL Server instance.

1. Completeness — nulls in required columns

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

COUNT_BIG rather than COUNT: Fabric warehouses hold fact tables comfortably past the 2.1 billion row int limit.

2. Uniqueness — duplicate business keys

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

Fabric Warehouse supports PRIMARY KEY and UNIQUE constraints only as NOT ENFORCED metadata for the optimiser. They do not reject duplicates. If a pipeline reruns a partition, the duplicates land, the constraint still claims uniqueness, and the optimiser may even produce wrong results by trusting it. Validating uniqueness explicitly is not redundancy here — it is the only enforcement that exists.

3. Conformity — values outside an allowed set

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

Keep the null guard: NULL NOT IN (...) is UNKNOWN and the row disappears from the count.

4. Accuracy — numbers outside a plausible range

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

Delta stores decimals faithfully, so use decimal for money rather than float. Watch for Spark-side schema drift: a notebook that writes a column as double one day and decimal the next will change the column's type at the SQL endpoint, and a range check that used to compare cleanly starts comparing with rounding error.

5. Conformity — malformed identifiers

There is no regex. As on SQL Server, anchored fixed-shape patterns translate to LIKE with character classes:

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

Anything involving alternation, optional groups or unbounded repetition cannot be expressed. Catalyst translates a regex rule to LIKE when the pattern permits and reports an explicit unsupported-dialect error when it does not — so the check surfaces as an error rather than quietly passing. For genuinely complex patterns, do the validation in the Spark notebook that writes the table, where you have a real regex engine, and keep a coarse LIKE or validValues rule at the SQL layer as the backstop.

6. Timeliness — freshness

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

SYSUTCDATETIME() rather than GETDATE(), so the check does not drift with the capacity's region or with daylight saving.

Access and authentication

Fabric authenticates through Microsoft Entra ID rather than SQL logins. For an automated validator, use a service principal:

Register an app in Entra ID and create a client secret.
Add the service principal to the Fabric workspace with the Viewer role — enough to read from the SQL endpoint, not enough to change anything.
Grant SELECT on the specific schemas if you want narrower scope than workspace-wide read.
Ensure the tenant setting that allows service principals to use Fabric APIs is enabled — this is off by default in many tenants and is the usual cause of an otherwise inexplicable login failure.

Catalyst stores the client secret encrypted and connects read-only; only metadata and check results leave the workspace.

Capacity is worth a thought too. Validation queries consume Capacity Units from the same pool as everything else in the workspace. A large suite of full-table scans running every fifteen minutes on an F2 capacity will throttle your reports. Scope checks to a partition where you can, and stagger schedules.

From ad-hoc SQL to a data contract

Declaring the expectations makes them reviewable and portable across all three Fabric surfaces. Catalyst uses the Open Data Contract Standard:

apiVersion: v3.0.0
kind: DataContract
info:
  title: orders
  version: 1.0.0
  owner: fabric-platform
schema:
  - name: orders
    physicalName: orders
    physicalType: table
    properties:
      - name: order_id
        logicalType: string
        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
        physicalType: decimal(12,2)
        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"
    quality:
      - rule: rowCount
        dimension: consistency
        severity: error
        mustBe: "> 0"

The table-level rowCount rule earns its place in Fabric specifically: an empty result after a pipeline run is the classic symptom of a Delta write that landed in the wrong path, and it is the check most likely to fire when the SQL endpoint and OneLake disagree.

Catalyst imports columns from INFORMATION_SCHEMA.COLUMNS, compiles each rule to Fabric-correct T-SQL, and records pass, warn or fail per check. The YAML round-trips through the visual builder, so the contract in your repository is the one that runs.

Fabric gotchas worth knowing

Trap	What happens	What to do
Endpoint metadata lag	Check reads pre-write state, false pass	Trigger from the pipeline, add row-level freshness
`NOT ENFORCED` keys	Duplicates land despite a primary key	Always add a `duplicateCount` rule
No regex in T-SQL	Pattern rules cannot be expressed	Translate to `LIKE`, or validate in Spark
Spark schema drift	Column type changes between runs	Pin types in the contract; alert on drift
Service principal blocked	Login fails with no useful error	Enable the tenant setting for Fabric APIs
Capacity throttling	Reports slow down when checks run	Scope to partitions, stagger schedules
`GETDATE()`	Capacity-local time	Use `SYSUTCDATETIME()`

Scheduling and alerting

Attach validation to the end of the Data Factory pipeline that produces the table, with a short buffer for the SQL endpoint to catch up. Alert on the transition into failure rather than on the ongoing state, and reserve error severity for the rules that should actually stop a downstream Power BI refresh — a semantic model rebuilt on top of a failed load is how bad data reaches an executive dashboard.

Frequently asked questions

Can I use SQL Server tools to validate Microsoft Fabric data?

Mostly. Fabric's Warehouse and SQL analytics endpoint speak the TDS protocol, so SQL Server clients and drivers connect. The differences are authentication (Entra ID rather than SQL logins), a narrower T-SQL surface on the read-only endpoint, and unenforced constraints. Rules written for SQL Server generally port unchanged.

Why do duplicates appear when my Fabric table has a primary key?

Because Fabric constraints are declared NOT ENFORCED — they inform the query optimiser but do not reject rows. A rerun pipeline will happily write duplicate keys. Validate uniqueness explicitly with a duplicateCount rule; it is the only enforcement present.

How do I run a regex check in Fabric?

You cannot at the SQL layer — T-SQL has no regex operator. Anchored fixed-length patterns can be rewritten as LIKE with [0-9]-style classes. For anything more complex, do the pattern validation in the Spark notebook that writes the table, and keep a coarse LIKE or validValues rule at the SQL endpoint as a backstop.

What permissions does a validation service principal need?

Workspace Viewer is normally enough to read from the SQL analytics endpoint, plus the tenant setting permitting service principals to use Fabric APIs. Grant SELECT on specific schemas if you want tighter scope. No write permission is required — every check is an aggregate SELECT.

Does validation consume Fabric capacity?

Yes. Queries run against your capacity's Capacity Units, in the same pool as pipelines and Power BI refreshes. Keep checks column-scoped and partition-scoped where possible, and stagger schedules so a validation sweep does not collide with the morning report refresh.