The Advanced Scheduler feature allows you to specify granular start times for data extraction. Using cron expressions, you can specify the exact times, days of the week, or even days of the month data extraction should begin.

Note: All replication scheduling methods (Replication Frequency, Anchor Scheduling, and Advanced Scheduling) define when data extractions begin. They do not control how long a replication job runs or when data is loaded into a destination.

In this guide, we’ll cover:


Uses for Advanced Scheduling

Using Advanced Scheduling, you can:

  1. Run reports on specified days. Using Advanced Scheduling, you can create a replication schedule that ensures reports for a weekly 9:00AM meeting are up-to-date.

  2. Whitelist hours for starting data extractions. For example: Scheduling replication during off-peak hours will reduce load on your production database.

    Note: An extraction may run over into “blackout” hours as the Advanced Scheduler only controls the times jobs start. See the Limitations section for more info.

  3. Reduce your row usage. Only scheduling data extractions when you need them can not only reduce load on data sources and your destination, it can reduce your overall row usage in Stitch.

  4. Reduce the re-replication of data. Because replication scheduling applies to all selected tables in an integration, tables using Full Table Replication will replicate in full each time a replication job runs. Reducing the number of replication jobs overall will decrease the number of times the same record is replicated.


Access to Advanced Scheduling

Advanced Scheduling is available during the Free Trial or on an Enterprise plan. Contact Stitch Sales for more info about Enterprise plans.

Plan downgrades

When the Free Trial ends or if you downgrade from an Enterprise plan, you’ll lose access to the Advanced Scheduling feature.

In the event that you downgrade, Stitch will automatically pause any integrations using Advanced Scheduling and reset their Replication Frequencies to their defaults. You will need to manually un-pause the integrations to continue replication.


Advanced Scheduling basics

Stitch’s Advanced Scheduler feature uses cron scheduling to create replication schedules. In this section, we’ll:

Introduction to cron

So, what’s cron? Cron is a time-based scheduler used in Unix-like operating systems such as Mac OS, Linux, etc. Tasks, or jobs, created through cron are called cron jobs. Stitch uses the Quartz standard for cron scheduling.

To create a cron job - in this case, an integration’s replication schedule - a cron expression is used. A cron expression describes the details of the schedule, and when combined, translates to the schedule Stitch will use to extract data from the integration.

Using a cron expression, you can create replication schedules such as “At 12:00AM every day” or “At 7:00PM every Monday, Wednesday, and Friday”. Stitch will then start replication jobs at these times.

Cron expression syntax

A cron expression in Stitch is made up of six fields that describe the elements of the schedule, separated by spaces.

Fields in the expression must be in the following order, and an expression must have all six fields to be considered valid:

[seconds] [minute] [hour] [day of month] [month] [day of week]

Fields can contain any of the allowed values, along with various combinations of the allowed special characters for that field. Note: Entering anything other than a field’s allowed values or special characters will result in an error when you try to save the integration’s settings. Refer to the Troubleshooting section for help resolving these errors.

Name Allowed values Allowed special characters
Seconds 0 n/a
Minutes 0-59 , - * /
Hours 0-23 , - * /
Day of month 1-31 , - * ? / L W
Month 1-12 or JAN-DEC , - * /
Day of week 1-7 or SUN-SAT , - * ? / L #

Allowed special characters

Each field in a cron expression has its own list of allowed special characters. These characters allow you to select all values, a list or range of values, specify increments, and more. Fields may also contain both a range and a list. Refer to the Example replication schedules section for examples.

Special character Allowed in Description and examples
*
ASTERISK
All fields

Selects all values within a field.

Examples:
  • * in the Minute field means “every minute”
  • * in the Hour field means “every hour”
  • * in the Month field means “every month”
  • * in the Day of week field means “every day of the week”
,
COMMA
All fields

Specifies a list of two or more values.

Examples:
  • 1,2,5 or JAN,FEB,MAY in the Month field means “the months January, February, and May”
  • 2,6 or MON,FRI in the Day of week field means “the days Monday and Friday”
-
HYPHEN
All fields

Specifies a range of values.

Examples:
  • 5-8 in the Hour field means “the hours 5, 6, 7, and 8”
  • 2-4 or MON-WED in the Day of week field means “the days Monday, Tuesday, and Wednesday”
?
QUESTION MARK
Day of month, Day of week

Indicates “no specific value”. This character is useful when you need to specify something in either the Day of month or Day of week field, but not the other.

Note: Quartz doesn’t currently support specifying a value for both the Day of week and Day of month fields. This value must be present in one of these fields for the expression to be valid.

Examples:
  • 15 in the Day of month field and ? in the Day of week field means “the 15th day of the month, regardless of the day of the week”
/
SLASH
All fields

Specifies increments. Formatted as: <value>/<value_to_increment>

Examples:
  • 0/15 in the Minute field means “the minutes 0, 15, 30, and 45”
  • 3/6 in the Hour field means “every 6 hours beginning on the third hour”
  • 1/5 in the Day of month field means “every 5 days beginning on the first day of the month”
L
LAST
Day of month, Day of week

Depending on the field L is used in, this can have different meanings:

  • Day of month: Used to indicate the last day of the month.
  • Day of week: If used by itself, this means 7, or SAT.

    If used after another value in this field (for example: 6L), this means “the last [xxx] day of the month”.

To avoid confusing or unexpected results, don’t specify lists or ranges of values with L.

Examples:
  • L in the Day of month field means “the last day of the month”, such as January 31 or February 28 (or 29 during a leap year)
  • 6L or FRIL in the Day of week field means “the last Friday of the month”
W
WEEKDAY
Day of month

Specifies the weekday (Monday - Friday) closest to the given day. Note: This character is only allowed when the Day of month value is a single day, not a range or list of days.

L can also be combined with W in the Day of month field to indicate “the last weekday of the month”.

Examples:
  • 15W means “the nearest weekday to the 15th of the month”. If the 15th were a Saturday, a replication job would begin on Friday the 14th.
  • 1W means “the nearest weekday to the 1st of the month”. If the 1st is a Saturday, the replication job would begin on Monday the 3rd. Advanced scheduling can’t skip over a boundary of days.
  • LW means “the last weekday of the month”
#
HASH
Day of week

Specifies the nth day of the month, such as “the third Friday of the month”. This is used with a day of week value (1-7 or SUN-SAT) and an integer of 1-5, formatted as <day_of_week_value>#<integer>.

Note: If <day_of_week_value>#5 is specified and there isn’t a fifth given day of week in the month, then the replication job will not be triggered.

Examples:
  • 4#2 or WED#2 means “the second Wednesday of the month”
  • 2#1 or MON#1 means “the first Monday of the month”
  • 7#5 or SAT#5 means “the fifth Saturday of the month”. If a month doesn’t have five Saturdays, a replication job will not be triggered.

Example replication schedules

Based on scheduling feedback we’ve collected, we’ve put together a list of some of the most commonly requested replication schedules. You can use these examples to define an integration’s replication schedule in Stitch, or create your own. Keep in mind that there are some limitations.

Run at midnight every day

Seconds Minutes Hours Day-of-month Month Day-of-week
0 0 0 ? * *
0 0 0 * * ?

Run at midnight every day from Monday through Friday

Seconds Minutes Hours Day-of-month Month Day-of-week
0 0 0 ? * 2-6
0 0 0 ? * MON-FRI

Run once a week at 11:00PM on Sunday

Seconds Minutes Hours Day-of-month Month Day-of-week
0 0 23 ? * 1
0 0 23 ? * SUN

Run at noon and midnight every day

Seconds Minutes Hours Day-of-month Month Day-of-week
0 0 0,12 * * ?
0 0 0,12 ? * *

Run Tuesday through Thursday every week at 3:00AM

Seconds Minutes Hours Day-of-month Month Day-of-week
0 0 3 ? * 3-5
0 0 3 ? * TUE-THUR

Run at midnight every Monday, Wednesday, and Friday

Seconds Minutes Hours Day-of-month Month Day-of-week
0 0 0 ? * 2,4,6
0 0 0 ? * MON,WED,FRI

Run at minute 30 and every minute between :05 and :10, every hour, every day in January

Seconds Minutes Hours Day-of-month Month Day-of-week
0 5-10,30 * ? 1 *
0 5-10,30 * * 1 ?

Limitations of cron scheduling

While using cron expressions will give you the most control over your integrations’ replication schedules, there are some limitations to keep in mind:

  1. Advanced scheduling isn’t available for all integrations. Some database integrations don’t currently support advanced scheduling. We are working on converting these integrations into Singer-powered taps, at which point advanced scheduling will be available.

  2. You can specify a Day of week OR Day of month value, but not both. Quartz, the cron implementation used by Stitch, doesn’t currently support specifying values for both fields in an expression. One of these fields must contain the ? character for the expression to be considered valid.

  3. Advanced Scheduling can only be used to whitelist extraction start times. This means that a job could start during a whitelisted time period but continue running beyond that window, depending on the duration of the extraction.

    For example: An integration has a schedule that tells it to run every 20 minutes between the hours of noon and 2:00PM, starting at noon.

    On average, extractions for this integration take between 2-5 minutes. However, the extraction that starts at 1:40PM takes longer than average, causing the job to continue running even after the 2:00PM mark. As a result, the job scheduled for 2:00PM will be skipped and resume at the next scheduled interval.


Create an Advanced Schedule for an integration

You can create an Advanced Schedule in an integration’s Settings page.

  1. To access this page, click the integration from the Stitch Dashboard and then click the Settings tab.
  2. In the Replication Frequency section, check the Advanced box located under the Anchor time menu. This will open the Advanced Scheduler.

    Note: This feature is only available during the Free Trial or on an Enterprise plan.

  3. Enter the values you want into each of the fields. Stitch will validate the schedule after each change. If the schedule is valid, a sample schedule will display under the fields.

  4. When finished, click the Save Integration button.

Additional cron resources



Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.