[Bug] Unit tests don't recognize snapshots with aliases

[gnilrets]

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

I have a snapshot with an alias defined. When running the unit test, I get SQL compilation error:

20:26:02    Runtime Error in unit_test test_wtf (models/test_wtf.yml)
  An error occurred during execution of unit test 'test_wtf'. There may be an error in the unit test definition: check the data types.
   Database Error
    002003 (42S02): SQL compilation error:
    Object '__DBT__CTE__ASSEMBLY_COMPONENTS' does not exist or not authorized.

Snapshot config:

{% snapshot snap_qb_etahub__assembly_components %}
   {{
        config(
          target_schema='qb_etahub',
          alias='assembly_components',
          transient=False,
          unique_key='record_id_nbr',
          strategy='timestamp',
          updated_at='_sdc_batched_at',
          invalidate_hard_deletes=True,
        )
    }}

    SELECT
      *,
      CURRENT_TIMESTAMP()::TIMESTAMP AS dbt_snapshot_at
    FROM
      {{ source('qb_etahub', 'assembly_components') }}
{% endsnapshot %}

The aliased name of my snapshot is called ASSEMBLY_COMPONENTS. If I reconfigure my snapshot to not use an alias, I do not get this error (but I don't want to have to rename the tables for all of my snapshots).

Expected Behavior

dbt should be able to run unit tests on snapshots with aliases.

Steps To Reproduce

1. Create a snapshot with an alias.
2. Create a unit test referencing the snapshot as a source.
3. Run the unit test.

Relevant log output

No response

Environment

- OS: Debian GNU/Linux 12 (bookworm)
- Python: Python 3.10.15
- dbt: 1.8.5

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

[dbeatty10]

Thanks for opening this @gnilrets!

I see what you are saying. See below for a reproducible example ("reprex").

Reprex

Create these files using dbt-postgres:

snapshots/my_snapshot.sql

{% snapshot my_snapshot %}
   {{
        config(
          target_schema=target.schema,
          alias='assembly_components',
          unique_key='id',
          strategy='timestamp',
          updated_at='_sdc_batched_at',
          invalidate_hard_deletes=True,
        )
    }}

    select 1 as id, {{ dbt.current_timestamp() }} as _sdc_batched_at

{% endsnapshot %}

models/my_model.sql

select * from {{ ref("my_snapshot") }}

models/_unit_tests.yml

unit_tests:
  - name: my_unit_test
    model: my_model
    given:
      - input: ref('my_snapshot')
        rows:
          - {id: 1, _sdc_batched_at: "2024-01-01"}
    expect:
      rows:
          - {id: 1, _sdc_batched_at: "2024-01-01"}

Run this command:

dbt build

Get this error:

02:38:29    Runtime Error in unit_test my_unit_test (models/_unit_tests.yml)
  An error occurred during execution of unit test 'my_unit_test'. There may be an error in the unit test definition: check the data types.
   Database Error
    relation "__dbt__cte__my_snapshot" does not exist
    LINE 24: ) select * from __dbt__cte__my_snapshot

Root cause

For unit tests, dbt will create a temporary table so that it can determine the relevant data types for the unit fixtures.

Looking at the logs within logs/dbt.log will show the logic for that temporary table. When the snapshot does not have an alias, the logic for that temporary table works. But if it has an alias, then it fails. See examples below.

Working temporary table (a snapshot without an alias)

  create temporary table "my_unit_test__dbt_tmp192138036045"
  
  
    as
  
  (
    select * from (
        with __dbt__cte__my_snapshot as (

-- Fixture for my_snapshot
select 
    
    cast(1 as integer)
 as id, cast(null as timestamp with time zone) as updated_at, cast(null as text) as dbt_scd_id, cast(null as timestamp without time zone) as dbt_updated_at, cast(null as timestamp without time zone) as dbt_valid_from, cast(null as timestamp without time zone) as dbt_valid_to, 
    
    cast('2024-01-01' as timestamp with time zone)
 as _sdc_batched_at
) select * from __dbt__cte__my_snapshot
    ) as __dbt_sbq
    where false
    limit 0

  );

Not working temporary table (a snapshot with an alias)

  create temporary table "my_unit_test__dbt_tmp192418314907"
  
  
    as
  
  (
    select * from (
        with __dbt__cte__assembly_components as (

-- Fixture for my_snapshot
select 
    
    cast(1 as integer)
 as id, 
    
    cast('2024-01-01' as timestamp with time zone)
 as _sdc_batched_at, cast(null as text) as dbt_scd_id, cast(null as timestamp with time zone) as dbt_updated_at, cast(null as timestamp with time zone) as dbt_valid_from, cast(null as timestamp with time zone) as dbt_valid_to
) select * from __dbt__cte__my_snapshot
    ) as __dbt_sbq
    where false
    limit 0

  );

We can see in the 2nd SQL query that there's just a mismatch between the CTE named __dbt__cte__assembly_components and then trying to select from it but calling it __dbt__cte__my_snapshot instead 💥

[victorasu]

Environment

• OS: Debian GNU/Linux 12 (bookworm)
• Python: Python 3.10.15
• dbt: 1.8.5

The bug was fixed with the release of dbt-core 1.8.6, so the solution in this specific case would be to upgrade dbt.