How to Use Schema in dbt: Syntax, Example, and Tips

In dbt, you use the schema configuration to specify the database schema where your models will be built. You can set the schema globally in dbt_project.yml or locally in model files using the config block or schema property. This helps organize your data and control where tables and views are created.

Syntax

The schema in dbt defines the database schema where your models are created. You can set it in different places:

• Globally: In dbt_project.yml under models: to apply to all models.
• Per model: Using the config block inside a model SQL file.
• Using variables: You can use Jinja expressions to dynamically set schema names.

Example syntax in dbt_project.yml:

models:
  my_project:
    schema: my_custom_schema

Example syntax inside a model file:

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

yaml

models:
  my_project:
    schema: my_custom_schema

Example

This example shows how to set a custom schema for a specific model using the config block. The model will be created in the analytics schema instead of the default.

sql

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

select * from raw_data.orders
where order_date >= '2023-01-01'

Output

This model will create a table or view named after the model in the database schema called 'analytics'.

Common Pitfalls

• Not setting schema explicitly can cause models to be created in unexpected default schemas.
• Using inconsistent schema names across models can make your project hard to manage.
• Forgetting to update dbt_project.yml when moving models to a new schema can cause deployment errors.
• Using hardcoded schema names without Jinja can reduce flexibility across environments.

sql

{{ config(schema='public') }}  -- Wrong: hardcoded schema may not match environment

{{ config(schema=target.schema) }}  -- Right: uses environment schema dynamically

Quick Reference

Place to Set Schema	Example	Purpose
dbt_project.yml	models: my_project: schema: analytics	Set default schema for all models in project
Model file config	{{ config(schema='analytics') }}	Set schema for individual model
Using Jinja	{{ config(schema=target.schema) }}	Use environment-specific schema dynamically

Key Takeaways

Set schema in dbt to control where your models are created in the database.

Use dbt_project.yml for global schema settings and config blocks for model-specific schemas.

Use Jinja expressions like target.schema for flexible environment-aware schemas.

Avoid hardcoding schema names to keep your project portable and manageable.

Consistent schema usage helps organize your data and prevents deployment issues.