Great Expectations - Data Quality limitation

[nayanex]

great expectations can't deal with checks on two or more tables.

like say date of column in table a should be greater than date of column in table B where table A and table B can be joined using an identifier

classic example is like say loan and loanpart where loan holds the info abt the mortoages and loanpart holds the info abt mortages payments. so the date in the loanpart should always be great than date in the loan as mortage payment can only be done for an active mortagage.

what great expectations can is check date if the date in the loan table is less than or greater than UTCDATE not compare dates between tables like mentioned above.

What approach would you suggest to overcome this limitation?

I only tried to use Great Expectations

[rachhouse]

Hi @nayanex, thanks for your question!

You are correct that the majority of GX Expectations are built to run on a single Data Asset. To use GX to test columns in separate tables, you have two possible options:

1. Transform the data outside of your GX workflow so that it can be treated as a single Table Asset
With this approach, you would first create a SQL view based off a query that joins table A and table B on the shared identifier column. You would then create a GX Table Asset using the SQL view (rather than the original table A and table B), and run the Expectations of interest, for example: expect_column_pair_values_a_to_be_greater_than_b.

2. Create a Custom Expectation that implements your desired check
GX supports the ability to create Custom Expectations - you could create a Custom Query Expectation to implement the behavior you are looking for. There are currently two contributed custom Query Expectations that might provide a helpful reference if you decide to build your own:

• expect_column_values_to_be_present_in_other_table
• expect_queried_column_values_to_exist_in_second_table_column

Our Discourse forum is a great place to reach out if you have any future questions on general GX usage.