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_namemacro. - 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 indbt_project.ymlnode: metadata for the model being builttarget.database: the default database for the active profile targettarget.name: the name of the active target, such asdevorprod
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:
- If no custom database is configured, use the target's default database.
- In production, use the custom database.
- 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
devtarget.
7. Common mistakes
- Calling
generate_database_name()directly inside the model SQL. - Creating the macro outside the project
macros/directory. - Using
databasein the model but forgetting that this override intentionally ignores it in development. - Checking
target.name == 'production'when the actual profile target is namedprod. - Running a production target without confirming database permissions and governance requirements.
- Assuming database routing also controls the schema. Configure
schemaseparately or usegenerate_schema_namefor schema behavior. - Referencing a model such as
stg_employeesthat does not exist in the project.
Summary
- dbt uses
generate_database_nameto 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 theSELECTstatement. - Use
dbt compileanddbt ls --output jsonto inspect the resolved relation before execution.