NYC Homeless Shelter Reports

Application Areas
Consulting
Logistics / Infrastructure
Datatypes
Numeric
Datetime

← Back to Datasets

Midterm Vignette

In his final book, 2024’s Genesis: Artificial Intelligence, Hope, and the Human Spirit, tech visionary Henry Kissinger joined forces with former Google CEO Eric Schmidt and a third guy to explore how AI “enables the achievement of higher, more difficult, and grander problems than those currently solvable by the human brain” (40). With their trademark concern for the disadvantaged, Kissinger and friends describe an exhilarating technology-driven future, wherein

“Distributed AI systems might be designed to connect, via mass-manufactured robots and AI-optimized infrastructural systems, to the unconnected […] enabling the provision of food, clothing, and shelter to the half of humanity that still lacks those basic necessities. Alternatively, using new sustainable synthetic materials, AIs could build cities around the world to provide shelter, regulate temperature, ensure access to power and digital connections, and provide clean water, food, medicine, and sanitation.” (113)

How might we take some initial steps towards this brighter horizon, with its AI versions of already-existing-but-underfunded institutions like food banks and homeless shelters? In this project we will focus on the latter institutions, by overhaulting New York City’s current Homeless Shelter Data Portal.

As it is currently set up, the portal is not very amenable to the types of modern data-analytic tasks that data scientists carry out on a day-to-day basis: Daily data is simply entered into a shared Excel spreadsheet at each shelter location, and then the spreadsheet contents are aggregated into totals (rather than entries/exits) and exported to PDF once per day, at the following URL:

https://www.nyc.gov/assets/dhs/downloads/pdf/dailyreport.pdf

The PDF at this URL is then overwritten each day, so that statistics from prior days are no longer available after 24 hours: after this window of time, only aggregate monthly data is available (via the monthly reports accessible via the earlier link to the data portal).

The first phase of our plan is thus to implement an OLTP infrastructure, enabling each shelter to record events – e.g., entry, exit, and transfer of individuals and families – into an OLTP database.

Next, we will need some way to ensure that data from this OLTP infrastructure flows into a downstream OLAP-friendly format, so that our data scientists can derive core insights: which shelters tend to be most full on which days, for example, or which are more likely to have vegan-friendly or gluten-free food on a given day, which have more vacancies for individuals vs. vacancies for families, and so on.

Then, to actually carry out the computation to derive these insights, we need to use some technology that is optimized for OLAP workflows.

Finally, we will need some way of deriving reports and visualizations of the answers to these questions!

Question 1 Starter: OLTP Infrastructure

As mentioned in the vignette, although the nightly PDF report contains aggregate counts of the number of men, women, and children across all shelters, this is [technically, in our terminology] the OLAP form of the data as it currently exists.

The OLTP format — the form of the data as it is originally recorded — consists of entry and exit records, along with the timestamp of when the event was recorded. A small sample of this OLTP data is as follows. First, using head() to show the first five records that ever arrived in the OLTP DB (since it is an append-only log), we can see that the shelters started recording entries and exits late at night on September 26, 2026:

Code 
import polars as pl
import itables
shelter_pldf = pl.read_parquet('oltp_fam_children.parquet')
itables.show(shelter_pldf.head())
Loading ITables v2.8.1 from the internet... (need help?)

We can already see two potential challenges here, in terms of how efficiently we might be able to extract these OLTP records into an OLAP framework:

  • First, there may be countless entry errors and/or idiosyncrasies in terms of how specific desk workers enter each value:
    • Above we see full names for each of the non-empty family and children records, but in fact your approach will need to be able to handle cases where only a first name is recorded, or where the list of children actually just contains a single str value like "5 children" or "none", "NA", and so on!
  • Second, regardless of the specific ways in which the names are entered by each desk worker, the family and children fields are being stored as nested lists of strings. We know that the .parquet format can handle this, but in the OLTP DB these lists will just be stored in plaintext log format, like ["John Smith","Jane Doe"].
    • One consequence of this detail is that your approach will need to handle e.g. the difference between an empty list – a case where the desk worker successfully recorded the absence of additional family members or children during check-in/check-out – and a missing value, where for whatever reason no information is available about whether family members and/or children were part of that check-in/check-out.

The final tricky aspect of this setup becomes visible when we use .tail() to view the most recently-added records:

Code 
itables.show(shelter_pldf.tail())
Loading ITables v2.8.1 from the internet... (need help?)

The 9999-12-31 timestamps on these records may throw you off, but in fact this is an extremely common thing to see in certain DB setups: so common, in fact, that it has a name: the Type 2 Slowly Changing Dimension, often shortened to just SCD2. In fact, this way of recording “current” or “active” records is described on page 299 of Reis and Housley (2022), which is the page immediately following the book’s Kimball Star Schema diagram 🤯

The idea behind the SCD2 scheme1 is just that, at the end of the night when (for example) the late-shift receptionist clocks out and the morning-shift receptionist clocks in, the system might take each individual or family that did not check out during the past 24 hours and enter a purposefully-fake “checkout” date as far as possible into the future, as an indicator that the individual or family has been actively residing in the shelter from their checkout date to the present.

Since “the present” is an ever-changing time, that is only “known” (in terms of having a specific timestamp) at the exact moment a particular query might be made, the SCD2 approach allows “activeness” to be recorded as a date, instead of (say) having to always keep an active vs. non_active.

References

Reis, Joe, and Matt Housley. 2022. Fundamentals of Data Engineering: Plan and Build Robust Data Systems. O’Reilly Media, Inc.

Footnotes

  1. Actually, this is just one example of how SCD2 might be employed in a particular OLTP or OLAP setup – there are multiple ways to implement this scheme, and multiple reasons why it might be useful… see the book for more!↩︎