Is it possible to build a custom data quality rule that validates hierarchical relationships across two different tables?

Question

Is it possible to build a custom data quality rule that validates hierarchical relationships across two different tables?

Siddharth Chakraborty 20

I am trying to write a rule saying if TableA_Field1 contains 'PlantABC', TableB_Field2 should contain either 'PlantA', 'PlantB' or 'PlantC' where TableA-->TableB has a one-many relationship. How can this be solved using Purview DQ?

Accepted answer

0 additional answers

Your answer

Answer 1

Venkat Reddy Navari 1,270 Microsoft External Staff

Hi @Siddharth Chakraborty Yes, it's possible to create a custom data quality (DQ) rule in Microsoft Purview to validate hierarchical relationships across two different tables, though it requires a few workarounds since Purview’s out-of-the-box DQ capabilities are primarily column-level and table-level.

In your scenario, since TableA → TableB has a one-to-many relationship and the rule depends on cross-table logic, you can approach it as follows:

Use a Custom SQL Rule: If your data source supports it (like Azure SQL DB, Synapse, etc.), you can define a custom SQL query in your DQ rule that joins TableA and TableB based on the relationship and checks the logic. For example:
```
   SELECT B.*
   FROM TableA A
   JOIN TableB B ON A.Key = B.ForeignKey
   WHERE A.Field1 = 'PlantABC'
   AND B.Field2 NOT IN ('PlantA', 'PlantB', 'PlantC')
```
This query identifies the records violating your logic. In Purview, you can configure a custom DQ rule with this logic and define it as a "Row-level" validation.
Custom Rule in DQ Rule Set: Go to the Data Map → Rules → Add Rule Set → Custom Rule → SQL Rule (or Spark rule if using a Spark-based scan). Include the logic above and assign it to the dataset.
Validation: After applying the rule, run a scan with DQ enabled to evaluate the quality. The DQ results will show how many records pass/fail the rule.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Siddharth Chakraborty 20 Reputation points

2025-04-17T12:20:44.8933333+00:00

Thanks @Venkat Reddy Navari I am aligned with the logic stated above. Could you confirm if you have mentioned about adding it as a Custom Classification Rule in Point No.2?
Venkat Reddy Navari 1,270 Reputation points Microsoft External Staff

2025-04-17T14:35:35.3733333+00:00
@Siddharth Chakraborty the logic I mentioned in Point No.2 refers specifically to Custom Data Quality (DQ) Rules, not Custom Classification Rules.

Custom DQ Rules are used to validate data quality conditions, such as cross-table relationships or value checks, like in your PlantABC scenario.

Custom Classification Rules, on the other hand, are used for classifying sensitive data based on patterns or regex and are part of the data classification feature — not data quality.

So, in this case, you’ll want to define a Custom DQ Rule (e.g., a custom SQL rule) inside a DQ Rule Set, and assign it to the relevant assets during scan configuration.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Siddharth Chakraborty 20 Reputation points

2025-04-17T16:48:21.5433333+00:00

So basically referencing other tables could be done by preprocessing the data outside of Purview (e.g., in Synapse or Azure Data Factory) to create a unified view that includes data from both tables. And then we can use it for our DQ checks within Purview.

Is that the correct understanding?
Venkat Reddy Navari 1,270 Reputation points Microsoft External Staff

2025-04-18T11:09:56.5533333+00:00
@Siddharth Chakraborty Yes, your understanding is correct. Microsoft Purview Data Quality rules currently operate on a single dataset at a time, and do not support joins across multiple datasets or tables directly.

So, if your DQ checks require logic that spans multiple tables, the recommended approach is to:

Preprocess and combine the tables externally (e.g., in Azure Synapse, Azure Data Factory, or another ETL tool).

Register the resulting combined dataset in Purview.

Apply your Data Quality rules to that unified dataset.

This way, you can validate complex cross-table relationships while staying within the Purview DQ framework. I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Venkat Reddy Navari 1,270 Reputation points Microsoft External Staff

2025-04-21T09:13:20.1666667+00:00

@Siddharth Chakraborty Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

Is it possible to build a custom data quality rule that validates hierarchical relationships across two different tables?

0 additional answers

Your answer