Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 41 ยท Packages and Deployment

Environments, Targets and Profiles

Separate dev and production in dbt with profiles.yml targets, env_var(), custom schemas and generate_schema_name. Includes a runnable schema-naming stand-in.

You never want to test a half-written model on the tables your CEO's dashboard reads. dbt solves this with targets: named sets of connection settings, so the same project builds into a personal development schema on your laptop and into the production schema when a scheduled job runs. This lesson shows how targets, profiles and schemas fit together.

profiles.yml

Connection details do not belong in the repository, because they contain credentials. dbt reads them from a profiles.yml file, by default in ~/.dbt/. The profile: name in dbt_project.yml selects an entry:

YAML
# ~/.dbt/profiles.yml
shop_analytics:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      user: "{{ env_var('DBT_USER') }}"
      password: "{{ env_var('DBT_PASSWORD') }}"
      port: 5432
      dbname: analytics
      schema: dbt_alice
      threads: 4

    prod:
      type: postgres
      host: warehouse.example.com
      user: "{{ env_var('DBT_PROD_USER') }}"
      password: "{{ env_var('DBT_PROD_PASSWORD') }}"
      port: 5432
      dbname: analytics
      schema: analytics
      threads: 16
YAML
# dbt_project.yml
name: shop
profile: shop_analytics

Each entry under outputs is a target. target: dev picks the default. Switch with the command line:

Terminal
dbt run                     # uses target dev, schema dbt_alice
dbt run --target prod       # uses target prod, schema analytics
dbt debug                   # verify the connection and project

The key difference between environments is the schema (and sometimes the database or warehouse size). Every developer has a personal schema (dbt_alice, dbt_ben), so nobody overwrites another person's work, and production data stays untouched until a deploy.

env_var and secrets

{{ env_var('NAME') }} reads an environment variable at runtime. Use it for passwords, tokens and account names, never plain text. env_var('NAME', 'default') gives a fallback. Variables prefixed DBT_ENV_SECRET_ are scrubbed from logs. You can also use env vars in dbt_project.yml and in models, for example to read a start date.

Using the target in code

The target object is available in Jinja:

SQL
select *
from {{ ref('stg_orders') }}

{% if target.name == 'dev' %}
  where ordered_at >= current_date - interval '30 days'
{% endif %}

That keeps development runs small and fast: dev builds only the last 30 days, while production builds everything. Useful target fields: target.name, target.schema, target.type, target.database, target.threads. Do not let logic differ so much between dev and prod that dev stops predicting production.

Custom schemas and generate_schema_name

Setting +schema: marts on a folder does not mean the model lands in a schema literally called marts. By default dbt concatenates: <target schema>_<custom schema>, giving dbt_alice_marts in dev and analytics_marts in prod. That keeps developers separated, but many teams prefer clean names in production (marts) and prefixed names in dev.

You control this by overriding the macro generate_schema_name:

SQL
-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- if target.name == 'prod' and custom_schema_name is not none -%}
        {{ custom_schema_name | trim }}
    {%- elif custom_schema_name is none -%}
        {{ target.schema }}
    {%- else -%}
        {{ target.schema }}_{{ custom_schema_name | trim }}
    {%- endif -%}
{%- endmacro %}

The rule in words: in prod use the custom name alone; elsewhere prefix it with the developer's schema. The same rule, written as SQL so you can try different targets:

SQL Stand-in: schema naming per target

Press Run (or Ctrl + Enter) to see the result here.

In prod, fct_orders goes to marts; in dev it goes to dbt_alice_marts.

Environments in dbt Cloud and CI

dbt Cloud has environments (development, staging, production) with their own connection settings and jobs that run in them (see deploying jobs). With dbt Core you create the same idea with targets and CI variables. A typical setup:

Environment Who runs it Schema Data
dev A person, on their laptop dbt_<name> Limited, recent
ci A pull request check dbt_ci_<pr number> Limited or cloned
prod A scheduled job analytics Full
flowchart LR
  D["dev: dbt_alice"] -->|"pull request"| C["ci: dbt_ci_123"]
  C -->|"merge"| P["prod: analytics"]

Code moves between environments; data does not.

Common mistakes

  • Committing profiles.yml with passwords. Keep it outside the repo and use env_var().
  • Running dbt run and forgetting that the default target is prod. Make dev the default.
  • Letting dev builds hit production source tables with write access: give dev users read-only access to sources.
  • Hard-coding schema names in SQL instead of using ref() and source().

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is a dbt target?

    Each entry under outputs is a target, such as dev or prod, chosen with the target key or --target.
  2. 2. How should you provide a database password in profiles.yml?

    env_var reads the environment at runtime, so no secret lands in version control.
  3. 3. By default, where does a model with +schema: marts land when the target schema is dbt_alice?

    The default generate_schema_name joins target schema and custom schema with an underscore.
  4. 4. Which macro do you override to change schema naming?

    generate_schema_name receives the custom schema and node and returns the final schema.

Summary

  • profiles.yml holds targets (dev, prod); pick one with target: or --target.
  • Keep secrets in environment variables with env_var().
  • The target object lets code differ by environment, in moderation.
  • Override generate_schema_name for clean production schemas and prefixed dev schemas.

Next: Deploying dbt: Jobs and Scheduling