# How do I specify a different schema for my source at run time?

**URL:** <https://discourse.getdbt.com/t/how-do-i-specify-a-different-schema-for-my-source-at-run-time/561>\
**Category:** Help\
**Tags:** variables, yaml\
**Created:** [September 9, 2019, 4:44pm UTC](https://discourse.getdbt.com/t/how-do-i-specify-a-different-schema-for-my-source-at-run-time/561 "2019-09-09T16:44:38Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![chanwd](https://avatars.discourse-cdn.com/v4/letter/c/b782af/32.png) [@chanwd](https://discourse.getdbt.com/u/chanwd)\
**Post date:** [September 9, 2019, 4:44pm UTC](https://discourse.getdbt.com/t/how-do-i-specify-a-different-schema-for-my-source-at-run-time/561/1 "2019-09-09T16:44:38Z")

</div>

Is it possible for me to specify a different schema at the DBT command line to override the one that is present in my source .yml file?

Here’s an example of what we have today:

```auto
sources:
  - name: IC
    description: "IC data from MySQL Prod"
    database: ic_reporting
    schema: icmysql

```

Currently all of my staging tables are referencing IC as a source. I’d like to be able to change the schema to `icmysqlqa` when I’m running this code via my DBT Cloud job that runs for our QA environment. For Production and Dev, I’d like for this value to be as is.

I also thought about creating another source called IC\_qa but that would involve repeating the metadata I already have in the other source and that wouldn’t be good for maintenance.

Thanks  
W

---

<div class="post-metadata">

**Author:** ![drew](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/drew/32/9_2.png) [@drew](https://discourse.getdbt.com/u/drew)\
**Post date:** [September 9, 2019, 5:39pm UTC](https://discourse.getdbt.com/t/how-do-i-specify-a-different-schema-for-my-source-at-run-time/561/2 "2019-09-09T17:39:27Z")

</div>

Hey @chanwd - this is a great question! I think you generally have two options here:

## 1. Use a `var()`

In this approach, you can supply a variable on the command line (or in your dbt Cloud step) which renders into the definition of the source. In practice, this might look like:

```auto
sources:
  - name: IC
    description: “IC data from MySQL Prod”
    database: ic_reporting
    schema: "{{ var('ic_schema', 'icmysql') }}"

```

This configuration sets up a dbt [Variable](https://docs.getdbt.com/docs/var) called `ic_schema` with a default value of `'icmysql'`. This value can be overridden on the command line using the `--vars` flag, eg:

```auto
$ dbt run --vars 'ic_schema: icmysqlqa'

```

When this variable is supplied, dbt will set the source schema for the `IC` dataset to be `icmysqlqa` instead of `icmysql`.

This is a pretty good approach if you have a single dataset where you want to toggle between dev and prod source datasets. It doesn’t scale quite as well if you have more than one such dataset, or if you want to introduce additional environment-specific datasets.

## 2. Use YAML Anchors

The YAML spec supports [Anchors](https://confluence.atlassian.com/bitbucket/yaml-anchors-960154027.html), which should work in dbt source definitions. Anchors (and aliases) could be a good way to maintain separate definitions for these sources without duplicating any attached metadata/tests.

In practice, this might look like:

```auto
version: 2

sources:
    - name: ic_prod
      schema: icmysql
      tables: &tables
          - name: table1
            description: "abc123"
          - name: table2
            description: "def456"

    - name: ic_qa
      schema: icmysqlqa
      tables: *tables

```

This config would create two difference sources with an identical set of tables. With this syntax, you can also overrides tests/descriptions for specific environments if necessary - check out the “Override values” section in the link shared above!

The big benefit here is flexibility: you can have a dedicated source for each dataset + environment pair. If you go this route, you can switch between them in you staging models, eg:

```auto
-- models/ic/stg_model.sql

select *

{% if target.name == 'qa' %}
from {{ source('ic_qa', 'table_1') }}
{% else %}
from {{ source('ic', 'table_1') }}
{% endif %}

```

Let us know which of these you end up going with!

---

<div class="post-metadata">

**Author:** ![chanwd](https://avatars.discourse-cdn.com/v4/letter/c/b782af/32.png) [@chanwd](https://discourse.getdbt.com/u/chanwd)\
**Post date:** [September 9, 2019, 7:16pm UTC](https://discourse.getdbt.com/t/how-do-i-specify-a-different-schema-for-my-source-at-run-time/561/3 "2019-09-09T19:16:36Z")

</div>

Thanks for taking the time to answer my question so thoroughly and promptly.

I opted for the 2nd option and created a 2nd source to represent my QA database information while also leveraging the aliases to avoid repeating information within the yml file.

I then created a macro that takes two parameters (schema and table) and called it `db_source`.

Something like this:

```auto
{% macro db_source(source_name, table_name) %}

{% if target.name == 'qa' %} 
{{ source(source_name + '_qa', table_name)}}
{% else %}
{{ source(source_name, table_name)}}
{% endif %}

{% endmacro %}

```

And then referencing it in all of my staging table SQL as:

```auto
from {{ db_source('ic', 'fct_table') }}

```

The one thing I do want to point out with the macro is that the expectation is that each of your sources are distinguished by a suffix, so QA should have a section with the same name as production but with `_qa` to distinguish the yml source name.

Something like this:

```auto
sources:
  - name: ic
  - name: ic_qa

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [September 7, 2022, 7:28pm UTC](https://discourse.getdbt.com/t/how-do-i-specify-a-different-schema-for-my-source-at-run-time/561/4 "2022-09-07T19:28:48Z")

</div>

Closed on September 7, 2022, 7:28pm UTC

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.