[Feature] new configuration to run tests on only the "new" data for snapshots and incremental models

[graciegoheen]

Describe the feature

A more general solution for #10236 and #10864 and dbt-labs/dbt-snowflake#1198

Folks want to be able to test just their "new" data before it's inserted into their existing table. This is relevant for:

• snapshots
• incremental models

For example, you may want to test that your unique_key is actually unique for the "new" data. If that test fails don't insert the "new" data into the existing table.

We should create a new data test configuration to allow you to configure a given data to only run on the "new" data.

models:
  - name: my_model
    columns:
      - name: id
        data_tests:
          - unique:
              new_records_only: true  # name/spec TBD

Acceptance criteria:

• you can set this config on a data test for an incremental model
• you can set this config on a data test for a snapshot
• when set, the data test will only run on "new" data before insert/merge
• if data test fails, the subsequent insert/merge will be skipped

[QuentinCoviaux]

Reminds of this talk from Coalesce on how the team at Lyst did this by overwriting the incremental macro.

For the spec, I'm thinking you'd probably want to be able to test the new data in the tmp table, but also probably the final table.

For unique checks, the way I think about it is that even if you assert that your new record are in fact unique, that uniqueness might not be true when compiled with old records.

Super excited to have this for other tests though!

[NaomiJohnson]

Thanks @QuentinCoviaux for sharing my talk

@graciegoheen we have built a solution in house that will test necessary data while ensuring the primary key testing is always 100% accurate, in Snowflake
The implementation varies depending on whether the incremental model:

1. does a merge or delete+insert where the primary key is part of the unique_key
2. or, does a delete+insert where the PK is not part of the unique_key or it's just an insert

It's also been built so that the user only has to add unique_test_key (i.e. the primary key) in the model config, if the unique_key isn't the primary key
So, it's all automatic

Would be happy to chat through with you

[jasonqng]

Was running into the same thing and stumbled on this thread. Not sure if this is a new feature since this thread was opened, a teammate pointed me to where in data tests (doc):

The where condition is templated into the test query by replacing the resource reference with a subquery.

E.g.

version: 2

models:
  - name: large_table
    columns:
      - name: my_column
        tests:
          - accepted_values:
              values: ["a", "b", "c"]
              config:
                where: "date_column = current_date"
      - name: other_column
        tests:
          - not_null:
              where: "date_column < current_date"