Selecting the right extract and ingestion mode for your DI syncs
The most common question we see after a first sync goes live isn't about connections or credentials — it's "why are there duplicate rows in my Snowflake table?" Almost always the answer is the same: the wrong ingestion mode was chosen at setup. It's a one-click decision with significant downstream consequences. Here's how to get it right.
Two decisions, one step
When you're selecting objects in Step 1 of the sync wizard, you're making two connected choices: how data is pulled from the source (extract mode) and how it lands in the destination (ingestion mode). These interact — getting one wrong means the other one can't save you.
Extract mode: delta vs all
Delta pulls only records that changed since the last run. It's the right default for most operational data and requires a reliable timestamp field the source maintains — Celigo derives this automatically for various applications such as Salesforce, NetSuite or any other sources with Object mode. For other sources with export mode, check whether delta fields are available before assuming.
All re-extracts every record on every run. It's appropriate for small reference or lookup tables that don't have a reliable change cursor. The trap is using All on a large transactional table because you're worried about missing records. This hammers the source API, inflates your loaded row count on every run, and forces you into Replace mode at the destination — where you lose historical data entirely. However if your business demands full extract in order to maintain data integrity then you can consider this option.
Ingestion mode: merge, append, replace
Merge upserts by primary key — it adds new records, updates changed ones, and leaves the rest alone. Use it for mutable operational records: Opportunities, Accounts, Orders, Invoices, Customers. It requires at least one field marked as primary key — without it the sync fails at the load stage. Composite keys are supported if no single field is unique on its own.
Append adds every extracted record as a new row, keeping all prior rows intact. Use it for immutable event and audit streams — email events, ticket audits, login history — where accumulating full history is the point. Don't use it for mutable records. Append on Opportunities means every sync run adds another copy of every changed record. Your table grows but doesn't reflect current state, and downstream models have to dedupe — which is expensive and fragile and if your downstream transformation steps are not configured right way.
Replace drops all existing data and reloads from the current extract on every run. Use it for small reference and lookup tables that are always a full snapshot: product catalog, price book, country codes, configuration tables. Don't use it for large tables — the full reload burns warehouse credits — or for any table where historical trends matter, because every run wipes the prior state.
Three questions before you configure
Before setting the mode on any object, ask:
Does this data change after it's created? If yes, use Merge so updates land correctly. If no, use Append to accumulate history.
Does this data have a reliable primary key? If yes, Merge is available and the right call for mutable records. If no, you're in Append or Replace territory — and if the table is large, consider whether you need a surrogate key before committing.
How big is this table and how often does it change? Small tables with infrequent changes are fine with Replace. Large tables with frequent changes need Merge with delta extract. Large tables with all extract and Append is almost always a mistake.
The short version
Merge for anything mutable with a key. Append for immutable event streams. Replace for small snapshots. Delta extract for almost everything. All extract only when there's no cursor field and the table is small enough to reload without consequence.
Have a data type that gave you trouble when choosing ingestion mode? Share it in the comments — edge cases are where the real learning happens.