Expect empty resultset

[darian-heede]

Is there a way to expect no return values, i.e. an empty resultset? Leaving the dbt_unit_testing.expect() call empty will yield a can't execute an empty query error.

It would be great to be able to call dbt_unit_testing.expect_nothing() for such cases.

Another solution would be to be able to specify the expected row count to be returned, which might also be useful for other use cases as well and could solve this issue by expecting 0 rows returned.

[psousa50]

Hi @darian-heede, we can implement that; it makes perfect sense.

Meanwhile, you can use something like this to test an empty result set:

  {% call dbt_unit_testing.mock_ref ('some_model') %}
    select 1 where false
  {% endcall %}

Let me know if it works for you.

Thank you!

[darian-heede]

Hi @psousa50 , thank you for your reply!

Creating an empty mock ref makes sense and will work, but how do you use it to expect an empty resultset? Using

{% call dbt_unit_testing.expect() %}
  SELECT 1 WHERE FALSE
{% endcall %}

will return the can't execute an empty query error.

[real-soothsayer]

For me, it works when you specify the column names, even if the query will return empty

{% call dbt_unit_testing.expect() %}
  SELECT '' as col1, '' as col2, ... WHERE FALSE
{% endcall %}

ps: I'm using dbt with automate-dv package

[psousa50]

Please take a look at the latest release (0.4.0) and check if it fixes this issue.

Thank you!

[darian-heede]

Hi @psousa50, I tested dbt_unit_testing.expect_no_rows() with v0.4.0 and still got the error can't execute an empty query. The issue seems to be fixed in v0.4.12. This is exactly what I need 😃 thank you very much for adding this feature!

[SheldonNunes]

I got the following error when trying to use WHERE FALSE solution:
Query without FROM clause cannot have a WHERE clause

For those who are having this issue but haven't been able to update versions I found a workaround:
SELECT 1 FROM (SELECT 1) WHERE false

You may have to replace SELECT 1 with the actual column names in order for it to configure the expected result correctly