TABLE_NOT_FOUND Error During Unit Testing in dbt-athena 1.8 Due to Jinja Macro Dependency

[valerio-auricchio]

Is this a new bug in dbt-athena?

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

Current Behavior

I have configured dbt-athena 1.8 and I'm trying to run a unit test on a model article_sold that depends on a Jinja macro sale_start_date(). This macro relies on another model retail_calendar
The unit_test is defined in this way:

unit_tests:
  - name: reporting-article_sold
    description: "Check if reporting.article_seasonality_sold follows model logic"
    model: article_sold
    given:
      - input: ref('sale_item')
        rows:
          - { sale_date: '2024-01-01', material_number: 'WA16333' }
          - { sale_date: '2022-01-01', material_number: 'WWYY23' }
          - { sale_date: '2024-01-01', material_number: 'YYEE455' }
      - input: ref('article')
        rows:
          - { material_number: 'WA16333' }
          - { material_number: 'WA16333' }
      - input: ref('retail_calendar')
        rows: 
          - { retail_year_cy: 2014 }
    expect:
      rows:
        - { material_number: 'WA16333' }

When I run the test, I get this error:

TABLE_NOT_FOUND: line 5:14: Table 'awsdatacatalog.dbt_default.__dbt__cte__retail_calendar' does not exist
It seems that the SQL Athena query for the sale_start_date() macro is not correctly generated when I run the unit test. In fact, if I simply run the model article_sold, the SQL generated for the same macro sale_start_date() is different compared to the SQL generated when I run the unit test on article_sold.

The SQL code that is generated from the models where the macro is correctly referenced:

        select
        case when min(date) <= cast(cast(year(current_date) - 2 as varchar)||'-01-01' as date) then min(date) else cast(cast(year(current_date) - 2 as varchar)||'-01-01' as date) end
        from "awsdatacatalog"."ga_public_calendar"."retail_calendar"
        where retail_year_cy = year(current_date) - 2

When we lunch unit tests we get the wrong SQL:

        select
        case when min(date) <= cast(cast(year(current_date) - 2 as varchar)||'-01-01' as date) then min(date) else cast(cast(year(current_date) - 2 as varchar)||'-01-01' as date) end
        from __dbt__cte__retail_calendar
        where retail_year_cy = year(current_date) - 2

In the second SQL the from is not correct.

Expected Behavior

I expected the unit test for the model article_sold to execute successfully, with the SQL query for the sale_start_date() macro correctly incorporating the dependent model retail_calendar. The expected behavior is that the test runs without errors and correctly validates the logic of the article_sold model based on the provided input data. This test wit postgress adapter works well but with dbt-athena-community 1.8.1 not.

Steps To Reproduce

Here are the steps to reproduce our issue.
We are using Athena and materializing the tables in Iceberg. We are working with two models, A and B, both written in Athena and Iceberg. We created a macro that is used from model A and macro reads from model B, but when we run it, we encounter dependency problems.
awsdatacatalog.dbt_default.__dbt__cte__retail_calendar' does not exist

Environment

- dbt: 1.8.0
- dbt-athena-community: 1.8.1

Additional Context

No response

[nicor88]

Seems to be a rendering issue, __dbt__cte__retail_calendar should be a CTE, therefore should be rendered as __dbt__cte__retail_calendar without the full path.

@valerio-auricchio could you try to use materialization table or view for all your models as a workaround at the moment? and then confirm that it works?

[valerio-auricchio]

@nicor88 I've completed the test you requested and configured both tables with materialized='view'. However, I still encounter the same error as before.

[nicor88]

@valerio-auricchio Thanks for checking, I think that I get it now.
ref('retail_calendar') generates under the hood __dbt__cte__retail_calendar, we most propably needs to overwrite the ctes rendered by "dbt" under the hood in case of unit testing, that I believe makes the unit testing feature not fully working with athena.