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
--targetFlag - 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 yourprofiles.yml. - Set a default target, and override it using the
--targetCLI 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.