如何在 DuckDB 与 dbt 流水线中验证 LLM 输出

我最近搭建了一个让我引以为豪的流水线,它解决了我实际面临的一个问题:如何追踪我正在进入的行业——数据工程——中发布的职位?我勉强算是解决了这个问题。我有一个流水线,从两个来源提取数据:Hacker News(让我了解该行业发布的文章)和 RemoteOk(追踪该行业发布的相关远程职位)。
下面是该流水线的详细架构图。
但还缺少一些东西。这条流水线只能告诉我某个职位存在,以及它何时开放和关闭。我无法知道这个职位具体在要求什么。
当我完成我的就业市场流水线时,我对它很满意。它从 RemoteOK 拉取远程数据职位,加载到 DuckDB,用 dbt 进行清洗,并使用快照来记录某个职位列表何时开放和关闭。但我希望这条流水线能更丰富一些。
于是我决定加入 AI,但有一个原则:它必须在流水线中真正承担一项工作。
在本文中,我将分享我如何用 AI 让就业市场流水线更加丰富的完整过程。我会展示代码、在真实职位上的首次运行、我不得不停下来检查模型工作的那些时刻,以及一次数据才是真正罪魁祸首的经历。
我选择把 AI 作为流水线中的一个阶段,放在加载职位和测试职位之间,而不是在顶层放一个通用的聊天机器人。
正如你从下面的架构中看到的:
这里还有一个额外的 AI 信息补全步骤。它的目标是帮我更好地理解哪些职位真正与我相关——无论是从我的技能组合、资历级别还是所在地区来看。
对于每个职位,我都会要求提取五项信息:
skills:职位描述中明确提到的工具和技术,以小写短名称列表的形式存储。
seniority:该职位期望的经验级别。可能的取值为 intern、junior、mid、senior、lead 或 unknown。
years_experience_min:要求的最低工作年限。以数字形式存储,若未明确要求则留空。
open_to_nigeria:表示身处尼日利亚的人是否有资格申请。可能的取值为 yes、no 或 unclear。
summary:用一句通俗易懂的话描述该职位的职责内容。
尼日利亚这一字段是我最关心的。我正在拉各斯找工作,而“远程”往往意味着“远程,但仅限于某个国家”。一张能为每个在招职位回答这个问题的表格,让我不必再逐个阅读职位信息去确认。
模型方面,我用的是 Google 免费额度里的 Gemini Flash-Lite 模型。我之前已经被账单门槛坑过一次:Google Cloud 要求先预付 30 美元,我才能在尼日利亚使用 BigQuery,这也是整个项目跑在 DuckDB 上的原因。一个不需要绑卡就能用的免费 API key 对我来说很重要。脚本用 Python 标准库直接与 API 通信,所以不需要额外安装任何东西。
构建信息补全脚本
这个脚本就是一个文件 enrich_jobs.py,我通过四个小决策把它搭了起来。
保留职位描述文本。最初我的 RemoteOK 提取器把 description 丢掉了,因为我只需要职位名称和公司名。模型读不到它从未收到的东西,所以第一个改动是在要保留的字段列表里加一行,并在加载器里对应加一行:
FIELDS_TO_KEEP = [ "id", "position", "company", "tags", "location", "salary_min", "salary_max", "url", "date", "description", # 完整的职位描述文本;AI 步骤会读取这里]
要求固定的输出结构。这个 API 允许你描述希望返回的确切 JSON,包括允许哪些取值。任何超出允许取值范围的答案都是坏答案。
RESPONSE_SCHEMA = { "type": "OBJECT", "properties": { "skills": {"type": "ARRAY", "items": {"type": "STRING"}}, "seniority": {"type": "STRING", "enum": SENIORITY}, "years_experience_min": {"type": "INTEGER", "nullable": True}, "open_to_nigeria": {"type": "STRING", "enum": NIGERIA}, "summary": {"type": "STRING"}, },}
提示词还要求模型在职位描述没有说明时使用 “unknown” 或 “unclear”,而不是去猜。它还告诉模型,职位描述属于不可信文本,绝不应该遵循其中写下的指令。招聘帖是陌生人写的,我不希望一条写着 “ignore your instructions” 的帖子能起任何作用。
绝不要读取同一个职位两次。enrichment 表与 jobs 表分开,而且它永远不会被替换。每次运行只挑选还没有答案的职位:
SELECT r.id, r.position, r.company, r.descriptionFROM raw_remoteok_jobs rLEFT JOIN raw_job_enrichment e ON e.job_id = r.idWHERE e.job_id IS NULL AND r.description IS NOT NULL
这保护了免费配额。这也意味着,enrichment 表会保留那些后来已关闭职位的答案,而这一点在我之后编写 dbt 测试时很重要。
在保存每个答案之前都要检查它。schema 告诉模型我想要什么结构。它并不能让模型遵守,我也不希望我的数据仓库依赖于模型表现良好。因此,每个答案都会经过一个校验函数,不合格的答案会被跳过并上报,绝不会被保存:
seniority = answer.get("seniority")if seniority not in SENIORITY: raise ValueError(f"seniority '{seniority}' is not one of {SENIORITY}")
剩下的都是我如今在每个项目里都会写的管道代码。每个请求在遇到速率限制和网络错误时会重试,且尝试之间的暂停时间会逐渐增加。错误的模型名称或耗尽的配额会在一次请求失败后停止整个运行,因为它对每个职位都会失败。而且每个职位在得到答案的那一刻就会被保存,所以即使中途崩溃,已完成的工作也会保留下来。
第一次运行,以及那个我并不信任的“yes”
我先在三个职位上运行它,以确保免费配额安全,并看看会返回什么。
[1/3] AlgaeCal 助理数据科学家,初级,4 项技能,尼日利亚:不确定[2/3] SEAhub Asia 数据分析师,初级,5 项技能,尼日利亚:不确定[3/3] Arabian Private Holdings 数据分析助理,初级,0 项技能,尼日利亚:是
第三行让我心里犯嘀咕。一条简短的招聘信息里写着 0 项技能,倒也说得过去,但一家名叫 Arabian Private Holdings 的公司对尼日利亚直接给出一个干脆的“是”,看上去像是猜的。
为了核实,我写了一个小小的检查模式,把模型的答案和真实的招聘原文并排打印出来,这样我就能自己判断。随后我在 RemoteOK 网站上查了这个职位。它的工作地点是迪拜,是用阿拉伯语写的。
这时我发现了自己代码里的 bug。模型从来没见过地点信息。我的脚本只发送了职位名称、公司和招聘正文,别的什么都没发。唯一说明雇主希望员工住在哪里的那个字段,就一直躺在我的数据库里,从未被使用。所以,我判为“不确定”的两个职位和判为“是”的一个职位,其实是模型在掌握的信息比我还少的情况下给出的答案。
把地点也发过去。对模型看到的每条职位信息来说,这只是个小补充,而且模型能顺利读阿拉伯语,这是关键词过滤做不到的。
让模型给出它的证据。凡是对尼日利亚给出“是”或“否”的判断,模型都必须引用支持该判断的原话。然后脚本会检查这段引用是否真的出现在地点字段或招聘正文里,如果没有,就丢弃这个答案:
if nigeria in ("yes", "no"): if normalise(evidence) in ("", "none", "n/a"): raise ValueError(f"said '{nigeria}' for Nigeria but gave no evidence") if normalise(evidence).strip(".\"' ") not in normalise(source_text): raise ValueError(f"evidence quote not found in the posting: '{evidence[:80]}'")
这项检查是刻意从严的。如果模型只是改写而不是原样复制,正确答案就会被跳过。我宁愿重跑一个被跳过的任务,也不愿存下一个无法追溯的答案。
我用 --redo 标志清除了旧答案,把同样的三个任务又跑了一遍。AlgaeCal 和 SEAhub Asia 都从“unclear”变成了“no”。既然模型现在能看到它们的招聘地点,这正是我预期的结果。
Arabian Private Holdings 这个任务仍然是“yes”,而这里正是我判断错的地方。从我读到的那部分招聘信息来看,没有任何内容说尼日利亚是可以的,所以我以为模型还在猜。然后我看到了它给出的证据行:
> 这种远程模式让我们可以根据个人的智力能力而非其实际所在地来招聘人员
那句话出自我尚未读到的那部分招聘信息。招聘信息明确说地点无关紧要,尽管职位列表上写的是迪拜。模型选择了更明确的信号,如果我没有要求它展示推理过程,我本会把这当成一个错误。这条引文把一种模糊的担忧变成了一次十秒钟的核查,还给了我一个以后可以在 dbt 里测试的字段。
在完整运行中,18 个任务里有 17 个干净地保存了下来。第 18 个被跳过了,而这一次并不是模型的错。
被跳过的任务,以及本来就已经损坏的文本
这就是那个被跳过的任务在输出里的样子:
[14/18] Haystack 的数据分析师已跳过:在招聘信息中未找到证据引文:“Wien, Wien, Wien, Österreich”
原文较长,译文为节选;译文由英文原文机器翻译,术语按行业惯例统一,可在文末展开原文核对。
查看英文原文(How to Validate LLM Output in a DuckDB and dbt Pipeline)
I built a pipeline I was proud of recently, and it solved a problem I was actually facing. How do I keep track of jobs posted in the industry I'm breaking into? - data engineering. I managed to solve that problem to a point. I had a pipeline that extracts data from two sources, Hacker News (to keep myself updated on articles posted in the industry) and RemoteOk (to keep track of relevant remote jobs posted in the industry).
Here was the detailed architectural diagram of what the pipeline looks like

But something was missing. The pipeline could only tell me a job existed and when it opened and close. I couldn't tell what the job was asking for.
When I finished my job market pipeline, I was happy with it. It pulled remote data jobs from RemoteOK, loaded them into DuckDB, cleaned them with dbt, and used a snapshot to remember when a listing opened and closed. I wanted the pipeline to be richer than that.
So I decided to add AI, with one rule though: it has to do a real job inside the pipeline.
In the article, I'll share the entire process on how I made my job market pipeline more enriched with AI. I'll show the code, the first run on real postings, the moments where I had to stop and check the model's work, and one moment where the data was the real culprit.
I chose to put the AI inside the pipeline as one stage, between loading the jobs and testing them, instead of putting a generic chatbot on top.
As you can see from the architecture below:
There's an extra AI enrichment step. It's goal is to help me understand better which jobs are actually relevant to me - whether that's my skillset, level of seniority and location.
For each job I ask for five things:
skills: The tools and technologies explicitly mentioned in the job description, stored as a list of short, lowercase names.
seniority: The expected experience level of the role. Possible values are intern, junior, mid, senior, lead, or unknown.
years_experience_min: The minimum number of years of experience required. This is stored as a number or left empty if no requirement is specified.
open_to_nigeria: Indicates whether someone based in Nigeria is eligible to apply. Possible values are yes, no, or unclear.
summary: A single, plain-language sentence describing what the role involves.
The Nigeria field is the one I care about most. I'm job hunting from Lagos, and "remote" often turns out to mean "remote, but only from one country." A table that answers that for every open job saves me from reading each posting to find out.
For the model I used a Gemini Flash-Lite model on Google's free tier. I'd already been burned by a billing wall once. Google Cloud wanted a $30 prepayment before I could use BigQuery from Nigeria, which is why this whole project runs on DuckDB. A free API key that needs no card mattered a lot. The script talks to the API with Python's standard library, so there's nothing extra to install.
Building the enrichment script
The script is one file, enrich_jobs.py, and I built it in four small decisions.
Keep the posting text. Initially, my RemoteOK extractor had been throwing away the description, because I only needed titles and companies. A model can't read what it never received, so the first change was one line in the list of fields to keep, and one matching line in the loader:
FIELDS_TO_KEEP = [ "id", "position", "company", "tags", "location", "salary_min", "salary_max", "url", "date", "description", # the full posting text; the AI step reads this]
Ask for a fixed shape. The API lets you describe the exact JSON you want back, including which values are allowed. Anything outside the allowed values is a bad answer.
RESPONSE_SCHEMA = { "type": "OBJECT", "properties": { "skills": {"type": "ARRAY", "items": {"type": "STRING"}}, "seniority": {"type": "STRING", "enum": SENIORITY}, "years_experience_min": {"type": "INTEGER", "nullable": True}, "open_to_nigeria": {"type": "STRING", "enum": NIGERIA}, "summary": {"type": "STRING"}, },}
The prompt also tells the model to use "unknown" or "unclear" when a posting doesn't say, instead of guessing. It tells the model that the posting is untrusted text and that it should never follow instructions written inside it. A job post is written by a stranger, and I didn't want a posting that says "ignore your instructions" to have any power.
Never read the same job twice. The enrichment table is separate from the jobs table, and it is never replaced. Each run only picks up jobs that have no answer yet:
SELECT r.id, r.position, r.company, r.descriptionFROM raw_remoteok_jobs rLEFT JOIN raw_job_enrichment e ON e.job_id = r.idWHERE e.job_id IS NULL AND r.description IS NOT NULL
That protects the free quota. It also means the enrichment table keeps its answers for jobs that have since closed, which matters later when I write the dbt tests.
Check every answer before saving it. The schema tells the model what shape I want. It doesn't make the model obey, and I didn't want my warehouse to depend on good behavior. So every answer goes through a validation function, and a bad one is skipped and reported, never saved:
seniority = answer.get("seniority")if seniority not in SENIORITY: raise ValueError(f"seniority '{seniority}' is not one of {SENIORITY}")
The rest is plumbing I've now written in every project. Each request retries on rate limits and network errors, with growing pauses between attempts. A wrong model name or an exhausted quota stops the whole run after one failed request, because it would fail for every job. And each job is saved the moment it's answered, so a crash halfway through keeps the work already done.
The first run, and the "yes" I didn't trust
I ran it on three jobs first, to keep the free quota safe and to see what came back.
[1/3] Associate Data Scientist at AlgaeCal junior, 4 skills, nigeria: unclear[2/3] Data Analyst at SEAhub Asia junior, 5 skills, nigeria: unclear[3/3] Data Analyst Assistant at Arabian Private Holdings junior, 0 skills, nigeria: yes
The third line bothered me. Zero skills is believable for a short posting, but a flat "yes" for Nigeria from a company called Arabian Private Holdings looked like a guess.
To check, I wrote a small check mode that prints the model's answer next to the real posting text, so I can judge it myself. Then I looked the job up on the RemoteOK website. Its location was Dubai, written in Arabic.
That's when I found my own bug. The model had never seen the location. My script sent the job title, the company and the posting text, and nothing else. The one field that says where the employer wants people to live was sitting in my database, unused. So two jobs I'd called "unclear" and one "yes" were answers from a model working with less information than I had.
Send the location too. It's a small addition to what the model sees for each job, and the model reads Arabic fine, which a keyword filter wouldn't have.
Make the model show its evidence. For any "yes" or "no" about Nigeria, the model has to quote the exact words that support it. Then the script checks that the quote really appears in the location field or the posting, and throws the answer away if it doesn't:
if nigeria in ("yes", "no"): if normalise(evidence) in ("", "none", "n/a"): raise ValueError(f"said '{nigeria}' for Nigeria but gave no evidence") if normalise(evidence).strip(".\"' ") not in normalise(source_text): raise ValueError(f"evidence quote not found in the posting: '{evidence[:80]}'")
This check is deliberately strict. If the model paraphrases instead of copying, a correct answer gets skipped. I'd rather re-run a skipped job than store an answer I can't trace.
I cleared the old answers with a --redo flag and ran the same three jobs again. AlgaeCal and SEAhub Asia both changed from "unclear" to "no." That's what I'd expect now that the model could see where they hire.
The Arabian Private Holdings job stayed "yes," and this is the part I got wrong. From the first part of the posting I'd read, nothing said Nigeria was fine, so I assumed the model was still guessing. Then I saw its evidence line:
> This remote model allows us to hire individuals based on their intellectual capability rather than their physical location
That sentence was in the part of the posting I hadn't read. The posting explicitly says location doesn't matter, even though the listing says Dubai. The model chose the more explicit signal, and I'd have called that a mistake if I hadn't asked it to show its work. The quote turned a vague worry into a ten-second check, and it also gave me a field I can test in dbt later.
On the full run, 17 of the 18 jobs saved cleanly. The 18th was skipped, and that one wasn't the model's fault.
The skipped job, and the text that was already broken
This is what the skipped job looked like in the output:
[14/18] Data Analyst at Haystack skipped: evidence quote not found in the posting: 'Wien, Wien, Wien, Österreich'
The Ö caught my attention. It should have been Ö, as in Österreich (Austria). Somewhere along the way, the text had been read using the wrong character encoding. UTF-8 bytes had been interpreted as an older Western encoding, leaving behind garbled characters. This is known as mojibake.
At first, I considered whether the model had somehow corrupted the quote. But there was another, more likely explanation: the location stored in my database was already garbled. If that was the case, the model was simply quoting what it had been given, and the quote would fail to match the original text.
I checked the stored location:
SELECT id, LENGTH(location) AS lenFROM raw_remoteok_jobsWHERE company = 'Haystack';
A clean Wien, Wien, Wien, Österreich is 28 characters long. The value in my database was 29. That extra character was another clue that something had gone wrong with the encoding.
I couldn't establish exactly where the corruption happened. My code reads the response as UTF-8 and writes the text to the database without modifying it. That makes me suspect the problem originated in RemoteOK's data, but I can't say that with certainty.
Regardless of where it started, I wanted to handle it as early as possible in the pipeline. The fix belongs at the point where the data enters the system.
I added a function to reverse the encoding mistake. It converts the garbled characters back into their original bytes, then decodes those bytes as UTF-8. If the conversion fails, it leaves the original text untouched:
def repair_text(value): if isinstance(value, list): return [repair_text(item) for item in value] if not isinstance(value, str) or value.isascii(): return value try: raw = bytearray() for ch in value: try: raw += ch.encode("cp1252") except UnicodeEncodeError: raw += ch.encode("latin-1") fixed = bytes(raw).decode("utf-8") except (UnicodeEncodeError, UnicodeDecodeError): return value return fixed
The part about leaving the text alone is important. A clean string like Österreich won't pass the conversion, so it stays unchanged. The same goes for Arabic text, which can't be represented in those older encodings. The function only repairs text that looks like it has suffered this particular encoding mistake.
Before using it on the actual data, I tested it with a few examples: clean text, garbled text and Arabic text. I wanted to make sure it corrected the problem without introducing new ones.
I also made the extractor log a message whenever it repairs a string. That way, the correction isn't happening silently. After applying the fix and running the pipeline again, the previously skipped job went through.
There was one side effect worth mentioning. My dbt snapshot tracks changes to the location column, so the repaired values showed up as changes and were captured as a new version. That was expected.
This wasn't an AI problem. It was a data quality issue that the AI happened to expose. The strict quote check made the mismatch visible instead of letting it pass unnoticed.
The script already checks every answer before saving it, but I still wanted dbt to check the table afterward, for the same reason I put locks on more than one door. If someone changes the script, loads rows another way, or edits the table by hand, the warehouse should still refuse bad data.
So the AI's table got the same treatment as every other source. A staging model gives it a clean name. Then two new tables sit on top of it:
job_postings_enriched has one row per job that's open right now, with the AI's findings attached. A job the AI hasn't read yet still appears, flagged as unread, so gaps are visible and not silently missing.
skill_demand counts how many open jobs ask for each skill. It splits each job's list of skills into one row per skill, then counts.
The validation checks cover five things:
Allowed values for seniority and Nigeria eligibility: Checks that the model uses only the categories I defined rather than inventing new ones. If it returns an invalid value, the pipeline fails.
Evidence for Nigeria eligibility: Checks that every yes or no answer about Nigeria has a supporting quote from the job posting. If there's no evidence, the pipeline fails.
Valid skill names: Checks that extracted skills look like actual tool or technology names, not empty values, capitalized phrases or entire sentences. If the check fails, the pipeline fails.
Garbled characters: Checks for broken character encoding in the model's output. If any are found, the pipeline raises a warning.
AI extraction coverage: Checks whether more than 20% of open jobs were left unread by the AI step. If that threshold is exceeded, the pipeline raises a warning.
Here's the evidence test in full. It follows dbt's usual pattern for custom tests: the query returns the bad rows, and zero rows means pass.
select job_id, open_to_nigeria, 'answered ' || open_to_nigeria || ' without evidence' as failure_reasonfrom {{ ref('stg_job_enrichment') }}where open_to_nigeria in ('yes', 'no') and ( nigeria_evidence is null or trim(nigeria_evidence) = '' or lower(trim(nigeria_evidence)) in ('none', 'n/a') )
Choosing between fail and warn was a real decision, and it's easy to skip. A made-up category means the data is wrong, so it fails. Garbled text and a drop in coverage mean something is suspicious, but the pipeline still produces useful output, so those warn and ask a person to look. The coverage test is my favorite. If my API key expired tomorrow, nothing would crash. The numbers would just quietly get worse, and this test is the thing that notices.
I left one test out on purpose. It's tempting to check that every AI answer belongs to a job that's currently open. But the enrichment table keeps answers for jobs that have since closed, so that test would fail for a good reason. A test that always cries wolf teaches you to ignore tests.
Once the tests were green, I finally got to ask the warehouse a question it couldn't answer before: what skills do these jobs ask for?
SQL leads, and the rest of the top of the list is the classic analyst toolkit: Power BI, Python, Excel and Tableau. That matches what I know about the market, which is a nice sign that the AI read the postings sensibly.
Two things stood out when I looked closer.
First, none of the 18 jobs had an engineering title. This feed is full of data analyst roles, and dbt shows up in just one posting. So if you take this chart as "what data engineers need," it's probably not the right chart. It's just what RemoteOK's data jobs ask for, and it's an honest picture of the data I fed in.
Second, my Nigeria question came back with one "yes" out of 18. Most of the rest were "no," and the one "yes" is the Arabian Private Holdings job from earlier, the posting that says it hires regardless of physical location. It also listed no tools at all. So the one job I could apply to from Lagos is the one the skills chart says nothing about. That's a limit of a small sample, and it's also a useful reminder that a clean chart can hide an awkward fact next to it.
What I'd do differently, and what's next
The lessons I'm taking from this are less about AI and more about habits I'd want in any pipeline.
1. Give the model all the information you have. Some of my earliest mistakes came from asking the model to make decisions with less information than I had. For example, I had the job's location but wasn't passing it to the model.
2. Ask the model to show its evidence, then verify it with code. A simple "yes" requires me to take the model's word for it. A supporting quote gives me something I can check against the original job posting.
3. Validate at two stages. The Python script checks the data before it reaches the database, while dbt runs another set of checks afterwards. I don't want to rely on just one layer of validation.
4. Be clear about what should stop the pipeline and what should raise a warning. Invalid data should stop the process. Suspicious data should raise a warning and, where necessary, be reviewed by a person.
5. When the model gets something wrong, don't assume the model is always the problem. The skipped job in my case wasn't an AI failure. The strict evidence check simply exposed an encoding problem that was already present in the source data.
If I were building this again, I'd also keep track of which prompt produced each set of answers. I used a --redo flag to clear the existing answers whenever I changed the prompt. It worked, but it also meant losing the results from previous versions. Looking back, keeping that history would have made it easier to compare prompts and understand how each change affected the output.
The bigger limitation right now is the size of the dataset. Eighteen job postings are a small sample, so I wouldn't draw conclusions about the wider job market from them. That will change with time. As the pipeline runs daily, the jobs table will grow, dbt will keep track of changes through snapshots, and the enrichment data will start to reveal more useful patterns around skill demand, new openings and closed positions.
That's what I want to tackle next. So far, I've been running everything manually, which is one reason the pipeline only has eighteen jobs in it. In the next article, I'll bring in Apache Airflow to orchestrate the entire workflow. The extractors, AI enrichment, and dbt runs, tests and snapshots will all execute in the right order, every day, without me having to trigger each step manually.
This is part of my ongoing series documenting my transition from systems analyst to data engineer. If you've been following along, thank you. If this is your first article in the series, the earlier ones are linked above.
Connect with me on LinkedIn, YouTube, and Twitter.
Large Language Models for Coding Tasks
Inside disaggregated LLM inference — the architecture shift behind 2-4x cost reduction that most ML teams haven't adopted yet.
April 15, 202614 min read
A physicist's approach to building production-grade agents
Learn how to build an application that might inspire your next road trip
September 26, 202315 min read
Playing Minesweeper with Augmented Reasoning
A cheaper and faster unified fine-tuning technique
April 19, 20247 min read
the definition, and building a team of agents that refine your CV and Cover Letter for job applications
July 3, 202414 min read
A strategic guide to build vs. buy for GenAI
January 7, 20256 min read
LLM case study: subject-verb agreement
February 19, 20255 min read
原文为英文,正文由自动抓取并保留原文。
原文发布于 Towards Data Science(How to Validate LLM Output in a DuckDB and dbt Pipeline),标题、摘要与正文由机器翻译,术语按行业惯例统一,未经人工逐句校订。