Purview Custom SQL query

Hi Anuj Pratap Solanki,
You’re on the right track exploring the Data Quality Rules experience in Purview. At the moment, full SQL-based validations aren’t supported in Health Management. The custom rule framework is limited to the built-in expression logic (column + operator/function + condition), and there isn’t an option to plug in a standalone SQL query for rule evaluation.

For scenarios where SQL logic is required, a common approach is to handle those validations outside Purview. For example, you can run your SQL checks as part of an ETL or data validation pipeline using services like Azure Data Factory or Azure Synapse Analytics, and then capture or surface the results back in Purview (for example via metadata enrichment or lineage context).

Another practical workaround is to encapsulate your SQL logic into views or derived columns at the source, and then apply Purview’s expression-based rules on top of those. This keeps your rule definitions within Purview while still leveraging SQL where needed.

As of now, there’s no publicly available roadmap confirming support for SQL-based rules in this area. If this capability is important for your use case, I’d recommend sharing it through the feedback channel so the product team can track demand: https://aka.ms/purviewideas

You can also refer to the current documentation for supported rule capabilities here: https://dotnet.territoriali.olinfo.it/en-us/purview/data-quality-rules

Hope this helps! Let me know if you’d like to walk through your specific SQL logic , happy to suggest how it can be adapted.

SQL-based rules are supported in Microsoft Purview Data Quality, but only in a constrained form and not as arbitrary, full SQL queries.

1. Support for SQL-based custom rules

In Health management → Data quality → Rules, the Custom rule type supports three expression modes:

• Regular expression language
• Azure Data Factory (ADF) expression language
• SQL expression language

For SQL, Purview provides Custom (SQL) rules that use Spark SQL predicates:

• Rules are written as row expressions (and optional filter and null expressions) using Spark SQL.
• Only predicates over a single dataset are supported; the rule must operate on one table/file.
• Joins are not supported in custom SQL rules.
• DML/DCL and harmful SQL (INSERT, UPDATE, DELETE, GRANT, TRUNCATE, DROP, ALTER, etc.) are not supported, because rules must not modify data or database state.

Examples from the documentation show typical patterns:

• Column pattern checks with RLIKE and filters:
  • Row: rateCodeId RLIKE '^1[0-9]+$'
  • Filter: paymentType IN ('CRD', 'CSH')
• Column comparisons:
  • Row: puLocationId > doLocationId AND fareAmount > tripDistance * 10
• Range checks:
  • Row: tripDistance BETWEEN 5 AND 10
• Aggregate-style checks expressed as predicates:
  • Row: (SELECT avg(CASE WHEN fareAmount IS NULL THEN 1 ELSE 0 END) FROM nycyellowtaxidelta1BillionPartitioned) < 0.20

So SQL-based validations are supported, but only as predicate-style Spark SQL expressions inside the rule, not as arbitrary multi-statement or multi-table SQL scripts.

2. Limitations and workarounds / enhancements

Current limitations for SQL-based custom rules include:

• No joins: “Custom SQL rules in Microsoft Purview Data Quality don't support joins. The rules must operate on a single dataset. You can't join multiple tables or datasets when writing these custom rules.”
• No DML/DCL or harmful SQL: INSERT, UPDATE, DELETE, GRANT, TRUNCATE, DROP, ALTER are blocked.

Known behavior/constraints in Health management:

• “No SQL query is generated for custom data quality rules failures. SQL queries are only generated for failures of out-of-the-box rules.”

Workarounds within current capabilities:

• Use Custom (SQL) rules to encode validations as Spark SQL predicates over a single dataset (row, filter, null expressions).
• Where SQL is too limited (for example, needing joins or more complex logic), use Custom rules with ADF expression language and functions, as long as the required functions are supported in the Purview subset.
• For regex-heavy validations, use the regex support in custom rules (including Java-based regex with back-quoted <regex> where applicable in ADF expressions).

The context indicates active known issues and limitations but does not document any specific roadmap or planned enhancement to allow arbitrary full SQL queries (with joins or DML) as data quality rules. Only the current predicate-based Spark SQL and ADF-expression-based custom rules are described.

References:

• Create data quality rules
• Data governance known issues