Skip to main content
Documentation
Feature Requests Trust Center Status Page

Community

Transformations: Support multiple environments with deployment.yml

Reuben User

We wish to use a single dbt project to target multiple destinations. In particular, we want to run transformations against both a Test destination and a Prod destination, and use variables to control the source database definitions.

Without this, we are not able to do Staging testing of project changes in a fivetran environment, prior to releasing the changes to Prod. 

We need either;

• Ability to specify a destination-specific deployment.yml on the fivetran dbt Transactions setup page, analogous to the Project Path advanced setting, but specifying an alternative deployment.yml 

• Or, ability to specify destination variables to pass into the deployment.yml (in lieu of / analogous to environment variables) on the fivetran dbt Transactions setup page. Variables defined here would be mapped either as environment variables, or just made available to inject into deployment.yml 

Please sign in to leave a comment.

Comments

6 comments

Date Votes
  • Sandeep Kumar User

    This can be still done, I hope you are using target.name to select different db for test/prod etc. So in order to change the target name we can use the changes that came in March 2021 release. 

    https://fivetran.com/docs/changelog/march-2021#customizabletargetname

    In deployment.yml file now we can specify targetName: dev or prod. So jobs will be different for test/prod.

  • Sandeep Kumar User

    I had raised a similar request https://support.fivetran.com/hc/en-us/community/posts/1500000521381-Transformations-Allow-customization-to-the-generated-profiles-yml-for-Dbt

  • Reuben User

    Can't find any details on how to use targetName. If it's statically assigned inside the deployment.yml... that doesn't help me, although it's a step in the right direction. 

    Also, I don't want access to the fivetran generated profiles.yml. That holds credentials and I don't want them available. 

    Thanks for commenting Sandeep. 

  • Sandeep Kumar User

    HI Reuben,

    I will tell you what I have done to achieve this, do let me know if this still does not work out for you.

    Setting destination(dbt_project.yml):-

    models:
        <proj_name>:
            # Applies to all files under models/staging/
            staging:
                +materialized: view
                +database: |
                        {%- if  target.name == "dev" -%} <dev_db>
                        {%- elif target.name == "qa"  -%} <qa_db>
                        {%- elif target.name == "prod"  -%} <prod_db>
                        {%- else -%} invalid_database
                        {%- endif -%}
     
    Setting Source(source.yml):-
    sources:
      - name: salesforce
        description: Fivetran synced data from Salesforce
        database: |
                  {%- if  target.name == "dev" -%} FIVETRAN_POC
                  {%- elif target.name == "qa"  -%} FIVETRAN_QA
                  {%- elif target.name == "prod"  -%} FIVETRAN
                  {%- else -%} invalid_database
                  {%- endif -%}
        schema: |
                  {%- if  target.name == "dev" -%} SALESFORCE_SANDBOX
                  {%- elif target.name == "qa"  -%} SALESFORCE_uat
                  {%- elif target.name == "prod"  -%} SALESFORCE
                  {%- else -%} invalid_schema
                  {%- endif -%}
     
    deployment.yml(for scheduling via fivetran):-
    jobs:
    # Give each job a unique name. The name will be displayed in the Fivetran dashboard once your jobs are imported.
     - name: dev_daily
      targetName: dev
       schedule: 0 12 * * * # Define when this job should run, using cron format. This example will run every day at 12:00pm (according to your warehouse timezone). For help with cron formatting, visit https://crontab.guru/.
       steps:
         - name: run models # Give each step in your job a name. This will enable you to track the steps in the logs.
           command: dbt run # Enter the dbt command that should run in this step. This example will run all your models. For a list of available commands visit https://docs.getdbt.com/reference/model-selection-syntax/.
         - name: test models
           command: dbt test # This example will run all your tests.
     
     - name: prod_daily
       schedule: 0 12 * * * # Define when this job should run, using cron format. This example will run every day at 12:00pm (according to your warehouse timezone). For help with cron formatting, visit https://crontab.guru/.
       steps:
         - name: run models # Give each step in your job a name. This will enable you to track the steps in the logs.
           command: dbt run # Enter the dbt command that should run in this step. This example will run all your models. For a list of available commands visit https://docs.getdbt.com/reference/model-selection-syntax/.
         - name: test models
           command: dbt test # This example will run all your tests.
  • Jan van der Pas User

    Sandeep Kumar given you example does that mean that one job per destination is always failing? For example given your solution I would expect that in the dev/test destination the prod_daily task is failing and in the productioon destination the dev_daily task is failing.

  • Sandeep Kumar User

    Jobs in deployment.yml are for a specific environment, eg dev_daily job will be executed only for the dev target and not prod.