I have been trying to add a target_schema in the dbt_project.yml as well as in the model file itself.
models:
project_name:
model_name:
+target_schema: new_schema
To my understanding and what I read here in the official documentation, this should work. But it is ignored instead and shows up as the standard dbo. I created the schema by hand to make sure it exists and if I set a schema it creates one, in the format dbo_schema just as described in the documentation. But the target_schema keeps getting ignored?
Is this simply not supported by SQL Server and dbt?
There is no config called target_schema. The docs you link to use that name for the schema config defined in your active target, which is configured in your profiles.yml file:
# profiles.yml
my_profile:
target: dev # this is the default target
outputs:
dev:
schema: dbo # this is what the docs call target_schema
On an individual model (or a directory of models), you can additionally set a config that is also just called schema. This sets what the docs call a custom_schema. This config is read from dbt_project.yml, a "properties" .yml file, or from a {{ config() }} block in a model file.
# dbt_project.yml
models:
project_name:
model_name:
+schema: new_schema # this sets what the docs call custom_schema
The above two config files together will materialize model_name to a schema called dbo_new_schema, as explained in the docs that you link to, since the default behavior is <target_schema>_<custom_schema>.
If you just want to materialize all of your models to new_schema, then change the value in your profiles.yml file, and do not set a schema config in your dbt_project.yml or anywhere else. If you only want some models materialized to new_schema, then I recommend sticking with the default convention for custom schemas, since that'll generalize better to multiple environments (for developers, QA, etc.). Finally, if that really isn't what you want, the docs describe how to override the custom schema name-generating behavior.