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 two lineage columns: _ingest_ingested_at (when Ingest read it from the source) and _ingest_load_id (the load that wrote it; its receipt in _ingest_loads names the run). A row read from a file also carries _ingest_source_file and _ingest_row_number, the line it came from. Older tables may also have _ingest_run_id and _ingest_request_id: Ingest no longer writes them, so they are empty on rows written since.

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: the run, when it ran, and how many rows it wrote. Join a row's _ingest_load_id to it to see which run wrote the row. A load that fails partway is discarded and the next run reads the source again: merge and replace tables still never hold a row twice, but an append table can.
  • _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.

Sources certified against a real API today: Abilene TX 311, Albany NY 311, Ann Arbor MI 311, Anne Arundel County MD 311, Anthropic, Asana, Auburn WA 311, Aurora IL 311, Austin TX 311, Baltimore MD 311, Baton Rouge LA 311, Beaumont TX 311, Bellevue WA 311, Bellingham WA 311, Berkeley CA 311, Bethlehem PA 311, Bloomington IN 311, Boca Raton FL 311, Boston MA 311, Bridgeport CT 311, Brockton MA 311, Brookline MA 311, Buffalo NY 311, Burlington VT 311, Cambridge MA 311, Cambridge MA Open Data, Charleston County SC 311, Charlotte NC 311, Chula Vista CA 311, Cincinnati OH 311, Chicago IL 311, New York City NY 311, Clayton County GA 311, Cleveland OH 311, Cobb County GA 311, Columbus OH 311, Corona CA 311, Dallas TX 311, Davenport IA 311, Denver CO 311, Detroit MI 311, Elgin IL 311, Encinitas CA 311, Everett WA 311, Federal Way WA 311, Fort Lauderdale FL 311, Gilbert AZ 311, Google Ad Manager, Google Analytics 4, Greenwich CT 311, Hartford CT 311, Honolulu HI 311, Houston TX 311, Howard County MD 311, Huntington WV 311, Huntsville AL 311, Indianapolis IN 311, Jersey City NJ 311, Jira, Kansas City MO 311, Kent WA 311, Knox County TN 311, Los Angeles CA 311, Lexington KY 311, Lincoln NE 311, LinkedIn Ads, Livonia MI 311, Louisville KY 311, Lubbock TX 311, Macon-Bibb County GA 311, Manchester NH 311, Maui County HI 311, Medford MA 311, Memphis TN 311, Miami FL 311, Miami-Dade County FL 311, Minneapolis MN 311, Montgomery County MD 311, Multnomah County OR 311, Naperville IL 311, Nashua NH 311, Nashville TN 311, Newark NJ 311, NOAA, New Orleans LA 311, Norfolk VA 311, Oakland CA 311, Omaha NE 311, Open Brewery DB, Paterson NJ 311, Peoria IL 311, Philadelphia PA 311, Pinellas County FL 311, Pittsburgh PA 311, PokéAPI, Portland ME 311, PostgreSQL, Prince George's County MD 311, Provo UT 311, Raleigh NC 311, Ramp, Renton WA 311, The Rick and Morty API, Sacramento CA 311, Salesforce, San Antonio TX 311, San Francisco CA 311, San Gabriel CA 311, San Jose CA 311, San Mateo County CA 311, San Rafael CA 311, Santa Fe NM 311, Scottsdale AZ 311, Seattle WA 311, Shopify, Somerville MA 311, Springfield MO 311, St. Petersburg FL 311, Tacoma WA 311, Toledo OH 311, Topeka KS 311, Vallejo CA 311, Washington DC 311, Wayne County MI 311, West Des Moines IA 311, Wichita KS 311, Worcester MA 311, Youngstown OH 311.

Where the tables land: Amazon S3, Google Cloud Storage, Azure Blob Storage, Apache Iceberg, Ingest Managed Lakehouse, MotherDuck, Postgres, Amazon Athena, Databricks, Redshift, BigQuery, ClickHouse, MySQL / generic SQL, Snowflake.

Start a pipeline