Tests Being Put into Own "tests_schema"

[ernestoongaro]

Hello! I have some standard tests in my project, starting with dbt-fabric 1.8 this is what's happening:

1. Create a standard built in test
2. Run that test
3. Expect: test does not need to create a view or anything; it's transient
4. Results, a view is attempted to be created. Seems to be related to this: https://github.com/microsoft/dbt-fabric/blame/adf9f8d6557e41eaae8ddacc14a08c057d67a687/dbt/include/fabric/macros/materializations/tests/helpers.sql#L5

EXEC('create view 
      dbt_eongaro_tests_schema.testview_4608
     as 
    
    

with all_values as (

    select
        region as value_field,
        count(*) as n_records

    from "sa_demo"."dbt_eongaro"."dim_customers"
    group by region

)

select *
from all_values
where value_field not in (
    ''AFRICA'',''MIDDLE EAST'',''ASIA'',''EUROPE'',''AMERICA''
)


;')
    select
      
      count(*) as failures,
      case when count(*) != 0
        then 'true' else 'false' end as should_warn,
      case when count(*) != 0
        then 'true' else 'false' end as should_error
    from (
      select * from 
      dbt_eongaro_tests_schema.testview_4608
    
    ) dbt_internal_test;

    EXEC('drop view 
      dbt_eongaro_tests_schema.testview_4608
    ;')

  
15:12:06 Opening a new connection, currently in state closed
15:12:06 fabric adapter: Using connection string: DRIVER={ODBC Driver 18 for SQL Server};SERVER=5xxoty5si6telax6vdkh6id534-fjebxqqv6kvexcr6vmlnwsjs2e.datawarehouse.pbidedicated.windows.net;Database=sa_demo;Authentication=ActiveDirectoryServicePrincipal;UID={ca9252f9-6361-4d5e-af31-8587eca1a429};PWD=***;encrypt=Yes;TrustServerCertificate=No;APP=dbt-fabric/1.8.2;ConnectRetryCount=1
15:12:08 fabric adapter: Connected to db: sa_demo
15:12:08 fabric adapter: Database error: ('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The specified schema name "dbt_eongaro_tests_schema" either does not exist or you do not have permission to use it. (2760) (SQLExecDirectW)')

[ernestoongaro]

It's not expected for tests to create views, and certainly not outside the specified schema. in this case my schema is dbt_eongaro and it's attempting to create them in dbt_eongaro_tests_schema

[ernestoongaro]

Hi @prdpsvs as requested via Slack here's a repro:

1. Create a model my_model.sql:

with source_data as (

    select 'A' as something
    union all
    select 'B' as something

)

select *
from source_data

2. create a yml file my_example.yml

version: 2

models:
    - name: my_model
      columns:
          - name: id
            tests:
                - not_null
                - accepted_values:
                    values:
                      - 'B'
                      - 'A'

3. run dbt build --select my_model
4. Observe that the test is attempted to be built in _tests_schema instead of and that actually it shouldn't try and execute EXEC('create view as tests are ephemral

error

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

5. Note that the not_null test is perfectly fine as it does not try and build a view

[prdpsvs]

@ernestoongaro , I looked into these errors.

1. Microsoft Fabric DW does not support nested CTE's. For that reason, the adapter cannot support ephermal tests.

2. Adapter now will default these tests to dbt_test__audit (generated by dbt) schema to ensure consistency.

I will close this issue for now. Hope this helps.

[ThomsenS]

@prdpsvs I just updated to 1.8.4. The schema dbt_test__audit is not auto generated.

12:11:16 Database Error in test date_is_not_in_future_latest_inventory_movement_latest_inventory_movement (models\marts_marts_models.yml)
('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The specified schema name "dbt_test__audit" either does not exist or you do not have permission to use it. (2760) (SQLExecDirectW)')
compiled Code at target\run\msbid\models\marts_marts_models.yml\date_is_not_in_future_latest_i_85e765eeda8d5db57964dc06f11dfe72.sql

[jtcohen6]

The schema dbt_test__audit is not auto generated.

By way of explanation: On every run, dbt-core will automatically check to see which schemas exist, and create those schemas for DAG nodes that are selected and "relational" (meaning, dbt is going to materialize this node as a database relation).

For some time, we've had conditional logic within dbt-core to generate schemas for tests if (and only if) "store failures" is enabled, via test config or the --store-failures flag.

I think there are two mitigation paths forward:

1. dbt-fabric should use {{ target.schema }} instead of {{ node.schema }} as the location to create these views, under the assumption that the user is likely to be putting other models in their default configured schema, and dbt will create the schema accordingly.
2. We investigate changing the logic in dbt-core so that test nodes' is_relational property is not conditional on should_store_failures (here). This is a change we'd want to make carefully, as it could have unrelated consequences.

We could change the default schema config of tests from dbt_test__audit to None. Given that it's been the former for many years (since v0.20 in 2021 IIRC), this would be a significant behaviour change and would require a migration plan.

Longer-term, it would be ideal for dbt-fabric use temp tables (scoped to the session) instead of persistent views (registered in a specific database/schema).

@prdpsvs I recommend reopening this issue in the meantime, while we sort out the appropriate mitigation.

[prdpsvs]

@jtcohen6 , @ernestoongaro , I did not address this completely but dbt-fabric 1.8.6 creates target schema {{ target.schema }} if it does not exist.