Skip to main content

schema

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 you 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.marketing.model_name.

Refer to Usage for more examples.

Definition

Optionally specify a custom schema for a model or seed. (To specify a schema for a snapshot, use the target_schema config).

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:

dbt_project.yml
tests:
+store_failures: true
+schema: the_island_of_misfit_tests

Warehouse specific information

  • BigQuery: dataset and schema are interchangeable
0