Skip to main content

Multiple Targets (Dev vs Prod)

The fundamental difference between old-school SQL scripting and modern Analytics Engineering is the strict separation of Environments.

In old-school scripting, an analyst writes a CREATE TABLE script and runs it directly in the Production database. If they make a typo, the CEO's dashboard breaks.

In dbt, you develop and test your code in a personal Sandbox Environment (Dev). Once your code is peer-reviewed and tested, an automated system deploys exactly the same code to the Production Environment (Prod).

To achieve this, dbt uses Targets.

Topics Covered​

  • Configuring Multiple Targets in profiles.yml
  • The --target Flag
  • The default target
  • The {{ target.name }} Jinja variable

1. Configuring Multiple Targets​

Open your ~/.dbt/profiles.yml file. You will notice an outputs: block. Under this block, you can define as many target environments as you want.

Let's assume our developer, Priya, wants to set up a local development sandbox, but also wants the ability to manually deploy to production (usually, a CI/CD tool does this, but for this example, Priya will do it).

# ~/.dbt/profiles.yml
beems_retail:
target: dev # This is the DEFAULT target if no flag is passed

outputs:
# 1. The Development Target (Priya's Sandbox)
dev:
type: snowflake
account: abc1234.ap-south-1.aws
user: PRIYA_DEV
password: "{{ env_var('DBT_PASSWORD') }}"
role: SYSADMIN
database: BEEMS_DEV
warehouse: COMPUTE_WH
schema: DEV_PRIYA # Her isolated sandbox schema

# 2. The Production Target
prod:
type: snowflake
account: abc1234.ap-south-1.aws
user: DBT_PRODUCTION_USER
password: "{{ env_var('DBT_PROD_PASSWORD') }}"
role: SYSADMIN
database: BEEMS_PROD
warehouse: HEAVY_COMPUTE_WH # A larger warehouse for production
schema: MARTS # The final, clean production schema

2. Using the --target Flag​

Now that Priya has defined two environments, how does she tell dbt which one to use?

Developing Locally​

If Priya is writing a new fct_orders.sql model and wants to test it, she runs:

dbt run

Because target: dev is set at the top of her profiles.yml, dbt will automatically build the table in BEEMS_DEV.DEV_PRIYA.fct_orders. The production data is completely untouched and safe.

Deploying to Production​

Once Priya's Pull Request is approved, she wants to deploy her code to Production. She runs the exact same command, but passes the --target flag:

dbt run --target prod

dbt looks at the prod block in her profiles.yml, connects as DBT_PRODUCTION_USER, and builds the table in BEEMS_PROD.MARTS.fct_orders.

Notice the magic: Priya didn't have to change a single line of SQL code! dbt dynamically routed the execution to the correct database and schema based on the Target.

3. The {{ target.name }} Jinja Variable​

Sometimes, you need your actual SQL code to behave differently depending on the environment.

For example, your raw stripe_payments table has 5 billion rows. If Priya runs dbt run locally in her sandbox, building 5 billion rows will take 2 hours. She only wants to process 3 days of data when she is developing, but she wants to process all 5 billion rows in Production.

She can use the {{ target.name }} variable inside her SQL file!

-- models/staging/stg_stripe_payments.sql

SELECT *
FROM raw_database.stripe.payments

{% if target.name == 'dev' %}
-- If we are developing locally, only grab 3 days of data to save time!
WHERE created_at >= CURRENT_DATE - 3
{% endif %}

When Priya runs dbt run, the WHERE clause is added to the SQL, and she tests her code in 5 seconds. When GitHub Actions runs dbt run --target prod, the target.name is prod. The {% if %} statement evaluates to False, the WHERE clause is completely omitted, and all 5 billion rows are processed for the final dashboard!

Summary​

  • Targets allow you to separate Development Sandboxes from the Production environment.
  • You configure targets under the outputs: block in your profiles.yml.
  • Set a default target, and override it using the --target CLI flag.
  • Use the Jinja variable {{ target.name }} in your SQL code to apply limits or filters strictly in development to save time and compute costs.