Custom model name for specific environment fails

[jtrudel]

Hi! I'm having an issue where the schema and table name is different for a model depending on the dbt environment.

Say I have a dbt model named schema__table_name. On staging, we just use the whole model name as the table name:
staging.schema__table_name

But on production, we have a macro to split the model name into schema and table based on the double underscore:
schema.table_name

Problem is, when we are in production environment and give the model name to
dbt_unit_testing.test(schema__table_name)

it expects to find the table schema.schema__table_name and the test fails.

Is there any way we can support custom schema name based on the environment with the current dbt-unit-testing tools?

[psousa50]

Hey @jtrudel, could you kindly provide some code examples to help illustrate this? It would greatly assist us in grasping the problem. Thank you!

[adrian-damaschin]

We have the same issue on our end.

We are using a builder to generate a model code and based on dispatch it creates CTE's inside with the name:
__dbt__cte__stg_generic_system__vdim_currency and selects from it

When dbt-unit-test runs it renames that CTE to CORE_DBT_MODULE__STG_GENERIC_SYSTEM__VDIM_CURRENCY__ but not the usage. Therefore the test fails because it can't find __dbt__cte__stg_generic_system__vdim_currency in the tested model

Is there any way to control the CTE name of the mocked resource?

Test Code:

-- depends_on: {{ ref('stg_generic_system__vdim_currency') }}
{{ config(tags=['unit-test']) }}

{% call dbt_unit_testing.test('vdim_currency', 'Test VDIM_Currency Creation', options={"use_qualified_sources": "True"}) %}

  {% call dbt_unit_testing.mock_ref ('core_dbt_module', 'stg_generic_system__vdim_currency') %}
        SELECT '85F5608AB7978A279B315D4FD200934D8042803C44F89852A81B54DA52A4EA2D' AS CURRENCY_HK
            , 'CORE~EUR' AS CURRENCY_BK
            , '2023-03-31 10:06:43.000000000' AS ROW_START_DATE
            , '9999-12-31 00:00:00.000000000' AS ROW_END_DATE
            , false AS ROW_IS_DELETED
            , '2023-03-31' AS CURRENT_AS_OF_DATE
            , 'CORE' AS ORIGIN_SYSTEM
            , 'EUR' AS CURRENCY_A3_CODE
            , 978 AS CURRENCY_NUM_CODE
            , 'Euro' AS CURRENCY_NAME
            , '€' AS CURRENCY_SYMBOL
  {% endcall %}

  {% call dbt_unit_testing.expect() %}
    SELECT 'EUR' AS CURRENCY_A3_CODE
          , 978 AS CURRENCY_NUM_CODE
          , 'Euro' AS CURRENCY_NAME
          , '€' AS CURRENCY_SYMBOL
  {% endcall %}
{% endcall %}

Error:

09:34:02  1 of 1 START test first_test ................................................... [RUN]
09:34:08  1 of 1 ERROR first_test ........................................................ [ERROR in 5.88s]
09:34:08  
09:34:08  Finished running 1 test in 0 hours 0 minutes and 13.47 seconds (13.47s).
09:34:08  
09:34:08  Completed with 1 error and 0 warnings:
09:34:08  
09:34:08  Database Error in test first_test (tests/unit/first_test.sql)
09:34:08    002003 (42S02): SQL compilation error:
09:34:08    Object '__DBT__CTE__STG_GENERIC_SYSTEM__VDIM_CURRENCY' does not exist or not authorized.
09:34:08  
09:34:08  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Generated LOG Code (formated a bit):

USE DATABASE VMX_INT;
select *
    FROM (
        select count(1) as expectation_count
        from (
            select count(1) as count, "CURRENCY_A3_CODE","CURRENCY_NUM_CODE","CURRENCY_NAME","CURRENCY_SYMBOL"
            from (
                SELECT 'EUR' AS CURRENCY_A3_CODE
                      , 978 AS CURRENCY_NUM_CODE
                      , 'Euro' AS CURRENCY_NAME
                      , '€' AS CURRENCY_SYMBOL
            ) as s
            group by "CURRENCY_A3_CODE","CURRENCY_NUM_CODE","CURRENCY_NAME","CURRENCY_SYMBOL"
        ) as exp
    ) as exp_count
    , (
        select count(1) as actual_count
        from (
            select count(1) as count, "CURRENCY_A3_CODE","CURRENCY_NUM_CODE","CURRENCY_NAME","CURRENCY_SYMBOL"
            from (
                with "COLUMNS" as (
                    select * from "MY_DB"."INFORMATION_SCHEMA"."COLUMNS" where false
                )
                , "TABLES" as (
                    select * from "MY_DB"."INFORMATION_SCHEMA"."TABLES" where false
                )
                , "CORE_DBT_MODULE__STG_GENERIC_SYSTEM__VDIM_CURRENCY__" as (
                    SELECT '85F5608AB7978A279B315D4FD200934D8042803C44F89852A81B54DA52A4EA2D' AS CURRENCY_HK
                        , 'CORE~EUR' AS CURRENCY_BK
                        , '2023-03-31 10:06:43.000000000' AS ROW_START_DATE
                        , '9999-12-31 00:00:00.000000000' AS ROW_END_DATE
                        , false AS ROW_IS_DELETED
                        , '2023-03-31' AS CURRENT_AS_OF_DATE
                        , 'CORE' AS ORIGIN_SYSTEM
                        , 'EUR' AS CURRENCY_A3_CODE
                        , 978 AS CURRENCY_NUM_CODE
                        , 'Euro' AS CURRENCY_NAME
                        , '€' AS CURRENCY_SYMBOL
                )
                select *
                from (
                    with cte_master_datasets as
                    (
                        select master.CURRENCY_A3_CODE
                             , master.CURRENCY_NUM_CODE
                             , master.CURRENCY_NAME
                             , master.CURRENCY_SYMBOL

                        from __dbt__cte__stg_generic_system__vdim_currency as master
                    )

                    select master.CURRENCY_A3_CODE
                         , master.CURRENCY_NUM_CODE
                         , master.CURRENCY_NAME
                         , master.CURRENCY_SYMBOL
                    from cte_master_datasets as master
                ) as t
            ) as s
            group by "CURRENCY_A3_CODE","CURRENCY_NUM_CODE","CURRENCY_NAME","CURRENCY_SYMBOL"
        ) as act
    ) as act_count

My compiled model from compiled folder:

with  __dbt__cte__stg_generic_system__vdim_currency as (
SELECT CURRENCY_HK
     , CURRENCY_BK
     , ORIGIN_SYSTEM
     , CURRENCY_A3_CODE
     , CURRENCY_NUM_CODE
     , CURRENCY_NAME
     , CURRENCY_SYMBOL
     , ROW_START_DATE
     , CURRENT_AS_OF_DATE
     , ROW_IS_DELETED
FROM TABLE ( MY_DB.stage_mart.udtf_base_generic_system__vdim_currency () )
),cte_master_datasets as 
(
    select master.CURRENCY_A3_CODE
         , master.CURRENCY_NUM_CODE
         , master.CURRENCY_NAME
         , master.CURRENCY_SYMBOL
        
    from __dbt__cte__stg_generic_system__vdim_currency as master    
)

select master.CURRENCY_A3_CODE
         , master.CURRENCY_NUM_CODE
         , master.CURRENCY_NAME
         , master.CURRENCY_SYMBOL
        
from cte_master_datasets as master

I also tried mocking the UDTF but the same issue arrises.