Environment management (dev, staging, prod) in dbt - Mini Project: Build & Apply

Environment management (dev, staging, prod)

📖 Scenario: You work as a data analyst using dbt to build data models. Your team uses three environments: development (dev), staging, and production (prod). Each environment has its own database schema to keep data separate and safe.Managing these environments correctly helps you test changes without affecting live data.

🎯 Goal: You will create a dbt project configuration that defines separate schemas for dev, staging, and prod environments. Then, you will write a model that uses the correct schema based on the environment. Finally, you will print the schema name used in the current environment.

📋 What You'll Learn

Create a dbt profiles.yml configuration with dev, staging, and prod schemas

Create dbt_project.yml linking to the profile

Write a dbt model SQL file that uses the schema based on target

Print the schema name used for the current environment

💡 Why This Matters

🌍 Real World

Managing multiple environments in dbt helps teams develop and test data models safely without affecting production data.

💼 Career

Data engineers and analysts often need to configure and use environment management in dbt to ensure reliable and safe data workflows.

1

Create dbt profiles.yml with dev, staging, and prod schemas

Create a profiles.yml file with a profile named my_dbt_project. Define three targets: dev with schema my_schema_dev, staging with schema my_schema_staging, and prod with schema my_schema_prod. Set dev as the default target.

dbt

# Write the profiles.yml content here
# Your code here

Need a hint?

Use YAML syntax to define the profile and targets. Remember to indent properly.

2

Create the dbt_project.yml configuration

Create a dbt_project.yml file named my_dbt_project that links to the profile my_dbt_project. Use config-version: 2.

dbt

# Write the dbt_project.yml content here
# Your code here

Need a hint?

Define the project name, version, config-version, and profile in YAML format.

3

Write a dbt model SQL file using the schema based on target

Create a dbt model SQL file named my_model.sql. Use the Jinja variable target to select the schema dynamically. Write a simple SELECT statement that selects all columns from a table named source_table in the schema {{ target.schema }}.

dbt

-- Write SQL for my_model.sql below
-- Your code here

Need a hint?

Use Jinja templating syntax with double curly braces to insert the schema dynamically.

4

Print the schema name used in the current environment

Modify your my_model.sql to log the schema name used for the current target environment using dbt's {{ log() }} function.

dbt

-- Add the log statement here
-- Your code here

Need a hint?

Use {{ log('Current schema: ' ~ target.schema) }} to print the schema name dynamically.