Rules

Different Types of Expectations

Please find the different types of possible expectations

Possible Row Data Quality Expectations

rule_description	category	tag	rule_expectation
Expect that the values in the column should not be null/empty	null_validation	completeness	[col_name] is not null
Ensure that the primary key values are unique and not duplicated	primary_key_validation	uniqueness	count(*) over(partition by [primary_key_or_combination_of_primary_key] order by 1)=1
Perform a thorough check to make sure that there are no duplicate values, if there are duplicates preserve one row into target	complete_duplicate_validation	uniqueness	row_number() over(partition by [all_the_column_in_dataset_b_ comma_separated] order by 1)=1
Verify that the date values are in the correct format	date_format_validation	validity	to_date([date_col_name], '[mention_expected_date_format]') is not null
Verify that the date values are in the correct format using regex	date_format_validation_with_regex	validity	[date_col_name] rlike '[regex_format_of_date]'
Expect column value is date parseable	expect_column_values_to_be_date_parseable	validity	try_cast([date_col_name] as date)
Verify values in a column to conform to a specified regular expression pattern	expect_column_values_to_match_regex	validity	[col_name] rlike '[regex_format]'
Verify values in a column to not conform to a specified regular expression pattern	expect_column_values_to_not_match_regex	validity	[col_name] not rlike '[regex_format]'
Verify values in a column to match regex in list	expect_column_values_to_match_regex_list	validity	[col_name] not rlike '[regex format1]' or [col_name] not rlike '[regex_format2]' or [col_name] not rlike '[regex_format3]'
Expect the values in a column to belong to a specified set	expect_column_values_to_be_in_set	accuracy	[col_name] in ([values_in_comma_separated])
Expect the values in a column not to belong to a specified set	expect_column_values_to_be_not_in_set	accuracy	[col_name] not in ([values_in_comma_separated])
Expect the values in a column to fall within a defined range	expect_column_values_to_be_in_range	accuracy	[col_name] between [min_threshold] and [max_threshold]
Expect the lengths of the values in a column to be within a specified range	expect_column_value_lengths_to_be_between	accuracy	length([col_name]) between [min_threshold] and [max_threshold]
Expect the lengths of the values in a column to be equal to a certain value	expect_column_value_lengths_to_be_equal	accuracy	length([col_name])=[threshold]
Expect values in the column to exceed a certain limit	expect_column_value_to_be_greater_than	accuracy	[col_name] > [threshold_value]
Expect values in the column not to exceed a certain limit	expect_column_value_to_be_lesser_than	accuracy	[col_name] < [threshold_value]
Expect values in the column to be equal to or exceed a certain limit	expect_column_value_greater_than_equal	accuracy	[col_name] >= [threshold_value]
Expect values in the column to be equal to or not exceed a certain limit	expect_column_value_lesser_than_equal	accuracy	[col_name] <= [threshold_value]
Expect values in column A to be greater than values in column B	expect_column_pair_values_A_to_be_greater_than_B	accuracy	[col_A] > [col_B]
Expect values in column A to be lesser than values in column B	expect_column_pair_values_A_to_be_lesser_than_B	accuracy	[col_A] < [col_B]
Expect values in column A to be greater than or equals to values in column B	expect_column_A_to_be_greater_than_B	accuracy	[col_A] >= [col_B]
Expect values in column A to be lesser than or equals to values in column B	expect_column_A_to_be_lesser_than_or_equals_B	accuracy	[col_A] <= [col_B]
Expect the sum of values across multiple columns to be equal to a certain value	expect_multicolumn_sum_to_equal	accuracy	[col_1] + [col_2] + [col_3] = [threshold_value]
Expect sum of values in each category equals certain value	expect_sum_of_value_in_subset_equal	accuracy	sum([col_name]) over(partition by [category_col] order by 1)
Expect count of values in each category equals certain value	expect_count_of_value_in_subset_equal	accuracy	count(*) over(partition by [category_col] order by 1)
Expect distinct value in each category exceeds certain range	expect_distinct_value_in_subset_exceeds	accuracy	count(distinct [col_name]) over(partition by [category_col] order by 1)

Possible Aggregation Data Quality Expectations

rule_description	rule_type	tag	rule_expectation
Expect distinct values in a column that are present in a given list	expect_column_distinct_values_to_be_in_set	accuracy	array_intersect(collect_list(distinct [col_name]), Array($compare_values_string)) = Array($compare_values_string)
Expect the mean value of a column to fall within a specified range	expect_column_mean_to_be_between	consistency	avg([col_name]) between [lower_bound] and [upper_bound]
Expect the median value of a column to be within a certain range	expect_column_median_to_be_between	consistency	percentile_approx([column_name], 0.5) between [lower_bound] and [upper_bound]
Expect the standard deviation of a column's values to fall within a specified range	expect_column_stdev_to_be_between	consistency	stddev([col_name]) between [lower_bound] and [upper_bound]
Expect the count of unique values in a column to fall within a specified range	expect_column_unique_value_count_to_be_between	accuracy	count(distinct [col_name]) between [lower_bound] and [upper_bound]
Expect the maximum value in a column to fall within a specified range	expect_column_max_to_be_between	accuracy	max([col_name]) between [lower_bound] and [upper_bound]
Expect the minimum value in a column fall within a specified range	expect_column_sum_to_be_between	accuracy	min([col_name]) between [lower_bound] and [upper_bound]
Expect row count of the dataset fall within certain range	expect_row_count_to_be_between	accuracy	count(*) between [lower_bound] and [upper_bound]

Possible Query Data Quality Expectations

rule_description	rule_type	tag	rule_expectation
Expect distinct values in a column must be greater than threshold value	expect_column_distinct_values_greater_than_threshold_value	accuracy	(select count(distinct [col_name]) from [table_name]) > [threshold_value]
Expect count between two table or view must be same	expect_count_between_two_table_same	consistency	(select count(*) from [table_a]) = (select count(*) from [table_b])
Expect the median value of a column to be within a certain range	expect_column_median_to_be_between	consistency	(select percentile_approx([column_name], 0.5) from [table_name]) between [lower_bound] and [upper_bound]
Expect the standard deviation of a column's values to fall within a specified range	expect_column_stdev_to_be_between	consistency	(select stddev([col_name]) from [table_name]) between [lower_bound] and [upper_bound]
Expect the count of unique values in a column to fall within a specified range	expect_column_unique_value_count_to_be_between	accuracy	(select count(distinct [col_name]) from [table_name]) between [lower_bound] and [upper_bound]
Expect the maximum value in a column to fall within a specified range	expect_column_max_to_be_between	accuracy	(select max([col_name]) from [table_name]) between [lower_bound] and [upper_bound]
Expect the minimum value in a column fall within a specified range	expect_column_min_to_be_between	accuracy	(select min([col_name]) from [table_name]) between [lower_bound] and [upper_bound]
Expect referential integrity	expect_referential_integrity_between_two_table_should_be_less_than_100	accuracy	with refrentail_check as ( select * from [table_a] left join [table_b] on [condition] where [table_b.column] is null) select count(*) from refrentail_check) < 100