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
Edit this page