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.
Prerequisiteslink
-
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:
- 0.18.0 - 0.18.2
- 0.19.0 - 0.19.2
- 0.20.0 - 0.20.2
- 0.21.0 - 0.21.1
- 1.0.0, 1.0.1, 1.0.3 - 1.0.8
- 1.1.0 - 1.1.1
-
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:
- BigQuery
- Databricks
- PostgreSQL
- Redshift
- Snowflake
Setup instructionslink
IMPORTANT: If you already have transformations in the Fivetran dashboard, skip ahead to the Migrate existing transformations section.
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:
Setup instructions with dbt projectlink
Learn how to set up Transformations for dbt Core for your existing dbt project.
Get started
-
In the left column of your Fivetran dashboard, go to Transformations.
-
A pop-up message appears that prompts you to set up Transformations for dbt Core. Click on it.
-
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.
- Your dbt project must have a
Grant Fivetran access to Git repository
-
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.
-
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
-
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.
-
-
In the Fivetran setup form, enter your Git repository’s URL.
-
Enter your chosen default schema name. This production schema will contain your transformed data.
-
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.
-
Choose your dbt Core Version.
-
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.
-
Click Save & Test.
-
When your changes have been saved and tests have been passed, you will see a All connection tests passed! message. Click Transformations.
-
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:
-
Go to Fivetran’s dbt hub.
-
Click on the name of your connector.
-
Follow the instructions in the Installation section at the top of the page.
-
Run the
dbt deps
command. The data models for your connector will automatically install in thedbt_modules
folder. -
If you’re ready to start your data models, run the
dbt run
command.
Setup instructions with no dbt projectlink
Learn how to create a dbt project and set up Transformations for dbt Core.
Get started
-
In the left column of your Fivetran dashboard, go to Transformations.
-
A pop-up message appears that prompts you to set up Transformations for dbt Core. Click on it.
-
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
-
Open a terminal window.
-
Ensure that you have dbt installed on your computer by running the
dbt --version
command. -
Run the
dbt init my_git_repo_name
command to create your starter dbt project. -
Open your source code editor.
-
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.
-
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. -
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. -
Be sure that the profile name in your
dbt_project.yml
file matches the profile name in yourprofiles.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. -
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
-
In a terminal window, ensure that you are in the directory of your new dbt project.
-
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
-
Open your Git repository to see your new dbt project.
Grant Fivetran access to Git repository
-
Return to your Fivetran dashboard. You will be redirected to 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.
-
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
-
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.
-
-
In the Fivetran setup form, enter your Git repository’s URL.
-
Enter your chosen default schema name. This production schema will contain your transformed data.
-
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.
-
Choose your dbt Core Version.
-
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.
-
Click Save & Test.
-
When your changes have been saved and tests have been passed, you will see a All connection tests passed! message. Click Transformations.
-
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 to your Fivetran dashboard, follow these steps:
-
On the Transformations main page, click +Add Transformation.
-
Click on the text box to generate a drop-down list of all of the output models in your Git repository that have not yet been scheduled. Fivetran automatically syncs this information from your repository.
-
Select the output model you want to schedule.
-
Select Integrated schedule for transformations you want Fivetran to manage, or select Custom schedule to set your own frequency. By default, your transformation runs on the same schedule as its associated connector. You can choose to run your transformation at the following intervals:
- Every 5 minutes
- Every 15 minutes
- Every 30 minutes
- Every hour
- Every 2 hours
- Every 3 hours
- Every 6 hours
- Every 8 hours
- Every 12 hours
- Every 24 hours
You can also choose which days you want your transformations to run.
-
Click Save. Depending on your schedule settings, the transformation run may not begin immediately (for example, if you create a transformation on Monday that only runs on Wednesday).
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:
-
Go to Fivetran’s dbt hub.
-
Click on the name of your connector.
-
Follow the instructions in the Installation section at the top of the page.
-
Run the
dbt deps
command. The data models for your connector will automatically install in thedbt_modules
folder. -
If you’re ready to start your data models, run the
dbt run
command.
Migrate existing transformationslink
If you already have existing transformations in the Fivetran dashboard, we recommend that you upgrade to the Transformations for dbt Core - Scheduled in Fivetran product. There are two types of existing transformations:
- Transformations for dbt Core - Scheduled in Code
- Basic SQL Transformations
Follow the instructions below for your transformation type.
Transformations for dbt Core - Scheduled in Codelink
If you’re currently using Scheduled In Code and want to upgrade to Scheduled in Fivetran, contact Fivetran Support to enable Scheduled in Fivetran for your account.
Basic SQL transformationslink
-
Follow the setup instructions above to create, configure, and share your Git repository with Fivetran.
-
Contact Fivetran Support to enable Transformations for dbt Core - Scheduled in Fivetran for your account.
* 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.