Public data, kept honest

1The guardrail is the whole design

The rule the loop runs under: filings for the metro area may not move by more than ±3% unless the change is explained. Every run records the headline counts alongside the change it made, so a fix that quietly moved the answer gets caught by its own log rather than by someone noticing months later.

That is the only reason I trust the output. Twenty-three logged runs, each one a single change with its numbers before and after.

2Geography, which was the messiest

Mapping a filing to where the job actually is turned out to be four separate bugs wearing one coat. Date strings mixed date and datetime formats, which silently dropped three entire fiscal years. Postal codes needed population-weighted mapping to counties rather than a naive lookup, which is what was misplacing jobs in smaller metros. Connecticut needed the legacy county delineations, which alone took unresolved rows from 4,056 down to 397 and found 33 metros that had not existed in the output before.

After all of it, all seventeen fiscal years are populated, running between 342,000 and 622,000 cases a year nationally.

3One company, one row

Employer identity is the other repeatable failure. The fix that mattered was small and counterintuitive: when the same tax identifier appears under several spellings, prefer the longer one, because the most common spelling is usually a truncation rather than the real name.

Worth recording that this pass changed no counts at all. It made the data correct without moving a single headline number, which is exactly the shape of change the guardrail is designed to let through.

4Coverage

Pulling in every file back to 2010 meant mapping four separate legacy schemas. 9.64 million raw rows reduce to 7.17 million distinct cases; with the later files folded in, 8.65 million cases are in scope. Nationally that is 1.64 million filings across 110,943 employers.

For the metro the dashboard actually serves: 5,912 filings in the last three years across 1,535 employers, of which 2,167 are new hires. Consultancies account for 38% of filings, which is the single number people are most surprised by.

5What is still open

Paused since mid-September. The pipeline runtime reached 1,352 seconds on the full history, well past the five-minute budget the nightly loop was built around, so the loop cannot currently run unattended on the whole dataset. That is the thing to fix before anything else.

The population estimates that came out of this — a metro Indian-origin population of 19,104 ± 2,079, cross-checked against a second census table at 18,466 ± 1,434 — are survey estimates with real error bars, and I would rather they were quoted with the bars than without.

6Evidence

Every step was written down at the time as a claim, its evidence and a verdict — Public data, kept honest (4 parts). The discarded runs are in there too.

Where the work lives:

• ~/apps/h1b_firms_list_kansas_city — the pipeline and its loop.
• ~/apps/h1b_firms_list_kansas_city/program.md — the loop's instructions.
• ~/apps/h1b_firms_list_kansas_city/results.tsv — the experiment log.