[Bug] Runtime Error in unit_test when using versioned models #10623

marcilj · 2024-08-28T15:53:24Z

Is this a new bug in dbt-core?

I believe this is a new bug in dbt-core
I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

The problem I'm having

DBT Unit Test is not able to find the version 2 of my model.

Let's take an example of a model I'm trying to test.

with

source as (

    select
        *
    from {{ ref('model') }}

)

, model_with_version as (

    select
        col1
        , col2

    from {{ ref('model_with_version', v=2) }}

)

select * from source

(This is an example, I know model_with_version is not selected in any way in this query)

With this unit test

unit_tests:
  - name: filter_test
    model: my_model_name
    overrides:
      macros:
        is_incremental: false
    given:
      - input: ref('model_with_version')
        rows:
          - {col1: test, col2: test2}
          - {col1: test2, col2: test2}
    expect:
      rows:
          - {col1: test, col2: test2}

If I run the command dbt test --select "my_model_name,test_type:unit" dbt will return

  Unit_Test 'filter_test` depends on a node named `model_with_version` with version '2' which was not found.

I've make sure the version 2 exist.
I can easily run dbt run -m model_with_version.v2 and it run successfully.

Note that the latest version is currently version 1, in my YAML file.

I've also tried to add the pin version to the ref in the unit test yml file.
Like this - input: ref('model_with_version', v=2)
But ended up with this error.

  An error occurred during execution of unit test 'filter_test'. There may be an error in the unit test definition: check the data types.
   Database Error
    002003 (42S02): SQL compilation error:
    Object '__DBT__CTE__MODEL_WITH_VERSION_V2' does not exist or not authorized.

The context of why I'm trying to do this

I'm trying to configure a unit test for a model that uses DBT version.

What I've already tried

Configuring the models version in the input.
Trying without version

Some example code or error messages

  Unit_Test 'filter_test` depends on a node named `model_with_version` with version '2' which was not found.

  An error occurred during execution of unit test 'filter_test'. There may be an error in the unit test definition: check the data types.
   Database Error
    002003 (42S02): SQL compilation error:
    Object '__DBT__CTE__MODEL_WITH_VERSION_V2' does not exist or not authorized.

More Informations

If I configure the input as - input: ref('model_with_version') then in the query I can see that the query generate the static inputs

__dbt__cte__model_with_version

but my code want to select from

    select
        address
        , validator_commission_rate

    from __dbt__cte__model_with_version_v2

Which fails.

Expected Behavior

I shouldn't receive any errors as the __DBT__CTE__STG_FIGMENT_VALIDATOR_REPOSITORY_V2 CTE should be created instead of __DBT__CTE__STG_FIGMENT_VALIDATOR_REPOSITORY

Steps To Reproduce

Create 2 version of a single models.
In my scenario the version 1 is the latest_version.
In my scenario v1 has an alias with the same name, but WITHOUT the version ex alias:model_with_version. (not alias:model_with_version_v1)
Version 2 is called model_with_version_v2.
Create a yml for the unit_tests.
add the required inputs. In my situation I have 2. (One without version and one referencing version 2 of my model)
The input is configure like this - input: ref('model_with_version', v='2')
This will generate the error

  An error occurred during execution of unit test 'my_test'. There may be an error in the unit test definition: check the data types.
   Database Error
    002003 (42S02): SQL compilation error:
    Object '__DBT__CTE__MODEL_WITH_VERSION_V2' does not exist or not authorized.

The reason behind this error, when you look at the SQL query that was runned is that the CTE that DBT creates to inject the inputs values is name __DBT__CTE__MODEL_WITH_VERSION, but the one in the actual query that select * from has the version number after it __DBT__CTE__MODEL_WITH_VERSION_V2.
Since select * from __DBT__CTE__MODEL_WITH_VERSION_V2 doesn't exist because the CTE that was created is called __DBT__CTE__MODEL_WITH_VERSION this break the test.
I've tried with DBT 1.8.0 and 1.8.5 and got the same error.

Relevant log output

An error occurred during execution of unit test 'my_test'. There may be an error in the unit test definition: check the data types.
   Database Error
    002003 (42S02): SQL compilation error:
    Object '__DBT__CTE__MODEL_WITH_VERSION_V2' does not exist or not authorized.

Environment

- OS: MacOS (Intel) Sonoma 14.6.1. 
- Python:Python 3.9.19
- dbt:1.8.0 and 1.8.5

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

The text was updated successfully, but these errors were encountered:

dbeatty10 · 2024-08-29T00:32:43Z

Thanks for reaching out @marcilj !

Does this cover the scenario that you're describing? #10528 (comment)

marcilj · 2024-08-29T15:19:58Z

Yes @dbeatty10 I think the example you provided covered the scenario I'm describing except that in my scenario the latest_version: 1. But I tried changing it and got the same error.

There seems to be a little difference from what I'm experiencing vs what's describe in the issue #10528.

The error I get is linked to the generated SQL fixture code from the input which doesn't include the version prefix _V2 when generating the inputs values, but does include the version prefix in the select statement.

with

 __dbt__cte__my_model as ( -- (Doesn't include V2)

-- Fixture for __dbt__cte__my_model

 )
 , my_model_cte as (

    select
      col1

    from __dbt__cte__my_model_v2 -- (Inlcudes v2)

)

Hope that helps.

Thank you very much for the quick response btw.

dbeatty10 · 2024-08-29T17:05:00Z

Thanks @marcilj, that does help 👍

See below for a slightly simplified reproducible example ("reprex"). Could you try it out and verify that it replicates the issue you are seeing?

And then can you try adding - input: ref('model_with_version', v=2) to models/_unit_tests.yml? Specifying the version worked for me in this particular example, so I'm curious if it works for you or not.

Reprex

Create all of these files:

models/_unit_tests.yml

unit_tests:
  - name: filter_test
    model: my_model_name
    given:
      - input: ref('model_with_version', v=2)
        rows:
          - {id: 2}
    expect:
        rows:
          - {id: 2}

models/_models.yml

models:
  - name: model_with_version
    latest_version: 1
    versions:
      - v: 1
        columns:
          - include: all
            exclude: [added_column]
      - v: 2
        columns:
          - include: all

models/model_with_version_v1.sql

select 1 as id

models/model_with_version_v2.sql

select 1 as id, 2 as added_column

models/my_model_name.sql

select *
from {{ ref('model_with_version', v=2) }}

Then run these commands:

dbt run --empty
dbt test -s filter_test

See this error output:

$ dbt test -s filter_test
17:03:52  Running with dbt=1.8.0
17:03:52  Registered adapter: duckdb=1.8.3
17:03:53  Found 3 models, 410 macros, 1 unit test
17:03:53  
17:03:53  Concurrency: 1 threads (target='dev')
17:03:53  
17:03:53  1 of 1 START unit_test my_model_name::filter_test .............................. [RUN]
17:03:53  While compiling 'filter_test':
Found an unpinned reference to versioned model 'model_with_version' in project 'my_project'.
Resolving to latest version: model_with_version.v1
A prerelease version 2 is available. It has not yet been marked 'latest' by its maintainer.
When that happens, this reference will resolve to model_with_version.v2 instead.

  Try out v2: {{ ref('my_project', 'model_with_version', v='2') }}
  Pin to  v1: {{ ref('my_project', 'model_with_version', v='1') }}

17:03:53  1 of 1 ERROR my_model_name::filter_test ........................................ [ERROR in 0.04s]
17:03:53  
17:03:53  Finished running 1 unit test in 0 hours 0 minutes and 0.24 seconds (0.24s).
17:03:53  
17:03:53  Completed with 1 error and 0 warnings:
17:03:53  
17:03:53    Compilation Error in unit_test filter_test (models/_unit_tests.yml)
  Unit_Test 'unit_test.my_project.my_model_name.filter_test' (models/_unit_tests.yml) depends on a node named 'model_with_version' with version '2' which was not found
17:03:53  
17:03:53  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

marcilj · 2024-08-30T13:54:50Z

Hey @dbeatty10 I've reproduce this example locally, and here's what I got

Example with `- input: ref('model_with_version', v=2)`

2212420: my_dbt (main*) $ dbt test -s filter_test
13:42:09  Running with dbt=1.8.0
13:42:10  Registered adapter: snowflake=1.8.3
13:42:13  Found 723 models, 203 data tests, 269 sources, 923 macros, 2 groups, 1 unit test
13:42:13  
13:42:36  Concurrency: 1 threads (target='dev')
13:42:36  
13:42:36  1 of 1 START unit_test my_model_name::filter_test .............................. [RUN]
13:42:36  1 of 1 ERROR my_model_name::filter_test ........................................ [ERROR in 0.48s]
13:42:36  
13:42:36  Finished running 1 unit test in 0 hours 0 minutes and 23.83 seconds (23.83s).
13:42:37  
13:42:37  Completed with 1 error and 0 warnings:
13:42:37  
13:42:37    Runtime Error in unit_test filter_test (models/unit_test.yml)
  An error occurred during execution of unit test 'filter_test'. There may be an error in the unit test definition: check the data types.
   Database Error
    002003 (42S02): SQL compilation error:
    Object '__DBT__CTE__MODEL_WITH_VERSION_V2' does not exist or not authorized.
13:42:37  
13:42:37  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Also here's the query on Snowflake that was generated and failed.

create or replace temporary table DBT_DB.DBT_JACOBMARCIL_None.filter_test__dbt_tmp
         as
        (select * from (
        with __dbt__cte__model_with_version as (

-- Fixture for model_with_version
select 
    
        try_cast('2' as NUMBER(1,0))
     as id, try_cast(null as NUMBER(1,0)) as added_column
) select *
from __dbt__cte__model_with_version_v2
    ) as __dbt_sbq
    where false
    limit 0

        );

Example with `- input: ref('model_with_version')`

my_dbt (main*) $ dbt test -s filter_test
13:46:53  Running with dbt=1.8.0
13:46:54  Registered adapter: snowflake=1.8.3
13:46:57  Found 723 models, 203 data tests, 269 sources, 923 macros, 2 groups, 1 unit test
13:46:57  
13:47:23  Concurrency: 1 threads (target='dev')
13:47:23  
13:47:23  1 of 1 START unit_test my_model_name::filter_test .............................. [RUN]
13:47:23  While compiling 'filter_test':
Found an unpinned reference to versioned model 'model_with_version' in project 'data_team_dbt'.
Resolving to latest version: model_with_version.v1
A prerelease version 2 is available. It has not yet been marked 'latest' by its maintainer.
When that happens, this reference will resolve to model_with_version.v2 instead.

  Try out v2: {{ ref('data_team_dbt', 'model_with_version', v='2') }}
  Pin to  v1: {{ ref('data_team_dbt', 'model_with_version', v='1') }}

13:47:23  1 of 1 ERROR my_model_name::filter_test ........................................ [ERROR in 0.05s]
13:47:23  
13:47:23  Finished running 1 unit test in 0 hours 0 minutes and 25.45 seconds (25.45s).
13:47:24  
13:47:24  Completed with 1 error and 0 warnings:
13:47:24  
13:47:24    Compilation Error in unit_test filter_test (models/unit_test.yml)
  Unit_Test 'unit_test.data_team_dbt.my_model_name.filter_test' (models/agoric/core/rewards/unit_test.yml) depends on a node named 'model_with_version' with version '2' which was not found
13:47:24  
13:47:24  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Final test

I've also tied to change all reference from ref('model_with_version', v=2) to ref('model_with_version', v='2') but got the __DBT__CTE__MODEL_WITH_VERSION_V2 does not exist or not authorized.

More info

Tested with

dbt --version
Core:
  - installed: 1.8.0
  - latest:    1.8.6 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - snowflake: 1.8.3 - Up to date!

marcilj · 2024-08-30T14:06:56Z

Update.

Did upgrade to

dbt --version
Core:
  - installed: 1.8.6
  - latest:    1.8.6 - Up to date!

Plugins:
  - snowflake: 1.8.3 - Up to date!

And got a successful runs for inputs

- input: ref('model_with_version', v='2')
- input: ref('model_with_version', v=2)

and
- input: ref('model_with_version') gives me the following error, which I think can be expected from DBT.

dbt test -s filter_test
14:00:44  Running with dbt=1.8.6
14:00:45  Registered adapter: snowflake=1.8.3
14:00:48  Found 723 models, 203 data tests, 269 sources, 923 macros, 2 groups, 1 unit test
14:00:48  
14:01:23  Concurrency: 1 threads (target='dev')
14:01:23  
14:01:23  1 of 1 START unit_test my_model_name::filter_test .............................. [RUN]
14:01:23  While compiling 'filter_test':
Found an unpinned reference to versioned model 'model_with_version' in project 'data_team_dbt'.
Resolving to latest version: model_with_version.v1
A prerelease version 2 is available. It has not yet been marked 'latest' by its maintainer.
When that happens, this reference will resolve to model_with_version.v2 instead.

  Try out v2: {{ ref('data_team_dbt', 'model_with_version', v='2') }}
  Pin to  v1: {{ ref('data_team_dbt', 'model_with_version', v='1') }}

14:01:23  1 of 1 ERROR my_model_name::filter_test ........................................ [ERROR in 0.05s]
14:01:23  
14:01:23  Finished running 1 unit test in 0 hours 0 minutes and 35.05 seconds (35.05s).
14:01:24  
14:01:24  Completed with 1 error and 0 warnings:
14:01:24  
14:01:24    Compilation Error in unit_test filter_test (modelsunit_test.yml)
  Unit_Test 'unit_test.data_team_dbt.my_model_name.filter_test' (models/unit_test.yml) depends on a node named 'model_with_version' with version '2' which was not found
14:01:24  
14:01:24  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

So it seems like upgrading to 1.8.6 resolved the issue.

Last test. Updagred to 1.8.5 failed

Seems like the issue was resolved from 1.8.5 to 1.8.6.

Running 1.8.5 gives me the same error as before, with the CTE Fixture not having the correct suffixe V2

dbt test -s filter_test                      
14:03:41  Running with dbt=1.8.5
14:03:43  Registered adapter: snowflake=1.8.3
14:03:43  Unable to do partial parsing because of a version mismatch
14:03:58  Found 723 models, 203 data tests, 269 sources, 923 macros, 2 groups, 1 unit test
14:03:58  
14:04:31  Concurrency: 1 threads (target='dev')
14:04:31  
14:04:31  1 of 1 START unit_test my_model_name::filter_test .............................. [RUN]
14:04:31  1 of 1 ERROR my_model_name::filter_test ........................................ [ERROR in 0.53s]
14:04:31  
14:04:31  Finished running 1 unit test in 0 hours 0 minutes and 33.13 seconds (33.13s).
14:04:32  
14:04:32  Completed with 1 error and 0 warnings:
14:04:32  
14:04:32    Runtime Error in unit_test filter_test (models/unit_test.yml)
  An error occurred during execution of unit test 'filter_test'. There may be an error in the unit test definition: check the data types.
   Database Error
    002003 (42S02): SQL compilation error:
    Object '__DBT__CTE__MODEL_WITH_VERSION_V2' does not exist or not authorized.
14:04:32  
14:04:32  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Well glad that this was resolved by only upgrading the version to 1.8.6.

Thank for you help @dbeatty10

graciegoheen · 2024-11-05T20:18:34Z

Sounds like this may be fixed and we can close this out?

marcilj added bug Something isn't working triage labels Aug 28, 2024

dbeatty10 added unit tests Issues related to built-in dbt unit testing functionality model_versions labels Aug 29, 2024

dbeatty10 added awaiting_response and removed triage labels Aug 29, 2024

github-actions bot added triage and removed awaiting_response labels Aug 29, 2024

dbeatty10 removed the triage label Aug 29, 2024

dbeatty10 mentioned this issue Aug 29, 2024

[Bug] Unit Test fails when using a versioned model as an input #10528

Closed

2 tasks

clairegchen mentioned this issue Oct 17, 2024

[Bug] Unit test fails with versioned inputs #10880

Closed

2 tasks

devmessias mentioned this issue Oct 21, 2024

fix: unit tests with versioned refs #10889

Merged

5 tasks

MichelleArk closed this as completed in #10889 Nov 14, 2024

dbeatty10 mentioned this issue Nov 24, 2024

[Bug] Unit tests don't recognize versioned sources #11039

Closed

2 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[Bug] Runtime Error in unit_test when using versioned models #10623

[Bug] Runtime Error in unit_test when using versioned models #10623

marcilj commented Aug 28, 2024 •

edited by dbeatty10

Loading

dbeatty10 commented Aug 29, 2024

marcilj commented Aug 29, 2024

dbeatty10 commented Aug 29, 2024

Reprex

marcilj commented Aug 30, 2024

marcilj commented Aug 30, 2024

graciegoheen commented Nov 5, 2024

[Bug] Runtime Error in unit_test when using versioned models #10623

[Bug] Runtime Error in unit_test when using versioned models #10623

Comments

marcilj commented Aug 28, 2024 • edited by dbeatty10 Loading

Is this a new bug in dbt-core?

Current Behavior

The problem I'm having

The context of why I'm trying to do this

What I've already tried

Some example code or error messages

More Informations

Expected Behavior

Steps To Reproduce

Relevant log output

Environment

Which database adapter are you using with dbt?

Additional Context

dbeatty10 commented Aug 29, 2024

marcilj commented Aug 29, 2024

dbeatty10 commented Aug 29, 2024

Reprex

marcilj commented Aug 30, 2024

Example with - input: ref('model_with_version', v=2)

Example with - input: ref('model_with_version')

Final test

More info

marcilj commented Aug 30, 2024

Update.

Last test. Updagred to 1.8.5 failed

graciegoheen commented Nov 5, 2024

marcilj commented Aug 28, 2024 •

edited by dbeatty10

Loading

Example with `- input: ref('model_with_version', v=2)`

Example with `- input: ref('model_with_version')`