Environment management (dev, staging, prod) in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Purpose of Different Environments in dbt

Why do data teams use separate environments like dev, staging, and prod in dbt projects?

A To allow multiple users to edit the same model simultaneously without conflicts

B To run the same models multiple times for faster results

C To isolate development work, test changes safely, and deploy stable models to production

D To store different versions of data in separate databases permanently

💡 Hint

Think about how changes are tested before affecting real data.

Separate environments help teams develop and test changes without affecting live data. Dev is for building, staging for testing, and prod for stable, live data.

❓ Predict Output

intermediate

dbt Profile Target Output

Given this profiles.yml snippet, what is the active target when running dbt run --target staging?

my_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      database: dev_db
    staging:
      type: snowflake
      database: staging_db
    prod:
      type: snowflake
      database: prod_db

A The database used will be <code>staging_db</code>

B The database used will be <code>dev_db</code>

C The database used will be <code>prod_db</code>

D dbt will raise an error because <code>target</code> is set to <code>dev</code> in the profile

💡 Hint

The --target flag overrides the default target in the profile.

Running dbt run --target staging uses the staging output configuration, connecting to staging_db.

❓ data_output

advanced

Effect of Environment Variable in dbt Model

Consider this dbt model SQL snippet that uses an environment variable to filter data:

select * from raw.sales
where region = '{{ env_var('REGION', 'all') }}'

If you run dbt run with REGION set to north, what will be the output?

A All rows because default <code>all</code> is used

B Rows where <code>region</code> equals <code>north</code>

C No rows because <code>region</code> is filtered by a missing variable

D Syntax error due to incorrect use of <code>env_var</code>

💡 Hint

Check how env_var uses the environment variable if set.

The env_var function uses the environment variable REGION if set, filtering rows to region = 'north'.

🔧 Debug

advanced

Debugging Environment-Specific Model Failures

A model runs fine in dev but fails in prod with a missing table error. What is the most likely cause?

A The <code>prod</code> environment has incorrect Python dependencies

B The model SQL syntax is invalid only in <code>prod</code>

C The <code>dev</code> environment has a different dbt version causing the error

D The <code>prod</code> environment is missing a required source table or schema

💡 Hint

Think about differences in data availability between environments.

Missing tables or schemas in prod cause errors, while dev has them available.

🚀 Application

expert

Configuring dbt for Multiple Environments

You want to configure dbt to use different schemas for dev, staging, and prod environments automatically. Which dbt_project.yml snippet correctly sets the schema based on the active target?

A

models:
  my_project:
    +schema: '{{ target.name }}_schema'

B

models:
  my_project:
    +schema: '{{ target.schema }}'

C

models:
  my_project:
    +schema: 'prod_schema' if target.name == 'prod' else 'dev_schema'

D

models:
  my_project:
    +schema: 'schema_{{ env_var('DB_ENV') }}'

💡 Hint

Use the target variable to dynamically set schema names.

Using {{ target.name }}_schema sets schema names like dev_schema, staging_schema, or prod_schema automatically based on the active target.