Job Market Intelligence Engine

The problem

If you need a visa to take a job, one question decides whether a posting is worth reading: is this employer legally allowed to sponsor me? Job boards almost never say. The usual answer in side projects is to ask an LLM to read the posting and guess — which produces a confident, unverifiable label, and quietly hallucinates.

So I built the signal the other way round. Every posting's company is cross-referenced against the IND register of recognised sponsors — the ~12,800 Dutch employers legally permitted to sponsor a highly-skilled-migrant visa. The register is scraped into a dbt seed, and both sides of the join are put through the same company-name normalisation macro. Every match carries that company's KvK (Chamber of Commerce) number, so any flag on the site can be checked against a public register by hand. Hallucination isn't mitigated here — it's structurally impossible.

The reason this matters is visible in the data. On remote-first boards, only ~1% of companies appear in the register. On the Netherlands-local corpus (Adzuna NL) it is ~34%. A relocation search filtered on the deterministic flag surfaces a completely different — and actually actionable — set of jobs, and it works for postings whose text never mentions visas at all.

Architecture

Key decisions & why

• A public register as the primary signal; the LLM as the secondary one. The deterministic IND join is the load-bearing feature: it is reproducible, testable, and auditable by KvK number. The LLM reads the posting text into a visa enum with a confidence score and verbatim supporting evidence — useful where the company isn't in the register, but it never overrides a register match. Ranking the weaker signal below the stronger one is the whole design.
• One normalisation macro, applied to both sides of the join. ASML Netherlands B.V. and Adyen N.V. have to collapse to asml and adyen — strip accents, drop legal forms and noise tokens, collapse whitespace — and the register and the postings must be normalised identically or the join silently under-matches. One dbt macro (mirrored by the scraper in Python) means there is exactly one definition of "same company", version-controlled and covered by grain tests rather than trusted.
• Evidence travels with the flag. Every sponsorship flag carries its KvK number and, for the LLM path, the verbatim sentence it was derived from. A claim a recruiter can't verify is worth nothing on a portfolio, so the schema forces the provenance to survive all the way to the app.
• Text-to-SQL over the gold layer, not the raw data. The "Ask the Data" agent queries curated marts, so natural-language questions hit clean, well-named columns. It's guard-railed to SELECT-only, single-statement, forced-LIMIT, read-only — not hardened against a hostile user, and this case study says so.

-- deterministic IND recognised-sponsor cross-reference:
-- the company is legally authorised to sponsor a NL work visa
    (s.company_norm is not null)  as is_recognised_sponsor,
    s.kvk_number                  as sponsor_kvk
from postings p
left join sponsors s
    on {{ jmi_normalize_company('p.company_name') }} = s.company_norm

Supporting detail: the warehouse

• DuckDB + MotherDuck instead of a cloud warehouse — the dataset is medium-sized and the priority was €0 running cost. The same dbt project runs against local DuckDB and MotherDuck's free tier, so dev and prod stay identical. A pragmatic call for this scale, not a claim about production architecture.
• dbt medallion (staging → intermediate → marts) — raw API payloads are messy and overlap across sources. Isolating cleaning (stg), cross-source dedup (int) and business marts (facts/dims) makes each layer testable and the lineage obvious.
• A pluggable LLM provider — the enrichment flow abstracts the model behind one interface (Ollama / Gemini / Claude), which keeps the pipeline free by default and swappable when quality matters more than cost.

Results

A reproducible pipeline: uv for envs, ruff/mypy/pytest in CI, and dbt tests as data contracts — so a broken source fails loudly instead of silently corrupting the app. Model and test counts are read from the project's own manifest.json; the sponsor-register size is the row count of the scraped seed.

Honest status

As of July 2026: the IND cross-reference, the Pydantic contracts and the dbt medallion are production-grade. Ingestion breadth is a demo — 4 keyless boards plus Adzuna and JobTech, a fraction of the real market, since LinkedIn and Indeed sit behind paid anti-bot. LLM enrichment is quota-bound on Gemini's free tier, so coverage accumulates over daily runs rather than landing all at once. Prefect flows are instrumented and reporting, but the worker-based deployment is documented rather than running, because that would not be €0.

What I'd do differently now