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

Help
,
3

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:

{% 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:

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:

sources:
  - name: ic
  - name: ic_qa