dbt Setup Guide Betalink
Follow our setup guide to set up dbt transformations for Fivetran.
Prerequisiteslink
-
dbt installed on your computer. If you have not installed dbt yet, follow dbt's installation instructions.
-
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
To see how to set up dbt transformations with Fivetran, read the instructions below or watch this short video:
Get startedlink
-
In the left column of your Fivetran dashboard, go to Transformations.
-
Your Transformations page varies slightly based on whether or not you have set up any basic transformations in your Fivetran dashboard.
-
If you have not set up any basic transformations, click the Try dbt transformations button at the bottom of the page.
-
If you have set up basic transformations, click Enable dbt transformations in the top right corner of the page.
NOTE: If you have basic transformations, you will be prompted to download them. You can then re-purpose these scripts in your dbt project.
-
-
You will be redirected to a page with information about dbt transformations. Read it, then click Enable dbt transformations.
-
In the pop-up window, you'll be prompted to choose a setup option:
- If you do not have a dbt project yet, click Help me set up my dbt project.
- If you have a dbt project you'd like to connect, click I am ready to connect my Git repo. Your Git repo must include your dbt project at its root directory. Your dbt project must have both a
deployment.yml
file, which is specific to Fivetran, and adbt_project.yml
file. To learn more about thedeployment.yml
file, see Step 5.
-
If you clicked I am ready to connect my Git repo, skip ahead to Step 7. If you clicked Help me set up my dbt project, continue to the next section.
Create new Git repository link
Create a new repository for your new dbt project using your Git provider.
Set up a basic dbt projectlink
-
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 package. -
Open your source code editor.
-
Open the newly created dbt project.
Connect dbt to destinationlink
NOTE: If you already have a dbt project, skip ahead to Step 5.
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 will load your transformed data into. Learn more in dbt's Configure your profile documentation.
-
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 to connect to your destination. How you configure this file differs based on your destination. See dbt's profiles documentation for setup instructions for your destination.For additional help, see dbt's
profiles.yml
documentation. -
Be sure that the profile name in your
dbt_project.yml
file matches the profile name in yourprofiles.yml
file. -
Run the
dbt run
command. If you have successfully linked your destination, you will see a Completed successfully message.
NOTE: The
profiles.yml
file you just created is generally used for local development and testing purposes. Once Fivetran begins to run your dbt commands, we generate the production version of theprofiles.yml
file for your dbt project that you cannot view or edit.
Write dbt jobslink
-
Download the
deployment.yml
file. This file contains jobs that enable Fivetran to run your dbt commands on a schedule. -
Edit the jobs in the
deployment.yml
file to reflect how and when you want Fivetran to run your dbt commands. The file contains instructions on how to do this. -
In your source code editor, go to your dbt project and add your edited
deployment.yml
file to the same folder as thedbt_project.yml
file.
Link dbt project to Git repositorylink
-
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 $ 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 master
-
Open your Git repository to see your new dbt project.
Grant Fivetran access to Git repositorylink
-
Return to your Fivetran dashboard. You will be redirected to the dbt 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.
GitHub
-
In your GitHub profile, go to your repository page.
-
Click Settings.
-
In the repository settings sidebar, click Deploy keys.
-
Click Add deploy key.
-
In the Title field, enter a name for your key (for example, 'Fivetran').
-
In the Key field, paste the Fivetran public key you found in step 2 of this section.
-
Click Add key.
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.
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.
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.
AWS CodeCommit
-
Open your Amazon IAM console.
-
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.
-
Open the newly created Fivetran user.
-
Go to the Security Credentials tab.
-
Click Upload SSH public key.
-
In the SSH public key field, paste the Fivetran public key you found in step 2 of this section.
Finish Fivetran Configurationlink
-
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 dbt 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 works with the master branch of your repository, 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.
-
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.
-
In the top right corner, set the toggle to ON to enable dbt transformations.
-
Your dbt jobs are synced directly from your
deployment.yml
file. If you have not added any dbt jobs yet, click Help me write dbt jobs to learn how.
Install Fivetran dbt packages (optional)link
Fivetran has created dbt packages for some of our most popular connectors. To install Fivetran's dbt packages, do the following:
-
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 dbt packages for your connector will automatically install in thedbt_modules
folder. -
If you're ready to start your dbt packages, run the
dbt run
command.