After you select a source and click an object or export to sync, you have two options to configure:
- Extract mode
- Ingestion mode.
An extract mode determines which records Celigo sync pulls from the source. An ingestion mode determines how those records are saved to the destination table: replace existing data, add to it, or update matching records and insert the rest.
You can select both modes separately for each object or export in a sync. This lets one sync apply different combinations per table — for example, extracting only changed records for one export while extracting all records for another, or merging a high-volume transactional table while replacing a small reference table.
This article describes what each extract mode and ingestion mode does, what each requires, and how data lands in the destination during a run so that you can choose the right combination for each table. For the exact steps to reach this panel, see Create a sync from an application's objects or Create a sync from an application's exports.
Choose an extract mode
Choose the required extract mode:
- Delta: Extracts only records created or updated since the last successful run. For an object, this relies on the field you designate to detect changes. For an export, it relies on the delta configuration set in the export itself. If you run a full historical sync, the delta date is the historical date you select, not the date of the last successful run.
- All: Extracts every record each time the sync runs.
Choose an ingestion mode
Choose the required ingestion mode:
-
Replace: Deletes all data in the destination table and replaces it with new data from the export. There is no primary key field in this mode, and the data lands in a single operation at the end of the run.
Use replace for small tables you want rebuilt from scratch each run, such as reference or lookup data. Replace deletes the destination table's existing records, so don't use it for a table that holds data the source no longer returns. -
Append: Preserves all data in the destination table while adding new data from the export, even if a duplicate record would be added. There is no primary key field in this mode, and the data lands in a single operation at the end of the run.
Use append for immutable events or log data, where every run adds new records and nothing needs updating. A re-run of an append table loads its records again, so a source that returns the same records twice produces duplicate rows. -
Merge: Adds new records and replaces any duplicate records in the destination table with the exported data; all other existing records are preserved. The primary key field is available in this mode, and you can select the required field as primary key. The data lands in chunks as the run progresses.
Use merge for tables you sync repeatedly and want kept current, such as orders, customers, or tickets. Merge is the only mode that updates existing rows in place, and it's the mode to use for high-volume tables.
Pair the mode with an extract mode that matches. Merge with Delta extract keeps a table current while limiting each run to changed records; Replace is typically paired with All, since it rebuilds the table from a full extract each run.
Primary keys for merge
When merging tables, Celigo sync auto-identifies a primary key for an object, though you can change it. For an export, you must set at least one primary key yourself. A primary key can include the child of a flattened object. If all root-level fields are arrays, assign a primary key within each structure, as though they were already normalized. Fields marked as a primary key can't be masked.
How merge loads data in chunks
For merge, Celigo sync loads data to your destination in chunks rather than in one operation at the end of the run. Each chunk becomes queryable as it lands, so you can read the records that have already loaded without waiting for the entire sync to finish. This happens automatically, and no configuration is required.
You can observe the following for chunk loading:
- Data appears during a run. A merge table can hold part of a run's data while the run is still in progress. A query that runs mid-sync returns the chunks that have landed, not the full result set.
- A failed run keeps what is already loaded. If a merge run fails partway through the load stage, the chunks already written to the destination remain there. The next run re-extracts from the source and merges over them. Because merge matches on the primary key, re-merging the same records updates them rather than duplicating them.
Supported destinations
The following destinations are supported:
- Google BigQuery,
- Microsoft SQL,
- NetSuite Analytics Warehouse (NSAW),
- Snowflake