You are looking to optimize your Fivetran sync frequencies for row-based transactional destinations.
Though the MDS (Modern Data Stack) consists of a columnar data-warehouse, there might be instances where you would prefer to use a row-based database as a destination. These include the following destinations found in the docs:
- SQL Server
You may be dealing with slowness due to backups syncing at the same time as Fivetran’s processing or all of your connectors happen to sync at the same intervals. Your goal may be to optimize your destination database loads. If you’ve maxed out your CPU and memory capabilities and are still noticing slowness, then you could try the steps below.
- Minimize the sync frequency so that changes are happening more frequently. This means that the incremental syncs should consist of smaller batches of data as opposed to larger batches of data from your various connectors trying to sync more changes all at once.
- Set the sync frequency of your different connectors to varying times in order to offset the load
- Change the "schedule_type" to "manual" using the Modify a Connector endpoint.
- Trigger syncs according to your own custom schedule via Sync Connector Data endpoint.
- If you only need data once a day then we suggest selecting the exact time you need the sync to occur—ideally outside of busy operating hours for your destination or database
- Check out this custom use case if you need to set up a custom sync schedule
- Increase the timeout settings on your row-based destination. You can find additional information on this process in the documentation specific to your destination or in community boards.
The best long-term solution for destination improvements would be to upgrade your warehouse from row stores to a horizontally scalable column store like Snowflake, Redshift, BigQuery, etc.