dbt Setup Guide Beta

Get started

1. In the left column of your Fivetran dashboard, go to Transformations.

2. A pop-up message appears that prompts you to set up Transformations for dbt Core. Click on it.

3. In the pop-up window, you’ll be prompted to choose a setup option. Click I am ready to connect my Git repo. You will be redirected to the Fivetran setup form.

   • Your dbt project must have a dbt_project.yml file. Learn more in dbt Labs’ dbt_project.yml documentation.
   • By default, Fivetran expects your dbt project to be at the root of your Git repo. If your dbt project is elsewhere, you must specify its file path in your Fivetran setup form in Step 3.

Grant Fivetran access to Git repository

1. In the Fivetran setup form, find the public key and click the blue clipboard icon to copy it. You will need it to grant Fivetran SSH access to your Git repository.

2. Follow the instructions for your Git provider below to grant Fivetran SSH access to your repository.

   • In GitHub: Follow GitHub’s Deploy key setup instructions. In your SSH key’s Key field, paste the Fivetran public key you found in step 2 of this section.

   • In GitLab: Follow GitLab’s Deploy key instructions. In your SSH key’s Key text box, paste the Fivetran public key you found in step 2 of this section.

   • In BitBucket: Follow these BitBucket SSH key setup instructions. In your SSH key’s Key field, paste the Fivetran public key you found in step 2 of this section.

   • In Azure Repos: Follow step 2 of Microsoft’s Azure Repos SSH key setup instructions. In your SSH key’s Public Key Data text box, paste the Fivetran public key you found in step 2 of this section.

   • In AWS CodeCommit:

     i. Open your Amazon IAM console.

     ii. Click Add User to create a new AWS IAM user for Fivetran. This new user must have Programmatic Access and clone access to your repository.

     TIP: The easiest way to set up clone access is to give the user AWSCodeCommitReadOnly permission.

     iii. Open the newly created Fivetran user.

     iv. Go to the Security Credentials tab.

     v. Click Upload SSH public key.

     vi. In the SSH public key field, paste the Fivetran public key you found in step 2 of this section.

Declare sources

NOTE: This step is required if you plan to use integrated scheduling.

1. In your dbt project, open or create a source .yml file (often named source.yml).

2. Declare each of your upstream Fivetran connectors as a source. How you do this depends on whether you are using database connectors or not.

   TIP: You can declare multiple sources in the same .yml file. Learn more in dbt’s Declaring a source documentation.

   Non-database connectors

   Add the following code to your source .yml file. Replace connector_name and table_name with your connector name and destination table name.

   version: 2
   
   sources:
     - name: connector_name
       tables:
         - name: table_name_1
         - name: table_name_2
   

   Database connectors

   Add the following code to your source .yml file. Replace connector_name_destination_schema with your connector name, an underscore, then your destination schema name. Replace table_name with your destination table name.

   version: 2
       
   sources:
     - name: connector_name_destination_schema
       tables:
         - name: table_name_1
         - name: table_name_2
   

   For example, if your connector is named sql_server, your destination schema is dbo, and your destination table is Account, you would use the following code:

   version: 2
       
   sources:
     - name: sql_server_dbo
       tables:
         - name: Account
   

3. In your dbt project, open any dbt model file that references a source table.

4. Use the dbt ref function to reference the source that you configured in the previous step.

For example, a statement like

select * from connector_name.table_name

should become

select * from {{ source('connector_name', 'table_name') }}

Finish Fivetran Configuration

1. Follow the instructions for your Git provider below to find the URL of your Git repository. The URL must be in the format git@host.xz:path/to/repo.git (except for AWS CodeCommit).

   • In GitHub:

     i. Go to your repository page and click Code.

     ii. In the drop-down window, select SSH, then copy the repository URL that appears in the text box below.

   • In GitLab:

     i. Go to your repository page and find the HTTPS/SSH dropdown.

     ii. Select SSH, then copy the repository URL that appears in the text box on the right.

   • In BitBucket:

     i. Go to your repository’s Source page.

     ii. Click Clone.

     iii. In the pop-up window, select SSH, then copy the repository URL that appears in the text box below (be sure to remove the git clone prefix). Click Close.

   • In Azure Repos:

     i. Go to your repository page and click Clone.

     ii. In the pop-up window, select SSH, then copy the repository URL that appears in the text box below.

     iii. Close the window.

   • In AWS CodeCommit, go to your repository page and copy the SSH URL.

     IMPORTANT: The SSH URL must be in the following format ssh://Your-SSH-Key-ID@git-codecommit.us-east-2.amazonaws.com/v1/repos/MyDemoRepo. To find your SSH Key ID, go to your Amazon IAM console > Users > IAM user you created for Fivetran > Security Credentials > SSH keys.

2. In the Fivetran setup form, enter your Git repository’s URL.

3. Enter your chosen default schema name. This production schema will contain your transformed data.

4. By default, Fivetran accesses your destination for Transformations for dbt Core using the same credentials that you configured in the Destination section of your Fivetran dashboard. If your destination is Snowflake, you can choose to customize the credentials that Fivetran uses instead. In the Credentials section, select the Custom option and specify your custom credentials.

   Your destination credentials (combined with your custom dbt credentials, if you chose to use them) define the target database where dbt Core will put the transformed data. By default, dbt Core looks for the source data in that same database. If you want to fetch the source data from a different database, you must define source properties in your dbt project and override the source database.

5. Choose your dbt Core Version.

6. By default, Fivetran works with the main branch of your repository. Fivetran searches for a dbt project in the root folder and uses one thread for working on your dbt commands. If you would like to change the branch, the path to the dbt project or the number of threads used, click Show Advanced Options and enter your chosen values. Note that the number of threads must be compatible with your destination type, for example, Snowflake supports only 8 concurrent queries.

7. Click Save & Test.

8. When your changes have been saved and tests have been passed, you will see a All connection tests passed! message. Click Transformations.

9. You will be redirected to your main Transformations page. Fivetran will sync your project in a few minutes. Your dbt models are synced directly from your git repository.

Add a transformation

Follow the instructions in our Transformations tab documentation to add transformations.

Install Fivetran data models (optional)

Fivetran has created dbt Core-compatible data models (formerly known as “Fivetran dbt packages”) for some of our most popular connectors. To install Fivetran’s data models, do the following steps as shown in the tutorial below:

1. Go to Fivetran’s dbt hub.

2. Click on the name of your connector.

3. Follow the instructions in the Installation section at the top of the page.

4. Run the dbt deps command. The data models for your connector will automatically install in the dbt_modules folder.

5. If you’re ready to start your data models, run the dbt run command.

Get started

1. In the left column of your Fivetran dashboard, go to Transformations.

2. A pop-up message appears that prompts you to set up Transformations for dbt Core. Click on it.

3. In the pop-up window, you’ll be prompted to choose a setup option. Click Help me set up my dbt project.

Create new Git repository

Create a new repository for your new dbt project using your Git provider.

Set up a basic dbt project

1. Open a terminal window.

2. Ensure that you have dbt installed on your computer by running the dbt --version command.

3. Run the dbt init my_git_repo_name command to create your starter dbt project.

4. Open your source code editor.

5. Open the newly created dbt project.

Connect dbt Core to destination

You’ll use the profiles.yml file to connect dbt to your destination. The file specifies which destination you’re connecting to, the credentials to access that destination, and the schema that dbt Core will load your transformed data into. Learn more in dbt Labs’ Configuring your profile documentation.

The profiles.yml file you’ll configure in this step is generally used for local development and testing purposes. Once Fivetran begins to run your dbt commands, we generate the production version of the profiles.yml file for your dbt project that you cannot view or edit.

1. Open the profiles.yml file that was automatically created as part of your dbt installation.

   NOTE: profiles.yml is a hidden file. It is created in your ~/.dbt folder, not in the dbt project.

2. Set up your profiles.yml file to enable dbt Core to connect to your destination. How you configure this file differs based on your destination. See dbt Labs’ profiles documentation for setup instructions for your destination.

   For additional help, see dbt Labs’ profiles.yml documentation.

3. Be sure that the profile name in your dbt_project.yml file matches the profile name in your profiles.yml file.

   NOTE: The profile names only need to match for testing purposes. Once Fivetran begins to run your dbt commands, we ignore the profile name in your dbt_project.yml file.

4. Run the dbt run command. If you have successfully linked your destination, you will see a Completed successfully message.

Link dbt project to Git repository

1. In a terminal window, ensure that you are in the directory of your new dbt project.

2. Run the following commands. Replace the example URL https://github.com/USERNAME/dbt-tutorial.git with the URL of your Git repository.

   $ git init -b main
   $ git add .
   $ git commit -m ‘add your commit message here’
   $ git remote add origin https://github.com/USERNAME/dbt-tutorial.git
   $ git push -u origin main
   

3. Open your Git repository to see your new dbt project.

Declare sources

NOTE: This step is required if you plan to use integrated scheduling.

1. In your dbt project, open or create a source .yml file (often named source.yml).

2. Declare each of your upstream Fivetran connectors as a source. How you do this depends on whether you are using database connectors or not.

   TIP: You can declare multiple sources in the same .yml file. Learn more in dbt’s Declaring a source documentation.

   Non-database connectors

   Add the following code to your source .yml file. Replace connector_name and table_name with your connector name and destination table name.

   version: 2
   
   sources:
     - name: connector_name
       tables:
         - name: table_name_1
         - name: table_name_2
   

   Database connectors

   Add the following code to your source .yml file. Replace connector_name_destination_schema with your connector name, an underscore, then your destination schema name. Replace table_name with your destination table name.

   version: 2
       
   sources:
     - name: connector_name_destination_schema
       tables:
         - name: table_name_1
         - name: table_name_2
   

   For example, if your connector is named sql_server, your destination schema is dbo, and your destination table is Account, you would use the following code:

   version: 2
       
   sources:
     - name: sql_server_dbo
       tables:
         - name: Account
   

3. In your dbt project, open any dbt model file that references a source table.

4. Use the dbt ref function to reference the source that you configured in the previous step.

For example, a statement like

select * from connector_name.table_name

should become

select * from {{ source('connector_name', 'table_name') }}

Grant Fivetran access to Git repository

1. Return to your Fivetran dashboard. You will be redirected to the Fivetran setup form.

2. Find the public key and click the blue clipboard icon to copy it. You will need it to grant Fivetran SSH access to your Git repository.

3. Follow the instructions for your Git provider below to grant Fivetran SSH access to your repository.

   • In GitHub: Follow GitHub’s Deploy key setup instructions. In your SSH key’s Key field, paste the Fivetran public key you found in step 2 of this section.

   • In GitLab: Follow GitLab’s Deploy key instructions. In your SSH key’s Key text box, paste the Fivetran public key you found in step 2 of this section.

   • In BitBucket: Follow these BitBucket SSH key setup instructions. In your SSH key’s Key field, paste the Fivetran public key you found in step 2 of this section.

   • In Azure Repos: Follow step 2 of Microsoft’s Azure Repos SSH key setup instructions. In your SSH key’s Public Key Data text box, paste the Fivetran public key you found in step 2 of this section.

   • In AWS CodeCommit:

     i. Open your Amazon IAM console.

     ii. Click Add User to create a new AWS IAM user for Fivetran. This new user must have Programmatic Access and clone access to your repository.

     TIP: The easiest way to set up clone access is to give the user AWSCodeCommitReadOnly permission.

     iii. Open the newly created Fivetran user.

     iv. Go to the Security Credentials tab.

     v. Click Upload SSH public key.

     vi. In the SSH public key field, paste the Fivetran public key you found in step 2 of this section.

Finish Fivetran Configuration

1. Follow the instructions for your Git provider below to find the URL of your Git repository. The URL must be in the format git@host.xz:path/to/repo.git (except for AWS CodeCommit).

   • In GitHub:

     i. Go to your repository page and click Code.

     ii. In the drop-down window, select SSH, then copy the repository URL that appears in the text box below.

   • In GitLab:

     i. Go to your repository page and find the HTTPS/SSH dropdown.

     ii. Select SSH, then copy the repository URL that appears in the text box on the right.

   • In BitBucket:

     i. Go to your repository’s Source page.

     ii. Click Clone.

     iii. In the pop-up window, select SSH, then copy the repository URL that appears in the text box below (be sure to remove the git clone prefix). Click Close.

   • In Azure Repos:

     i. Go to your repository page and click Clone.

     ii. In the pop-up window, select SSH, then copy the repository URL that appears in the text box below.

     iii. Close the window.

   • In AWS CodeCommit, go to your repository page and copy the SSH URL.

     IMPORTANT: The SSH URL must be in the following format ssh://Your-SSH-Key-ID@git-codecommit.us-east-2.amazonaws.com/v1/repos/MyDemoRepo. To find your SSH Key ID, go to your Amazon IAM console > Users > IAM user you created for Fivetran > Security Credentials > SSH keys.

2. In the Fivetran setup form, enter your Git repository’s URL.

3. Enter your chosen default schema name. This production schema will contain your transformed data.

4. By default, Fivetran accesses your destination for Transformations for dbt Core using the same credentials that you configured in the Destination section of your Fivetran dashboard. If your destination is Snowflake, you can choose to customize the credentials that Fivetran uses instead. In the Credentials section, select the Custom option and specify your custom credentials.

   Your destination credentials (combined with your custom dbt credentials, if you chose to use them) define the target database where dbt Core will put the transformed data. By default, dbt Core looks for the source data in that same database. If you want to fetch the source data from a different database, you must define source properties in your dbt project and override the source database.

5. Choose your dbt Core Version.

6. By default, Fivetran works with the main branch of your repository. Fivetran searches for a dbt project in the root folder and uses one thread for working on your dbt commands. If you would like to change the branch, the path to the dbt project or the number of threads used, click Show Advanced Options and enter your chosen values. Note that the number of threads must be compatible with your destination type, for example, Snowflake supports only 8 concurrent queries.

7. Click Save & Test.

8. When your changes have been saved and tests have been passed, you will see a All connection tests passed! message. Click Transformations.

9. You will be redirected to your main Transformations page. Fivetran will sync your project in a few minutes. Your dbt models are synced directly from your git repository.

Add a transformation

Fivetran has created dbt Core-compatible data models for some of our most popular connectors. To install Fivetran’s data models, do the following as shown in the tutorial below:

1. Go to Fivetran’s dbt hub.

2. Click on the name of your connector.

3. Follow the instructions in the Installation section at the top of the page.

4. Run the dbt deps command. The data models for your connector will automatically install in the dbt_modules folder.

5. If you’re ready to start your data models, run the dbt run command.