Skip to main content

Database Routing with generate_database_name

In the previous lesson, you used generate_schema_name to control the schema where a model is built. This lesson applies the same idea to the database level.

Learning objectives​

By the end of this lesson, you will be able to:

  • Explain why teams separate data across databases.
  • Override dbt's generate_database_name macro.
  • Configure a model with a custom database.
  • Keep development models in a safe sandbox database.
  • Route production models to a governed database.
  • Inspect the resolved relation before running a model.

1. Why route models to different databases?​

A company may separate data for security, governance, cost, or operational ownership. For example:

  • Retail data: BEEMS_RETAIL_PROD
  • Restricted HR data: BEEMS_HR_PROD

A model containing employee salaries or other confidential attributes should not accidentally be created in the retail database.

At the same time, developers should not write to a shared production database while experimenting. A useful policy is:

Development: keep models in the developer's sandbox database
Production: respect approved custom database assignments

2. How dbt chooses the database​

When dbt builds a model, it calls an internal macro named generate_database_name. The macro receives:

  • custom_database_name: the database configured on the model or in dbt_project.yml
  • node: metadata for the model being built
  • target.database: the default database for the active profile target
  • target.name: the name of the active target, such as dev or prod

The database macro decides the database portion of the final relation:

DATABASE.SCHEMA.MODEL

The model does not need to call generate_database_name() inside its SELECT statement.

3. Create the database override​

Create this file in the project macros/ directory:

macros/generate_database_name.sql

{% macro generate_database_name(custom_database_name=none, node=none) -%}
{%- set default_database = target.database -%}

{%- if custom_database_name is none -%}
{{ default_database }}
{%- elif target.name == 'prod' -%}
{{ custom_database_name | trim }}
{%- else -%}
{{ default_database }}
{%- endif -%}
{%- endmacro %}

This project policy means:

  1. If no custom database is configured, use the target's default database.
  2. In production, use the custom database.
  3. In development, ignore the custom database and stay in the developer's sandbox.

The target name must match your profile exactly. This example checks for prod; if your profile uses production, update the condition consistently.

4. Use the macro from a model​

Create this model:

models/hr/dim_employees.sql

{{ config(
materialized='table',
database='BEEMS_HR_PROD',
schema='hr'
) }}

SELECT
employee_id,
employee_name,
department,
salary_amount
FROM {{ ref('stg_employees') }}

The important setting is:

database='BEEMS_HR_PROD'

That value becomes custom_database_name in generate_database_name. dbt invokes the macro automatically while resolving the model relation.

You can also configure the same behavior in dbt_project.yml:

models:
your_project_name:
hr:
+database: BEEMS_HR_PROD
+schema: hr

Use either model-level configuration or project-level configuration according to your team's convention.

5. Compare development and production​

Assume Kavya's development profile contains:

target: dev
outputs:
dev:
type: snowflake
database: KAVYA_DEV
schema: DEV_KAVYA

Compile the model for development:

dbt compile --select dim_employees --target dev

The custom database is ignored, so the model remains in the sandbox:

KAVYA_DEV.HR.DIM_EMPLOYEES

Now compile with the production target:

dbt compile --select dim_employees --target prod

The custom database is respected:

BEEMS_HR_PROD.HR.DIM_EMPLOYEES

The model SQL does not change between environments. Only the active target changes the resolved database.

6. Inspect before running​

Use JSON output to inspect the resolved relation without creating the table:

dbt ls --select dim_employees --target dev --output json
dbt ls --select dim_employees --target prod --output json

Look for the relation_name field in each JSON record. It should show the database, schema, and identifier selected by dbt.

After reviewing the result, run the model only in the intended environment:

dbt run --select dim_employees --target dev

Only run the production target from an approved deployment environment. A local developer profile should normally use the dev target.

7. Common mistakes​

  • Calling generate_database_name() directly inside the model SQL.
  • Creating the macro outside the project macros/ directory.
  • Using database in the model but forgetting that this override intentionally ignores it in development.
  • Checking target.name == 'production' when the actual profile target is named prod.
  • Running a production target without confirming database permissions and governance requirements.
  • Assuming database routing also controls the schema. Configure schema separately or use generate_schema_name for schema behavior.
  • Referencing a model such as stg_employees that does not exist in the project.

Summary​

  • dbt uses generate_database_name to determine where a model is built at the database level.
  • This override keeps development models in the developer's sandbox database.
  • Production can route approved models to a custom database such as BEEMS_HR_PROD.
  • Activate the macro with database='BEEMS_HR_PROD'; do not call it inside the SELECT statement.
  • Use dbt compile and dbt ls --output json to inspect the resolved relation before execution.