Open Data Isn't the Same as Usable Data

Three federal datasets, three different ways the data was technically correct and still not usable as published -- all found in the same afternoon of actually trying to build on them, not by reading documentation that doesn't mention any of it.

BLM's national Mining Claims service has a field called GEO_STATE -- Geographic State, two-letter code, sounds like exactly the thing to filter a nationwide claims database by state. Querying GEO_STATE='NV' against a live 4.4-million-row layer returned exactly one record. Not because Nevada has one mining claim -- Nevada is the single largest mining-claims state in the country -- but because GEO_STATE is null on 95% of the entire table, populated for essentially only Alaska. The field that was actually populated, ADMIN_STATE, doesn't share a name, a description, or an obvious reason to prefer it over the field that looks right. Nothing in the service's metadata flags one field as usable and the other as effectively empty -- both are real, correctly-typed columns, and only one of them is load-bearing. The only way to find out was to ask the server directly -- a statistics query grouping by each candidate field, comparing the counts against what's independently known to be true about where mining actually happens in the US. Nevada showing "1" was the tell. If that number had looked even slightly plausible, the wrong field would have shipped.

EIA's electricity retail-sales API returns one row per state per month -- except it doesn't. It returns one row per state, per month, per customer sector: residential, commercial, industrial, transportation, and an all-sectors blended total, five rows sharing the same state and month unless the request explicitly filters to one sector. A database built expecting one row per state-month hits a hard constraint violation the moment it tries to write all five at once -- not a silent wrong answer this time, a real crash, which is actually the easier failure mode to catch. The harder version of this bug is the one that writes successfully and quietly keeps whichever of the five rows happened to load last.

Worth naming, so this doesn't read as every federal API being secretly broken: FRED's state-unemployment endpoint returned exactly what its documentation said it would, first try, no follow-up needed. The point isn't that government data is universally unreliable. It's that reliability isn't something you can tell from outside -- BLM, EIA, and FRED are all "official," all documented, all technically public, and they failed (or didn't) in three completely different, undocumented ways. Nothing about the word "official" predicts which.

Even once a source is trustworthy field-by-field, the sources still don't agree on how to say "where." BLM ships raw lat/long polygons and no county at all -- assigning a claim to a county means running our own spatial join, the centroid of each claim's polygon tested against county boundary geometries loaded separately. Census and BLS data comes keyed to county and state FIPS codes. FRED comes keyed to two-letter state abbreviations. None of the three has ever heard of a "metro" -- DMV, Silicon Forest, the Research Triangle -- because a metro isn't a Census or BLS or BLM concept at all, it's ours, a boundary we draw for our own reasons and have to resolve every incoming record into, regardless of which of the three geographic vocabularies it happened to arrive in.

None of the three problems above were about the data being wrong. BLM's field really is null, EIA's rows really do represent five real sectors, and both are accurate. They just aren't usable in that form -- and nothing about "published by a federal agency" tells you which fields to trust as consumable versus which ones need a second pass before anything can be built on them -- pulling raw counts, checking them against something independently known to be true, and going back to the source directly when a number looked implausible rather than trusting that it must be right because it came from an official service. That's not really a data-engineering skill. It's closer to the instinct an investigative reporter uses on a source that's technically on the record but hasn't necessarily told the whole story: the document is real, the agency is real, and it can still be wrong in ways nothing about its format warns you about.