Guide · Purview to OneLake Migration

How to move Purview data quality rules to another platform

Updated 2026-11-21Python and DuckDBRuns shown on generated records, PDF by email

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 rulerule_typeparameterA row passes when
Freshnessfreshnessdays, for example 30the newest value in the column is at most that many days before --as-of (the whole rule scores 100 or 0)
Unique valuesuniquenonethe value appears once in the column
String format matchformatenum:a,b,c, like:pattern or regex:patternthe value is in the list, matches the LIKE pattern, or matches the regular expression
Data type matchdatatypeinteger, decimal, date, timestamp or booleanthe value converts to that type
Duplicate rowsduplicate_rowscolumns, for example customer_id;order_dateno other row has the same values in those columns
Empty/blank fieldsemptynonethe value is neither null nor blank
Table lookuplookuptable.columnthe value is in that column of that table
Customcustoma SQL condition on the rowthe 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 rulesWhat a failure means
Every rule has one of the eight rule typesThe 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 folderThe table was not exported, or its file has another name.
Every rule names columns the table holdsThe column was renamed or dropped since the rule was written.
Every rule's parameter can be readA pattern, type, column list or threshold is malformed, so the rule cannot run.
Every rule checks at least one rowThe 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.
CheckThe mistake planted in run 2
Known rule typesA rule of type “range”, which is not one of the eight.
Known tablesA rule on a table named invoices, which the data folder does not hold.
Known columnsA rule on a phone column the customers table does not have.
Readable parametersA regular expression with an unclosed bracket.
Rules that check rowsA 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.

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.50 stays 12.50. That is why rules.sql reads those rules from txt.customers and txt.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.sql to 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.

See Purview to OneLake Migration