A Daily Pipeline Over 5,000 Job Boards

I wanted to know how long job postings stay open. No job board publishes that. The boards only show what is posted right now, so this pipeline collects every board every day and works it out from what disappears.

The result is live at jobs.willcharles.dev: a dashboard, and a search over every open posting in the warehouse.

What it collects

Four sources, loaded once a day by a GitHub Actions cron job. The target counts below are from the first full load of all four, which began on 3 September 2026:

The first full load across all four finished in 21 minutes 42 seconds. 5,056 of the 5,088 targets succeeded and returned 175,341 source rows. Those numbers drift every day as boards are added and postings change.

Architecture

Greenhouse + Ashby + Lever + USAJobs
            |
   Python loaders, pydantic contracts      (daily GitHub Actions cron)
            |
   raw tables: untouched JSONB             upsert on natural keys
   + one row per target per run            (run health)
            |  dbt
   staging views, one per source
            |
   int_jobs__conformed                     one contract for all sources
     |-- SCD2 snapshot                     daily board state -> lifecycle
     |-- star schema                       fct_job_postings, dim_company,
     |                                     dim_location, dim_skill
     |-- bridge_job_skill                  skill mentions from seed lists
     '-- presentation marts                trends, skills, salary, search
            |
   Evidence.dev static site + dbt docs     rebuilt and deployed daily

The warehouse is Postgres: Docker locally, Neon in production. Credentials only exist as GitHub Actions secrets.

The daily run

One workflow does the whole day: ingest all four sources, archive and purge aged raw rows, build and test the dbt models, check source freshness, generate dbt docs, rebuild the dashboard, deploy it, and then request the published pages to make sure they actually serve.

• Rerunning is safe. Raw tables store the untouched JSON and upsert with ON CONFLICT on each posting’s natural key. Running the same day twice does not double count, and a unique test on posting_key would fail the build if it did.
• Bad records fail at the edge. Every record goes through a pydantic model before it is written. Malformed data raises an error instead of landing in the warehouse.
• One broken board does not sink the run. Each target’s outcome is written to raw.ingestion_runs, and a failure there is logged and skipped. Source freshness warns if a source has not loaded in 26 hours.
• Raw is never transformed. Every transformation is in dbt and can be replayed from raw.

How a posting closes

A dbt snapshot (check strategy, hard_deletes: invalidate) keeps one version of each posting per change. When a posting drops off its board, its open version is closed. That gives first_seen, last_seen and days_open for every private-sector posting.

Two things had to be handled for that to be true:

1. A failed load looks like a mass closure. If a company’s board times out, every one of its postings is missing from that day’s data. The board-current models filter each company to its own latest successful load, so one failed request cannot close hundreds of postings.
2. First seen is not first posted. When a board is added, everything already on it gets first_seen equal to that day, whether it went up that morning or a year ago. fct_posting_lifecycle.has_true_start marks the postings the pipeline actually watched appear, and only those count toward durations. A job really posted on the day tracking started gets thrown out with the older ones. I chose to lose those rather than guess.

USAJobs is a paginated search API, so a posting missing from a page does not mean it closed. Federal postings show “not tracked” rather than a made-up duration.

Tests and CI

• Over 100 dbt data tests, 24 of them custom SQL assertions. Examples: exactly one open snapshot version per posting, every posting in search is actually open, open postings belong to a board still on the roster, and no posting date is in the future.
• 89 Python unit tests for ingestion and board discovery.
• CI runs on every push and pull request with deterministic fixture data. It never calls a live API or touches production. The schema in CI is created by the loader’s own ensure_tables(), so CI tests the same code path production uses.

Things that broke

A key that only failed silently. posting_key was hashed from (company, job_id) in the bridge and lifecycle tables but (source, company, job_id) in the fact table. Every SQL join I had written used the right columns, so no number was ever wrong. I found it while building a Power BI model on the same star schema, where a relationship on that column would have matched zero rows and shown blank visuals with no error. The keys are conformed now and relationships tests fail the build if they drift.

A hiring boom that was me adding boards. The first version of the hiring trend chart climbed from 57 to 158 new postings per hour. Three things were wrong, and all three were about measurement. The date label was a day ahead, because the run lands overnight US time and covers the previous working day. The windows were not 24 hours, because the gap between runs ranged from about 13 to 36 hours. The set of boards kept growing, and adding a few hundred boards doubles the count of openings without a single new job existing. The chart now uses a fixed cohort of boards, divides by hours actually watched, and leaves out days the collector did not fully cover. Measured that way the market is flat.

Removed boards never closed. Taking a board off the roster left its postings open forever. 238 postings from five boards dropped on 5 August were still in search seven weeks later. The board-current models now keep only rostered boards, and a test fails if any open posting belongs to a board that is no longer tracked.

Search without a server

The search page has no backend. At deploy time the search mart is written to a parquet file of about 1.7 MB, and Evidence ships DuckDB-WASM, so every filter is SQL running in the visitor’s browser. There is no API to rate limit, no credentials to leak, and nothing to pay for. I costed a serverless function over Neon and dropped it: it would give fresher results than a once-a-day pipeline can produce.

For live questions in plain English, the same search mart also backs the Job Search Agent.

Decisions I would defend

• Skills come from a curated list, not NLP. 105 skills and 143 aliases as dbt seeds. Ambiguous words like Go, R and Spring are left out. It undercounts on purpose, and the dashboard says the numbers are mentions, not requirements.
• Salary stats use federal postings only. Private postings rarely disclose salary, and hourly rates do not average into annual numbers. Both are counted, excluded and documented.
• Surrogate keys are deterministic md5() hashes. Facts and dimensions compute the same keys independently, with no lookup joins.
• The dashboard is static and rebuilt daily. The data changes once a day, so the site does too.

What is next

Orchestration is still a GitHub Actions cron. Moving to Dagster makes sense once the DAG is complex enough to need it. USAJobs has a native application close date that could be reconciled into the lifecycle model, and postings with several locations need a location bridge like the skills one.