{{ 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
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.
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.
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?
# 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
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
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.
