Data Factory Data Validation Gate

Overview

This flow adds a post-load quality gate to Azure Data Factory data loads. When an ADF load pipeline finishes, ADF's tail Web activity calls this flow with the completed run id. The flow confirms the load succeeded, runs a SQL validation query against the load target (row counts, null checks, referential integrity, reconciliation), and then gates: if the data is clean it starts a promote pipeline to publish downstream; if not it starts a quarantine pipeline and alerts. Every evaluation is logged to Dataverse and announced in Teams.

Why it matters: loading bad data silently corrupts downstream reports and consumers. A post-load validation gate stops bad batches from propagating and gives a full audit trail of every gate decision.

Ships Off (demo).

Use Case

A data engineering team runs nightly/continuous ADF loads into a SQL target. Before downstream models, dashboards, or exports consume a freshly loaded batch, they want it automatically validated and either promoted (clean) or quarantined (dirty) - with no manual checking and a record of every decision.

Flow Architecture

Environment Variables

Connectors & Connections

Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.

Customization Guide

Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.

Validation query

Replace flowlibs_ValidationQuery with your own checks. It must return a single column named FailCount. For reconciliation, return the count of mismatched/missing rows.

Soft vs hard gate

Raise the fail threshold above 0 to tolerate a small number of exceptions (soft gate) instead of blocking on any defect (hard gate).

Outcome pipelines

Point the promote and quarantine pipeline names at your real ADF pipelines. Promote can publish to production or refresh a dataset; quarantine can move the batch aside and open a ticket.

Multiple checks

Split the single query into several SQL actions (nulls, RI, reconciliation) and sum their fail counts before the gate, or branch per check type.

Trend reporting

The Dataverse audit table makes pass-rate-over-time and quarantine-frequency reports trivial in Power BI.

Key Expressions

The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.

@{coalesce(triggerBody()?['correlationId'], guid())}

@if(empty(coalesce(body('Run_Validation_Query')?['ResultSets']?['Table1'], json('[]'))), 0, int(coalesce(first(body('Run_Validation_Query')?['ResultSets']?['Table1'])?['FailCount'], 0)))

@body('Start_Promote_Pipeline')?['runId']