Every table that Celigo sync creates includes system columns alongside your source fields — Celigo-managed metadata that supports soft deletes, merge behavior, and run traceability. This article defines each column so you can use them in downstream queries and understand why they appear in your destination tables.
Celigo sync adds up to six system columns: four to every table, and two conditionally.
Columns added to every table
-
_celigo_deleted(Boolean) - The soft-delete flag. Celigo sync soft-deletes rather than removing rows, so this is the column downstream consumers filter on to exclude deleted records. Where the source has its own native delete flag, Celigo sync maps to that (for example, Salesforce's IsDeleted); where it doesn't, Celigo sync supplies the value. -
_celigo_extracted_at(Timestamp) - When the data was pulled from the source. Set from the sync run's start time, so it's uniform across every row in a run rather than a per-record extraction time. -
_celigo_loaded_at(Timestamp) - When the row was written to the destination. Evaluated when the row is processed, so unlike _celigo_extracted_at it varies within a single run. Together, the pair gives you source-to-warehouse latency. -
_celigo_job_id(String, 255) - Which sync run produced the row. More than a debugging column: it's load-bearing for the Merge ingestion mode, which deletes destination rows whose job ID doesn't match the current run — this is how a full-refresh merge drops records that no longer exist at the source.
Columns added conditionally
-
_celigo_id(String, 255) - A stable per-row surrogate key. Added unless the table is flat (non-normalized) and already has at least one user-defined primary key. Normalized child tables always get it, because their composite key is built entirely from system-managed columns, so a durable row identity is still needed. -
_celigo_index(Integer) - The array ordinal for normalized child tables — the position of an element in its source array, giving one row per element. Two behaviors worth knowing:- Its primary-key status depends on the ingestion mode: it becomes a primary key only under Merge, and only when the table has no other primary key. Under Append and Replace it is never a primary key.
- It propagates downward: in grandchild tables it reappears under a prefixed name (for example,
Products__celigo_index) to build the composite foreign-key chain back to the root.