Transformations for dbt Core

Orchestrate custom data transformations in your destination with Transformations for dbt Core*.

Overview

Fivetran hosts dbt Core to power our transformations. dbt Core, by dbt Labs, is an open-source transformation tool that enables you to perform sophisticated data transformations in your destination using simple SQL statements. With dbt Core, you can:

• Write and test SQL transformations
• Use version control with your transformations
• Create and share documentation about your transformations

Once you have set up a dbt Core project and written models, you can sync them to Fivetran. Fivetran runs dbt Core against your project with a hosted service, executing the resulting SQL in your destination to build tables and views.

To develop models locally with dbt Core, you can either use the dbt CLI, an open-source command line interface, or dbt Cloud, dbt Labs' hosted service. Fivetran can run dbt projects created with either dbt Cloud or dbt CLI.

Supported destinations

Fivetran supports Transformations for dbt Core for the following destinations:

• Azure Synapse
• BigQuery
• ClickHouse Cloud
• Databricks - see Databricks on Azure limitations
• MySQL - see the limitations
• PostgreSQL
• Redshift
• Snowflake
• SQL Server - see the limitations

Databricks on Azure limitations

Databricks on Azure destinations with OAuth authentication don't support Transformations for dbt Core.

MySQL limitations

For MySQL destinations, Fivetran only supports the following dbt Core versions: v1.0.x.

For Transformations for dbt Core, Fivetran does not support MySQL 5.7, even though Fivetran supports MySQL 5.7 as a destination.

SQL Server limitations

The dbt Core SQL Server adapter only supports SQL Server 2016+, even though Fivetran supports SQL Server 2012+ as a destination.

Supported dbt Core versions

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.7.19
• 1.8.4, 1.8.9
• 1.9.4, 1.9.8
• 1.10.3

How it works

A job is set of commands you run against your dbt project to execute your transformations. You create jobs that you want Fivetran to run in the Fivetran dashboard or with the deployment.yml file using dbt commands. We connect to your Git provider, sync your project, and run those jobs against it utilizing a schedule you choose.

Each transformation consists of the following elements:

• Job Commands: You can create multiple dbt commands that are executed in order, allowing you to build, run, and test in a single scheduled run.
• Schedule: A customizable schedule that determines how often Fivetran runs your job. Learn more in our Transformations scheduling documentation or in the following section.

Scheduling options

Fivetran syncs your dbt models from your Git provider every few minutes to ensure that we are up to date. You can choose when to run them.

There are several scheduling options for Transformations for dbt Core:

• Integrated scheduling (recommended): We run your dbt models in your destination as soon as we update any relevant destination data.
• Custom scheduling : We run your dbt models in your destination according to the schedule that you set in the Fivetran dashboard.
• Cron scheduling: We run your dbt models in your destination according to the cron schedule you specify.
• deployment.yml: We run your dbt models in your destination according to the schedule that you set in your dbt project.

Integrated scheduling

Fivetran connects to your Git provider and runs your dbt models as soon as we update any relevant destination data. Integrated schedules reduce data latency and ensure that your analytics tools reflect new data as quickly as possible. They can also reduce compute costs, since downstream transformations do not run if their associated connector fails to sync.

To run a transformation with integrated scheduling, Fivetran performs the following steps:

2. Matches source table references in the dbt models to the source table names written by Fivetran connectors.
3. Unifies your pipelines into end-to-end directed graphs.
4. Executes the pipelines in order, which minimizes latency on the analytics-ready tables in your destination.

Fivetran transformation pipelines use the following elements:

• The start is the connector sync interval that initiates the pipeline.
• A connector updates source tables in the destination.
• A junction waits for multiple connectors to finish syncing before it triggers a transformation.
• A transformation is a model or a collection of models that updates downstream tables in the destination.
• An output model generates an analytics-ready table. It is usually the final element in a pipeline.
• A test is an assertion that you make about the models in your dbt project. A test may succeed or fail independently of model execution.

Each start node defines its own data pipeline. In the following example, the start node is the connector sync frequency. The oracle connector runs every 15 minutes. When it successfully finishes syncing, that initiates downstream transformations to produce the customers output model.

Fivetran comes with a fixed set of start nodes corresponding to different sync frequencies. When you select a frequency in the dashboard, the pipelines that activate those syncs are aware of overlaps and automatically adjust to them. In the example below, the oracle connector is on a 15-minute schedule, the netsuite connector is on an hourly schedule, and the salesforce connector is on a 24-hour schedule.

• The 15-minute node activates every 15 minutes, except when the 1-hour or 24-hour node activates.
• The 1-hour node activates every hour, except when the 24-hour node activates.
• The 24-hour node activates all three connector syncs.

Custom scheduling

Fivetran connects to your Git provider and runs your dbt models in your destination according to the schedule you set in the Fivetran dashboard. If you have enabled the Smart syncing option, when your transformation's schedule overlaps with the associated connector's schedule, we wait until the connector finishes syncing and only then run the transformation.

With custom scheduling, you can:

• Save on warehouse costs by running your transformation less frequently than your connector.
• Ensure that all upstream connectors have synced when you run your transformation by matching its schedule to your slowest upstream connector.

To run a transformation with custom scheduling, Fivetran performs the following steps:

2. Matches source table references in the dbt models to the source table names written by Fivetran connectors.
3. Unifies your pipelines into end-to-end directed graphs if your upstream connector’s and transformation’s schedules overlap. Otherwise, we create two separate pipelines.
4. Runs your transformation according to the schedule you chose.
5. If you enabled the Smart syncing option, when the transformation’s schedule overlaps with the upstream connector's schedule, Fivetran waits until the connector finishes syncing and only then runs the transformation.

For example, you have an output model, churn, that is expensive to run. You choose to run it every 24 hours, but you don't want to run it until your destination data has been updated. You create a custom schedule so that the transformation runs every 24 hours once all of its associated connectors successfully sync:

Cron scheduling

Fivetran runs your dbt models in your destination according to the cron schedule you specified. You can define and use multiple cron schedules to flexibly execute your transformations. Cron scheduling provides more flexibility than Custom scheduling.

Learn more in the Cron scheduling section of the Transformations Scheduling documentation.

deployment.yml

Fivetran connects to your Git provider and runs your dbt models in your destination according to the schedule that you set in your dbt project's deployment.yml file.

To run a transformation using the deployment.yml file, Fivetran performs the following steps:

1. Verifies the deployment.yml file in your dbt project.

2. Parses the deployment.yml file to create new jobs or update existing jobs.

3. Runs the jobs according to their individual cron or integrated schedules you specified in your deployment.yml file. For each job run, we do the following:

   i. Prepare an environment with the corresponding dbt CLI version installed, the clean project working directory, and the profile.yml file.
   ii. Execute the dbt deps service command to install the required project packages.
   iii. Execute the steps in each job one-by-one until all scheduled jobs have run.

Known limitations

• You cannot integrate a transformation's schedule with a connector schedule.
• You cannot add, edit, or delete a transformation or its schedule on the Fivetran dashboard. You must do so in your dbt project.
• You cannot see links between the dbt test jobs and connectors in the transformation list.

Fivetran data models

Fivetran offers pre-built, dbt Core-compatible data models for our top connectors (formerly known as "Fivetran dbt packages"). Learn more about Fivetran Data Models.

Setup guide

To learn how to use Transformations for dbt Core, follow the Transformations for dbt Core setup guide.

Use cases

To see common use cases for Transformations for dbt Core - integrated or custom scheduling, see our Transformations Scheduling documentation.

Notifications

You can enable email notifications for each of your transformations on your Fivetran dashboard's Notifications tab. To learn more about email notifications, see our Notifications documentation.

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