Using databases

Configuring custom databases

The logical database that dbt models are built into can be configured using the database model configuration. If this configuration is not supplied to a model, then dbt will use the database configured in the active target from your profiles.yml file. If the database configuration is supplied for a model, then dbt will build the model into the configured database.

The database configuration can be supplied for groups of models in the dbt_project.yml file, or for individual models in model SQL files.

Configuring database overrides in dbt_project.yml:

This config changes all models in the jaffle_shop project to be built into a database called jaffle_shop.

dbt_project.yml
name: jaffle_shop
models:
my_project:
database: jaffle_shop
# For BigQuery users:
# project: jaffle_shop

Configuring database overrides in a model file

This config changes a specific model to be built into a database called jaffle_shop.

models/my_model.sql
{{ config(database="jaffle_shop") }}
select * from ...

generate_database_name

Changelog

The database name generated for a model is controlled by a macro called generate_database_name. This macro can be overridden in a dbt project to change how dbt generates model database names. This macro works similarly to the generate_schema_name macro.

To override dbt's database name generation, create a macro named generate_database_name in your own dbt project. The generate_database_name macro accepts two arguments:

  1. The custom database supplied in the model config
  2. The node that a custom database is being generated for

The default implementation of generate_database_name simply uses the supplied database config if one is present, otherwise the database configured in the active target is used. This implementation looks like this:

get_custom_database.sql
{% macro generate_database_name(custom_database_name=none, node=none) -%}
{%- set default_database = target.database -%}
{%- if custom_database_name is none -%}
{{ default_database }}
{%- else -%}
{{ custom_database_name | trim }}
{%- endif -%}
{%- endmacro %}

Considerations

BigQuery

When dbt opens a BigQuery connection, it will do so using the project_id defined in your active profiles.yml target. This project_id will be billed for the queries that are executed in the dbt run, even if some models are configured to be built in other projects.