Transformations for dbt Core - Scheduled in Fivetran Setup Guide Beta

Follow our setup guide to set up Transformations for dbt Core* - Scheduled in Fivetran.

NOTE: If you prefer to schedule transformations in code, follow our Transformations for dbt Core - Scheduled in Code setup guide.

Prerequisites

• dbt Core installed on your computer. If you have not installed dbt Core yet, follow dbt Labs' installation instructions. We support the following versions of dbt Core:

  • 1.0.0, 1.0.1, 1.0.3 - 1.0.9
  • 1.1.0 - 1.1.3, 1.1.5
  • 1.2.0 - 1.2.4, 1.2.6
  • 1.3.0 - 1.3.2, 1.3.7
  • 1.4.5, 1.4.9
  • 1.5.1, 1.5.6, 1.5.8, 1.5.9
  • 1.6.0, 1.6.1, 1.6.3, 1.6.6, 1.6.9
  • 1.7.3, 1.7.9, 1.7.14
  • 1.8.4

• A source code editor of your choice (such as VSCode or Atom)

• An account with a Git provider of your choice (such as GitHub or BitBucket) with permissions to add deploy keys at the repository level

• One of the following destinations:

  • Azure Synapse
  • BigQuery
  • Databricks
  • MySQL
  • PostgreSQL
  • Redshift
  • Snowflake
  • SQL Server
  • ClickHouse

Setup instructions

How you set up Transformations for dbt Core depends on whether or not you already have a dbt project in a Git repository. Follow the instructions that apply to you:

• I have a dbt project in a Git repository
• I do not have a dbt project in a Git repository

Setup instructions with dbt project

Learn how to set up Transformations for dbt Core for your existing dbt project.

IMPORTANT: Your dbt project cannot exceed 1 GB in size or have symbolic links.

Get started

1. In the left navigation column, click Transformations and select your destination.
2. Your Transformations page varies slightly based on whether or not you have set up any transformations for this destination:
   • If you don't have any transformations:
     1. Click the Get Started button.
     2. Under dbt Core integration, click Connect your dbt project.
   • If you have set up transformations, click Add transformation > dbt Transformation.
   • 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 4.

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 or custom scheduling.

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

   TIP: You should store source .yml files in the models/ directory of your dbt project. See dbt's YAML docs for more best practices.

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.

   TIP: For File connectors using cloud-based storage or Merge Mode, the connector name contains both the schema name and the table name. For example, let's say your Amazon S3 connector is named s3.prices. In the source file, name: = s3 and tables: name: = prices.

   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 drop-down.

     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. Choose the Connection Method. There are two ways to connect to your git repository:

   • Directly
   • Using Proxy Agent

   The Fivetran Proxy Agent is installed in your network and creates an outbound network connection to the Fivetran-managed SaaS. This allows for secure communication between Fivetran processes and your repository without opening an inbound port in your firewall and/or other access control systems.

   Refer to our Connection Options documentation to learn how to generate Proxy Agent settings and install a Proxy Agent.

4. Enter your chosen Default Schema Name. This production schema will contain your transformed data.

5. Choose the dbt Core Version to run your project.

6. (Optional) Add Environment Variables (optional) in the NAME=VALUE format.

   IMPORTANT: The variable name must start with the DBT_ prefix and contain the following characters only: A-Z, 0-9, dash, underscore, or dot. Variable names must use capital letters. For example, DBT_TESTING_VARIABLE=HELLO is correct, but DBT_testing_variable=hello is not correct.

7. By default, Fivetran does the following:

   • Works with the main branch of your repository
   • Searches for a dbt project in the root folder
   • Accesses your destination using the same credentials that you configured in the Destination section of your Fivetran dashboard
   • Uses the same values as defined for your destination for other additional settings, such as, for example, the number of threads for working on your dbt commands.

   If you would like to override these parameters:

   i. Set the Show Advanced Options toggle to ON.

   ii. (Optional) Enter the Git branch.

   iii. (Optional) Enter the Project path (optional).

   iv. Manage the targets.

   IMPORTANT: Changing the target name used with an existing data model will update the target name for that model.

8. Click Save & Test.

9. When your changes have been saved and tests have been passed, you will see a Done message. Click Transformations.

10. 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.

Setup instructions with no dbt project

Learn how to create a dbt project and set up Transformations for dbt Core.

Get started

1. In the left navigation column, click Transformations and select your destination.
2. Click Get Started.
3. Click Connect your dbt project under the dbt Core integration. You will be redirected to the Fivetran setup form.
4. (Optional) Click Invite a teammate to invite a user from your group.

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.

IMPORTANT: Your dbt project cannot exceed 1 GB in size.

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 or custom scheduling.

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

   TIP: You should store source .yml files in the models/ directory of your dbt project. See dbt's YAML docs for more best practices.

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.

   TIP: For File connectors using cloud-based storage or Merge Mode, the connector name contains both the schema name and the table name. For example, let's say your Amazon S3 connector is named s3.prices. In the source file, name: = s3 and tables: name: = prices.

   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 drop-down.

     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. Choose the Connection Method. There are two ways to connect to your git repository:

   • Directly
   • Using Proxy Agent

   The Fivetran Proxy Agent is installed in your network and creates an outbound network connection to the Fivetran-managed SaaS. This allows for secure communication between Fivetran processes and your repository without opening an inbound port in your firewall and/or other access control systems.

   Refer to our Connection Options documentation to learn how to generate Proxy Agent settings and install a Proxy Agent.

4. Enter your chosen Default Schema Name. This production schema will contain your transformed data.

5. Choose the dbt Core Version to run your project.

6. (Optional) Add Environment Variables (optional) in the NAME=VALUE format.

   IMPORTANT: The variable name must start with the DBT_ prefix and contain the following characters only: A-Z, 0-9, dash, underscore, or dot. Variable names must use capital letters. For example, DBT_TESTING_VARIABLE=HELLO is correct, but DBT_testing_variable=hello is not correct.

7. By default, Fivetran does the following:

   • Works with the main branch of your repository
   • Searches for a dbt project in the root folder
   • Accesses your destination using the same credentials that you configured in the Destination section of your Fivetran dashboard
   • Uses the same values as defined for your destination for other additional settings, such as, for example, the number of threads for working on your dbt commands.

   If you would like to override these parameters:

   i. Set the Show Advanced Options toggle to ON.

   ii. (Optional) Enter the Git branch.

   iii. (Optional) Enter the Project path (optional).

   iv. Manage the targets.

   IMPORTANT: Changing the target name used with an existing data model will update the target name for that model.

8. Click Save & Test.

9. When your changes have been saved and tests have been passed, you will see a Done message. Click Transformations.

10. 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

IMPORTANT: You can only add one transformation schedule per output model.

To add a transformation, follow these steps:

1. Click Transformations.

2. Type the destination name in the Search by name field to find the destination, or select the destination from the list.

3. Click Add transformation.

4. Select the transformation type from the drop-down menu.

5. Define the settings for the selected transformation type.

   • Quickstart data models - perform the following steps:

     i. Select the Source type.

     ii. Select Connector.

     iii. (Optional) You can Read model descriptions in the embedded documentation for the Output models.

   • dbt Core - perform the following steps:

     i. Select target.

     ii. Select model.

6. Set schedule by selecting either of the following schedule types:

   • Integrated - this schedule type is not available for dbt for Core transformations until you add a connector to your transformations
   • Custom

   NOTE: To use integrated or custom scheduling, you need to declare sources in your data models.

7. Click Save.

Install Fivetran data models (optional)

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.

* dbt Core is a trademark of dbt Labs, Inc. All rights therein are reserved to dbt Labs, Inc. Fivetran Transformations is not a product or service of or endorsed by dbt Labs, Inc.