[CT-793] [Feature] Testable _tmp tables in incremental models

[NiallRees]

Is this your first time opening an issue?

• I have read the expectations for open source contributors

Describe the Feature

Currently, it's only possible to apply dbt tests to the final table of an incremental model. There are circumstances where it would be useful to apply tests (both singular and generic) to the incremental _tmp table created by dbt in an incremental model. In large tables, it would drastically increase the speed and performance of tests by only tested new/updated data.

Describe alternatives you've considered

No response

Who will this benefit?

No response

Are you interested in contributing this feature?

No response

Anything else?

No response

[jtcohen6]

Thanks @NiallRees! Two reasons I can see for wanting this:

• Prevent the merge/upsert/overwrite if the test fails (lengthy discussion about this just now: Provide option to allow tests to be ran before a model's data is updated #5687)
• Save time & money by querying just the new data — it sounds like this is your main interest

I know folks have written (very tricky) custom materializations to accomplish both outcomes before. So long as the merge/upsert/overwrite logic is simple enough (and dbt really wants to keep it simple), there should be minimal or zero risk of the test passing on the "new" data (no dupes, no nulls, etc), and yet failing on the "final" table.

Out of curiosity, have you experimented with using the where config for generic tests? (It's not supported for singular tests, but I suppose it or something like it could be.) For instance, if you're always updating the last 1/3/7 days of data, could you limit the test query with a filter accordingly? That should be enough for Snowflake and BigQuery to prune partitions and run faster queries, though perhaps still slower than if the test ran against new data only.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.

[mrcool4]

Hi Team, Any update on this feature?
Addition of this feature would mitigate the necessity for a rollback should a test failure occur.