The problem I’m having
I want to know if there is a way of defining a test for all materialized='view' models at once, without having to specify a test for each one of them. Because we want all of those views to be perform that testno matter what.
The context of why I’m trying to do this
In my company, we use dbt with Redshift a lot and we have thousands of models. A huge majority of them being views. Since views are just created (and not queried), we get that the model has been created, but when users go and try to query them, they fail for a missing column. This is clearly expected.
I know, that the test can_be_queried can be added to the model. We tend to do that for a majority of models, however, we have also a big set of users and they often forget to do so, giving them the false impression that their models worked and then having to create a new PR just to add the test + fixes. We want them to not have to worry about that, and still see if their views work properly after the checks that we run on production before merging, as well as if they break in the future due to source tables changing.
What I’ve already tried
I already tried something like this:
models:
project_name:
+tests:
- can_be_queried
Which I wasn’t expecting to work, and it didn’t. I tried to hack my way through it with the code below, which actually worked when creating the views, but not when doing dbt test, which I expected also to not work because I know post_hooks are only executed during dbt run.
Is there a way to do this? Or a possible workaround for it? Would it be a possible feature to implement in future versions?
models:
+post_hook:
- "{% if model.config.materialized == 'view' %} select * from {{ this }} limit 0 {% else %} -- Not necessary to test non-view models {%- endif -%}"