Transformation Scheduling

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

• For Scheduled in Code transformations, in deployment.yml file
• For Scheduled in Fivetran transformations, either in the Schedule type group when you create a transformation or on the Schedule tab when you edit the existing transformation
• For 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

IMPORTANT: Fivetran runs connector syncs and transformation jobs within a transformation pipeline sequentially. For both Integrated and Smart Syncing schedules, when a connector sync and transformation job are part of the same pipeline, the next job or sync will not start until the current pipeline completes.

This may cause a delay in connector syncs. For example, if a connector has a 1-hour schedule, but the job run for a downstream model with integrated scheduling takes 2 hours, Fivetran runs the next sync only after the transformation has finished.

The more complex your transformation pipeline is, the higher the probability of such a delay in connector syncs.

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 Scheduled in Code and Scheduled in Fivetran transformations for dbt Core* and Quickstart transformations.

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

Integrated scheduling with overlapping connector schedules

You have three connectors 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 connectors sync every hour, but the oracle connector syncs every 15 minutes.

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

Integrated scheduling with sometimes overlapping connector schedules

You have three connectors 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 connectors sync every 2 hours, but the oracle connector syncs every 3 hours.

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

Custom scheduling

This schedule type is available for Scheduled in Fivetran transformations for dbt Core 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 connectors' sync schedule and state.
• With the Smart syncing option enabled, when a transformation's schedule overlaps with the associated connector's schedule, we wait until the connector finishes syncing before we run the transformation.

Custom scheduling with Smart Syncing enabled

This schedule type is available for Scheduled in Fivetran transformations for dbt Core and Quickstart transformations.

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

With Custom scheduling with Smart Syncing enabled, 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 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 connector’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 connector's schedule, we wait until the connector 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 connectors successfully sync:

Cron scheduling

This schedule type is available for Scheduled in Code and Scheduled in Fivetran 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.

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 fopllows:

| | | | | | | | | └─ 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.