docs / guides / check-the-tables

How to check the tables Ingest lands

What Ingest records with every load, how to compare a table with its source, when a run fails instead of loading, and how curated tables are checked.

Every load leaves a receipt in your warehouse: _ingest_loads holds one row per table per load, with its status and row count, and every data row carries the load that last wrote it. A value that does not fit its column lands in a quarantine table instead of vanishing, and a run that expected rows and got none fails. So you can check a table against its source yourself, in SQL, without taking anyone's word for it.

What does Ingest record about each load?

Three control tables live beside your data (The tables Ingest creates lists their columns). The one to start with is _ingest_loads:

select table_name, status, rows, started_at, finished_at, run_id
from _ingest_loads
order by finished_at desc
limit 20

Each row is a receipt: which table, which run, when, and how many rows it wrote. Every data row carries _ingest_load_id, the load that last wrote it, and _ingest_ingested_at, when Ingest read it from the source.

How do I compare a table with the source?

Count the same thing on both sides, for the same period: the rows in the table, and the records the vendor's own screen or report shows. Three properties of the tables decide what a match looks like:

  • A merge table is current state. An update overwrites the previous values, so the table answers "what does this look like now", and its row count matches the source's count of records that exist today.
  • A record deleted at the source stays in a merge table. An incremental sync only sees records that exist. Where the vendor reports deletions, the connector lands that as a column you can filter on; where it does not, a Full refresh on the pipeline page rebuilds the tables from the source.
  • An append table keeps every record it reads. If a load fails partway, it 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.

When does a run fail instead of loading?

Ingest treats these as failures, never as a successful run with less data: zero rows where rows were expected, an error returned inside a response that says it succeeded, a missing value in the key a table merges on, and a saved position that cannot be read back. The failed run names the table and the reason on the run's page.

A value that arrives in a shape its column cannot hold, such as text where the column has always held numbers, goes to <table>__quarantine as raw JSON with the reason, and the run is flagged so you see it. The column's type does not change underneath your queries, and the row stays queryable.

Which tables should people query: raw, silver or gold?

The tables Ingest lands are raw: typed and deduplicated, but shaped like the source. On Snowflake, Ingest can also design curated tables over them, which your Snowflake keeps and refreshes as Dynamic Tables: silver tables clean and conform what Ingest lands, and gold tables answer questions. Nothing is built until a member confirms the design.

Every curated table is checked when it is designed, again when it is built, and every day after:

  • Its grain is unique and never empty. The row count must equal the count of distinct grain values, exactly.
  • An aggregate cannot fan out. When a table adds up a metric, every join from the table the metric comes from must be many-to-one, so a total cannot count a record twice.
  • An inner join keeps every row it joins from. A join that silently drops rows that find no match, such as an order whose customer is gone, fails with the count of rows it left out.
  • Zero rows is a failure, unless the design says the table may be empty.

These are the checks that catch the usual ways a correct-looking query is wrong. The Snowflake setup guide covers the curation user these tables are built with.

Sources certified against a real API today: Abilene TX 311, Albany NY 311, Ann Arbor MI 311, Anne Arundel County MD 311, Anthropic Admin API (Claude API), 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, OpenAI (ChatGPT Enterprise), Chula Vista CA 311, Cincinnati OH 311, Chicago IL 311, New York City NY 311, Clayton County GA 311, Cleveland OH 311, Clockify, Cobb County GA 311, Columbus OH 311, Corona CA 311, Azure Cosmos DB, 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, Google Drive, Google Sheets, 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, OpenAI Admin API (API Platform), 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, PrimeGov public meetings, Prince George's County MD 311, Provo UT 311, Raleigh NC 311, Ramp, Renton WA 311, The Rick and Morty API, Rippling, 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, ObsessionDB, MySQL / generic SQL, Snowflake.

Start a pipeline