schema

Model

Specify a custom schema for a group of models in your dbt_project.yml file or a config block.

For example, if you have a group of marketing-related models and want to place them in a separate schema called marketing, you can configure it like this:

dbt_project.yml

models:
  your_project:
    marketing: #  Grouping or folder for set of models
      +schema: marketing

This would result in the generated relations for these models being located in the marketing schema, so the full relation names would be analytics.target_schema_marketing.model_name. This is because the schema of the relation is {{ target.schema }}_{{ schema }}. The definition section explains this in more detail.

Seeds

Configure a custom schema in your dbt_project.yml file.

For example, if you have a seed that should be placed in a separate schema called mappings, you can configure it like this:

dbt_project.yml

seeds:
  your_project:
    product_mappings:
      +schema: mappings

This would result in the generated relation being located in the mappings schema, so the full relation name would be analytics.mappings.seed_name.

Snapshots

Saved queries

Specify a custom schema for a saved query in your dbt_project.yml or YAML file.

dbt_project.yml

saved-queries:
  +schema: metrics

This would result in the saved query being stored in the metrics schema.

Test

Customize a custom schema for storing test results in your dbt_project.yml file.

For example, to save test results in a specific schema, you can configure it like this:

dbt_project.yml

tests:
  +store_failures: true
  +schema: test_results

This would result in the test results being stored in the test_results schema.

Refer to Usage for more examples.

Definition

Optionally specify a custom schema for a model, seed, snapshot, saved query, or test.

For users on dbt Cloud v1.8 or earlier, use the target_schema config to specify a custom schema for a snapshot.

When dbt creates a relation (table/view) in a database, it creates it as: {{ database }}.{{ schema }}.{{ identifier }}, e.g. analytics.finance.payments

The standard behavior of dbt is:

• If a custom schema is not specified, the schema of the relation is the target schema ({{ target.schema }}).
• If a custom schema is specified, by default, the schema of the relation is {{ target.schema }}_{{ schema }}.

To learn more about changing the way that dbt generates a relation's schema, read Using Custom Schemas

Usage

Models

Configure groups of models from the dbt_project.yml file.

dbt_project.yml

models:
  jaffle_shop: # the name of a project
    marketing:
      +schema: marketing

Configure individual models using a config block:

models/my_model.sql

{{ config(
    schema='marketing'
) }}

Seeds

dbt_project.yml

seeds:
  +schema: mappings

Tests

Customize the name of the schema in which tests configured to store failures will save their results. The resulting schema is {{ profile.schema }}_{{ tests.schema }}, with a default suffix of dbt_test__audit. To use the same profile schema, set +schema: null.

dbt_project.yml

tests:
  +store_failures: true
  +schema: _sad_test_failures  # Will write tables to my_database.my_schema__sad_test_failures

Ensure you have the authorization to create or access schemas for your work. To ensure that the required schemas have the correct permissions, run a sql statement in your respective data platform environment. For example, run the following command if using Redshift (exact authorization query may differ from one data platform to another):

create schema if not exists dev_username_dbt_test__audit authorization username;

Replace dev_username with your specific development schema name and username with the appropriate user who should have the permissions.

This command grants the appropriate permissions to create and access the dbt_test__audit schema, which is often used with the store_failures configuration.

Warehouse specific information

• BigQuery: dataset and schema are interchangeable