跳到正文
DEV Community· David Buchan·· 12 小時前AI 評分49

我讓 Claude 查詢 16,739 則招聘職位資料,並以 SQL 作答

I pointed Claude at 16,739 job postings and it answers with SQL

AI 導讀

作者建立 job-market-qa,讓 Claude 把自然語言問題轉成 SQL,透過 DuckDB 查詢招聘職位資料並回傳數字或小型資料表。一次查詢使用來自 76 個來源的 16,739 則職位資料,得出初級遠端職位有 32 個、遠端職位佔 15.35%。查詢會經過安全規則檢查;需要 API key 的模型查詢不可用時,工具會以模式引擎回答能識別的問題。

正文

My job discovery pipeline sweeps roughly 17,000 postings a day. A dashboard shows the shapes, but sometimes I want one specific answer: how many junior remote roles are up today, which companies are hiring data engineers, what the remote share actually is. So I built a small layer that answers questions in plain English over that data: job-market-qa.

The chain is short. The question goes to Claude along with the table schema and a few rules. The model replies with SQL. The SQL passes a guard. DuckDB runs it. The answer comes back as a number or a small table.

The guard is the product

A text-to-SQL demo is easy. One you would show a data team is mostly guard. Every query, whether written by a model or typed by me, has to be:

  • one statement, no semicolons
  • a SELECT or a CTE, nothing else
  • free of file functions and anything that writes
  • capped at 200 rows at fetch time, so an unbounded query never materialises the whole table

Validate the model's output, then trust it. If the SQL breaks a rule, the exact guard error goes back to the model once and it retries. That is the same pattern as my llm-contract library: define the contract, fail on violation, retry once with the exact error.

What it answered this morning

Against the snapshot I froze today, 16,739 postings from 76 sources:

  • how many junior remote roles are up: 32
  • what share of postings are remote: 15.35%
  • which companies are hiring the most data engineers: Anthropic, Airwallex, Riot Games and openai, five each
  • which companies are hiring junior data or analytics people in Germany: 17 rows, from Celonis and Picnic down to consultancies I had never heard of

That last answer is why the tool exists. The number of junior-flavoured data roles sitting in Germany on a random Tuesday is a fact I could not have gotten from scrolling, and it reframed where I aim my own applications.

A fallback that admits what it is

Without an API key the tool still answers the common shapes: counts with filters for junior, remote, role, source, country and timeframe, plus top companies, top sources, remote share and per-day trends. When a question falls outside that, it says so instead of guessing. Two engines, one guard, and the honest failure mode is "I did not understand that" rather than a hallucinated number.

The tests caught me three times

Twenty-eight tests cover the guard, the pattern engine and the loader against a six-row fixture. No network, no API calls.

  1. My first guard checked read_csv with a word boundary, which read_csv_auto walks straight through because the underscore counts as a word character. Forbidding it as a substring closed the hole.
  2. I first appended a LIMIT to every query. That caps the result inside the database, so the fetch can never see row 201 and the truncation flag never fires. The cap moved to fetch time.
  3. DuckDB sniffed the fixture's posted column as a DATE, so the empty-string handling died on a trim of a date. The loader now forces every column to VARCHAR and casts explicitly.

All three were caught by the tests before the first real demo. That is the tests doing their job, and it is also the argument for testing the guard rather than the happy path: the happy path never broke.

Known limits

The posting date covers about 85% of rows, and date questions filter the NULLs out rather than inventing dates. Greenhouse reports a posting's updated date rather than its first-published one, so a few dormant postings carry old dates. Junior detection is a keyword rule over titles, so phrasing like "early careers" is missed. The LLM path needs an API key; without it the pattern engine answers what it recognises and nothing else.

The data ships alongside the tool. job-market-daily on Hugging Face accumulates every daily sweep with first and last seen dates per posting URL, and the live dashboard over the same data is here.

Repo: https://github.com/Reaver1000/job-market-qa

來源:DEV Community · dev.to