Dbt test not testing on custom schema

[dbtFabricator]

After upgrading to 1.8, i.e. latest version on dbt cloud, the tests seem to have stopped running correctly on my schema when I run on my production environment. This worked as intended in 1.7. It works fine on 1.8 in the dev environment.

Some tests seem to pass correctly and some don't, I haven't been able to find a pattern.

Note:
I am using https://docs.getdbt.com/docs/build/custom-schemas, under macros/generate_schema_name.sql, however it seems to apply the tests on the generated schemas so not sure if it is related at all.

{% macro generate_schema_name(custom_schema_name, node) -%}

    {%- set default_schema = target.schema -%}

     {%- if target.name == 'prod' or target.name == 'test' -%}
        {%- if custom_schema_name is none -%}

            {# Check if the model does not contain a subfolder (e.g, models created at the MODELS root folder) #}
            {% if node.fqn[1:-1]|length == 0 %}
                {{ default_schema }}    
            {% else %}
                {# Concat the subfolder(s) name #}
                {% set prefix = node.fqn[1:-1]|join('_') %}
                {{ prefix | trim }}
            {% endif %}

        {%- else -%}

            {{ default_schema }}_{{ custom_schema_name | trim }}

        {%- endif -%}

    {%- else -%}

        {# Always use default schema for other environments #}
        {{ default_schema }}

    {%- endif -%}

{%- endmacro %}

Some examples of tests in my models.yml file:

version: 2

models:
  - name: stg_bc__customers
    description: Customer data. 
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - "companyno"
            - "no"
    columns:
      - name: "no"
        tests:
          - not_null
  - name: stg_bc__customerledgerentries
    description: Customer ledger entries. 
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - "companyno"
            - "entryno"
    columns:
      - name: "entryno"
        tests:
          - not_null

debug (34).log
console_output (16).log

[prdpsvs]

@dbtFabricator , are you seeing tests that use views failing?
There are currently some issues with the target platform with CTE and views. The adapter is not able to generate ephermal materializations to properly run the tests.
I made some changes in v1.8.6. Note that it will use your {custom schema}__dbt_test_aud.

This might not be the solution you are looking for, please test and let me know.

Thanks for providing the repro. I will repro this and see if I can make this easy in the future releases.

[dbtFabricator]

Hi again, it seems like it.
The tests that are failing is the built in "accepted values" and a dbutils test: dbtutils.unique_combination_of_columns. I also have a few custom singular and generic tests as well that are failing, e.g. test_not_empty, which uses a cte but seems to automatically create a view in the test.

I re-ran dbt test on keep at latest which I assume is v.1.8.6, correct me if Im wrong.

See attachment for example at row 7040->7079.
debug (41).log

Generic test

{% test test_not_empty ( model  ) %}  
with row_count_tmp as (
    
    select count(*) as row_count
    from {{model}}

)


select * from row_count_tmp
where row_count = 0

-- This test ensures the source table has at least one row.
-- An empty table will result in a NULL value for 'success', causing the test to fail.
{% endtest %}

Singular test

with test as (
    select 
        fc."FromCurrency", 
        fc."ToCurrency", 
        fc."Start Date",
        fc."End Date",
        fc."FX rate",
        row_number() over (partition by fc."FromCurrency", fc."ToCurrency", fc."Start Date", fc."End Date" order by fc."Start Date") as row_counter

    from {{ ref('dim_currencies') }} as fc

    left join {{ ref('dim_currencies') }} as oc
        on fc."FromCurrency" = oc."FromCurrency"
        and fc."ToCurrency" = oc."ToCurrency"
        and (fc."Start Date" < oc."End Date" and fc."End Date" > oc."Start Date") 

)

-- We are left self-joining with intervals that are overlapping, and there will be a maximum of one overlap (the row itself), otherwise there are duplicates or overlaps, i.e. the test should fail.
select * from test
where row_counter > 1

[prdpsvs]

@dbtFabricator , Yes because Fabric does not support different types of CTE, adapter currently wraps ephermal with a view but this does not work 100% at this time.

Once nested CTE support is available, I will update the adapter to support the scenarios you are testing. For now, please stand-by. I will not close the issue yet.

[joshrodgersKA]

@prdpsvs - we just started receiving the same errors on our tests. We were on the adapter version 1.8.7 and had no issues at all with dbt core.

We are working on moving to dbt cloud, which seems to be on a newer version of the adapter (1.8.8) and the same tests are failing now.

('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The specified schema name "tests_schema" either does not exist or you do not have permission to use it. (2760) (SQLExecDirectW)')

Any thoughts?