{{ schema }} not returning custom schema

The problem I’m having

{{ schema }} is not returning the schema defined in project.yml for this model’s folder. It is instead returning the default target schema; the same as {{ target.schema }} would. This isn’t the behaviour I’d expect from reading the documentation.

nb. I’m overriding generate_schema_name() so that a custom schema is used without the default schema as a prefix.

The context of why I’m trying to do this

My model builds to a custom schema, and needs to reference a UDF in that same schema.

What I’ve already tried

{{ schema }}
{{ target.schema }}

Some example code or error messages

1, Create a model in a subfolder e.g. “reporting”
2, Define the custom schema for this folder in project.yml

    reporting:
      schema: reporting

3, Override the dbt schema generator ; create macros\generate_schema_name:

{% 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 %}

may i know the dbt version your using.
Could you also share your project.yml file and schema.yml file
please check if u are using schema config in your schema.yml and model.sql file

I’m using dbt cloud.

My schema.yml file is blank ; I intend to use it for tests later on:

version: 2

project.yml I define the schema at model-folder level (it’s the gold schema in this case):


# Name your project! Project names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: 'catalyst_model'
version: '1.0.0'
config-version: 2

# This setting configures which "profile" dbt uses for this project.
profile: 'default'

# These configurations specify where dbt should look for different types of files.
# The `source-paths` config, for example, states that models in this project can be
# found in the "models/" directory. You probably won't need to change these!
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"


models:
  catalyst_model:
    archive:
      +enabled: false
    gold:
      schema: gold
      materialized: table
    staging:
      schema: dbt_staging
      materialized: table

However the use of {{ schema }} in any syntax in a model within the gold folder is generating dbt_staging, which is my default target schema. It still generates the model in gold, correctly.

1 Like

may i know the dbt version you’re using.

cloud, or do you mean something else

in your logs you could see something like
image

Cloud logs work differently & don’t show the version ; you’re using CLI there I take it? I think cloud is always on the latest dbt runtime.

can u try this {{model['schema']}}

Hiya - still giving me the default schema, instead of the one configured in project.yml

Thank you for the clear reproduction steps!

The docs you linked to do say that {{ schema }} should be the same as {{ model['schema'] }}, but would you mind trying that as well? It’s odd to me that the model is being built in the right place but the jinja variable is returning the incorrect value.

Hiya - yeah I’ve tried this too. Actually there’s a much easier replication method ; just compile the selection within a model like this. These should all say “gold” in my case, since that’s the project config.

Capture1

I’ve just tested it without the custom schema macro (override), same issue. So it’s actually quite a simple thing to replicate, dunno if anyone else wants to try it?

can you show us the dbt_project.yml file


# Name your project! Project names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: 'catalyst_model'
version: '1.0.0'
config-version: 2

# This setting configures which "profile" dbt uses for this project.
profile: 'default'

# These configurations specify where dbt should look for different types of files.
# The `source-paths` config, for example, states that models in this project can be
# found in the "models/" directory. You probably won't need to change these!
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"


models:
  catalyst_model:
    archive:
      +enabled: false
    gold:
      schema: gold
      materialized: table
    staging:
      schema: dbt_staging
      materialized: table
    reporting:
      schema: reporting
      materialized: view

final check
can u add this {{ this.schema }} in one of the models in gold folder and check.

Is this happening only to one model in the gold folder? or is it happening to all models in the gold folder

Same issue - yeah, happening in every model in the folder.

I am experiencing something like this as well running dbt-sqlserver, core 1.5… My models are not accepting my custom schema and materialization while spitting out the default schema and materialization

Should this be raised as a bug?

OK I have just had a chance to try this out properly; sorry for the delay!

It behaves correctly in the CLI:

# dbt_project.yml
models:
  +materialized: view
  schema: alternate_schema

image

But in dbt Cloud previews it shows the incorrect behaviour as you showed above.

I wonder if this might be an issue with the soon-to-be-replaced dbt-rpc library which currently powers the IDE experience - I’ll ask around and update this thread if I hear anything.

I have same issue.

It works fine when I click “Build”, but gives wrong value when I click “Preview”

My workaround was to create a variable with the default schema and then pass that to the “generate_schema_name” macro.

 {{ generate_schema_name(var('udf-schema'))}}.fn_json_array_value__to_csv_string(i.field) ,