How to Use Database in dbt Config: Simple Guide

In dbt, you set the database parameter in your dbt_project.yml or model config to specify which database your models should be built in. This helps organize your data and control where dbt writes the results.

Syntax

The database parameter can be set globally in dbt_project.yml or locally in model configurations. It tells dbt which database to use when creating or referencing models.

Example parts:

• database: your_database_name - sets the target database.
• Can be used under models: for global or folder-level settings.
• Can be set per model using config(database='your_database').

yaml

models:
  your_project_name:
    +database: your_database_name

# Or inside a model SQL file
{{ config(database='your_database_name') }}

Example

This example shows how to set the database globally for all models in dbt_project.yml and override it for a specific model.

yaml

# dbt_project.yml
name: your_project_name
version: '1.0'
config-version: 2

models:
  your_project_name:
    +database: analytics_db

# models/sales.sql
{{ config(database='sales_db') }}

select * from raw.sales_data

Common Pitfalls

Common mistakes when using database in dbt config include:

• Setting database in the wrong place, so it is ignored.
• Forgetting to override the target database in the profile, causing conflicts.
• Using inconsistent database names across models causing confusion.

Always check your dbt debug output to confirm the database your models will use.

yaml

models:
  your_project_name:
    +database: wrong_database_name  # This might not exist or be wrong

# Correct way
models:
  your_project_name:
    +database: correct_database_name

Quick Reference

Setting	Description	Scope
database	Name of the database where models are built	Global or model-level
config(database='name')	Set database for a single model	Model-level
target database in profile	Default database if not overridden	Profile-level

Key Takeaways

Set the database in dbt_project.yml under models to control where models build.

Override database per model using config(database='name') for flexibility.

Ensure database names match your warehouse setup to avoid errors.

Use dbt debug to verify which database your models will use.

Keep database settings consistent to maintain clear data organization.