Is it possible for a Custom SQL Data Quality Rule to be written using subqueries?

Question

Is it possible for a Custom SQL Data Quality Rule to be written using subqueries instead of joins (which are not available to use)?

Eg. I want measure the completeness of an email address where the customer has opted in to receive email correspondence.

Where the mail address is held on a Customer table and the opt in flag is on preferences table

Answer 1

Hi Luke Pettigrew,
The statement that joins and subqueries are not supported in Microsoft Purview Unified Catalog Custom SQL Data Quality rules is correct.

This aligns with current product limitations, where custom rules are evaluated as row-level expressions on a single data asset only, and do not support cross-table operations such as joins or subqueries.

To meet your requirement, you need to pre-join the Customer and Preferences data into a single table or view, and then apply the data quality rule on that combined dataset.

Reference Links : https://learn.microsoft.com/purview/unified-catalog-data-quality-rules#custom-rules

Hope this helps. If you have any follow-up questions, please let me know. I would be happy to help.

Please do not forget to "Accept Answer" and "up-vote" wherever the information provided helps you, as this can be beneficial to other community members.

Answer 2

Yes. Custom SQL Data Quality Rules in Microsoft Purview can use subqueries.

Custom rules are authored as Spark SQL predicates, and Spark SQL fully supports subqueries, including in WHERE and SELECT clauses. The documentation explicitly shows a custom rule using a subquery, for example:

distance_km > 
( 
  SELECT min(distance_km) 
  FROM Tripdata t  
  WHERE t.payment_type = payment_type
)

This pattern can be adapted to measure completeness of an email address for customers who have opted in, even when joins are not directly available in the rule UI, by expressing the logic as a subquery predicate.

When doing so, it is important to avoid ambiguous column references and column shadowing. If a column name exists both in the outer query and the subquery, qualify or alias columns so the outer reference is unambiguous, as shown in the corrected example from the documentation:

distance_km > 
( 
  SELECT min(u.distance_km) 
  FROM ( 
    SELECT distance_km, payment_type AS pt 
    FROM Tripdata 
  ) u 
  WHERE u.pt = payment_type   -- binds to outer payment_type
)

The same technique can be used for an email/opt‑in scenario: place the appropriate subquery in the rule’s row expression, and carefully qualify or alias columns to ensure the predicate uses the intended outer row values.

---

References:

• Create data quality rules
• Subqueries (SQL Server)