The Missouri Training Ledger: An Open-Data Workforce Pipeline
Data Operations
What is the Missouri Training Ledger?
The Missouri Training Ledger is a public dataset and site that sets projected job openings against the training programs that supply people for them, by region and by occupation. It is published at workforceadam.com as a county map, a ledger, and pages for programs, demand, and funding. Behind it is a Python and DuckDB pipeline that collects openly published data, checks it, and publishes a new version of the dataset without ever leaving the live site half-updated.
I designed and built the pipeline, the publishing process, and the site. This page covers how the data is collected, how a release is made safe, and where the method runs out.
What question does it answer?
It answers where openings outrun training, in a form a dean, a workforce board director, or an employer can read. The state publishes occupational projections. Colleges, apprenticeship sponsors, and federally funded training providers each report completions to a different agency in a different format.
The ledger adds them together. For each occupation in each region it lists projected openings beside the completers counted from credit programs, workforce-funded training, apprenticeships, and non-credit catalogs, and it reports the gap between them.
How is the data collected and built?
The pipeline is Python with DuckDB as its single analytical database. Each public source has its own adapter module, and there are 26 of them. The sources include state occupational projections from MERIC, wage and employment data from the Bureau of Labor Statistics, Census earnings and commuting data, IPEDS completions, the Department of Labor’s training provider results, O*NET, and OpenStreetMap.
Collection
Adapters respect each site’s robots.txt and crawl delay and identify themselves honestly. A source that fails does not stop the run. The failure is recorded, the previous good data for that source is carried forward, and the release is marked as degraded for that source.
Drive times
Access to training is measured as driving time, not straight-line distance. A routing engine running on OpenStreetMap road data computed 3,301,384 pairs of census tract and training site, and none of them was unroutable.
Site
The site is a static build from Jinja templates with plain JavaScript and d3 for the charts and the map. The chart libraries are vendored and pinned by checksum. The site reads the published dataset and holds no data of its own.
A full dry run on the first complete build covered 342,288 projected statewide openings across 698 statewide ledger rows, with 4,329 training programs and 277 providers counted.
How do you publish a data site so a failed update cannot break the live pages?
Each release uploads a complete, versioned folder first and moves a single pointer last. The pipeline writes every file of a release into a new folder named for its timestamp. Only after every file is uploaded does it replace one small file, current.json, which names the version the site reads. The pointer is uploaded under a temporary name and renamed into place.
A failed upload leaves the pointer where it was, so visitors keep seeing the last complete version. A version folder is never modified after upload. Rolling back is a one-line edit that points current.json at an earlier folder, and the last five versions are kept for that purpose.
The upload accounts are scoped the same way. The account that publishes data can reach only the data folder. The account that publishes pages is a different account, and the release script refuses to run if the two share a username or if a page release touches a data path.
How is every published number tied to its source?
A record cannot be published without a source and a date. Every public record carries a non-empty source field and an as-of date, and a guard checks the whole public folder for both before upload. A second guard holds an allowlist of public fields and a list of forbidden ones, so a field from the private working data cannot reach the public files by accident.
Unknown is published as unknown. When a training program reports no completer count, the pipeline does not count it as zero. The affected ledger row publishes its gap as empty and carries a low bound, a high bound, and a status instead. On the first full build that applied to 191 of the 698 statewide rows.
I checked the arithmetic by hand before the first release. Five statewide ledger rows were recomputed from the downloaded source files, not from the pipeline’s database, and all five matched. The front-end test run on the day of the latest audit recorded 305 passing tests.
Where does the method run out?
A zero in the supply column is not always a real gap. Training for nursing aides and for several large service occupations is not degree-program training, so it is absent from the completions data the ledger reads, and those rows overstate the shortfall. The site’s method page says this, and the fix is a source the pipeline does not yet ingest.
The operation depends on one computer. A single scheduled task runs the pipeline once a day, and only while I am logged in. The state projection files have to be downloaded by hand when they change. Failure alerts are written and not yet wired into the run, so a failed night is something I find in the log.
Those limits are recorded in the operations document beside the procedures. The method and sources page lists every source with its release and license, and the ledger is the result.