DBT Test Views Defaulting to dbo Schema: How to Change Schema?

Help
, ,
1

Issue:
I’m working on a production database with access only to the [DBT] schema. I’ve created generic tests in the tests folder and applied them through my schema.yml file. When running dbt test, I get the following error:

12:26:09 fabric adapter: Database error: ('42000', '[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server] The specified schema name "dbo" either does not exist or you do not have permission to use it.

EXEC('create view
    dbo.testview_11856 as

    with test_target as (
        select CEL_PercVerOpenToAllTrades as [column]
        from "DW_SIGMA"."DBT"."VW_SCF_R_SAS_MODELS"
    )
    select *
    from test_target
    where [column] < 0 and [column] not in (-1, -2, -5, -6, -7, -9)

;')
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 dbo.testview_11856
) dbt_internal_test;

EXEC('drop view dbo.testview_11856;')

Problem:
The views are being created in the dbo schema instead of the [DBT] schema, which results in a permissions error.

Question:
How do I change the schema for these test views from dbo to [DBT]?

2

Have you had any luck with this?

3

Hi yes i have solved it

4

What was your solution?

5

To resolve the issue of dbt trying to create test views in the dbo schema instead of the [DBT] schema, I had to override the default macro responsible for generating the test SQL by copying the macro into your project’s macros directory and modifying it to use the correct schema.

Here’s the approach I used :

Solution:

  1. Copy the Default Macro: First, copy the default fabric__get_test_sql macro into your project’s macros directory.
  2. Modify the Schema: In the copied macro, replace the hardcoded dbo schema with your desired schema [DBT] or use generate_schema_name to dynamically assign the correct schema.
  3. Override in Your Project: Once the macro is in your project, dbt will automatically override the default one with your customized version. Here’s an example of how I changed the schema to [DBT] in the macro:
{% macro fabric__get_test_sql(main_sql, fail_calc, warn_if, error_if, limit) -%}
  {% if main_sql.strip().lower().startswith('with') %}
    {% set testview %}
      {{ generate_schema_name('DBT') }}.testview_{{ range(1300, 19000) | random }}
    {% endset %}

    {% set sql = main_sql.replace("'", "''") %}

    EXEC('create view {{testview}} as {{ sql }};')
    select
      {{ "top (" ~ limit ~ ')' if limit != none }}
      {{ fail_calc }} as failures,
      case when {{ fail_calc }} {{ warn_if }}
        then 'true' else 'false' end as should_warn,
      case when {{ fail_calc }} {{ error_if }}
        then 'true' else 'false' end as should_error
    from (
      select * from {{testview}}
    ) dbt_internal_test;

    EXEC('drop view {{testview}};')

  {% else %}
    select
      {{ "top (" ~ limit ~ ')' if limit != none }}
      {{ fail_calc }} as failures,
      case when {{ fail_calc }} {{ warn_if }}
        then 'true' else 'false' end as should_warn,
      case when {{ fail_calc }} {{ error_if }}
        then 'true' else 'false' end as should_error
    from (
      {{ main_sql }}
    ) dbt_internal_test
  {%- endif -%}
{%- endmacro %}
6

also had to add this to drop the schema duplicating ie dbt-dbt. :

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

    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}

        {{ default_schema }}

    {%- else -%}

        {{ custom_schema_name | trim }}

    {%- endif -%}

{%- endmacro %}
7

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.