What building a real data pipeline with an AI coding agent actually looks like

This is part 3 of a three-part series built on the same dataset: all 28 EU and EEA countries’ worth of clinical trial data, pulled through CTIS’s undocumented public API. Part 1 covers what the data shows about Central Europe’s place in global pharma. Part 2 takes one finding from that data and asks what it means for how sponsors choose where to run a trial. This piece is about how the pipeline itself got built.

I’ve written before about evaluating AI agent platforms in the abstract: what they claim, what the demos show, what’s actually different between them. This piece is the opposite of that. It’s an account of handing one of them a genuinely messy, multi-day piece of real analytical work, the kind with no clean answer key, and watching what happened when I asked it to check its own earlier output.

The starting problem was ordinary enough: the EU publishes clinical trial data through CTIS, its trial register, with no supported public API. If you want the actual data behind a trial rather than what the search UI shows you, you’re reverse-engineering an undocumented JSON interface, working around whatever quirks the underlying system has, and building a pipeline that can survive being interrupted halfway through a multi-hour run. Not a toy example. Not a demo dataset. A real target with real, undocumented edge cases.

Building the thing

The first pass went the way these things usually do. The agent found the two request endpoints (one to search, one to retrieve full detail for a specific trial), built a SQLite schema to hold the result, and got a working extractor running against Hungary within an afternoon. Then it hit the first real wall: past roughly page 100 of results, the search endpoint stops returning anything at all, silently, regardless of how many records are actually left to page through. No error, no signal, just empty pages where data should be.

The fix that emerged wasn’t obvious from the API surface. Rather than one long paginated sweep, the agent restructured the search into twelve smaller sweeps, one per trial status code, each of which stays comfortably under the pagination ceiling on its own. It’s a workaround a community R package for the same underlying registries had already documented, which I only found out later, and which became relevant again in a different way once I asked what else might be wrong.

The turn: asking “are there more bugs?”

Once Hungary and Poland were both fully downloaded and I had a working analysis report in hand, I asked a second, more skeptical question: audit your own earlier work, and tell me honestly if anything’s broken. This wasn’t a rhetorical prompt. I wanted a real second pass, treated as seriously as the first one.

It found two things. One was cosmetic: a field meant to hold each trial’s parsed therapeutic area had actually been overwritten with an unrelated list of participating countries, a copy-paste-shaped bug from early in development that nobody had gone back to check. The other was worse. A field meant to record each trial’s funding source was reading from the wrong location in the API’s response structure, one level too shallow, and had been silently returning an empty list for every single trial in both databases. Not a crash, not a warning, just quietly discarded data across thousands of rows, the kind of failure that’s invisible unless someone specifically goes looking for it.

Both were fixed and both country databases re-retrieved from scratch before any analysis was run on them, which is the only reason I’m comfortable writing about the bugs in public: the interesting part isn’t that a pipeline had defects, it’s that one of them would have survived indefinitely without a second pass explicitly asked for.

That’s the useful distinction to sit with: an agent writing code and an agent critically checking its own code are two different, separately valuable steps, and the second one doesn’t happen automatically just because the first one worked. It has to be asked for, deliberately, as its own pass, not folded into “seems done” the moment something runs without an error.

The judgment call that mattered more than any bug

The more interesting moment, in hindsight, wasn’t a bug at all. Partway through the project I asked whether it made more sense to just download the entire EU register, all 28 countries, instead of the two I’d started with, on the theory that the overlap between countries would make the extra scope cheap. The honest answer, backed by an actual count rather than a guess, was no: roughly three-quarters of the full register’s trials touched neither Hungary nor Poland at all. The overlap I’d assumed would make the expansion nearly free simply wasn’t there.

I asked for the full register anyway, and the agent found a non-wasteful way to get it: merge the trials already downloaded rather than re-fetching them, and only pull the genuinely new ones, cutting a multi-hour job down by roughly a quarter before it even started. That sequence, a real pushback grounded in a real number, followed by a concrete way to make the decision cheaper once made, is a better demonstration of what useful AI-assisted work looks like than any polished demo I’ve seen. It isn’t generating plausible-looking output. It’s reasoning about a tradeoff correctly, saying so plainly, and then adapting once a decision is made.

What this actually means for adopting these tools

If you’re deciding how to bring agentic AI tools into a real organization, the lesson here isn’t “the tool is smart” or “the tool is fast,” though both were true often enough. It’s that the value compounds when you pair it with someone who knows the domain well enough to ask the second question: not “did it run,” but “did it run correctly,” not “give me an answer,” but “now check your own answer for what you missed.” That’s not a limitation of the tooling. It’s the actual shape of how this kind of work gets done well, and it’s the same judgment a good analyst or engineer brings to any project, human-built or otherwise.

That pairing, domain expertise plus agentic execution plus the discipline to verify before declaring something finished, is the conversation I have with clients trying to figure out where these tools actually earn their keep in their own organization, versus where they’re theater. If you’re weighing that question for your own team, that’s worth talking through before you commit to a direction either way.

---

Related: my earlier post evaluating AI agent platforms covers the tooling landscape in more general terms; this piece is the applied follow-up. Data referenced here comes from the EU’s public CTIS clinical trials register, accessed via its undocumented public API, building on prior community reverse-engineering work including the R package ctrdata and a Python client, euclinicaltrials.py.