Tare: Data Ingestion

DineSafe is the food inspection service for the City of Toronto. If you’ve ever eaten out in Toronto before, you might have seen these certificates posted conspicuously (by law!) on the storefront:

Tare gets its restaurant data from DineSafe's website, which is naturally a fairly complete dataset. It's also a fairly small dataset, given that Tare is georestricted to Toronto. A benefit of this constraint is that we can use more “local” tools (like DuckDB, SQLite) to store and process data. We can also run the ingestion jobs during the wee hours of the night without worrying about CPU or network contention.

Roughly, the ingestion pipeline looks like this:

Here, the “S” parquet files denote DineSafe snapshots; they’re just snapshots of the DineSafe data at one point in time. The “M” means “mapping”. Mapping files represent mappings from DineSafe's external IDs to Tare's internal IDs. You need to keep track of this for record linkage – more on this later. The “C” means changeset, which is basically a list of actions to apply to the main SQLite db to update it with the new DineSafe data. All these files are stored as intermediate artifacts for bookkeeping and debugging.

Step 0: The daily job

The data update job runs daily at noon-ish via a systemd timer. It basically just runs a Python script, the same one that's run on dev.

Step 1: DineSafe Data Grievances

Restaurant data ingestion is done by scraping the DineSafe website. The snapshot parquet files come out to roughly 1MB.

If I had a million dollars, I’d use the Google Maps API, which unfortunately seems to be of higher quality (in terms of breadth, recency, location accuracy) than the DineSafe data.

There’s also an initiative called Open Data Toronto that hosts regularly-updated DineSafe datasets (https://open.toronto.ca/dataset/dinesafe/). You can download the entire dataset as one CSV file, which is more ergonomic than scraping, but this dataset suffers from data quality and consistency issues. (Last I checked was about a year ago). The scraped data from the actual DineSafe site tends to have less of that, for whatever reason.

Some examples of data quality issues (that exist across all DineSafe datasources):

• IDs are not as durable as they should be. All establishments in DineSafe have an “Establishment ID”. Sometimes these IDs get reused across establishments. Sometimes IDs even get swapped across existing establishments? This is by far the most annoying thing I have to deal with downstream as it makes record linkage substantially harder.

• Some establishments are missing. Surprisingly, this is usually more of a function of the restaurant just not being inspected (yikes!) than something wrong in their internal system. You can just email the city if this is the case and they’ll try to quickly inspect the place. Apparently, when opening a restaurant, the owners must notify at least the Toronto licensing department and health department. Restaurants that only notify the licensing department can go for years without a health inspection. I’m a bit disappointed the data is siloed off like this, but that’s life, I guess.

• A high rate of localized, more forgivable, data quality issues:

• Establishments in the same mall all share the same coordinates (because the address is the same).

• Chain locations not being named consistently. For example: “A &W”, “a and w”, “a&w”.

• Just general misspellings in the restaurant name.

• Sometimes, a restaurant can occupy two addresses right next to each other. DineSafe is inconsistent about whether it groups them together or lists them as separate locations. For example, as of the time of writing, there’s “Koh Lipe - 33 Baldwin Street” and “Koh Lipe Thai Kitchen - 35 Baldwin Street”, but it’s really the same restaurant.

The dataset is probably small enough to have an LLM fully audit it. An exercise for the reader. :)

I’ll admit some of these issues are because I’m not using the DineSafe data as intended. I’m glad this data exists, of course. It’s just that it adds more work for me to do. OK. Done complaining.

Step 2+3: Record linkage and changeset generation

The fallibility of DineSafe IDs means consolidating the snapshot from one point in time with the previously polled snapshot is a lot harder than it needs to be. The broad strokes are:

1. Dedup and normalize all rows in a new dataset.

2. Filter out any obvious unwanted rows — there are a bunch of factories and schools in the dataset, for example.

3. Group all the rows by (establishment id, establishment name, establishment address).

4. Best-effort match these new grouped rows with the grouped rows of the most recently applied DineSafe snapshot. Note that none of the grouped columns are guaranteed to be durable by themselves. IDs can change or be swapped, names can change (usually by fixing a typo), same thing for addresses. But if you take them together, you get a “good enough” picture of what could be going on:

   • If the name and address are the same (or within some small Levenshtein distance), but the IDs changed, you can assume that it’s the same establishment.

   • If the name changes (within some larger Levenshtein distance) but the address and ID stay the same, you can assume it’s some sort of typo fix for the name.

   These rules put together allow us to match most of the new DineSafe rows with exactly one of the old rows. If a row exists in the new data but doesn’t find a match in the old, then you can assume it’s a new restaurant. The other way means a restaurant likely shut down. There's also the chance that all three columns change, but the underlying restaurant is the same. I assumed the chances of this happening were negligible -- perhaps another benefit of limiting our dataset to Toronto.

   
   

   The story gets more complicated for the 1->many, many->1 and many->many mappings (mapping from old DineSafe rows to new). The solution I’ve settled on is to take these “leftover” old and new rows, reduce them to a bipartite graph, and find the connected components. The job then inspects each component and tries to pattern match different cases. If the job can’t automatically resolve these cases, it then requests manual intervention. (Hey, that’s me!)

5. We now have a mapping from the DineSafe IDs to Tare’s internal establishment IDs, and we can then use this mapping (along with the current and previous snapshots) to generate a changeset. A changeset is just another parquet file that contains a list of different commands (add this new entity, update this entity with x) that are applied to the main SQLite database.

   The schema looks like this:

   {
     action, 
   
     new_internal_id,      old_internal_id, 
     new_dinesafe_name,    old_dinesafe_name, 
     new_dinesafe_address, old_dinesafe_address,
     ...
   }
   
   

   Where “action” can be one of add, update, delete, merge. Depending on the command, some of these columns can be NULL. For example, if the command is add, then we expect all the old_dinesafe_* columns to be NULL.

Step 4+5: Changeset application + Search DB

Changeset application works how you probably think it does. We just go over all the changeset rows and perform each action. One nice thing about having smaller datasets (limited to the number of restaurants in Toronto: ~40k) is that you can wrap the entire operation in one giant transaction. So automatic rollback is a non-issue. The invertible nature of the changeset schema allows for manual rollbacks too, although I’ve never had to do one; it’s generally a lot easier to just fix forward than roll back.

After the main DB is updated, the search DB is completely regenerated (another benefit of smaller datasets). The search DB is basically a SQLite DB with a couple of not-so-super-well-supported plugins for search. For example, it imports a SQLite plugin for calculating embeddings. I chose to keep this separate from the main DB because of these plugins, and also because the retention and durability requirements for this DB are different from the main one. For example, we don’t really care about recoverability from Litestream because we can always just regenerate it on the fly.

After this step, the job is considered complete and the statuses are updated accordingly.

Closing Thoughts

I’m skipping over a lot of stuff. Wrangling real life data is rarely so nice. But this captures the broad strokes of how Tare does it.

Looking back, I probably didn’t need to represent the intermediate files as parquet. My data engineering sensibilities probably got the best of me here; just keeping everything SQLite would have been fine. Parquet has better file sizes (it’s automatically compressed), for example, but the datasets are too small to really require this. DuckDB has pretty nice SQLite support, too.

Also, after designing and implementing this, I discovered the Overture Maps project. Overture Maps seems to be an effort by a consortium of big tech companies (Microsoft, Meta, AWS) to tackle Google’s dominance over the online mapping space. Shrug. It looks like they have similar ideas about how to wrangle real world data.

Skimming through their documentation, it looks like they have a system called GERS (Global Entity Reference System) for structuring and matching real-life map data. Their “Bridge” files seem to be analogous to Tare’s mapping files (i.e. external-ID to internal-ID mapping). Their “Data Changelog” seems to roughly be analogous to Tare’s changeset files, too. I guess this is a bit reassuring for Tare’s design.