Incremental model testing results in an error stating incremental model relation does not exist

[Rutvijjs]

Hello,

I'm trying to test an incremental model in our project but while running the tests i'm getting a "relation incremental_model__ does not exist" error. Has anyone faced this?
dbt version is 0.3.2 (but 0.3.3 also results in the same error without the trailing underscores)

Here is my test:

{{ config(tags=['unit-test']) }}

{% call dbt_unit_testing.test('incremental_model', 'check_incremental_logic', options = {"run_as_incremental": "True"})%}
    {% call dbt_unit_testing.mock_ref("stg", "stg__model", options = {"include_missing_columns": true}) %}
        select 1 as id,'2023-08-25 10:23:51.131'::timestamp as date , 10 as value
        union all
        select 2 as id,'2023-08-25 10:23:51.131'::timestamp as date , 12 as value
        union all
        select 3 as id,'2023-08-25 10:23:51.150'::timestamp as date , 44 as value
        union all
        select 4 as id,'2023-08-25 10:23:51.152'::timestamp as date, null as value
    {% endcall %}

    {% call dbt_unit_testing.mock_ref("incremental_model", options = {"include_missing_columns": true}) %}
        select 1 as id,'2023-08-25 10:23:51.131'::timestamp as date , 10 as value
        union all
        select 2 as id,'2023-08-25 10:23:51.131'::timestamp as date , 12 as value
    {% endcall %}
    
    {% call dbt_unit_testing.expect() %}
        select 3 as id,'2023-08-25 10:23:51.150'::timestamp as date , 44 as value
        union all
        select 4 as id,'2023-08-25 10:23:51.152'::timestamp as date, null as value
    {% endcall %}

{% endcall %}

model is as follows:

{{
    config(
        materialized = 'incremental',
        unique_key = 'id'
    )
}}

select
    id,
    date,
    value
from
    {{ dbt_unit_testing.ref("stg", 'stg_model') }}
{% if dbt_unit_testing.is_incremental() -%}
    where
        date > coalesce((
            select max(date) from {{ this }}
        ), '    2020-01-01')
{% endif %}

error is as follows:

Database Error in test test_check_incremental_logic (tests/test_check_incremental_logic.sql) 20:23:56 relation "incremental_model__" does not exist 20:23:56 LINE 65: select max(modified_on) from "incremental_model__"

[MarcinZegar]

Same issue here

[Rutvijjs]

Bumping this issue as I'm still having this issue

[psousa50]

Hi all,

There is actually a problem with the usage of include_missing_columns in incremental modules. It will be fixed in the next release, probably during this week.

Thank you very much for your feedback 👍

[psousa50]

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

Thank you!

[Rutvijjs]

Thanks @psousa50, The test runs now but we are now seeing that no matter if we put "run_as_incremental" in the options or not. It only shows the results of the model as it would in a full refresh test.

When running the test using run as incremental, our expect should only include what is added in the incremental step (rather than expecting what is in the full table) as per the docs. When running the test, it fails with a mismatch error showing that the model results has more than just what we expect. When re running the test with the expect including all the rows the table has, it passes (even tho we're running the test with the option "run as incremental"). Is this expected or am i doing something wrong?

[psousa50]

Hi @Rutvijjs , that's odd, the tests are running fine for that option. Could you please post your code here, so that I can take a look?

Thanks

[Rutvijjs]

@psousa50 its the same code as in the initial post here.

Test:

{{ config(tags=['unit-test']) }}

{% call dbt_unit_testing.test('incremental_model', 'check_incremental_logic', options = {"run_as_incremental": "True"})%}
    {% call dbt_unit_testing.mock_ref("stg", "stg__model", options = {"include_missing_columns": true}) %}
        select 1 as id,'2023-08-25 10:23:51.131'::timestamp as date , 10 as value
        union all
        select 2 as id,'2023-08-25 10:23:51.131'::timestamp as date , 12 as value
        union all
        select 3 as id,'2023-08-25 10:23:51.150'::timestamp as date , 44 as value
        union all
        select 4 as id,'2023-08-25 10:23:51.152'::timestamp as date, null as value
    {% endcall %}

    {% call dbt_unit_testing.mock_ref("incremental_model", options = {"include_missing_columns": true}) %}
        select 1 as id,'2023-08-25 10:23:51.131'::timestamp as date , 10 as value
        union all
        select 2 as id,'2023-08-25 10:23:51.131'::timestamp as date , 12 as value
    {% endcall %}
    
    {% call dbt_unit_testing.expect() %}
        select 3 as id,'2023-08-25 10:23:51.150'::timestamp as date , 44 as value
        union all
        select 4 as id,'2023-08-25 10:23:51.152'::timestamp as date, null as value
    {% endcall %}

{% endcall %}

Model:

{{
    config(
        materialized = 'incremental',
        unique_key = 'id'
    )
}}

select
    id,
    date,
    value
from
    {{ dbt_unit_testing.ref("stg", 'stg_model') }}
{% if dbt_unit_testing.is_incremental() -%}
    where
        date > coalesce((
            select max(date) from {{ this }}
        ), '    2020-01-01')
{% endif %}

[psousa50]

Hi @Rutvijjs, I should have realised that the code ia the same 🤦‍♂️, sorry for that.
I run your code and it worked fine ( it works on my computer 😂)
Could you please run your test with '--vars "verbose: true" and paste here the output?

Thank you

[Rutvijjs]

Hi @psousa50 sorry, that was my mistake. I missed adding the dbt_unit_testing. in front of the incremental call in the model in my code. It looks good now. Thank you for the help