Restaurant Health Inspection Data by City: APIs and Traps

Verified dataset IDs, a live freshness check, and the schema traps behind restaurant health inspection data by city: inverted scores, null sorts, row fanout.

Most large US cities publish restaurant inspections through the same Socrata Open Data API. That uniformity is a trap. The transport is identical; the column names, the score polarity, the row granularity, and the update cadence are not. This guide lists seven municipal datasets and the specific failure modes I hit while querying every one of them live on 2026-07-20.

Every number, column name, and dataset ID below came from a request I actually ran against the live endpoint on that date. Where I could not confirm something, I say so instead of guessing.

The seven datasets

All seven answered HTTP 200 to an unauthenticated request. The date column is the one you need for any range filter, and it is different in three of the seven.

Check freshness before you trust a city

"Open data portal" does not mean "current." Two cheap signals tell you where a dataset really stands: max() on the date column, and the X-SODA2-Truth-Last-Modified response header, which every one of these portals returns. Run this before you build anything on top of a city.

const DATASETS = [
  { city: 'New York, NY',        domain: 'data.cityofnewyork.us',       id: '43nn-pn8j', date: 'inspection_date' },
  { city: 'Chicago, IL',         domain: 'data.cityofchicago.org',      id: '4ijn-s7e5', date: 'inspection_date' },
  { city: 'Cincinnati, OH',      domain: 'data.cincinnati-oh.gov',      id: 'rg6p-b3h3', date: 'action_date' },
  { city: 'Austin, TX',          domain: 'datahub.austintexas.gov',     id: 'ecmv-9xxi', date: 'inspection_date' },
  { city: 'King County, WA',     domain: 'data.kingcounty.gov',         id: 'f29f-zza5', date: 'inspection_date' },
  { city: 'Boulder County, CO',  domain: 'data.colorado.gov',           id: '6ytb-f2cq', date: 'rec_date' },
  { city: 'Montgomery Cnty, MD', domain: 'data.montgomerycountymd.gov', id: 'dkrp-gr48', date: 'inspection_start_date' },
];

const DAY = 86400000;

for (const d of DATASETS) {
  const url = `https://${d.domain}/resource/${d.id}.json`
    + `?$select=max(${d.date}) AS newest&$where=${d.date} IS NOT NULL`;
  const res = await fetch(url, { headers: { Accept: 'application/json' } });
  if (!res.ok) {
    console.log(`${d.city.padEnd(22)} HTTP ${res.status}`);
    continue;
  }
  const newest = (await res.json())[0]?.newest ?? null;
  const lag = newest ? Math.round((Date.now() - Date.parse(newest)) / DAY) : null;
  const modified = res.headers.get('x-soda2-truth-last-modified');
  console.log(
    `${d.city.padEnd(22)} newest=${(newest ?? 'none').slice(0, 10)}` +
    `  lag=${lag === null ? '?' : lag + 'd'}  portal_modified=${modified ?? 'n/a'}`
  );
}

Save it as fresh.mjs and run node fresh.mjs on Node 18 or newer. This is the output I got on 2026-07-20:

Three things fall out of that. New York, Chicago, and Montgomery County are effectively current. King County had not published an inspection newer than 2025-11-26, and its portal had not been touched since 2025-12-04, so a consumer-facing "current health grade" built on it would have been eight months out of date. And Cincinnati returned a negative lag, because it carries one row dated 2026-07-29, in the future. Do not assume max(date) is today's ceiling.

Re-run this check on a schedule. A portal that was fresh last quarter can quietly stop publishing without any error surfacing in your pipeline.

Trap 1: DESC sort returns nulls first

The obvious way to page newest-first is $order=inspection_date DESC. On Socrata, rows with a null date sort ahead of every real row. King County has 419 null-date rows and Cincinnati has 46, so your first page is garbage:

curl -s -G 'https://data.kingcounty.gov/resource/f29f-zza5.json' \
  --data-urlencode '$select=inspection_date,name' \
  --data-urlencode '$order=inspection_date DESC' \
  --data-urlencode '$limit=3'

That returns three establishments with no inspection_date key at all, alphabetized. Socrata omits null fields from JSON entirely rather than emitting null, so naive code reading row.inspection_date gets undefined, not a value it can test against. Add an explicit guard to the $where:

curl -s -G 'https://data.kingcounty.gov/resource/f29f-zza5.json' \
  --data-urlencode "\$select=inspection_date,name" \
  --data-urlencode "\$where=inspection_date IS NOT NULL" \
  --data-urlencode "\$order=inspection_date DESC, :id ASC" \
  --data-urlencode "\$limit=3"

That version returns the genuine newest rows, all dated 2025-11-26. New York, Austin, Boulder County, and Montgomery County had zero null date rows when I checked, but the guard costs nothing and future-proofs you.

Trap 2: scores point in opposite directions

A cross-city "score" column is the single most dangerous field in this space, because the polarity flips. I confirmed each direction by grouping scores against the city's own outcome label:

Averaging or ranking those four score columns together produces a number that means nothing. Normalize to a per-city percentile, or keep the raw value alongside its city and never compare across the boundary.

One more type detail: Socrata returns these as JSON strings, not numbers. New York gives "score":"35", Austin gives "score":"97.000000", and latitude arrives as "40.887304169271". Cast everything explicitly or your sorts will be lexicographic.

Trap 3: one inspection is not one row

Row counts across these portals are not inspection counts, and the inflation factor differs by city and by cause.

New York fans out per violation. Filtering to inspections after 2026-06-01 returned 13,318 rows covering only 3,522 distinct camis plus inspection_date pairs, a factor of 3.78. The worst single inspection carried 20 rows. Group on the establishment ID and date before you count anything.

Chicago fans out per license. Chicago is nominally one row per inspection, but a business holding several licenses produces one row per license for the same visit. One restaurant at 120 N La Salle St returned three rows for its 2026-07-16 inspection under license numbers 3090603, 3090493, and 3090492:

curl -s -G 'https://data.cityofchicago.org/resource/4ijn-s7e5.json' \
  --data-urlencode "\$select=license_,dba_name,address,inspection_date" \
  --data-urlencode "\$where=dba_name = 'OLD TOWN POUR HOUSE' AND inspection_date = '2026-07-16'"

Note the column is license_, with a trailing underscore. Because the license number changes over a business's life, it is not a stable establishment key. Chicago addresses also carry inconsistent trailing whitespace, so trim before you join on them.

Montgomery County publishes exact duplicates. This is the sharpest one. The dataset held 2,241,321 rows for only 11,984 distinct registration numbers since June 2024. In the 30 days before 2026-07-20 it returned 3,570 rows for 311 distinct combinations of registration number, inspection number, and date, a factor of 11.5. I pulled the 60 rows dated after 2026-07-16 and compared them field by field: they collapsed to 30 distinct keys, each appearing exactly twice, and the paired rows were identical in every one of the 30 columns. Deduplicate on the full row or on that three-part key.

Two further Montgomery County notes. inspection_number is not unique and is not a business identifier: the value 25-4475 appears 472 times across four different registration numbers. Use registration_number for the business. And the dataset has no violation rows at all; compliance is encoded as 16 wide columns such as cold_holding_temperature and proper_hand_washing, each holding "In Compliance", "Not Observed", or similar.

Trap 4: no shared vocabulary for outcomes

There is no word that means "failed" across these portals. Grouping each city's outcome column gave completely disjoint vocabularies:

• Chicago (results): Pass, Fail, Pass w/ Conditions, Out of Business, No Entry, Not Ready, Business Not Located
• King County (inspection_result): Satisfactory, Unsatisfactory, Complete, Incomplete, Not Accessible, Not Applicable, Baseline Data
• Cincinnati (action_status): Not Abated, Abated, Abated On-Site, Approved - Minor Violations, Not In Compliance
• Montgomery County (status): Pass, Fail, Closed, Incomplete, Closed With Complaint, Cancelled
• Boulder County (result): Pass, Reinspection Required, Closure
• New York (action): full sentences, such as "Violations were cited in the following area(s)." Closures read "Establishment Closed by DOHMH..."
• Austin: no outcome column exists. The dataset has seven columns and publishes a score only.

A substring search for "fail" therefore matches Chicago and Montgomery County and nothing else. Two Chicago traps hide in there as well: "Pass w/ Conditions" covers 46,503 rows that a results = 'Pass' filter drops and a "pass" substring filter wrongly folds in, and "Out of Business", "No Entry", "Not Ready", and "Business Not Located" together account for about 44,000 rows that are not health outcomes at all. Build an explicit per-city mapping table; there is no shortcut.

Trap 5: sentinel dates and the boundary operator

New York uses 1900-01-01 as a placeholder for establishments with no inspection on record. There were 3,586 such rows, and they are genuinely empty: no action, no violation_code, no grade, no score, no inspection_type. Filter with inspection_date > '1901-01-01' or they will drag your city's minimum date back 126 years.

Watch the comparison operator too. On New York, inspection_date > '2026-07-01' returned 4,386 rows while >= returned 4,809. The 423-row gap is the inspections dated exactly 2026-07-01, since these timestamps sit at midnight. If your caller says "since 2026-07-01" and means it inclusively, you need >=.

Also worth knowing on New York: critical_flag has three values, not two. Critical (155,713 rows), Not Critical (132,752), and Not Applicable (8,123). Casting to a boolean silently buries that third category.

Pagination that does not drift

Socrata pages at 1,000 rows by default. On the New York dataset, a request with no $limit returned exactly 1,000 rows and $limit=50000 returned 50,000, so use the larger page size and make far fewer round trips.

Offset pagination ordered only by date is unstable, because the date is not unique and ties have no defined order between pages. Both :id and :updated_at are exposed and orderable on these datasets, so add :id as a tiebreaker:

curl -s -G 'https://data.cityofnewyork.us/resource/43nn-pn8j.json' \
  --data-urlencode "\$select=:id,camis,inspection_date,violation_code" \
  --data-urlencode "\$where=inspection_date > '1901-01-01'" \
  --data-urlencode "\$order=inspection_date DESC, :id ASC" \
  --data-urlencode "\$limit=50000"

For incremental syncs, :updated_at is the right high-water mark: it reflects when the portal last wrote the row, not when the inspection happened, so it catches backfills and corrections that an inspection_date cursor would miss.

On rate limits: twelve rapid unauthenticated requests to the New York endpoint all returned 200 today, and no rate-limit headers came back, so I cannot state a specific quota. Socrata's own app-token documentation describes throttling for anonymous traffic and specifies the X-App-Token header as the way to register a request, so send one on any scheduled or high-volume job. Treat 429 and 5xx as retryable with exponential backoff regardless.

If you would rather not maintain the mapping layer

The per-city mapping is the entire job here, and it drifts when portals rename columns. If you want it maintained for you, this scraper covers exactly the seven datasets above and emits one normalized schema with a granularity field marking whether a row is violation-level or inspection-level. Being straight about its limits: it is the same seven portals, so it inherits every staleness figure in the table above; its "result contains" filter is a plain substring match, which for the reasons in Trap 4 will match only Chicago and Montgomery County if you type "fail"; and its date filter uses a strict greater-than, so a start date is exclusive. Rolling your own against the IDs in this guide is entirely reasonable, and for one or two cities it is probably the better call.

What I verified, and what I did not

Verified live on 2026-07-20: all seven dataset IDs and domains reachable and returning HTTP 200; every column name cited; the freshness figures and last-modified headers; null-first ordering on King County and Cincinnati; the score polarity of all four scoring cities; the New York, Chicago, and Montgomery County row-multiplication factors; every outcome vocabulary listed; the 1900-01-01 sentinel and its empty rows; the strict-versus-inclusive date boundary; the 1,000 default and 50,000 maximum page size; and the availability of :id and :updated_at. Every code block on this page was executed before publication.

Not verified, and stated as such above: any specific rate-limit threshold, and whether the portals whose data has gone stale intend to resume publishing. Coverage here is these seven jurisdictions only; other cities publish inspections on Socrata and elsewhere, and this guide makes no claim about them. Figures will drift, so re-run the freshness script rather than trusting the table.