Refresh

Refresh is a process in Fivetran Local Data Processing that initially loads data from source to target location. Refresh loads your data from source into the target tables you already have. If the tables are not there, Refresh creates them for you. Refresh is a function that you use in the context of a Channel. In this channel, the source for Refresh should be a database location, while the target can be either a database or a file location.

Refreshing from a source location is supported only on certain location types. For the list of supported source location types, see section in .

Local Data Processing offers the following methods of executing Refresh:

• via UI – see
• via CLI – see
• via API – see /api/latest/hubs/{hub}/channels/{channel}/refresh in .

You can use a Local Data Processing channel exclusively for doing a Refresh job. In this case, you must also define this channel with and actions, and running is not required.

A Refresh job cannot be run simultaneously with the job because it can lead to data inconsistency. Therefore, when a Refresh job is started, Local Data Processing forces the Integrate job into SUSPEND and creates a control file to block the Integrate job from running. When the Refresh job is complete, Local Data Processing automatically removes the control file and unsuspends the Integrate job. Note that the Integrate job is restored to its previous before the Refresh was executed.

The control files are created on the Local Processing Hub System in the directory HVR_CONFIG/hubs/hubname/channels/channelname/control.

In case the Refresh job fails and the block control files are not removed automatically, the Integrate job cannot be restarted (or unsuspended). When that happens, an error shows up. To resolve this error, remove the control files with names matching .ctrl-channelname-integ-targetlocation-*_block from the hub directory HVR_CONFIG/hubs/hubname/channels/channelname/control and then manually Unsuspend the Integrate job.

Refresh Types

There are two types of Refresh you can choose from:

• Bulk Refresh

• Row-by-row Refresh

You can set a Refresh type via:

• the CLI
• choosing or in the UI

Bulk Refresh

Bulk Refresh means that the target object is truncated, and then the bulk copy is used to refresh the data from the read location. On , during Bulk Refresh table indexes and constraints will be temporarily dropped or disabled and will be reset after the refresh is complete.

During Bulk Refresh, Local Data Processing typically streams data directly over the network into a bulk loading interface (e.g. direct path load in Oracle) of the target database. For that do not support a bulk loading interface, Local Data Processing streams data into intermediate temporary staging files (in a staging directory) from where the data is loaded into the target database. For more information about staging files/directory, see section "Burst Integrate and Bulk Refresh" in the respective .

Row-by-Row Refresh

Row-by-Row Refresh, also referred to as Row-wise Refresh, compares data on read and write locations and produces a 'diff' result based on which only rows that differ are updated on the write location, each row is refreshed individually. This results in a list of a minimal number of inserts, updates or deletes needed to re-synchronize the tables.

For column-oriented databases (e.g., Redshift, Snowflake, Google BigQuery), Row-wise Refresh is best used on small amount of data, e.g., on tables with a small amount of changed data or on small tables. In other cases Row-wise Refresh on column-oriented databases takes a lot of time.

Slicing

Sometimes, the amount of data that the Refresh job needs to process is too big. In this case, you can choose to divide the table into a few batches and process them in parallel. In Local Data Processing, this is achieved via the functionality. By configuring Slicing, you can divide your database table into a few pieces that will be processed in parallel saving you a lot of time.

Local Data Processing suggests a few types of Slicing, each fitting best for a specific business case. To learn more about slicing types and when it's best to use them, see the concept page.