Loading Tables by Partition

DataSync allows you to load a table by partition instead of loading the entire table. This feature enables you to load data from an extraction table in smaller batches, called partitions, based on a date field. This can improve the performance and reliability of your data loading process.

 

When you enable this feature for an extraction table, DataSync will divide the data into partitions according to the partition field, the partition start date, and the day range that you specify. The partition field is a Date, Datetime, or Datetimeoffset field that determines how the data is split. The partition start date is the earliest date from the partition field that you want to load. The day range is the number of days that each partition covers.

 

DataSync will load each partition sequentially, starting from the partition start date. If a partition fails to load, the extraction run will fail, but all data from the previous partitions will be preserved. The next time you run Truncate and Load or Incremental Load on the extraction, DataSync will resume loading from the failed partition. When all partitions are loaded successfully, the table is considered fully loaded and any subsequent Incremental Load run will behave normally.

 

When you run Clean Deletes on an extraction with this feature enabled for a table, DataSync will also load data in partitions. Instead of starting from the partition start date, however, DataSync will start the load based on the quantity of days that you provided in the Run Extraction dialog box, as it does when the partition is disabled.

 

Note:  This feature is only available for extractions of Synchronization and Consolidation types. It is in Beta for all connector types except for Sage Intacct and Custom API.

To configure the table load by partition:

1. On the Extractions page, click the table link in the Tables column.

2. Select a table in the grid and click Partition Settings.

   

3. The Partition Settings dialog box is displayed:

   

   1. Select the appropriate partition field from the Field drop-down list. This list allows you to select a date, datetime, or datetimeoffset field from the source table that will be used to partition the data.

   2. Select the Start Date. The date picker allows you to select the earliest date from the partition field that you want to load.

      Note:  Please note that this date is in UTC.

   3. Enter the number of days that each partition will cover in the Day Range field (from 1 to 30 days). For example, if you enter 7, DataSync will create partitions of 7 days each.

   4. Use Partition Settings: This toggle allows you to enable or disable this feature for the selected table. If you disable it, DataSync will not use partitions and will load the data as usual.

      In order to validate the partition settings, it is recommended to do a Build and Validate with the setting Only Add Missing Tables/Fields/Indexes (Existing Data will persist).

4. Click Save.

   

To view the table load status:

1. On the Extractions page, click the table link in the Tables column.

2. Select a table in the list and click Table Status.

3. The Table Status dialog box is displayed. You can view if the table was successfully loaded or not and when the loading attempt was made.

   Note:  Please note that all dates and times are in UTC.

   The status can be one of the following:

   • Never loaded: You have never loaded the table. If a Truncate and Load or Incremental Load is run on the extraction, DataSync will attempt to load all data. If loading by partitions is enabled, it will load the data in partitions.

   • Done successfully: All data was successfully loaded.

     

     If there are any pending changes, a message will be displayed accordingly.

     

   • Error: An error was encountered. If loading by partitions is disabled, no data has been loaded. If it is enabled, the data may have been partially loaded. In this case, the data will resume loading next time a Truncate and Load or Incremental load is run.

     

   • Aborted: The run was aborted by a user. The partition may not have totally loaded when the run was aborted. This data will be cleaned up when loading is resumed.

   To override the default loading behavior described above, select one of the following options:

   • Keep all loaded partitions and resume loading from a specified date: This option allows you to enter a date and resume loading from that date. This date cannot be earlier than the partition start date or in the future.

   • Force reloading all data from the partition start date: This option allows you to reload all data from the table even if there were already some partitions loaded. This option is only available if the load was not successful or if the user aborted the load.

4. Click Save.