When rules break and we see the number of "Records" how is that number stored in the DQ Metastore?

Q: When rules break and we see the number of “Records” how is that number stored in the DQ Metastore?
A: Given the Collibra DQ screen shot below:

We can see there is a column marked “Records” which shows how many records broke for that given rule. In this case the number highlighted is: “33121.001”
I discussed this with Kirk Haslbeck, because I couldn’t find this Records number in the DQ Metastore. I was given a hint that this is a generated number based on the remainder of “.001” We agreed this is a code generated number based on the total number of records scanned multiplied by the percentage which broke. Thus the remainder and the lack of table column in the metastore.

BigDataBear

Posted 4 years ago · Edited 1 year ago·Last reply 3 years ago

2 comments

George Luft

·4 years ago · Edited

This query may help:

select
	ro.dataset,
	ro.rule_nm,
	ro.run_id,
	ro.score,
	ro.perc,
	ds.rc,
	ds.rc * ro.perc / 100 as records
from
	public.dataset_scan ds
inner join public.rule_output ro
on
	ds.dataset = ro.dataset
	and ds.run_id = ro.run_id;

George Luft

·3 years ago · Edited

Here is a modified version of this SQL:

select ds.dataset as DQ_Job_Name, ds.run_id as Run_Id, ds.rc as Row_Count,round(ds.rc * ro.perc / 100 ) as breaking_rows, ds.rc - round(ds.rc * ro.perc / 100 ) as passing_rows, ro.perc as percent_breaking, ro.rule_nm,  ro.rule_value,ro.rule_type, ds.updt_ts as dataset_updt_ts, ro.updt_ts as rule_updt_ts,(select dim_name from dq_dimension where dim_id=owr.dim_id) as DQ_Dimension
from rule_output ro
inner join dataset_scan ds on ds.dataset = ro.dataset and ds.run_id = ro.run_id
inner join owl_rule owr on owr.dataset = ro.dataset
where round(ds.rc * ro.perc / 100 ) <> 0