How to identify the rules that have been applied to rows that have NULL values for _errors and _warning columns

[csukel]

We have recently started using the DQX library which is very intuitive and helpful for building our data quality checks on databricks. Based on the documentation when a record fails for a DQ rule (and also based on the given criticality) the _errors and/or _warnings columns are being populated with the corresponding DQ Rules that fail.

Is it possible to also identify/label the rows that are not failing for a set of rules that have been applied. If yes can you please provide me some guidance on how we can achieve this through the DQX library ?

[hski-bayer]

I am relatively new to DQX, and others might have a more suitable answer, but the new column for errors and warnings actually contains a JSON array object with information about each rule which failed.

Guess you can use JSON SQL query if you are looking for a specific check. See here for some JSON SQL examples https://docs.databricks.com/aws/en/semi-structured/json.

For the dashboard we also created an additional dataset with the check results, which has one row per check error or warning. We are using "explode" function https://sparkbyexamples.com/pyspark/pyspark-explode-array-and-map-columns-to-rows/

[csukel]

Hi @hski-bayer ,

We are interested on the rows that haven't failed to be labeled as successful or lets say better partially succeeded (for at least one DQ Rule). Like having a new column for example _success and there it should list the DQ Rules that have been applied to the record and it passed them.

[mwojtyczka]

If you apply checks without a split, you can get valid rows by filtering records that have _warning or _error column not null. Vice versa to get invalid records you can use: select * from output where _warning is not None, select * from output where _error is not None. If you quarantine, then _errors and _warning column will only be in the quarantine dataframe.
Details on how to access the details here:
https://databrickslabs.github.io/dqx/docs/guide/quality_checks_apply/#quality-checking-results

We are also adding summary table, where all summary stats from checks from all table will be centralized in a single table.

[csukel]

Hi @mwojtyczka ,

Thanks for your response. I have already thought about it but was looking into a library capability to mark those records as successful under the list of checks that have been passed without any warnings and errors... Don't forget that there is a possibility that a rule may have a filter property set which means we also need to take into consideartion that factor while labeling records as passed for a list of rules.

[mwojtyczka]

btw. Summary metrics has been released today: https://databrickslabs.github.io/dqx/docs/guide/summary_metrics/