Labor market data pipeline ingesting SOC, OEWS, O*NET, and BLS data into a four-layer DuckDB warehouse with interactive web exploration.
21 lessons
Before choosing a similarity algorithm, understand whether your data uses binary membership (item has feature or doesn't) or continuous scores (item has every feature at varying levels). Set-based metrics like Jaccard collapse to a constant when every item has every feature — the signal is in the sc...
Occupation codes are not stable identifiers across taxonomy revisions. The same SOC code can refer to different occupations in different versions, and naively comparing values across revisions produces misleading results. A crosswalk — an explicit mapping from old codes to new codes with cardinality...
Government data sources contain artifacts of their internal production processes — temp files in archives, renamed columns between releases, duplicate hierarchical rows, suppressed values that look like nulls but carry legal meaning, and CDN configurations that reject non-browser HTTP clients. Defen...
Base observations are source truth; derived values are computed artifacts. Mixing them in the same table creates ambiguity about whether a number is a measurement or a calculation. Separating them into distinct tables — with explicit derivation methods and base-metric linkage — makes the distinction...
When two projects share an author, the stronger design system should inform the weaker one — but adopting visual feel is a different task than adopting architecture. Port the tokens and typography; don't port the rendering pipeline.
A four-layer warehouse architecture (raw, staging, core, marts) with strict separation of concerns at each layer produces a system where raw data is always recoverable, business meaning is assigned in exactly one place, and analytical queries never need to understand source formats.
Federal data sources are not designed for programmatic access. They block bare HTTP requests, publish in heterogeneous formats, embed preamble rows in spreadsheets, and experience periodic outages around major releases. A robust extract layer must handle all of these realities with browser-like head...
A static site can replicate a dynamic API by intercepting JavaScript `fetch()` calls and redirecting them to pre-built JSON files. The key technique is a monkey-patch of the global `fetch` function that routes API URLs to static file paths, with client-side filtering for search and client-side compo...
Geographic wage comparisons are inherently incomplete: nominal gaps do not account for cost-of-living differences, suppressed cells create invisible holes in small-occupation maps, and the same query pattern must work across national, state, and metro levels without separate code paths. A dimension-...
Data pipelines fail — downloads timeout, parsers hit unexpected formats, database connections drop. Idempotency (running the same operation twice produces the same result as running it once) must be designed into every layer: delete-before-insert for facts, check-before-insert for dimensions, and gr...
Comparing nominal wages across years is misleading because the dollar's purchasing power changes over time. Converting to constant dollars using CPI-U deflation separates genuine labor market shifts from background price-level changes and is essential for any multi-vintage wage trend analysis.
Once a warehouse holds multiple vintages of the same dataset, every query must explicitly decide whether it wants the latest snapshot or all history. Forgetting this decision produces silent data quality bugs — duplicate rows, empty columns, or misleading percentages — that look correct at the SQL l...
Percentage changes on small bases are statistically volatile and can dominate ranked lists even when the absolute economic impact is trivial. Any ranked-change display must show both percentage and absolute values so users can distinguish genuine labor market shifts from small-sample noise.
Government data sources change column names, add or remove columns, and retype columns between releases — often without notice. A pipeline that assumes a fixed schema will silently break or load garbage. Proactive drift detection at the staging boundary turns silent corruption into a loud, actionabl...
A server-side web application can be deployed to a static hosting platform by pre-rendering every page and API response as files, then injecting a JavaScript fetch shim that transparently redirects API calls to the corresponding JSON files. The application's JavaScript never knows it's running on a...
Separating tests by their infrastructure requirements — fixtures-only, in-memory server, real database — lets CI run fast on every push while reserving expensive real-data validation for local runs. The deployment pipeline then layers lint, format, test, build, and deploy into a strict sequence wher...
When building an analytical warehouse from multiple federal data products, the single most important architectural decision is identifying the stable external key that connects them. For labor data, that key is the Standard Occupational Classification (SOC) code — every design decision flows from tr...
When loading multiple vintages of the same dataset, dimension tables must deduplicate on business key alone — not on business key plus source release. Including the source release in dimension lookups gives the same real-world entity different surrogate keys in each vintage, making cross-vintage joi...
When a web framework dispatches synchronous endpoint handlers to a thread pool, a shared database connection will produce intermittent wrong results — not errors, but silently incorrect data. The fix is per-thread connections via `threading.local()`, with a global override path for test injection.
Fact tables store snapshots — single measurements at single points in time. Time-series analysis requires a separate normalization step that aligns snapshots across periods into a conformed schema with explicit metric definitions, and a further separation between base observations and derived series...
A web application that shows buttons, links, or filters for data that does not exist creates a worse experience than one that simply omits them. Every UI element that implies data availability must be backed by a runtime or build-time check that the data actually exists.