AI Strategy · Data Architecture

The Soft Join: SQL Discipline for Soft Data

The industry relates two piles of unstructured text by asking whether they feel related. There is an older, exact, free answer hiding in your folder names — and it is the difference between similarity and identity.

I was going to make it a RAG. That was the reflex — two piles of unstructured text, so embed both, drop the vectors in a store, and rank by similarity when you need to relate them.¹ It is the move everyone reaches for now, and it takes about an afternoon. Then I stopped, and decided something that felt almost heretical for 2026: RAG is easy, but it’s dumb. So I went the other way. I put a key on it instead — and I did a SQL join on soft data.

Here is the setup, because the whole argument lives in the specifics. For a couple of years I have fastidiously archived every Claude Code conversation across all my machines — snapshotted every half hour, on every box, backed up to one spot. Months ago I decided the transcripts might matter one day, so I kept them. Separately, I have my dev folder: a hundred-odd project folders, mostly Python, some markdown, some templates. Two corpora. Both soft, both messy, both plain text. The obvious task: relate them — connect the conversation about a project to the code of that project — so that when I ingest a project into a wiki, I can understand not just what the code does but why it was built.

The easy answer is similarity. Embed the conversations, embed the code, and for each project surface the transcripts that score closest. It would have worked, in the sense that it would have returned something. But look at what it actually gives you: a ranked list of conversations that resemble this code. Confident? No — scored, and approximate by construction.² Correct? Probably, mostly, on a good day. And critically, it answers the wrong question. Similarity can tell you a conversation is about a piece of code. It cannot tell you that conversation is the one that wrote it.

The join I didn’t believe would work

You know how you do a SQL join between two tables on a shared key? I did that with soft data, and I sat there not quite believing how well it worked.

The key was hiding in plain sight: the dev folder name. Claude Code scopes every session by directory — the transcripts are organised by the working directory they happened in. Which means the tool had already minted my foreign key for me. Every conversation carries the folder it ran in; every project is a folder. So the join is trivial, deterministic, and exact:

No embedding model ran. No similarity threshold was tuned. No vector store was provisioned. A key lookup, run before any model touched anything, returned the certain answer: these conversations, and only these, made this code. And once the transcripts were joined, a small class of claims fell out for free that similarity could never certify. The first message I ever typed into a project — before any grind contaminated it — turns out to be the North Star of that project, written involuntarily: maximum intent, zero noise, the “why” at its purest. The first AI reply is the first interpretation, the moment intent became a plan. Together they are the project’s commit zero, dated, retrievable, and true. I did not infer them. I joined to them.

Similarity is not identity

This is the distinction the whole piece turns on, so let me draw it sharply on the same pair of corpora — conversations and code — because that is the only honest comparison.

Notice this is not a claim that similarity is worthless. It is a claim that similarity is the fallback, not the default. Embeddings earn their cost exactly where no key exists — pure prose, cross-topic resemblance, the genuinely fuzzy relationships. The error the industry makes is reaching for them first, by reflex, even in the many cases where a real, exact, free key was sitting right there. “RAG is easy, but it’s dumb” is not anti-RAG. It is anti-reflex. Trade the probabilistic index for a relational one wherever a real key exists; keep the model for the parts that genuinely need judgment.

RAG could tell me these conversations are about this code. The join tells me they created it. That is not a better score — it is a different fact.

The star schema showed up on its own

Here is the part that gave me the strongest sense of having stumbled onto something older than me. Look at what I had actually assembled in Postgres after the join: a table of conversation turns, each carrying a foreign key to a project. That is a fact table pointed at a dimension. That is a star schema³ — the exact shape Ralph Kimball formalised for data warehouses in the 1990s — and it arrived in my soft-data warehouse the moment I refused the lazy substrate. I did not design it. It fell out of using a key.

This is more than a pleasing analogy; it is an inheritance. The instant your soft data is shaped like a warehouse, forty years of warehouse discipline arrives with its solutions already attached. Fact-and-dimension modelling. Slowly-changing dimensions for the version chains — which document superseded which, and when. The bronze/silver/gold tiering of raw, refined, and curated data that the analytics world calls a medallion architecture.⁴ The whole extract-transform-load playbook that every data engineer already owns. I have written before that this is BI for soft data — and the natural-key join is the moment the “BI” part stops being a metaphor and becomes a literal schema you can query. You are not asking your organisation to hire a new species. You are telling the data engineers that the unstructured 80% of the estate just became their territory.

Your organization is full of natural keys

A natural key is simply a key made of data that is already there — a real-world identifier the records carry on their own, as opposed to one you invent and bolt on.⁵ The dev folder name is a natural key. And once you have the eye for them, you cannot stop seeing them, because every piece of software your organization ever ran left an identity system lying around. Nobody is joining on them. They are joining on vibes.

So before you embed a single thing, inventory the keys. This is a treasure hunt with a very high hit rate:

The doctrine compresses to one instruction: find the identity system the old software left lying around. Every system left one. And every deterministic join you make before any model runs is context the AI gets for free — with certainty instead of a confidence score. Certain context is the cheapest, best context there is, and most teams are paying for the probabilistic kind while the free kind sits in their metadata.

The joins the model can’t fake — and the grep that proves them

The folder-name join is the exact case. But natural keys are not the only deterministic relationships hiding in a corpus, and it is worth seeing one more class, because it shows how far you can push discipline before you spend a token on judgment.

Consider copy-lineage — the fact that half my projects are forked from earlier work. Which project did this one descend from? Which modules do two projects share? The reflex, again, is similarity: these files look alike. The exact answer is a hash. Identical content produces an identical hash — it is the principle Git is built on, a content-addressed store that names every object by the SHA of its bytes.⁶ A cross-project file-hash match finds copy-lineage for free: shares-code-with, descended-from, computed with zero model calls and zero guessing. Content-addressing is a join key you never had to invent, because the content is the key.

The prettiest confirmation of the whole philosophy, though, came from the model itself. I gave the ingesting agent ripgrep, and before it would mint a “this project uses technology X” link, it started running a cheap check on its own: does this pattern actually recur across the corpus, or is it a one-off? Nobody told it to. It reached for the deterministic falsifier before committing to the ontology — evidence before assertion. That is exactly the instinct: don’t claim a relationship the data can’t certify when a two-millisecond grep can settle it. Emergent discovery is delightful; deterministic confirmation is nearly free. Do both.

Certainty is free; you keep buying confidence

Strip it all back and the discipline is almost embarrassingly old. The equijoin — a join on equality — is a foundational relational idea.⁷ The star schema is a 1990s idea. Content-addressing predates the AI boom by decades. None of this is clever. What is new is the willingness to point that boring, exact machinery at soft data — conversations, email, documents, photos — instead of assuming that because the data is unstructured, every relationship in it must be probabilistic too.

It doesn’t. Organizations are riddled with keys the software already minted, and a join on a real key is a different thing from a similarity score in the same way that knowing is a different thing from guessing well. The reflex to embed everything treats every corpus as structureless and every relationship as fuzzy. But the structure is there. You just have to go looking for the key before you reach for the model.

Once the conversations are joined to the code, of course, a second thing becomes possible — the transcript starts telling you which files the author actually cared about, which is a signal no code-only tool can see. But that is a story about what the join found, and it deserves its own article. This one is about the join itself: the moment I refused the easy, dumb substrate, put a key on my soft data, and watched the certainty I’d been paying a model to approximate turn out to be sitting in the folder names the whole time.

Find the identity system the old software left lying around. Every system left one.