How to Automate Data Quality Rules in Data Governance: Step by Step

João Barros 16 de September de 2026 5 min read

This tutorial shows how to automate Data Quality Rules in Data Governance to validate, record and notify data quality issues. Automating rules brings consistency, detects regressions early and makes auditing data quality easier.

Prerequisites

Account with access to a SQL environment (e.g.: Azure SQL or SQL Server).
PowerShell installed (or access to Azure Automation) to schedule tasks.
Permissions to create tables and jobs on the SQL server.
Basic knowledge of SQL and PowerShell scripts.

Step 1: Define the Data Quality Rules

Start by listing concrete and measurable rules. Common examples: not-null, formats (email), numeric ranges, uniqueness. Each rule should have: name, SQL expression, severity and execution frequency.

-- Exemplo de regras numa tabela de referência
-- rule_id | rule_name         | sql_check                                  | severity
INSERT INTO data_quality_rules (rule_id, rule_name, sql_check, severity)
VALUES
 (1, 'CustomerEmailNotNull', 'SELECT COUNT(*) FROM dbo.Customer WHERE Email IS NULL', 'High'),
 (2, 'OrderAmountPositive', 'SELECT COUNT(*) FROM dbo.Orders WHERE Amount <= 0', 'Medium');

Step 2: Create a rules table and a results repository

Store the rules and execution results for audit and trending. The results table should record timestamp, rule_id, count_violations and message.

CREATE TABLE dbo.data_quality_rules (
  rule_id INT PRIMARY KEY,
  rule_name NVARCHAR(200),
  sql_check NVARCHAR(MAX),
  severity NVARCHAR(50)
);

CREATE TABLE dbo.data_quality_results (
  result_id INT IDENTITY(1,1) PRIMARY KEY,
  rule_id INT,
  run_time DATETIME2 DEFAULT SYSUTCDATETIME(),
  violations INT,
  details NVARCHAR(MAX)
);

Step 3: SQL script to execute a single rule

Create a stored procedure that executes the sql_check expression dynamically, captures the number of violations and logs the result. This centralizes execution logic.

CREATE PROCEDURE dbo.ExecuteDataQualityRule @rule_id INT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql NVARCHAR(MAX); DECLARE @violations INT; DECLARE @details NVARCHAR(MAX);

  SELECT @sql = sql_check FROM dbo.data_quality_rules WHERE rule_id = @rule_id;
  IF @sql IS NULL
  BEGIN
    RAISERROR('Regra não encontrada', 16, 1); RETURN;
  END

  -- Executa a query que devolve um COUNT(*) como resultado
  DECLARE @execSql NVARCHAR(MAX) = 'DECLARE @cnt INT; SET @cnt = 0; ' + @sql + '; SELECT @cnt = (SELECT TOP 1 * FROM (SELECT 1) AS t);';
  -- Simples abordagem alternativa: assume que sql_check é 'SELECT COUNT(*) FROM ...'
  INSERT INTO dbo.data_quality_results (rule_id, violations, details)
  EXEC('DECLARE @v INT; ' + @sql + '; SELECT @v = (SELECT COUNT(*) FROM (SELECT 1) as x);');
END;

Step 4: PowerShell script to orchestrate and notify

Use PowerShell to read the rules, execute the stored procedure for each rule, and send notification (email or webhook) when there are violations above a threshold.

# Exemplo simplificado PowerShell
$server = 'myserver.database.windows.net'
$database = 'MyDB'
$user = 'admin'
$pwd = 'P@ssw0rd'

$connStr = "Server=$server;Database=$database;User ID=$user;Password=$pwd;Encrypt=True;"

Import-Module SqlServer
$rules = Invoke-Sqlcmd -Query "SELECT rule_id, rule_name FROM dbo.data_quality_rules" -ConnectionString $connStr

foreach ($r in $rules) {
  Invoke-Sqlcmd -Query "EXEC dbo.ExecuteDataQualityRule $($r.rule_id)" -ConnectionString $connStr
}

# Verificar resultados e enviar alerta
$results = Invoke-Sqlcmd -Query "SELECT r.rule_name, res.violations FROM dbo.data_quality_results res JOIN dbo.data_quality_rules r ON res.rule_id=r.rule_id WHERE res.run_time > DATEADD(hour,-1,sysutcdatetime())" -ConnectionString $connStr
foreach ($row in $results) {
  if ($row.violations -gt 0) {
    # Exemplo: registar ou chamar um webhook/Enviar email
    Write-Output "ALERT: $($row.rule_name) has $($row.violations) violations"
  }
}

Step 5: Schedule automatic execution

To automate, create a SQL Agent Job (SQL Server) or a Runbook in Azure Automation / Azure Logic Apps that runs the PowerShell script with the required frequency (hourly, daily, etc.).

-- Exemplo: criar um SQL Agent Job que executa um PowerShell step
-- (esta parte faz-se via SSMS GUI ou T-SQL system stored procedures para jobs)
-- Alternativa: colocar o PowerShell num Runbook e agendar no Azure Automation.

Verify the result

Confirm that the rules were executed by querying dbo.data_quality_results. Check recent timestamps, number of violations and details. Test with data that violates the rules and with clean data to confirm the system detects both scenarios.

SELECT r.rule_name, res.run_time, res.violations, res.details
FROM dbo.data_quality_results res
JOIN dbo.data_quality_rules r ON res.rule_id = r.rule_id
ORDER BY res.run_time DESC;

Conclusion

Automating Data Quality Rules in Data Governance allows detecting issues early and building a history of quality. Next steps: improve violation details (e.g.: recording affected row keys), integrate with Power BI for dashboards and add thresholds by severity. Tip: start with a few critical rules and expand gradually — what is the first rule you will automate?