The problem I’m having
Using dbt cloud, custom schemas using the generate_schema_name macro does not work
The context of why I’m trying to do this
dbt cloud
What I’ve already tried
using the generate_schema_name macro
Some example code or error messages
I am using dbt_cloud, and trying to generate a custom schema name when I run jobs for the prod envoronment, but for the prod environment (name is prod), it is ignoring the macro that is supposed to override the (default) schema set in the prod deployment environment. I am expecting that when I run a deployment job in the prod environment, it will create a schema named “stage” in the prod database. I coded this per the dbt online documentation, so confused as to why it’s not working.
NOTE: All my yml code in this post is indented properly. The cut and paste into the below removed my indentations.
My dbt_project.yml contents are below, where I specify the custom schema name:
models:
project name below:
jaffle_shop:
example:
+materialized: view
staging:
+materialized: view
+schema: stage
marts:
+materialized: table
…
Then… In my macros folder, I have this coded in the generate_schema_name.sql file:
{% macro generate_schema_name(custom_schema_name, node) %}
{% set default_schema = target.schema %}
{% if target.name == ‘prod’ and custom_schema_name is not none %}
{{ custom_schema_name | trim }}
{% else %}
{{ default_schema }}
{% endif %}
{% endmacro %} (edited)
I had to edit the job, and in the advanced settings, change the target from “default” to “prod”
It works now