How to generate Data Quality Rules in Copilot in Fabric

João Barros 08 de September de 2026 4 min read

This tutorial shows how to generate Data Quality rules in the Copilot of Fabric to validate columns, detect null values and identify outliers. It is useful to automate quality checks before publishing data to a Lakehouse or Warehouse.

Prerequisites

Account with access to Microsoft Fabric and permissions to open a Notebook or a Dataflow Gen2.
Sample dataset loaded in OneLake or the Lakehouse (CSV or table).
Basic knowledge of SQL or Python (minimum intermediate level).

Step 1: Open Copilot in the dataset context

Open the dataset file in Fabric (for example, in a Notebook or Dataflow Gen2). Select the view where you are seeing the columns. Then, open the Copilot pane so suggestions are based on the context of the opened file. This allows Copilot to analyze column names and types.

Step 2: Ask for an inventory of columns and common issues

Ask Copilot for a simple inventory of the columns with suggestions of checks to perform (nulls, uniqueness, format). Request an example of rules in natural language and in SQL/Python.

Prompt exemplo:
"Lista as colunas desta tabela e sugere 5 regras de Data Quality (nulos, unicidade, range, formato, outliers). Mostra regras em SQL e em Python pandas."

Step 3: Generate SQL rules to run in the Warehouse

With Copilot's output, copy the generated SQL rules and adapt the real table/column names. Typical rules are null counts, percentage of unique values, format checks with LIKE/REGEXP and outliers with percentiles.

Exemplo SQL (ajusta table_name/column):
-- 1. Percentagem de nulos
SELECT
  COUNT(*) AS total_rows,
  SUM(CASE WHEN column IS NULL THEN 1 ELSE 0 END) AS null_count,
  SUM(CASE WHEN column IS NULL THEN 1 ELSE 0 END)*1.0/COUNT(*) AS null_pct
FROM table_name;

-- 2. Verifica unicidade
SELECT COUNT(*) AS total_rows, COUNT(DISTINCT column) AS distinct_count
FROM table_name;

-- 3. Formato (ex.: email)
SELECT COUNT(*) AS invalid_emails
FROM table_name
WHERE column NOT LIKE '%_@__%.__%';

-- 4. Outliers (valores fora dos percentis 1% e 99%)
WITH pct AS (
  SELECT
    approx_percentile(column, 0.01) AS p01,
    approx_percentile(column, 0.99) AS p99
  FROM table_name
)
SELECT * FROM table_name, pct
WHERE column < p01 OR column > p99;

Step 4: Generate automated checks in Python (pandas) for prototyping

If you prefer to prototype locally or in a Notebook, ask Copilot for the corresponding pandas code. This helps validate quickly before moving to the Warehouse or Dataflow.

Exemplo Python (pandas):
import pandas as pd

df = pd.read_csv('table_name.csv')  # ou carregar do OneLake
# 1. Percentagem de nulos
null_pct = df['column'].isna().mean()
# 2. Unicidade
distinct_count = df['column'].nunique()
# 3. Formato simples (email)
invalid_emails = df[~df['column'].str.contains(r".+@.+\..+", na=False)]
# 4. Outliers (1% / 99%)
low = df['column'].quantile(0.01)
high = df['column'].quantile(0.99)
outliers = df[(df['column'] < low) | (df['column'] > high)]

print(null_pct, distinct_count, len(invalid_emails), len(outliers))

Step 5: Turn rules into reusable checks (Prompt Template)

Create a standard prompt for Copilot that contains the pattern of checks and parameters (acceptance thresholds). That way, you can reuse it for other tables by changing only the name and thresholds.

Prompt template exemplo:
"Gera um conjunto de checks de Data Quality para a tabela {table_name}. Inclui: percentagem de nulos por coluna, verificação de unicidade para chaves, formatos para colunas 'email' e 'date', e deteção de outliers com limiar 1%/99%. Devolve código SQL e Python."

Verify the result

Run the SQL scripts in the Warehouse or the Python code in the Notebook and confirm the returns: acceptable null percentages, count of invalid values and list of outliers. If values exceed thresholds, mark as failed. Common errors: different column names, incorrect types (string vs numeric) and SQL functions supported by the Warehouse engine — adjust as needed.

Conclusion

Using Copilot in Fabric you can quickly generate Data Quality rules in SQL and Python to validate tables before publication. Next steps: integrate these checks into a Dataflow Gen2 or schedule them in the Warehouse for continuous monitoring. Tip: keep a prompt template with thresholds to speed up audits; which rule would you like to automate first?