How to move Purview data quality rules to another platform
The short answer
Write each Purview data quality rule as one row of a CSV. Microsoft's Data Quality API, in public preview since December 2025, can create and manage rules, but this guide copies each rule from the Purview portal. One Python and DuckDB script turns every row into SQL, scores every rule on your data and checks the rules themselves.
Why data quality rules need a new home
Microsoft announced on November 12, 2026 that Purview Data Governance is retiring: “Support for Purview Data Governance will end Nov. 12, 2027” (Microsoft Security blog, read 2026-11-21). For data quality, Microsoft's transition guide (read 2026-11-21) names “Freshness and curation insights; workload and source tools” as the target today, and says Governance Health is not a rules, profiling and scoring engine. So the rules themselves need somewhere else to run.
A rule written as code runs wherever your data runs, can be reviewed like any other code, and keeps scoring after the Purview account is gone. This guide moves each rule into one row of a CSV, turns every row into SQL and scores the rule the way Microsoft describes.
What you will have at the end
- Your rules as one CSV, one row per rule, in a layout any team can read and review.
- Every rule as SQL in
rules.sql, written by the script, ready to adapt to the platform your data moves to. - A score per rule in
scores.csv: rows checked, rows passed and failed, the score and whether the score meets the rule's threshold. - Five checks on the rules themselves, so a rule that names a missing table or column, or checks no row at all, is caught instead of scoring quietly.
The eight rule types, and how each one runs
Microsoft's page Create Data Quality Rules in Unified Catalog (read 2026-11-21) lists eight rule types. The script follows the page's scoring: the unique values and string format rules ignore null and blank values unless the same column also has an empty/blank fields rule, and the freshness rule scores 100 or 0.
| Purview rule | rule_type | parameter | A row passes when |
|---|---|---|---|
| Freshness | freshness | days, for example 30 | the newest value in the column is at most that many days before --as-of (the whole rule scores 100 or 0) |
| Unique values | unique | none | the value appears once in the column |
| String format match | format | enum:a,b,c, like:pattern or regex:pattern | the value is in the list, matches the LIKE pattern, or matches the regular expression |
| Data type match | datatype | integer, decimal, date, timestamp or boolean | the value converts to that type |
| Duplicate rows | duplicate_rows | columns, for example customer_id;order_date | no other row has the same values in those columns |
| Empty/blank fields | empty | none | the value is neither null nor blank |
| Table lookup | lookup | table.column | the value is in that column of that table |
| Custom | custom | a SQL condition on the row | the condition is true |
Step 1. Write the rules down
Microsoft's Data Quality API (public preview, read 2026-11-21) can create and manage data quality rules; this guide does not depend on the API. Open each data asset's rules in the Purview portal and write one row per rule into rules.csv, with the columns rule_id, table, column, rule_type, parameter and threshold (the lowest passing score, 0 to 100). A custom rule built in Purview's expression builder is rewritten as one SQL condition.
Step 2. Run the rules on your data
Put each table your rules name into one folder as a CSV or Parquet file named after the table, for example orders.csv. Download rules_as_code.py, install DuckDB (pip install duckdb) and run:
python3 rules_as_code.py --rules rules.csv --data tables --as-of 2026-11-21
A rule passes when its score, before rounding, reaches the threshold. To run the rules from a scheduled job, add --fail-on-findings: the script then exits with status 1 when a rule cannot be scored or scores below its threshold.
| Check on the rules | What a failure means |
|---|---|
| Every rule has one of the eight rule types | The row names a type the script does not know, often a typo or a rule from another tool. |
| Every rule names a table in the data folder | The table was not exported, or its file has another name. |
| Every rule names columns the table holds | The column was renamed or dropped since the rule was written. |
| Every rule's parameter can be read | A pattern, type, column list or threshold is malformed, so the rule cannot run. |
| Every rule checks at least one row | The table is empty, or every value the rule would check is null or blank, so a score of 100 would mean nothing. |
Run 1. Eleven rules on generated data
The script writes generated data and a rules file when you ask for them: invented customers and orders with a few realistic flaws, and eleven rules covering all eight types, with a fixed seed.
python3 rules_as_code.py --make-demo demo
Wrote generated data to demo/data (customers 200 rows, orders 500 rows) and 11 rules to demo/rules.csv (seed 275)
python3 rules_as_code.py --rules demo/rules.csv --data demo/data --as-of 2026-11-21
Python 3.14.8 duckdb 1.5.6 Data: 2 tables in demo/data: customers (200 rows), orders (500 rows) Rules: 11 in demo/rules.csv; scored as of 2026-11-21 Rule Type Table.column Checked Failed Score Needs Result R01 unique customers.customer_id 200 0 100.0 100 PASS R02 empty customers.email 200 8 96.0 95 PASS R03 format customers.email 200 9 95.5 99 FAIL R04 format customers.region 200 1 99.5 99 PASS R05 freshness customers.updated_at 1 0 100.0 100 PASS R06 unique orders.order_id 500 2 99.6 100 FAIL R07 lookup orders.customer_id 500 1 99.8 100 FAIL R08 datatype orders.amount 500 1 99.8 100 FAIL R09 format orders.currency 500 0 100.0 100 PASS R10 duplicate_rows orders.customer_id;order_date;amou 500 0 100.0 100 PASS R11 custom orders.(row) 500 1 99.8 99 PASS Checks on the rules PASS Every rule has one of the eight rule types PASS Every rule names a table in the data folder PASS Every rule names columns the table holds PASS Every rule's parameter can be read PASS Every rule checks at least one row Wrote scores.csv (11 rows) and rules.sql (11 rules) to . RESULT: 11 rules scored, 4 below their threshold; 0 rules could not be scored.
How to read it
Every rule could run, so all five checks on the rules pass. Four rules score below their threshold because the generated data holds the flaws a real table often holds: blank and malformed email addresses, a repeated order id, an order for a customer who does not exist, and an amount written with a comma. Those are findings about the data, which is the job the rules exist to do. The email format rule counts blank emails as failures because the same column also has an empty/blank fields rule.
The first rule as SQL, from rules.sql:
-- R01: unique on customers.customer_id
SELECT count(*), count(*) FILTER (WHERE x IS NOT NULL AND n = 1) FROM (SELECT "customer_id" AS x, count(*) OVER (PARTITION BY "customer_id") AS n FROM txt."customers" WHERE "customer_id" IS NOT NULL AND trim(CAST("customer_id" AS VARCHAR)) <> '');
Run 2. The same rules with five planted mistakes in the rules
python3 rules_as_code.py --make-demo demo-planted --plant-errors
Wrote generated data to demo-planted/data (customers 200 rows, orders 500 rows) and 16 rules to demo-planted/rules.csv (seed 275) Planted one error for each check on the rules: BAD_PARAMETER, CHECKS_NOTHING, UNKNOWN_COLUMN, UNKNOWN_RULE_TYPE, UNKNOWN_TABLE
python3 rules_as_code.py --rules demo-planted/rules.csv --data demo-planted/data --as-of 2026-11-21 --tag planted
Python 3.14.8 duckdb 1.5.6
Data: 3 tables in demo-planted/data: customers (200 rows), notes (10 rows), orders (500 rows)
Rules: 16 in demo-planted/rules.csv; scored as of 2026-11-21
Rule Type Table.column Checked Failed Score Needs Result
R01 unique customers.customer_id 200 0 100.0 100 PASS
R02 empty customers.email 200 8 96.0 95 PASS
R03 format customers.email 200 9 95.5 99 FAIL
R04 format customers.region 200 1 99.5 99 PASS
R05 freshness customers.updated_at 1 0 100.0 100 PASS
R06 unique orders.order_id 500 2 99.6 100 FAIL
R07 lookup orders.customer_id 500 1 99.8 100 FAIL
R08 datatype orders.amount 500 1 99.8 100 FAIL
R09 format orders.currency 500 0 100.0 100 PASS
R10 duplicate_rows orders.customer_id;order_date;amou 500 0 100.0 100 PASS
R11 custom orders.(row) 500 1 99.8 99 PASS
Checks on the rules
FAIL Every rule has one of the eight rule types (1)
FAIL Every rule names a table in the data folder (1)
FAIL Every rule names columns the table holds (1)
FAIL Every rule's parameter can be read (1)
FAIL Every rule checks at least one row (1)
UNKNOWN_RULE_TYPE R12: rule type 'range' is not one of the eight
UNKNOWN_TABLE R13: no table 'invoices' in the data folder
UNKNOWN_COLUMN R14: table customers has no column phone
BAD_PARAMETER R15: regex: Invalid Input Error: missing ]: [A-Z{3}
CHECKS_NOTHING R16: the rule checked no row, so its score means nothing
Wrote scores-planted.csv (11 rows) and rules-planted.sql (12 rules) to .
RESULT: 11 rules scored, 4 below their threshold; 5 rules could not be scored.
| Check | The mistake planted in run 2 |
|---|---|
| Known rule types | A rule of type “range”, which is not one of the eight. |
| Known tables | A rule on a table named invoices, which the data folder does not hold. |
| Known columns | A rule on a phone column the customers table does not have. |
| Readable parameters | A regular expression with an unclosed bracket. |
| Rules that check rows | A format rule on the comment column of a notes table, blank in every row. |
Run the rules on your own data
Write your rules into rules.csv, one row per rule, with the columns rule_id, table, column, rule_type, parameter and threshold. Save each table the rules name in one folder called tables, as a CSV or Parquet file named after the table, and run python3 rules_as_code.py --rules rules.csv --data tables. You do not edit the script. Change --as-of to the date the freshness rules should be measured on, and --out <folder> to write the results somewhere other than the script's folder.
We tested a run on a reader's own data: other table and column names, a Parquet table beside CSV tables, upper-case values, empty fields and one planted mistake for each check. All five checks failed as planted, and the valid rules scored as expected.
Download rules_as_code.py · Get the full guide (PDF)
Results from run 1: scores.csv and rules.sql.
Get the full guide as a PDF
We'll email you a link to it. Please use your work email.
Thanks, . We're sending the link to now. If it isn't there in a few minutes, check your spam or junk folder, or email info@datameaning.com.
Limits
- The runs on this page use generated data, not a Purview tenant. The rule types and scoring follow Microsoft's page, read on 2026-11-21; Purview's own engine can differ in details this page does not describe.
- Purview's freshness rule reads the asset's last modified date. The script reads the newest date in the column the rule names.
- The table lookup rule also skips null and blank values unless the column has an empty/blank fields rule, and the data type rule always skips them; Microsoft's page does not say how either rule treats them.
- Purview's data type rule uses its own type system. The script uses five DuckDB types instead, so a value at the edge of a type can score differently. Dates and timestamps pass the data type rule when written year first, for example 2026-11-21; 11/21/2026 and 21/11/2026 fail.
- Format, lookup, unique, empty and data type rules test each value as the CSV file writes it, so
12.50stays12.50. That is whyrules.sqlreads those rules fromtxt.customersandtxt.orders, the tables as text. - A Purview custom rule has a row expression, a filter expression and a null expression. The script takes one SQL condition and checks every row. Write the filter and the null part into that condition, for example
NOT (filter) OR (row expression). A row Purview's filter would skip then counts as a passed row, so a custom rule with a filter can score higher than in Purview. - The script reads tables as CSV or Parquet files. To score rules where the data now lives, adapt the SQL in
rules.sqlto that platform. - Dates are as of 2026-11-21. This guide is about Purview Data Governance, whose support ends on November 12, 2027.
Tested on
Tested on
- Platform
- DuckDB 1.5.6, run from Python 3.14.8
- Edition
- Open-source DuckDB library (
pip install duckdb) - Date
- 2026-11-21
- Records
- Generated: 200 invented customers and 500 invented orders, fixed seed 275. Not a run against a Purview tenant.
- Code
- rules_as_code.py
- Results
- scores.csv, rules.sql
When this is not enough
This guide moves the rules and keeps every rule scoring. A migration also decides where each rule should run from now on, who owns each rule's failures, and how scores reach the people who used to read them in Purview.
Moving your whole Purview governance catalog, not only the quality rules?
Purview to OneLake Migration is in early access. The offering starts with the four-day Catalog Transition Assessment: one fixed fee of $12,000, and no commitment to anything else. You get an inventory of every object in your Purview governance catalog and a decision for each object. AI does the first pass, and your stewards sign every domain.