target_database

note

For versionless dbt Cloud accounts and dbt Core v1.9+, this functionality is no longer utilized. Use the database config as an alternative to define a custom database while still respecting the generate_database_name macro.

dbt_project.yml

snapshots:
  <resource-path>:
    +target_database: string

snapshots/<filename>.sql

{{ config(
  target_database="string"
) }}

Description

The database that dbt should build a snapshot table into.

Notes:

• The specified database must already exist
• On BigQuery, this is analogous to a project.
• On Redshift, cross-database queries are not possible. If you use this parameter, you will receive the following error. As such, do not use this parameter on Redshift:

Encountered an error:
Runtime Error
  Cross-db references not allowed in redshift (raw vs analytics)

Default

By default, dbt will use the target database associated with your profile/connection.

Examples

Build all snapshots in a database named snapshots

dbt_project.yml

snapshots:
  +target_database: snapshots

Use a target-aware database

Use the {{ target }} variable to change which database a snapshot table is built in.

Note: consider whether this use-case is right for you, as downstream refs will select from the dev version of a snapshot, which can make it hard to validate models that depend on snapshots.

dbt_project.yml

snapshots:
  +target_database: "{% if target.name == 'dev' %}dev{% else %}{{ target.database }}{% endif %}"

Use the same database-naming behavior as models

Leverage the generate_database_name macro to build snapshots in databases that follow the same naming behavior as your models.

Notes:

• This macro is not available when configuring from the dbt_project.yml file, so it must be configured in a snapshot config block.
• Consider whether this use-case is right for you, as downstream refs will select from the dev version of a snapshot, which can make it hard to validate models that depend on snapshots.

snapshots/orders_snaphot.sql

{{
    config(
      target_database=generate_database_name('snapshots')
    )
}}