docs / guides / tables

The tables Ingest creates

What lands in your warehouse (data tables, child tables, quarantine, the three _ingest_ control tables) and the semantics of each.

Ingest writes everything into one dataset (schema) in your destination. Your warehouse is the only durable home of your data: it holds the rows, the pipeline's saved position, and the schema history, and losing every byte on Ingest's side would lose none of it. During a load, staging copies can exist briefly under the staging prefix you configured (warehouses whose bulk load reads from object storage, like Redshift's COPY); Ingest removes them when the load completes. Five kinds of tables land in your dataset.

Data tables

One per resource: orders, contacts, deals. Typed columns, deduplicated rows, maintained incrementally by the resource's write disposition: append (every run adds rows), replace (every run rebuilds the table), or merge (rows are upserted by primary key, so the table tracks the source's current state).

Every row carries three lineage columns: _ingest_ingested_at (when it landed), _ingest_run_id (which run), and _ingest_request_id (which API request). Any row can be traced to the exact request that produced it.

Child tables

When a record contains a list of objects (an order's line items), the list becomes its own table instead of a JSON blob, named after its path: orders__line_items. Nesting continues the same way (orders__line_items__discounts). Plain nested objects, by contrast, flatten into prefixed columns on their parent (customer__address__city).

Link columns tie a child to its parents: _ingest_parent_id points one level up, _ingest_root_id points at the top-level record, and _ingest_list_idx preserves list order.

select o.id, li.sku, li.quantity
from orders o
join orders__line_items li on li._ingest_root_id = o._ingest_id

The subtree stays consistent: when a record is updated at the source, all of its child rows are replaced together with it. A line item removed upstream disappears here; it cannot linger as a stale orphan.

Quarantine tables

Created only when needed. If a value arrives that cannot fit the existing column type (a string in what has always been a number, beyond safe widening), those rows land in <table>__quarantine as raw JSON with a reason, instead of corrupting the table or being dropped silently. The run is flagged so you see it, and the rows stay queryable in your warehouse. Rows never vanish, and a column never mutates into a lowest-common-denominator type underneath your queries.

Control tables

Three small tables make the pipeline stateless on our side and durable on yours:

  • _ingest_state: where the pipeline left off. Cursors, watermarks, per-resource positions. Restored at the start of every run; written back only after data lands. If it cannot be read, the run fails rather than silently starting from scratch.
  • _ingest_loads: a receipt per table per load. This is what makes retries safe: a crashed run that re-runs skips everything already receipted, so rows are never loaded twice.
  • _ingest_schema_versions: the schema's change history, one row per version. Tables added, columns added, types widened. The version only advances when something actually changed.

State and schema are written in the same step as the final load receipt, so the saved position is never ahead of the data it describes.

Staging tables

Transient working tables (_stg_ prefix) used during a load: new data lands there, is deduplicated, then swapped or merged into the real table in one atomic step, so a query never sees a half-loaded table. They are dropped when the load finishes. One left behind by an interrupted run is inert and safe to remove.

Semantics worth knowing

Tables are current-state. On a merge resource, an update overwrites the previous values: the table answers "what does this look like now", not "what did it look like in March". Resources whose data is naturally event-like (transactions, log entries) are authored as append and keep every record.

Deletions at the source do not propagate to merge tables. An incremental sync only sees records that exist; a record hard-deleted upstream stays in your warehouse, indistinguishable from a live one. Where a vendor reports deletions in its API, the connector surfaces that as an ordinary column you can filter on. Where it does not, a Full refresh (on the pipeline page; it re-extracts everything and rebuilds the tables) is the way to true up a table where deletion accuracy matters.

Lake destinations differ slightly. In plain-file mode there are no tables: data is written as Parquet/JSONL files under per-load prefixes with a current-pointer for atomic replace, and merge is unsupported. In Iceberg mode, data and control tables are real Iceberg tables in your own catalog, and merge works.