If you don’t want to use evaluation parameters, here’s a short example of how you might validate ID columns. Let’s assume you have IDs in a table TableValues and you want to validate the IDs by ensuring that the ID values exist in the ID column of another table TableReferenceIDs.
- Write a query or view to bring the two ID columns together.
SELECT v.ID AS ValuesID, r.ID AS ReferenceID FROM TableValues v LEFT JOIN TableReferenceIDs r ON v.ID = r.ID
As written, this query will show all your IDs that you want to validate, and any IDs in the reference table that match. If there is no match, ReferenceID will be NULL.
- Validate the IDs by using the
expect_column_pair_values_to_be_equalexpectation against ValuesID and ReferenceID. Every row where they are not equal will be a row where the ID in the TableValues isn’t valid.
Note: You can be creative and accomplish the same thing using several approaches. For example, you could test that no NULLs exist in the ReferenceID column of this view and achieve the same results since a NULL value means that there was an ID in the TableValues that didn’t match with an ID in the TabeReferenceIDs. Or you could use INNER JOINs or RIGHT JOINS to accomplish different tests. Be creative with joins and expectations to accomplish your testing objective. Finally, you can also create a custom expectation or contribute a new expectation to the Great Expectations project to accomplish your testing objective.