[FEATURE] Provide Generic Tests with dbtvault for DataVault 2.0 specific use-cases

[DVAlexHiggs]

Testing is vital in any Data Warehouse.

Here's what we can do today with out-of-the-box dbt (and dbt-utils) tests:

• We can perform many basic tests such as the Hash key being Unique and Not Null in all of our tables.

• We can test that there are no orphaned records between hubs, links and satellites (i.e. test to make sure we don't have any satellite records without a corresponding hub record) This would use the relationships_where dbt_utils test.

• Many, many more, including column type checks between layers using custom tests

• Other tests which make use of dbt-expectations

These are great and a good starting point, however there are a number of other tests we have experience doing in clients which we have built specifically for certain cases and purposes.

We would like to add these tests to dbtvault as standard, so that users can make use of them in their own projects. This would be hugely beneficial for many reasons!

These would be offered as generic tests that ship with dbtvault and which users could configure and apply to the models which use dbtvault macros.

This is a good example of a custom test we could implement in dbtvault

AB#5375

[saqib-ali]

Test Ideas:

1. Automated check to confirm that all the columns (except for BKs) in the SAT are part of the HASHDIFF. There should be except clause to exclude fields that should not be part of the HASHDIFF e.g. _FIVETRAN_SYNC, LOAD_DATE APPLIED_DATE RECORD_SOURCE.
2. A duplicate record with same HASHKEY + LOAD_DATE + HASHDIFF should not be inserted into the SAT.