Source Overrides for Environments
In a mature Data Engineering pipeline, you never develop directly against production data. You have multiple environments:
- DEV: Your personal sandbox.
- STAGING (or QA): Where automated tests run before deployment.
- PROD: The live production environment.
When you configure a Source in sources.yml, you define the database and schema where the raw data lives. But what happens if the raw data lives in BEEMS_PROD_RAW in production, but BEEMS_DEV_RAW in your development environment?
If you hardcode BEEMS_PROD_RAW in your sources.yml, your development code will read from production, which is a massive security risk!
Topics Covered
- The Multi-Environment Problem
- The
target.nameVariable - Hands-On: Configuring Source Overrides
1. The Multi-Environment Problem
Let's look at a typical, hardcoded sources.yml file:
version: 2
sources:
- name: salesforce_crm
database: BEEMS_PROD_RAW # Hardcoded!
schema: SALESFORCE
tables:
- name: customers
- name: orders
If an analyst (e.g., Kavya) runs dbt run locally, dbt will compile {{ source('salesforce_crm', 'customers') }} into:
FROM BEEMS_PROD_RAW.SALESFORCE.customers.
Kavya is developing against the live production raw data. If she accidentally runs a script that modifies the data (or if your company policy forbids developers from seeing raw PII data), this violates security protocols.
2. The target.name Variable
dbt provides a special Jinja variable called {{ target.name }}.
When you configure your connection in profiles.yml (or in dbt Cloud Environments), you specify a "target" name.
- For local development, the target name is usually
dev. - For the CI pipeline, the target name is usually
ciorstaging. - For the official production run, the target name is
prod.
We can use Jinja if/else logic inside our sources.yml file to dynamically change the database based on which environment is running the code!
3. Hands-On: Configuring Source Overrides
Let's rewrite our sources.yml file using Jinja to safely override the database name.
Step 1: Update sources.yml
(Verify your target name in your CLI by running dbt debug or checking your dbt Cloud Environment settings).
version: 2
sources:
- name: salesforce_crm
# Use Jinja to dynamically set the database based on the target environment!
database: |
{%- if target.name == 'prod' -%}
BEEMS_PROD_RAW
{%- elif target.name == 'staging' -%}
BEEMS_STG_RAW
{%- else -%}
BEEMS_DEV_RAW
{%- endif -%}
schema: SALESFORCE
tables:
- name: customers
- name: orders
Step 2: How it Compiles
Now, let's see what happens when different systems compile the exact same model code:
SELECT * FROM {{ source('salesforce_crm', 'customers') }}
Scenario A: Kavya runs dbt run locally (target = dev)
- dbt evaluates the Jinja.
target.nameis not 'prod' or 'staging'. It falls to theelseblock. - Compiled SQL:
FROM BEEMS_DEV_RAW.SALESFORCE.customers - Kavya safely queries masked development data.
Scenario B: GitHub Actions runs a CI test (target = staging)
- dbt evaluates the Jinja.
target.nameis 'staging'. - Compiled SQL:
FROM BEEMS_STG_RAW.SALESFORCE.customers - The CI pipeline tests against QA data.
Scenario C: dbt Cloud runs the nightly batch job (target = prod)
- dbt evaluates the Jinja.
target.nameis 'prod'. - Compiled SQL:
FROM BEEMS_PROD_RAW.SALESFORCE.customers - The production pipeline reads the live, raw data.
4. Alternative: Overriding Schemas
You can use this exact same logic to override the schema property instead of the database property, depending on how your data warehouse is structured.
sources:
- name: stripe_payments
database: BEEMS_DB
schema: |
{%- if target.name == 'prod' -%}
RAW_STRIPE
{%- else -%}
DEV_RAW_STRIPE
{%- endif -%}
Summary
- Never hardcode production database names in your
sources.ymlif developers need to read from masked/sandbox data. - Use the
{{ target.name }}variable to identify the current environment (dev,staging,prod). - Use Jinja
if/elseblocks inside your YAML files to dynamically route{{ source() }}calls to the correct database or schema.