dbt™ database configuration looks simpler than schemas. It's not. You can accidentally write to production from dev, break permissions, or deploy code that works in CI but fails in prod.

This guide fixes that.

How dbt™ Database Configuration Works

Start in profiles.yml:

my_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      database: analytics_dev
      schema: dbt_john
      # ... connection details

    prod:
      type: snowflake
      database: analytics_prod
      schema

my_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      database: analytics_dev
      schema: dbt_john
      # ... connection details

    prod:
      type: snowflake
      database: analytics_prod
      schema

my_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      database: analytics_dev
      schema: dbt_john
      # ... connection details

    prod:
      type: snowflake
      database: analytics_prod
      schema

Run dbt run --target dev and every model lands in analytics_dev by default.

That's your base database. Everything else builds from here.

Target Database vs Custom Database

Two different things. Here's what they actually are.

Target database: Where dbt™ puts your models by default. Set in profiles.yml:

my_project:
  target: dev
  outputs:
    dev:
      database: analytics_dev  # ← This is your target database

my_project:
  target: dev
  outputs:
    dev:
      database: analytics_dev  # ← This is your target database

my_project:
  target: dev
  outputs:
    dev:
      database: analytics_dev  # ← This is your target database

Run dbt run --target dev and every model lands in analytics_dev. That's it.

Custom database: Your override. Set in the model:

{{ config(database='data_science') }}  # ← This is your custom database

select * from {{ ref('base_model') }}

{{ config(database='data_science') }}  # ← This is your custom database

select * from {{ ref('base_model') }}

{{ config(database='data_science') }}  # ← This is your custom database

select * from {{ ref('base_model') }}

When you set a custom database, dbt™ uses it directly. No concatenation. No prefix. Just switches databases.

Example:

• Target database: analytics_dev

• Custom database: data_science

• Where the model lands: data_science.dbt_john.my_model

Not analytics_dev_data_science. Just data_science.

Why no concatenation? Because database names need to be exact. You can't just make up database names. They have to exist and you need grants on them.

But this creates a problem. A big one.

The Problem with Custom Databases

Your model has database='data_science' in the config. Works in prod. Fails in dev.

Why? Your dev user doesn't have write access to data_science. Or worse - you accidentally write to the production data_science database from your laptop.

Both bad.

Here's what happens by default:

See it? Everyone hits the same data_science database. If it's the prod database, you just wrote dev data to prod. If it's a database you don't have access to, your run fails.

You need environment-specific logic.

Understanding Database Behavior: No Concatenation

Unlike schemas, databases don't concatenate. The generate_database_name macro works differently:

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

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

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

No underscore. No concatenation. If you set a custom database, dbt™ uses it directly.

This is why you need to override it.

Controlling Database Behavior: Development vs Production

Production needs cross-database writes. Dev doesn't (and shouldn't).

Create macros/get_custom_database.sql:

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

    {%- if target.name == 'prod' -%}
        {%- if custom_database_name is none -%}
            {{ default_database }}
        {%- else -%}
            {{ custom_database_name | trim }}
        {%- endif -%}
    {%- else -%}
        {# In dev, always use dev database #}
        {{ default_database }}
    {%- endif -%}
{%- endmacro %}

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

    {%- if target.name == 'prod' -%}
        {%- if custom_database_name is none -%}
            {{ default_database }}
        {%- else -%}
            {{ custom_database_name | trim }}
        {%- endif -%}
    {%- else -%}
        {# In dev, always use dev database #}
        {{ default_database }}
    {%- endif -%}
{%- endmacro %}

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

    {%- if target.name == 'prod' -%}
        {%- if custom_database_name is none -%}
            {{ default_database }}
        {%- else -%}
            {{ custom_database_name | trim }}
        {%- endif -%}
    {%- else -%}
        {# In dev, always use dev database #}
        {{ default_database }}
    {%- endif -%}
{%- endmacro %}

Now:

• Production: database='data_science' → data_science

• Dev: database='data_science' → analytics_dev (ignores config)

Developers can't accidentally write to prod databases. Config stays the same across environments. Problem solved.

Database Configuration Priority

Four places to set databases. Most specific wins.

1. Model Config Block

{{ config(database='data_science') }}

{{ config(database='data_science') }}

{{ config(database='data_science') }}

Highest priority. Overrides everything.

2. dbt_project.yml

models:
  my_project:
    exports:
      +database: data_science
      +schema: exports

    staging:
      +database: raw
      +schema

models:
  my_project:
    exports:
      +database: data_science
      +schema: exports

    staging:
      +database: raw
      +schema

models:
  my_project:
    exports:
      +database: data_science
      +schema: exports

    staging:
      +database: raw
      +schema

Directory-level configs. Applies to all models in the tree.

3. schema.yml

models:
  - name: export_to_science
    config:
      database

models:
  - name: export_to_science
    config:
      database

models:
  - name: export_to_science
    config:
      database

Model properties. Lower priority than config blocks.

4. Target Database

From profiles.yml. Your base database. Used when nothing else is set.

Common Database Patterns

Layered by Database

models:
  my_project:
    staging:
      +database: raw
      +schema: staging

    marts:
      +database: analytics
      +schema: marts

    exports:
      +database: data_science
      +schema

models:
  my_project:
    staging:
      +database: raw
      +schema: staging

    marts:
      +database: analytics
      +schema: marts

    exports:
      +database: data_science
      +schema

models:
  my_project:
    staging:
      +database: raw
      +schema: staging

    marts:
      +database: analytics
      +schema: marts

    exports:
      +database: data_science
      +schema

Regional Separation

models:
  my_project:
    eu_models:
      +database: analytics_eu

    us_models:
      +database

models:
  my_project:
    eu_models:
      +database: analytics_eu

    us_models:
      +database

models:
  my_project:
    eu_models:
      +database: analytics_eu

    us_models:
      +database

Dev Prefixing Pattern

Want to track costs by team but keep everything in dev?

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

    {%- if target.name == 'prod' -%}
        {%- if custom_database_name is none -%}
            {{ default_database }}
        {%- else -%}
            {{ custom_database_name | trim }}
        {%- endif -%}
    {%- else -%}
        {# Add prefix to custom databases in dev #}
        {%- if custom_database_name is none -%}
            {{ default_database }}
        {%- else -%}
            {{ default_database }}_{{ custom_database_name | trim }}
        {%- endif -%}
    {%- endif -%}
{%- endmacro %}

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

    {%- if target.name == 'prod' -%}
        {%- if custom_database_name is none -%}
            {{ default_database }}
        {%- else -%}
            {{ custom_database_name | trim }}
        {%- endif -%}
    {%- else -%}
        {# Add prefix to custom databases in dev #}
        {%- if custom_database_name is none -%}
            {{ default_database }}
        {%- else -%}
            {{ default_database }}_{{ custom_database_name | trim }}
        {%- endif -%}
    {%- endif -%}
{%- endmacro %}

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

    {%- if target.name == 'prod' -%}
        {%- if custom_database_name is none -%}
            {{ default_database }}
        {%- else -%}
            {{ custom_database_name | trim }}
        {%- endif -%}
    {%- else -%}
        {# Add prefix to custom databases in dev #}
        {%- if custom_database_name is none -%}
            {{ default_database }}
        {%- else -%}
            {{ default_database }}_{{ custom_database_name | trim }}
        {%- endif -%}
    {%- endif -%}
{%- endmacro %}

Results:

• Prod: database='data_science' → data_science

• Dev: database='data_science' → analytics_dev_data_science

All dev databases clearly marked. Structure mirrors prod.

Why Cross-Database Writes?

Common scenarios:

Data science sandboxes: Your data science team needs tables in their own database for experiments and model training.

Regional isolation: EU data in analytics_eu, US data in analytics_us for compliance.

Access control: Sensitive data in secure_analytics, everything else in analytics.

Cost tracking: Separate databases for different departments to track spend.

Source system alignment: Keep staging close to source systems in their native databases.

Debugging Database Issues

See where models compile

dbt compile --select my_model
# Check target/compiled/my_project/...

dbt compile --select my_model
# Check target/compiled/my_project/...

dbt compile --select my_model
# Check target/compiled/my_project/...

Full database.schema.table reference is right there in the compiled SQL.

List models with locations

dbt ls --select my_model --output

dbt ls --select my_model --output

dbt ls --select my_model --output

Debug the macro

{% macro generate_database_name(custom_database_name, node) -%}
    {{ log("Target: " ~ target.name, info=True) }}
    {{ log("Database: " ~ target.database, info=True) }}
    {{ log("Custom: " ~ custom_database_name, info=True) }}

    -- your logic
{%- endmacro %}

{% macro generate_database_name(custom_database_name, node) -%}
    {{ log("Target: " ~ target.name, info=True) }}
    {{ log("Database: " ~ target.database, info=True) }}
    {{ log("Custom: " ~ custom_database_name, info=True) }}

    -- your logic
{%- endmacro %}

{% macro generate_database_name(custom_database_name, node) -%}
    {{ log("Target: " ~ target.name, info=True) }}
    {{ log("Database: " ~ target.database, info=True) }}
    {{ log("Custom: " ~ custom_database_name, info=True) }}

    -- your logic
{%- endmacro %}

Run dbt compile and watch what values get passed in.

Test permissions

dbt run --select my_model --target prod --dry-run

dbt run --select my_model --target prod --dry-run

dbt run --select my_model --target prod --dry-run

Best Practices

Override generate_database_name for dev environments. The default behavior is dangerous.

Production should allow custom databases. Dev should ignore them or prefix them.

Document which databases exist and what they're for. Drop a note in the project README.

Use database configs sparingly. Most projects don't need them. Schemas are usually enough.

What Not to Do

Don't Allow Cross-Database Writes in Dev

Some people want feature parity between dev and prod:

{% macro generate_database_name(custom_database_name, node) -%}
    {%- set default_database = target.database -%}
    {%- if custom_database_name is none -%}
        {{ default_database }}
    {%- else -%}
        {{ custom_database_name | trim }}  # ❌ Works in all environments
    {%- endif -%}
{%- endmacro %}

{% macro generate_database_name(custom_database_name, node) -%}
    {%- set default_database = target.database -%}
    {%- if custom_database_name is none -%}
        {{ default_database }}
    {%- else -%}
        {{ custom_database_name | trim }}  # ❌ Works in all environments
    {%- endif -%}
{%- endmacro %}

{% macro generate_database_name(custom_database_name, node) -%}
    {%- set default_database = target.database -%}
    {%- if custom_database_name is none -%}
        {{ default_database }}
    {%- else -%}
        {{ custom_database_name | trim }}  # ❌ Works in all environments
    {%- endif -%}
{%- endmacro %}

Bad idea. Here's what happens:

Everyone writes to the same database. Developer 1 and Developer 2 overwrite each other. CI builds collide. If data_science is the prod database, you just mixed dev data into prod.

Fix: Block custom databases in dev.

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

    {%- if target.name == 'prod' -%}
        {%- if custom_database_name is none -%}
            {{ default_database }}
        {%- else -%}
            {{ custom_database_name | trim }}  # Custom databases work
        {%- endif -%}
    {%- else -%}
        {{ default_database }}  # Force dev database
    {%- endif -%}
{%- endmacro %}

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

    {%- if target.name == 'prod' -%}
        {%- if custom_database_name is none -%}
            {{ default_database }}
        {%- else -%}
            {{ custom_database_name | trim }}  # Custom databases work
        {%- endif -%}
    {%- else -%}
        {{ default_database }}  # Force dev database
    {%- endif -%}
{%- endmacro %}

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

    {%- if target.name == 'prod' -%}
        {%- if custom_database_name is none -%}
            {{ default_database }}
        {%- else -%}
            {{ custom_database_name | trim }}  # Custom databases work
        {%- endif -%}
    {%- else -%}
        {{ default_database }}  # Force dev database
    {%- endif -%}
{%- endmacro %}

Production gets cross-database writes. Dev stays isolated. Problem solved.

Don't Skip the Macro Override

"I'll just be careful" doesn't work. Someone will run dbt run against the wrong target. Someone will forget to check their active profile. Someone will deploy code without testing.

The macro is your safety net. Use it.

Key Takeaways

Default behavior: dbt™ uses custom databases directly. No concatenation. This is dangerous in dev.

• Override generate_database_name to control database behavior by environment.

• Production gets cross-database writes. Dev ignores custom databases or prefixes them.

• Configure at model, directory, or project level. Most specific wins.

• Test permissions before deploying. Grant failures are common.