Microsoft Fabric Warehouse Data Quality Assertion Gate

Microsoft Fabric Warehouse has no built-in way to turn a failed data quality rule into a notification: you either query the SQL endpoint by hand or you don't find out until a downstream report breaks. This blueprint runs a single SQL assertion on a schedule, compares its result to a threshold, and alerts the moment the rule is violated.

This blueprint was created by Mohit Jeswani.

How it works

1. run_assertion (io.kestra.plugin.microsoft.fabric.warehouse.Query) runs assertion_sql over JDBC against the warehouse's SQL endpoint, using Azure AD service principal authentication, and fetches the single result row with fetchType: FETCH_ONE. Read-only, as long as assertion_sql is a SELECT.
2. evaluate (io.kestra.plugin.core.output.OutputValues) reads violation_count from the result row and compares it to max_violation_count. A failed or empty query result is treated as CRITICAL, the same severity as a confirmed violation.
3. route (io.kestra.plugin.core.flow.If):
   • compliant: logs the passing count.
   • alert: posts the violation count to Slack.
4. summary logs the count and severity, closing the governance cycle.
5. errors alerts Slack if the flow itself fails outright, for example invalid service principal credentials.

What you get

• A data quality rule expressed as plain SQL, running against the same Fabric Warehouse your reports already query.
• A single, reusable gate: point assertion_sql at a different rule and reuse the same flow for null-key checks, duplicate-row checks, referential integrity checks, or any other count-shaped assertion.
• A severity that distinguishes an actual data quality violation from the assertion query itself failing to run, so a broken check never silently reads as compliant.

Who it's for

• Data platform teams running Microsoft Fabric who want a quality gate on a warehouse table without standing up a separate Great Expectations or Soda deployment for one or two rules.
• Teams evaluating the Microsoft Fabric plugin who want a worked example of warehouse.Query used for a read-only assertion. fabric-warehouse-query.yaml in this repo already covers scheduled reporting queries with fetchType: STORE; this blueprint is a narrower governance gate built on the same task: fetchType: FETCH_ONE against a single pass/fail assertion, a threshold comparison, and a severity-tagged Slack alert, not a general-purpose query runner.

Why orchestrate this with Kestra

The warehouse runs the SQL; it does not decide a cadence, compare the result to a threshold, or notify anyone. Kestra keeps assertion_sql and max_violation_count as flow inputs instead of a hardcoded script, turns the single-row result into a Slack page with severity attached, and keeps a full execution history of every check, compliant or not.

Prerequisites

• A Microsoft Fabric workspace with a Warehouse item and its SQL endpoint identifier.
• An Azure AD service principal (tenant ID, client ID, client secret) with read access to the warehouse.
• A table or view already queryable from the warehouse to assert against.
• A Slack incoming webhook URL.

This flow connects to the Fabric Warehouse SQL endpoint directly over JDBC; there is no official local Microsoft Fabric emulator, so run_assertion cannot be exercised against a local Docker container. Validate assertion_sql directly against the warehouse first, for example from the Fabric portal's SQL query editor.

Secrets

• FABRIC_TENANT_ID / FABRIC_CLIENT_ID / FABRIC_CLIENT_SECRET: the Azure AD service principal credentials used by run_assertion. In Kestra OSS, provide them as the base64-encoded environment variables SECRET_FABRIC_TENANT_ID, SECRET_FABRIC_CLIENT_ID, SECRET_FABRIC_CLIENT_SECRET.
• SLACK_WEBHOOK_URL: used by alert and the errors block. In Kestra OSS, provide it as the base64-encoded environment variable SECRET_SLACK_WEBHOOK_URL.

Inputs

Input	Default	Purpose
sql_endpoint_id	<your-sql-endpoint-id>	Fabric Warehouse SQL endpoint identifier
warehouse_id	<your-warehouse-id>	Warehouse item GUID, used as the JDBC database
assertion_sql	a null-check example	SELECT returning one row aliased violation_count
max_violation_count	0	violation_count above this triggers the alert

Outputs

• {{ outputs.run_assertion.row.violation_count }}: the raw value returned by the assertion.
• {{ outputs.run_assertion.size }}: number of rows returned (1 when the assertion matched a row, 0 otherwise).
• {{ outputs.evaluate.values.severity }}: OK, DATA_QUALITY_VIOLATION, or CRITICAL.

Pitfalls

• assertion_sql must alias its single output column violation_count exactly; this blueprint reads that column name directly, so a typo in the alias surfaces as a null comparison, not a clear error.
• This gate trusts assertion_sql to be read-only. The warehouse executes DDL/DML just as readily as a SELECT; nothing here stops an assertion query that was accidentally written as an UPDATE.
• Service principal authentication requires both clientId and clientSecret; when either is left unset, the task falls back to DefaultAzureCredential, which behaves differently depending on where Kestra itself is running and can fail in a way that is not obviously related to the warehouse or the query.

Quick start

1. Add the FABRIC_TENANT_ID, FABRIC_CLIENT_ID, FABRIC_CLIENT_SECRET and SLACK_WEBHOOK_URL secrets.
2. Validate assertion_sql in the Fabric portal's SQL query editor against a real table, confirming it returns one row with a violation_count column.
3. Set sql_endpoint_id and warehouse_id to match, then run the flow and confirm summary logs a count.
4. Lower max_violation_count below the real count to confirm alert fires.
5. Enable the hourly trigger once the threshold reflects your actual data quality bar.

How to extend

• Loop over a list of assertion_sql values with io.kestra.plugin.core.flow.Loop to run several data quality rules against the same warehouse in one scheduled run.
• Change fetchType to STORE and read the exported file in a downstream task when a violation needs the actual offending rows, not just the count.
• Chain io.kestra.plugin.microsoft.fabric.data.engineering.RunPipeline after a passing result, so a downstream pipeline only runs once the upstream data has been asserted clean.

Links

• Microsoft Fabric plugin: https://kestra.io/plugins/plugin-microsoft-fabric
• Fabric Warehouse SQL connectivity: https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-connectivity
• Slack incoming webhooks: https://api.slack.com/messaging/webhooks