Default schema and dataset for dbt environments

1.The default schema belongs to the environment

The environment, not the project code, decides where a model ends up. A dbt environment sets three things: the dbt version, the version of your code, and the warehouse connection information, including the target database/schema settings. A model with no schema configuration is built in that schema, which is called the target schema. When the same project runs in development, then CI, then production, only the environment changes, so the environment's schema is what separates the three runs.

The word "schema" is used for every warehouse. On BigQuery the matching object is a dataset, and dbt treats the two terms as the same thing. A question about the "default dataset" of a BigQuery environment is asking about the target schema. Developers who come from dbt Core will know it as the schema: parameter of a target in profiles.yml. In the dbt platform, each environment plays roughly the role that one entry in profiles.yml plays in Core.

You don't have to create the target schema in the warehouse first. dbt checks whether it exists and creates it when it doesn't. That needs the warehouse user to have permission to create schemas. If the user can't get that privilege, point the environment at an existing schema that the user is allowed to create relations in.

Sources1234

2.Where each environment type sets its schema

Development and deployment environments set the schema in different places, because one serves people and the other serves jobs. A project has a single development environment, which drives the Studio IDE and the dbt platform CLI. The target schema there is personal. Each developer enters it in their own credentials (account name, then Your profile, then Credentials) the first time they use the environment. The usual pattern is dbt_<username>, so each developer gets a sandbox, owns everything in it, and doesn't build on top of a colleague's tables.

A deployment environment has a single schema shared by every job in it, set under Deployment Credentials > schema. One mistake is easy to miss. A new deployment environment can start with the same schema the development environment already uses. Its jobs then build into that schema, and "production" sits in the same place as someone's dev work. Change the field to a dedicated name, such as production or analytics, before the first scheduled run. Name the production schema so end users can tell it's ready for analysis.

The schema field is only part of where a deployment environment builds. The deployment connection is set at the environment level and controls the database, schema and warehouse. The connection profile is a project-level setting assigned to the environment, and it controls how dbt authenticates. Some connection details can be overridden in the environment settings. On Snowflake, for example, these are Role, Database and Warehouse. For Postgres, Redshift, BigQuery and Spark that section doesn't appear, because all values come from the project's connection, and extended attributes are used to override them.

Sources325467

3.Custom schemas extend the default, they don't replace it

A schema config on a model, or +schema on a folder in dbt_project.yml, is added onto the environment's default. It doesn't replace it. The result is <target_schema>_<custom_schema>. Because the target schema is still part of the name, one config gives a different schema in every environment: alice_dev_marketing for Alice, dbt_cloud_pr_123_456_marketing for a CI run, analytics_prod_marketing in production.

Many people expect schema: marketing to build in marketing. The default is designed to avoid that. If dbt used only the custom schema, every developer and every CI run would build into the same marketing schema and overwrite each other's relations. The generate_schema_name macro below implements the default rule. If your project defines its own macro with that name, dbt uses yours instead. dbt ignores a generate_schema_name macro that comes from an installed package.

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

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

        {{ default_schema }}

    {%- else -%}

        {{ default_schema }}_{{ custom_schema_name | trim }}

    {%- endif -%}

{%- endmacro %}

Snapshots are an exception. A snapshot with target_schema set doesn't follow the environment's schema, so it builds in the same place no matter which environment runs it. To get environment-aware snapshots on v1.9+ or the dbt platform, remove target_schema and use the schema config if you still want a custom schema.

Sources18

4.Clean schema names in production only

A common goal is clean names like marketing or gold in production, while dev and CI stay isolated. The wrong way to get there is to delete {{ default_schema }} from the macro so it always returns the custom schema alone. That gives clean production names, but developers who set custom schemas then overwrite each other's models, and CI breaks too. The fix is to make the macro check which environment is running.

dbt includes a macro for this, generate_schema_name_for_env, but it's off by default. To turn it on, add a generate_schema_name macro to your project that calls it. In production, a model with a custom schema builds in that schema alone (marketing). Everywhere else, the custom schema is ignored and the model builds in the target schema (alice_dev, dbt_cloud_pr_123_456).

-- put this in macros/generate_schema_name.sql

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

The macro decides it's in production by checking the target name, not the schema. The production job must therefore set its target name to prod. Otherwise every production model falls back to the target schema and the custom schemas never show up. You can also write your own macro that branches on an environment variable, for example env_var('DBT_ENV_TYPE','DEV') == 'PROD'. Another option builds the schema name from the current branch with the built-in DBT_CLOUD_GIT_BRANCH variable. That variable only exists in the Studio IDE, not in the dbt platform CLI, and developers on the same branch share a schema.

{% macro generate_schema_name_for_env(custom_schema_name, node) -%}
    {%- set default_schema = target.schema -%}
    {%- if  ?  == 'prod' and custom_schema_name is not none -%}
        {{ custom_schema_name | trim }}
    {%- else -%}
        {{ default_schema }}
    {%- endif -%}
{%- endmacro %}

Sources17

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A model configured with schema: marketing is built in a schema called marketing.Why is that wrong?

   By default dbt adds the custom schema to the end of the environment's target schema, so the model builds in something like analytics_prod_marketing.

   Covered in Custom schemas extend the default, they don't replace it

2. 2.To get clean production schema names, edit generate_schema_name so it always returns the custom schema alone.Why is that wrong?

   Without the target schema, developers and CI runs that set custom schemas write into the same schema and overwrite each other. Branch on the environment instead.

   Covered in Clean schema names in production only

3. 3.Enabling generate_schema_name_for_env is enough on its own to make production use the custom schema names.Why is that wrong?

   The macro checks target.name, so the production job also has to set its target name to prod.

   Covered in Clean schema names in production only

4. 4.A generate_schema_name macro shipped in an installed package changes how your project names schemas.Why is that wrong?

   Only a generate_schema_name macro defined in your own project replaces the default. dbt ignores package copies.

   Covered in Custom schemas extend the default, they don't replace it