Transformation Scheduling

Depending on the transformation type, you define scheduling in the following places:

• Transformations for dbt Core:
  • In the deployment.yml file
  • In the Fivetran dashboard - either in the Schedule type group when you create a transformation or on the Schedule tab when you edit the existing transformation
• Quickstart transformations: Either in the Schedule type group when you create a transformation or on the Schedule tab when you edit the existing transformation

Fivetran supports the following scheduling types:

• Integrated
• Custom
• Custom with Smart Syncing
• Cron

Pipeline overview

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.

Integrated scheduling

This schedule type is available for Transformations for dbt Core* and Quickstart transformations.

Your output model, churn, uses an integrated schedule. The model references tables written by three connections, each on an hourly schedule. The churn, customers, and revenue models are all refreshed every hour, after all three connections have successfully synced.

Integrated scheduling with overlapping connection schedules

You have three connections referenced by the same output model, churn. The churn output model uses an integrated schedule and runs whenever there is new data in any source. The netsuite and salesforce connections sync every hour, but the oracle connection syncs every 15 minutes.

The churn, revenue, and customers models run every 15 minutes after the oracle connection syncs and every hour after the salesforce, netsuite, and oracle connections sync.

Integrated scheduling with sometimes overlapping connection schedules

You have three connections feeding into the same output model, churn. The churn output model uses an integrated schedule and runs whenever there is new data in any source. The netsuite and salesforce connections sync every 2 hours, but the oracle connection syncs every 3 hours.

The churn, revenue, and customers models run every 2 hours after the salesforce and netsuite connections sync, every 3 hours after the oracle connection syncs, and every 6 hours the salesforce, netsuite, and oracle connections sync.

Custom scheduling

This schedule type is available for Transformations for dbt Core - Integrated or custom scheduling and Quickstart transformations.

Custom scheduling allows you to select particular days of the week to run your transformation, a run frequency within a day, and, when the run frequency is set to 24 hours, a particular run time for your transformation.

It also provides the Smart syncing option that allows you to further adjust the schedule:

• With the Smart syncing option disabled, the models are run on the set schedule regardless of the upstream connections' sync schedule and state.
• With the Smart syncing option enabled, when a transformation's schedule overlaps with the associated connection's schedule, we wait until the connection finishes syncing before we run the transformation.

Custom scheduling with Smart Syncing enabled

This schedule type is available for Transformations for dbt Core - Integrated or custom scheduling and Quickstart transformations.

Fivetran runs your transformation according to the set schedule, except when a transformation's job schedule overlaps with the associated connection's schedule. In that case, we wait to run the transformation until the connection sync finishes.

With Custom scheduling with Smart Syncing enabled, you can:

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

To run a transformation with Custom scheduling with SmartSync enabled, 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 connection’s and transformation’s schedules overlap. Otherwise, we create two separate pipelines.
4. Runs your transformation according to the schedule you chose. If the transformation’s schedule overlaps with the upstream connection's schedule, we wait until the connection finishes syncing before we run 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 choose the SmartSync schedule so that the transformation runs every 24 hours once all of its associated connections successfully sync:

Cron scheduling

This schedule type is available for Transformations for dbt Core and Quickstart transformations.

Fivetran will run your transformation based on the specified cron schedule. You can define and use multiple cron schedules to flexibly execute your transformations. This scheduling type provides more flexibility than Custom scheduling.

Transformations scheduled with cron run in your destination's default time zone. To check your destination's time zone, go to the associated Destination Connection Details page in your dashboard.

Cron schedule format

In a standard cron schedule, the format is defined by five fields, each separated by a space, and each field represents a specific unit of time. The general format is as follows:

* * * * *
| | | | |
| | | | └─ Day of the week (0–7, where both 0 and 7 typically mean Sunday)
| | | └─── Month (1–12)
| | └───── Day of the month (1–31)
| └─────── Hour (0–23)
└───────── Minute (0–59)

Asterisks (*) mean "every possible value" for that field. For example, * in the minutes field means every minute, while * in the hours field means every hour, and so forth.

Here are some examples of cron schedule format:

Example 1

30 2 * * 1-5

Meaning: Run the job at 2:30 AM (minute 30, hour 2) every Monday through Friday (days of the week 1–5).

Details:

• 30 in the minute field means "at the 30th minute of the hour."
• 2 in the hour field means "at 2 AM."
• * in the day-of-month field means "every day of the month."
• * in the month field means "every month."
• 1-5 in the day-of-week field means "every weekday, Monday through Friday."

Use case: Triggering a dbt job only on weekdays at an off-peak hour, such as 2:30 AM.

Example 2

0 8-16/2 * * 1,3

Meaning: Run the job at the start of every second hour (0 minutes past the hour) from 8:00 AM through 16:00 (4:00 PM) on Mondays and Wednesdays. Details:

• 0 in the minute field means "at the top of the hour."
• 8-16/2 in the hour field means "every 2 hours starting at 8 AM and ending at 4 PM," which includes 8 AM, 10 AM, 12 PM, 2 PM, and 4 PM.
• 1,3 in the day-of-week field means "Monday and Wednesday."

Use case: Scheduling the job multiple times throughout the workday on specific weekdays.

Example 3

15 14 1 * *

Meaning: Run the job at 2:15 PM (14:15 in 24-hour format) on the 1st day of every month. Details:

• 15 in the minute field means "at the 15th minute."
• 14 in the hour field means "2 PM."
• 1 in the day-of-month field means "on the 1st day."
• * for month and day-of-week means "every month" and "every day of the week."

Use case: Run the job on a specific time on the first day of every month.

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