{"success":true,"data":{"author":"City of Boston (Inspectional Services Department, Health Division)","bucketAccessType":"public","bucketListable":false,"bucketName":"data.boston.gov","category":"Public Health","cloudProvider":"api","createdAt":"2026-08-15T00:00:00.000Z","description":"Twenty years of restaurant and food-establishment health inspections by the City of Boston Inspectional Services Department, from 4 April 2006 to the present: 896,379 rows, paired with a companion resource of 3,329 currently active food establishment licenses. The critical structural fact, and the one that makes most published analyses of this dataset wrong, is that a row is not an inspection. A row is one violation cited at one inspection, so an inspection that cited nine violations appears nine times with identical business, license, result and inspection-time fields, and an inspection that cited none appears once with null violation columns. The 896,379 rows resolve to 217,084 distinct (licenseno, resultdttm) inspections, an average of 4.1 rows per inspection, so counting rows overstates inspection counts fourfold. Each row carries the business name and doing-business-as name, the legal owner and contact name, the license number, category (FS food service, RF retail food, MFW mobile food walk-up, and others) and status, the license issue and expiry timestamps, the inspection result (HE_Fail, HE_Pass, HE_FailExt, HE_Hold and about a dozen more), the inspection timestamp, the violation code against the Boston sanitary code, the violation level (asterisk for non-critical, double asterisk for critical, triple for foodborne-illness-risk), the violation description, the inspector's free-text comment, the violation status and status date, and the address with ZIP, assessing property id and a (latitude, longitude) string. The companion active-licenses resource is a clean current-state snapshot with separate latitude and longitude columns and a cleaned daytime phone number, so it is the right join target for mapping. The paired structure supports work on inspection frequency and equity, repeat-offender identification, critical-violation rates by cuisine and neighborhood, and the relationship between inspection outcomes and license churn. The schema card documents the row grain, the result and violation-level vocabularies, and the fact that the location column is a single parenthesised string rather than two numeric columns. Published by the Inspectional Services Department under PDDL 1.0.","displayTitle":"Boston Food Establishment Inspections and Active Licenses","dockerImageUrl":"531775725781.dkr.ecr.us-east-1.amazonaws.com/scigantic-notebook-minimal:latest","id":"31e3c65a-06e8-5c3b-857b-04874906e4ce","license":"Open Data Commons Public Domain Dedication and License (PDDL) 1.0","referenceUrl":"https://data.boston.gov/dataset/food-establishment-inspections","region":"us-east-1","schemaCard":{"sampleRows":[{"licensecat":"FS","violdesc":"Clean Cloths  Hair Restraint","licenseno":"313440","__resourceId":"4582bec6-2b4f-4f9e-bc55-cbaa73117f4c","dbaname":null,"viol_level":"*","viol_status":"Fail","licstatus":"Inactive","namefirst":"Kenneth Pasquariello","address":"55  COURT ST","violdttm":"2018-03-20 14:54:25+00","__schema":"inspections and violations","descript":"Eating & Drinking","legalowner":"KHOSLA VIPAN","businessname":"1000 Degrees Pizza","resultdttm":"2018-03-20 14:54:25+00","zip":"02108","result":"HE_Fail","namelast":"Pasquriello LLC","violation":"13-2-304/402.11","_id":1,"status_date":null,"property_id":"156226","issdttm":"2017-08-14 12:49:37+00","comments":"One staff person without hair restraint. Provide","city":"BOSTON","location":"(42.35925954972639, -71.05890048027378)","expdttm":"2020-01-01 04:59:00+00","state":"MA"},{"__schema":"active licenses snapshot","address":"100 Federal","state":"MA","zip":"02110","latitude":"42.354770000088834","dbaname":null,"licensecat":"FS","_id":1,"licstatus":"Active","dayphn_cleaned":"6174345000","longitude":"-71.0561300019333","property_id":"57150","businessname":"100 Federal Market & Barista- 10th Floor","__resourceId":"f1e13724-284d-478c-b8bc-ef042aa5b70b","license_add_dt_tm":"2023-08-09 16:00:26.99+00","city":"Boston","descript":"Eating & Drinking"}],"totalObjects":2,"columns":[{"name":"_id","type":"int"},{"type":"text","name":"businessname"},{"name":"dbaname","type":"text"},{"name":"legalowner","type":"text"},{"name":"namelast","type":"text"},{"type":"text","name":"namefirst"},{"type":"text","name":"licenseno"},{"name":"issdttm","type":"text"},{"name":"expdttm","type":"text"},{"type":"text","name":"licstatus"},{"type":"text","name":"licensecat"},{"type":"text","name":"descript"},{"type":"text","name":"result"},{"type":"text","name":"resultdttm"},{"type":"text","name":"violation"},{"name":"viol_level","type":"text"},{"name":"violdesc","type":"text"},{"name":"violdttm","type":"text"},{"name":"viol_status","type":"text"},{"type":"text","name":"status_date"},{"name":"comments","type":"text"},{"name":"address","type":"text"},{"name":"city","type":"text"},{"name":"state","type":"text"},{"name":"zip","type":"text"},{"type":"text","name":"property_id"},{"name":"location","type":"text"}],"accessHints":[{"language":"python","snippet":"import io, json, urllib.error, urllib.parse, urllib.request\nimport pandas as pd\n\nCKAN = \"https://data.boston.gov/api/3/action\"\n\ndef ckan(action, **params):\n    \"\"\"Any CKAN action endpoint. Plain urllib works; no auth, no headers.\"\"\"\n    url = f\"{CKAN}/{action}?\" + urllib.parse.urlencode(params)\n    with urllib.request.urlopen(url, timeout=300) as r:\n        return json.load(r)[\"result\"]\n\ndef sql(query):\n    \"\"\"Server-side SQL over the DataStore. Warns when the 32,000-row cap clips.\"\"\"\n    res = ckan(\"datastore_search_sql\", sql=query)\n    if res.get(\"records_truncated\"):\n        print(\"WARNING: clipped at the 32,000-row cap; aggregate or page with OFFSET\")\n    return pd.DataFrame(res[\"records\"])\n"},{"language":"python","snippet":"# Bulk CSV download. pd.read_csv(url) on this portal raises 403: the CKAN link\n# 302s to a presigned S3 URL that the redirect fails on. Resolve it by hand and\n# drop the explicit :443, which is the single change that makes it a 200.\nclass _NoRedirect(urllib.request.HTTPRedirectHandler):\n    def redirect_request(self, *a, **k):\n        return None\n\ndef boston_csv(url, **read_csv_kwargs):\n    try:\n        urllib.request.build_opener(_NoRedirect).open(url, timeout=120)\n        signed = url\n    except urllib.error.HTTPError as e:\n        if e.code not in (301, 302, 303, 307, 308):\n            raise\n        signed = e.headers[\"Location\"].replace(\"s3.amazonaws.com:443\", \"s3.amazonaws.com\")\n    with urllib.request.urlopen(signed, timeout=900) as r:\n        return pd.read_csv(io.BytesIO(r.read()), **read_csv_kwargs)\n"},{"language":"python","snippet":"# Resolve a resource id by package name rather than hardcoding it, and page\n# past the 32,000-row cap. Verified against a 282,836-row resource.\ndef resources(package_name):\n    pkg = ckan(\"package_show\", id=package_name)\n    return [\n        {\"id\": r[\"id\"], \"name\": r.get(\"name\"), \"file\": r[\"url\"].rsplit(\"/\", 1)[-1],\n         \"format\": r.get(\"format\"), \"live\": bool(r.get(\"datastore_active\"))}\n        for r in pkg[\"resources\"]\n    ]\n\ndef read_all(resource_id, page=32000, **filters):\n    \"\"\"Every row of a DataStore resource, in _id order.\"\"\"\n    out, offset = [], 0\n    while True:\n        res = ckan(\"datastore_search\", resource_id=resource_id,\n                   limit=page, offset=offset, **filters)\n        rows = res[\"records\"]\n        out.extend(rows)\n        if len(rows) < page:\n            return pd.DataFrame(out)\n        offset += page\n"}],"starterCell":"import io, json, urllib.error, urllib.parse, urllib.request\nimport pandas as pd\n\nCKAN = \"https://data.boston.gov/api/3/action\"\n\ndef ckan(action, **params):\n    \"\"\"Any CKAN action endpoint. Plain urllib works; no auth, no headers.\"\"\"\n    url = f\"{CKAN}/{action}?\" + urllib.parse.urlencode(params)\n    with urllib.request.urlopen(url, timeout=300) as r:\n        return json.load(r)[\"result\"]\n\ndef sql(query):\n    \"\"\"Server-side SQL over the DataStore. Warns when the 32,000-row cap clips.\"\"\"\n    res = ckan(\"datastore_search_sql\", sql=query)\n    if res.get(\"records_truncated\"):\n        print(\"WARNING: clipped at the 32,000-row cap; aggregate or page with OFFSET\")\n    return pd.DataFrame(res[\"records\"])\n\nINSPECTIONS = \"4582bec6-2b4f-4f9e-bc55-cbaa73117f4c\"   # 896,379 violation rows\n\n# Count inspections at the correct grain, and the critical-violation rate per\n# inspection. count(*) here would count violations and be 4.1x too high.\ndf = sql(f'''\n    SELECT date_part(\\'year\\', \"resultdttm\"::timestamp)::int AS yr,\n           count(DISTINCT (\"licenseno\" || \\'|\\' || \"resultdttm\")) AS inspections,\n           count(\"violation\") AS violations,\n           sum(CASE WHEN \"viol_level\" LIKE \\'**%\\' THEN 1 ELSE 0 END) AS critical\n    FROM \"{INSPECTIONS}\"\n    WHERE \"resultdttm\" IS NOT NULL AND \"resultdttm\" <> \\'\\'\n    GROUP BY 1 ORDER BY 1\n''')\ndf[\"violations_per_inspection\"] = df[\"violations\"] / df[\"inspections\"]\nprint(df.to_string(index=False))\n","caveats":["DO NOT look for a FUSE mount. This archive has cloudProvider \"api\": nothing is mounted, ~/archive and /mnt/archive do not exist, and there are no files on disk to walk. The generic archive preamble you may have been given says otherwise; it is wrong for this row. All data is read over HTTPS from the CKAN API at https://data.boston.gov/api/3/action. The notebook has outbound internet; no credentials, tokens or headers are required for any endpoint below.","DO NOT trust a result set of exactly 32,000 rows. Both datastore_search and datastore_search_sql cap a single response at 32,000 records no matter what LIMIT you write, so a query over a million-row resource returns a full-looking frame that is silently a fragment. datastore_search_sql sets records_truncated: true in the result when it clips; check it on every call. The fixes: aggregate server-side (GROUP BY in SQL returns few enough rows to fit), or page with ORDER BY _id plus OFFSET, which is verified to work past 280,000.","DO NOT do arithmetic or date comparison on a column without casting it. Almost every DataStore column on this portal is typed text regardless of what it holds, so avg(\"latitude\") returns HTTP 409 Conflict rather than a number, and min/max on a date column sorts lexically and returns a confident wrong answer. Cast explicitly in SQL: \"latitude\"::float, \"open_dt\"::timestamp, date_part('year', \"open_dt\"::timestamp). Where a column IS genuinely typed numeric or timestamp it is marked as such in the columns list below, and those are safe to use directly.","SQL AGGREGATES COME BACK AS STRINGS, so arithmetic on the resulting DataFrame silently fails or, worse, concatenates. PostgreSQL `numeric` results are serialized as JSON strings by the CKAN API (sum(\"OVERTIME\") returns '153220443.25', not 153220443.25) while `int`/`bigint` and `double precision` come back as JSON numbers. So count(*) is an int, avg() and sum() over a numeric column are strings, and dividing one such column by another raises \"unsupported operand type(s) for /: 'str' and 'str'\". Run pd.to_numeric on every aggregate column before doing arithmetic on it. Do NOT blanket-coerce every column: ward \"06\", precinct \"0104\" and ZIP \"02128\" are zero-padded identifiers that must stay strings.","A 403 FROM datastore_search_sql MEANS THE SQL TEXT TRIPPED AN EDGE FILTER, NOT THAT YOUR QUERY IS WRONG. Some perfectly valid PostgreSQL is rejected with HTTP 403 Forbidden before it ever reaches the database, so there is no SQL error message to read and retrying the identical query never helps. Verified blocked, deterministically, 5 of 5 attempts each (2026-08-15): agg(...) FILTER (WHERE ...) · extract(<part> FROM ...) · substring(x FROM a FOR b) · trim(BOTH ... FROM ...) · NULLIF(...) · a literal constant comparison such as 1=2. Verified working substitutes for each: sum(CASE WHEN cond THEN 1 ELSE 0 END) for FILTER · date_part('year', ts) and date_part('epoch', ts_a - ts_b) for extract · left(col, n) for substring · CASE WHEN d = 0 THEN NULL ELSE d END for NULLIF. Subqueries, CTEs, LEFT JOIN, IN (...), HAVING, window functions, percentile_cont WITHIN GROUP, the regex operator ~, and casts are all fine. When a query 403s, rewrite it; do not retry it.","DO NOT call pd.read_csv() on a data.boston.gov download URL. It raises HTTP 403. The CKAN download link 302-redirects to a presigned S3 URL on the bucket og-production-open-data-bostonma-892364687672, and the request fails when it is followed from urllib's redirect path, which is what pandas uses. Verified 2026-08-15: pd.read_csv(url) -> 403, while requests.get(url) on the identical URL -> 200. Two fixes, both verified: read through requests (pd.read_csv(io.BytesIO(requests.get(url).content))), or resolve the redirect yourself and strip the explicit \":443\" from the returned Location before fetching it. The bucket itself is private (anonymous ListObjectsV2 and GetObject both return AccessDenied), so there is no S3 path around this: the presigned link is the only way in. See accessHints for a copy-paste helper. None of this applies to the /api/3/action endpoints, which are plain JSON over HTTPS and work with bare urllib.","Resource ids, not names, are the addressing key, and the names are unreliable: CKAN's resource `name` field is null on many Boston resources, and several packages publish generated filenames (tmp4myyj_u8.csv) that carry no year. The ids listed in this card were captured on 2026-08-15. The package NAME is the stable handle, so resolve ids at runtime with GET /api/3/action/package_show?id=<package-name> and match on the resource `name` or `url` rather than hardcoding an index into the resource array, whose order is not guaranteed.","_id is CKAN's synthetic 1-based row number added by the DataStore loader, not a key from the source data. It is the right column to ORDER BY for stable paging and it is meaningless as a join key or an entity id.","COUNT DISTINCT INSPECTIONS, NOT ROWS. count(*) counts violations. The inspection identity is the pair (licenseno, resultdttm); there is no inspection id column. Measured ratio on the full file: 896,379 rows to 217,084 inspections.","A NULL violation column means a clean inspection, not missing data. Dropping rows with null violation removes exactly the passing inspections and turns any pass-rate calculation into 100% failure.","The location column in the history file is a single string \"(42.35925954972639, -71.05890048027378)\", not two numeric columns. Parse it, or join to the active-licenses resource on licenseno, which has proper latitude and longitude columns.","businessname, dbaname and legalowner are all free text and all drift over time for the same physical establishment. licenseno is the stable identity, and property_id links to the assessing parcel. Grouping by business name splits chains and merges unrelated establishments that share a name.","The history file is described by the city as a LEGACY dataset. Its latest record is 2026-08-05, so it is current as of that date, but the label signals it may stop being updated. The active-licenses resource is a snapshot with no history: it tells you who holds a license now and nothing about the past.","An establishment appearing in the history with licstatus Inactive has not necessarily closed; licenses lapse and are reissued under new numbers. Do not infer business closure from license status alone."],"layout":{"example":"Critical-violation rate per inspection by license category, counted at the right grain. sum(CASE WHEN ...) rather than count(*) FILTER (WHERE ...), which this portal 403s:\n  SELECT \"licensecat\",\n         count(DISTINCT (\"licenseno\" || '|' || \"resultdttm\")) AS inspections,\n         sum(CASE WHEN \"viol_level\" LIKE '**%' THEN 1 ELSE 0 END) AS critical_violations\n  FROM \"4582bec6-2b4f-4f9e-bc55-cbaa73117f4c\"\n  GROUP BY 1 ORDER BY inspections DESC LIMIT 15","pattern":"THE ROW GRAIN IS THE WHOLE STORY. A row is NOT an inspection. A row is one VIOLATION cited at one inspection. An inspection that cited nine violations appears as nine rows with identical business, license, result and resultdttm fields; an inspection that cited none appears once with null violation columns. Measured: 896,379 rows resolve to 217,084 distinct (licenseno, resultdttm) inspections, so counting rows overstates inspections by 4.1x. Nearly every published \"Boston has N restaurant inspections\" figure derived from this file is that error.\nTWO RESOURCES. The inspections and violations history (896,379 rows, verified range 2006-04-04 to 2026-08-05, 27 columns) and a current-state snapshot of active licenses (3,329 rows, 15 columns). The snapshot is the better join target for mapping because it has separate numeric-looking latitude and longitude columns, where the history file only has a single parenthesised \"location\" string.\nCONTROLLED VOCABULARIES you will group on:\n  result       HE_Pass, HE_Fail, HE_FailExt, HE_Hold, HE_Filed, HE_NotReq, HE_Closure, ...\n  viol_level   \"*\" non-critical, \"**\" critical, \"***\" foodborne-illness risk factor\n  viol_status  Fail, Pass\n  licensecat   FS food service, RF retail food, MFW mobile food walk-up, FT food truck, ...\n  licstatus    Active, Inactive, Deleted\nThe violation column holds the Boston sanitary code citation (\"13-2-304/402.11\") and violdesc its short label (\"Clean Cloths Hair Restraint\"). comments is the inspector's free-text note and is often the only place the specific finding is recorded.\nRESOURCES:\n  Food Establishment Inspections (data.boston.gov/dataset/food-establishment-inspections): 1 DataStore resources of 1 total\n    4582bec6-2b4f-4f9e-bc55-cbaa73117f4c     896379 rows  Food Establishment Inspections\n  Active Food Establishment Licenses (data.boston.gov/dataset/active-food-establishment-licenses): 1 DataStore resources of 1 total\n    f1e13724-284d-478c-b8bc-ef042aa5b70b       3329 rows  Active Food Establishment Licenses"},"format":"v2-hybrid","generatedAt":"2026-08-16T17:39:25.112Z","rowCount":899708,"sampleObjects":["4582bec6-2b4f-4f9e-bc55-cbaa73117f4c  Food Establishment Inspections","f1e13724-284d-478c-b8bc-ef042aa5b70b  Active Food Establishment Licenses"]},"summary":"Twenty years of restaurant and food-establishment health inspections by the City of Boston Inspectional Services Department, from 4 April 2006 to the present: 896,379 rows, paired…","title":"Boston Food Establishment Inspections and Active Licenses","updatedAt":"2026-08-16T17:40:36.467Z","visibility":"public","websiteUrl":"https://www.boston.gov/departments/inspectional-services","whiteLabel":"boston"}}