dbt testsql capability to test whether models have valid sql in both incremental and full-refresh modes.

[djayatillake]

Describe the feature

dbt testsql capability to test whether models have valid sql in both incremental and full-refresh modes.

Describe alternatives you've considered

right now as part of testing during development I start running the model in incremental mode and full-refresh mode on a small warehouse and then hit ctrl-C to stop the model running both times and rollback any transactions

Additional context

the method above isn't great as it's very manual and you can only test one model at a time. Also if a model runs too fast then it can easily commit before you can cancel the run!

I would imagine that it would work as follows:

dbt testsql -m model1 model2

and it will do a 10/20 second run (or other way of establishing the model hasn't thrown a db error) and rollback for each model in both incremental and full-refresh modes, because it automatically rolls back it doesn't matter if the model completes or not.

Who will this benefit?

This will benefit anyone who is trying to test new models or model changes.

[drewbanin]

Hey @djayatillake - cool suggestion! I don't think we'd want to implement exactly this, but I do like where your head is at. One of the challenges here is that dbt can't generate all of the possible permutations of a model -- if you did something like:

select * from {{ ref(model) }}

{% if modules.datetime.datetime.now().isoweekday() == 4 %}
where 1 / 0
{% endif %}

this models would fail to build.... but only on Thursdays! As such, it's not really possible or desirable for dbt to try to make a claim like "this model's code passes regardless of the supplied configs (eg. if --full-refresh is present or not).

Instead, what do you think of something like #401? Now that Snowflake has explain capabilities, I think it would be awesome if we could do something like:

dbt run --explain -m model1 model2

This would run an explain plan on the specified models. If they contain syntax errors or invalid table references, then the explain plan should fail right away. Note that this would not help in cases like:

select 1 / 0

as it's a valid SQL statement that would encounter a runtime error.

I'm going to close this one out with a wontfix label as I don't imagine making the code change you've described exactly here, but I like the idea and I'm keep to discuss further in the comments below! Let me know what you think