Skip to main content

Custom databases

A word on naming

Different warehouses have different names for logical databases. The information in this document covers "databases" on Snowflake, Redshift, and Postgres; "projects" on BigQuery; and "catalogs" on Databricks Unity Catalog.

The values project and database are interchangeable in BigQuery project configurations.

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:
jaffle_shop:
+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

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 %}

💡 Use Jinja's whitespace control to tidy your macros!

When you're modifying macros in your project, you might notice extra white space in your code in the target/compiled folder.

You can remove unwanted spaces and lines with Jinja's whitespace control by using a minus sign. For example, use {{- ... -}} or {%- ... %} around your macro definitions (such as {%- macro generate_schema_name(...) -%} ... {%- endmacro -%}).

Managing different behaviors across packages

See docs on macro dispatch: "Managing different global overrides across packages"

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.

0