Transformations for dbt Core - Scheduled in Code Setup Guide Beta

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

NOTE: Transformations for dbt Core - Scheduled in Code does not allow you to schedule transformations in the Fivetran dashboard or access other in-dashboard features. Learn more about these features in our Transformations for dbt Core documentation.

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 - Scheduled in Code 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

To see how to set up Transformations for dbt Core for your existing dbt project, read the instructions below.

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 Quickstart transformations and want to connect a dbt Core project, click Settings in the top-right corner of the page and click the dbt Core tab.
   • If you have already connected your dbt Core project, click Add transformation > dbt Transformation. You will be redirected to the Fivetran setup form.
   • Your dbt project must have both a deployment.yml file, which is specific to Fivetran, and a dbt_project.yml file. To learn more about the deployment.yml file, see Step 5 of the next section.
   • 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 of this section.

3. (Optional) Click Invite a teammate to invite a user from your group.

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.

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 Repository URL.

3. Choose the Connection Method. There are two ways to connect to your git repository:

   • Connect directly
   • Connect via 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.

11. In the top right corner, set the toggle to ON to enable Transformations for dbt Core.

12. 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 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 - Scheduled in Code.

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 Core 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

NOTE: If you already have a dbt project, skip ahead to Step 4.

You'll use the profiles.yml file to connect dbt Core 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 Core 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.

Write dbt jobs

To schedule your dbt jobs, do the following steps as shown in the tutorial below:

1. Download the deployment.yml file. This file contains jobs that enable Fivetran to run your dbt commands on a schedule.

2. 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. All cron jobs defined in your deployment.yml file run according to your destination time zone.

   IMPORTANT: Do not add comments inside a multiline command because they cause parsing errors. You can split your command into multiple steps. For more information about comment usage, see the YAML format specification.

3. In your source code editor, go to your dbt project and add your edited deployment.yml file to the same folder as the dbt_project.yml file.

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.

Grant Fivetran access to Git repository

1. Return to your Fivetran dashboard. You will be redirected to the Transformations for dbt Core 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.

11. In the top right corner, set the toggle to ON to enable Transformations for dbt Core.

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

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