The following is intended for jobs that use Sage Intacct as a source connection.
Prerequisites
Before configuring any Sage Intacct replication job, verify the following in the tables/tasks of your job. You can verify each of the following prerequisites in the UI by navigating to the Overview tab of the task and reviewing the Source Information section. You will see the Incremental Check Column (ICC), Key/Index Column, and Capture Deletes fields:
- Incremental replication support: Determine whether the source table supports incremental replication. By default, Sync detects if a table has a good incremental check column. If the field below 'Incremental Check Column' is blank, there is no detected ICC. If an ICC is not detected and settings are editable, you must determine if there is a viable column. A good ICC is either a datetime or integer-based column in the table that can be used to identify new or modified records. The 'Whenmodified' column that is typically used is a great example of a good ICC.
- Primary key availability: Confirm whether a primary key (PK) exists for the table. If the field below 'Key/Index Column' is blank, there is no primary key.
- Capture Deletes supported: Confirm that Capture Deletes is either true or false for the table. If false capturing deletes are not supported, no deletes in the source will be deleted in the destination.
Reference(s)
When to Use Drop Table
Enabling drop mode for a task/table deletes the entire table before proceeding with the normal replication process. This requires a new table to be formed for each job run. Enable drop table in the following scenarios:
Example Scenarios
- Tables without ICC: When no Incremental Check Column exists and no substitute column can be found, incremental replication is not possible. Enabling drop table is required.
- Tables with ICC but no primary key: When a table has an Incremental Check Column but lacks a primary key, Sync can detect changed records but cannot perform merge operations. Without a primary key to uniquely identify rows, updates from the source appear as duplicate rows in the destination rather than updating existing records. Enabling drop table is necessary to prevent data duplication.
- Tables that do not 'Capture Deletes': When capture deletes is false for a table, no records will be removed from the destination table. It is necessary to enable drop table each run to ensure records deleted in the source are removed. Exceptions to this are the Gldetail and Glentry tables; both of these tables have their own logic implemented to ensure that deletes are captured.
Schema Modifications
Enable drop table when adding or removing columns from the table schema.
When to Avoid Drop Table Mode
Do not enable drop table for large tables that support incremental replication. Repeatedly dropping and recreating large tables increases the risk of HTTP 502 errors from Sage Intacct due to increasing request.
Configuration for Large, Frequently Updated Tables
For large tables with frequent updates:
- Enable incremental replication: Reduces load on Sage Intacct API and prevents 502 errors.
- Set replication interval: The recommended interval is a 15-day interval. This interval can vary based on the date range of the data you are replicating.
Handling API Latency for High-Volume Tables
The following recommendations apply specifically to large tables with frequent updates, such as Gldetail.
MinLastModTimeOffset Property
Sage Intacct treats certain field updates as record deletions rather than modifications. When the majority of changes are processed as deletions, we have noticed cases of extreme API latency. Implementing the MinLastModTimeOffset property improves replication reliability.
Configuration
Specify the offset in seconds using the advanced job options. For example, you can set the property 'MinLastModTimeOffset=21600'. This example sets a 6-hour offset (21,600 seconds = 6 hours).
Recommended Schedule
- Execute jobs at least twice daily for tables that are being modified often.
- Set minimum offset (MinLastModTimeOffset) of 3–4 hours.
- Avoid scheduling jobs between 2:00 PM–3:00 PM EST: Sage Intacct experiences high request volume during this period.
Reference(s)
- CData Sync - Advanced Job Options | 25.3.9396
- Controlling Min and Max LastModTime Values in Incremental Jobs (CData Sync) | Community
CHECKCACHE Validation Jobs
A CHECKCACHE job validates and repairs destination tables by identifying and correcting missing or extraneous records. This provides an additional data accuracy layer. For example, if you just updated a record and ran the job by itself, the record may not be picked up due to the latency of the API. A CHECKCACHE job will compare the source and destination and repair the destination tab to include the newly added record.
Configuration
Create a new job and use one of the following CHECKCACHE queries:
CHECKCACHE DestinationTable AGAINST SourceTable WITH REPAIR;Or
CHECKCACHE DestinationTable AGAINST SourceTable WITH REPAIR START '2024-01-01' END '2025-01-01'Both CHECKCACHE queries look at both the source and destination tables. Then, insert any missing records, update outdated records in the destination, and remove any records that are not in the source. The only difference is that the second query narrows down the repair to a specific date range. In the second query, we are only looking at the year 2024.
Recommended Implementation
Configure a post-job event to trigger the CHECKCACHE job immediately after the original replication job is completed. Running these jobs in tandem ensures that repairs are done right after the data is replicated. Ideal tables for this are large tables such as Gldetail and Glentry. Your post-job event should be similar to the following template:
<!-- Start Executing different Job -->
<api:set attr="job.JobName" value="Job2"/>
<api:set attr="job.WorkspaceId" value="default"/>
<api:set attr="job.ExecutionType" value="Run"/>
<api:set attr="job.WaitForResults" value="true"/> <!--Setting to true will wait for Job to complete -->
<api:call op="api.rsc/executeJob" httpmethod="post" authtoken="<YourAPIToken>" in="job"/>Reference(s)
Incremental Replication Start Date Format
Both datetime and integer time (Unix timestamp) formats are functionally equivalent:
- Integer time: 1639149390000 (Unix timestamp for 2021-12-10 15:16:30 GMT)
- DateTime: 2021-12-10 (begins at 00:00:00 on the specified date)
Start Integer vs. Start Date in Incremental Replication
Use datetime format for Sage Intacct jobs, as the Whenmodified column uses datetime values. Whenmodified is often the column detected and used as the ICC in Sage Intacct.
Expected Format
The Whenmodified column uses the following datetime format '2010-08-12 08:11:58.000000'. This format includes date, time, and microsecond precision.
Reference(s)
