Skip to main content

Source Overrides for Environments

In a mature Data Engineering pipeline, you never develop directly against production data. You have multiple environments:

  1. DEV: Your personal sandbox.
  2. STAGING (or QA): Where automated tests run before deployment.
  3. 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.name Variable
  • 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 ci or staging.
  • 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.name is not 'prod' or 'staging'. It falls to the else block.
  • 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.name is '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.name is '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.yml if developers need to read from masked/sandbox data.
  • Use the {{ target.name }} variable to identify the current environment (dev, staging, prod).
  • Use Jinja if/else blocks inside your YAML files to dynamically route {{ source() }} calls to the correct database or schema.