I Built a Knowledge Graph for a Warehouse With No Foreign Keys

Names and figures in this post are genericised. xx_ and yy_ stand in for real
publisher prefixes; volumes are rounded.

---

A star schema is already a graph. Tables are nodes, joins are edges. That's the whole
model.

So when I inherited a 30-table gold-layer warehouse with no documentation worth the name, my
first instinct was the usual one: write a big reference document. A Word file, a Confluence
page, a spreadsheet with a tab per table.

All three throw away the structure. A document is a list. A spreadsheet is a grid. Neither
of them can answer what joins to this table, which is the only question anybody actually
asks.

So I built a graph instead. Here's the model, the tooling, and the parts I got wrong first.

Why a graph, specifically

The warehouse sits on a Delta lakehouse. Which means — and this is the crux of the whole
project — there are no enforced foreign keys. Not disabled. Not unenforced-but-declared.
Absent. The platform has no idea that fact_crm_opportunity.accountid has anything
whatsoever to do with dim_crm_account.accountid.

Think about what that implies. In a traditional RDBMS the join graph is in the database.
You can query it, diagram it, validate it. Drop a referenced row and the engine stops you.

Here, the join graph exists in exactly one place: people's heads. And a sys.columns
dump, which gives you nodes with no edges at all.

That's not a documentation problem. That's a missing-data-structure problem. The graph has
to be materialised somewhere, and if it isn't in the platform then it has to be in your
knowledge layer. This is the argument for a knowledge graph over a document, and it's the
only argument you need.

The node model: one table, one note

I used Obsidian. Markdown files, [[wikilinks]], YAML frontmatter, a local folder. No
graph database, no triple store, no Neo4j.

That's a deliberate choice and I'd make it again. A property graph in Neo4j would model the
schema beautifully and nobody on the team would ever open it. Markdown in a folder is
diffable, greppable, reviewable in a pull request, and readable in any text editor in
thirty years' time. The graph capability you actually need — typed nodes, annotated edges,
reverse traversal, property queries — Obsidian gives you for free.

One table = one note. Thirty-two nodes: 23 dimensions, 7 facts, 2 views.

Each node has a fixed anatomy:

---
type: table
kind: fact
domain: opportunity
rows: 14,000
active_rows: 14,000
verified: 2026-07-30
tags: [crm/gold, crm/fact]
---

# fact_crm_opportunity

**What it is:** <2–4 sentences of actual prose>

## Grain & volume
<row count · distinct key count · load pattern>

## Columns
| Column | Type | Meaning |

## Joins
<the edges>

## Decoded values
<for lookup nodes: the full code→label set inline>

## Gotchas
<what will bite you>

## Query recipe
<working SQL>

Two of those sections do the graph work. The rest is payload.

Edges: ## Joins, and why they carry confidence

The ## Joins section is the edge list. Every entry is a wikilink plus the actual join
predicate:

- [[dim_crm_account]] via `accountid = dim_crm_account.accountid`
- [[dim_crm_customer]] via `customerid = dim_crm_customer.yy_legalcustomerid`
- [[dim_crm_currency]] via `transactioncurrencyid = dim_crm_currency.transactioncurrencyid`

That's a typed, predicated edge in seven words of markup. No schema migration, no Cypher.

But here's the part that made the graph genuinely useful rather than merely tidy: the
edges carry a confidence flag.

- [[dim_crm_brand]] via `yy_oi_brand = dim_crm_brand.xx_brandid` ⚠️ UNVERIFIED

Because with no foreign keys, an edge is a hypothesis until someone tests it. Twenty-four
of the original edges were marked unverified. Some were plausible-looking traps: a bridge
table joining a bigint internal id to a varchar GUID, which would never match a single
row.

A knowledge graph where every edge is asserted with equal confidence is lying to you. Mine
had three edge states — verified, unverified, and rejected — and the rejected ones got
deleted along with their nodes when I discovered two documented tables didn't exist in the
warehouse at all.

Later I tested the hypotheses with match-rate queries:

SELECT COUNT(*) AS left_rows,
       SUM(CASE WHEN b.xx_brandid IS NOT NULL THEN 1 ELSE 0 END) AS matched,
       CAST(100.0 * SUM(CASE WHEN b.xx_brandid IS NOT NULL THEN 1 ELSE 0 END)
            / NULLIF(COUNT(*),0) AS decimal(5,2)) AS match_pct
FROM dim_crm_customer c
LEFT JOIN dim_crm_brand b ON b.xx_brandid = c.yy_oi_brand AND b.etl_isactive = 1
WHERE c.etl_isactive = 1;

Two edges got promoted to verified. Nine went away with their nodes. Edge confidence is a
first-class property, and it changes over time. Design for that on day one.

Reverse edges are free, and that's the killer feature

Here's what sold me on the graph approach.

In SQL, there is no way to ask what joins to dim_crm_user? You can find the foreign
keys pointing at it — except there are no foreign keys. So you grep 30 files for the table
name and hope.

In a wikilink graph, that question is a backlink query. It's built in. The moment
fact_crm_opportunity writes [[dim_crm_user]], the user node knows it's been referenced.

That reverse traversal immediately told me something the forward edges didn't:
dim_crm_user was referenced by about a dozen tables — every ownerid, owninguser,
createdby, modifiedby, xx_deliverymanagerid, xx_verticallead, xx_leaduser. It was
the most connected node in the schema, and nothing in the original documentation said so.

Four nodes turned out to be hubs:

graph LR
    F1[fact_crm_opportunity] --> H1[dim_crm_opportunity]
    F2[fact_crm_opportunity_scd] --> H1
    F3[fact_crm_stage_tracking] --> H1
    F4[fact_crm_oi_stage_tracking] --> H1
    F5[fact_crm_revenueoverlay] --> H1
    F6[fact_crm_snapshot_mom] --> H1
    H1 --> H2[dim_crm_user]
    H1 --> H3[dim_crm_business_unit]
    H1 --> H4[dim_crm_mapping_statecode]
    D1[dim_crm_account] --> H2
    D2[dim_crm_brand] --> H2
    D3[dim_crm_vertical] --> H2

Node degree is a finding, not a diagram artifact. The high-degree nodes are the ones
that need the best documentation, the ones every query touches, and — as it turned out
later — the ones that need special handling when you feed the graph to an LLM.

Obsidian's graph view, which I'd written off as eye candy, made this visible in about four
seconds.

Node properties: frontmatter you can query

Frontmatter turns each note from a document into a record. Once kind, domain, rows,
active_rows and verified are properties, the graph becomes queryable:

```dataview
TABLE kind, rows, active_rows, verified
FROM #crm/gold
WHERE kind = "fact"
SORT rows DESC
```

That's a live inventory that maintains itself. And the properties encode a real finding:
rows versus active_rows is the SCD2 fan-out. One dimension held ~317k rows for ~14k
business entities — a 22× multiplier waiting to silently inflate anyone's numbers who
forgot a filter. Putting both numbers in the frontmatter makes the risk sortable.

I also added domain — opportunity, account, org, lookup, lead, revenue. Six
clusters. That's a partition of the graph, and it earns its keep later.

Hub notes: not every node is a table

The mistake I nearly made was assuming a knowledge graph is only entity nodes. It isn't.
Some of the most valuable nodes are synthesis nodes — what the Obsidian community calls
Maps of Content.

I added four:

Node	Role
schema_reference	The index. Entry point, table listings, cross-cutting structure
_Query Rules	The non-negotiables. Filter cheat sheet, join defaults, canonical skeleton
_Value Sets	Every decoded code in one place
_Open Questions	What's unknown, as checkboxes, grouped by whether it blocks

_Value Sets deserves a note of its own. Ten of the 23 dimensions are tiny lookup
tables — 3 to 22 rows each. For those nodes, the data is small enough that the data is
the documentation. So I inlined the complete code→label set into the note:

## Decoded values
| Code | Label |
|---|---|
| `456540000` | Fixed Price |
| `456540001` | Time & Material |
| `456540002` | Support/Managed Services |
| `456540004` | Performance based |

Note the gap at ...003. Codes are not contiguous, so nobody can generate them
arithmetically — which is exactly the sort of thing you only learn by dumping the table and
looking.

_Open Questions is the one I'd push hardest on. A knowledge graph that only records what
you know is half a graph. The gaps are nodes too: four blocking unknowns, six semantic
questions for the business, nine data-quality bugs, and a closed list so nobody re-litigates
settled questions.

Generate the graph, don't hand-write it

I wrote roughly 700 column descriptions. I did not type 700 column descriptions.

Hand-editing at that volume produces inconsistency and fatigue errors — by cell 400 you're
describing the same ETL column three different ways. So the graph is generated from a
metadata dictionary, with pattern rules for the long tail:

COMMON = {
    "etl_isactive": "**SCD2 current-version flag. Filter `= 1` on every join "
                    "or the table fans out.**",
    "ownerid":      "Record owner. → [[dim_crm_user]] `systemuserid`. Can also be "
                    "a *team*, so a LEFT JOIN may not match.",
}

PATTERNS = [
    (r"^dim_\w+_id$", "Surrogate key. Unique per SCD2 *version*, not per entity."),
    (r"_id$",         "Date-dimension FK. **Orphaned — no dim_date exists.**"),
]

One rule, applied consistently across thirty nodes. The 120-column merged history table
inherited its column meanings from the three tables it merges, each tagged as inherited —
sixty cells written once instead of twice.

The dictionary is the source of truth. The markdown is a build artifact. That inversion is
worth internalising: treat your knowledge graph as compiled output, and version the
inputs.

Validate the graph like code

A knowledge graph has integrity constraints, and unlike the warehouse it documents, you can
actually enforce them. Mine ran on every build:

nodes                                  : 36
broken wikilinks                       : none
empty Meaning cells                    : 0
empty Gotchas sections                 : 0
column counts vs live INFORMATION_SCHEMA: 30/30 match
references to non-existent tables      : none

Two of those are graph-integrity checks and two are reality checks.

Broken wikilinks are dangling edges. In Obsidian they render as a different colour and
create a phantom node. Left alone, they rot into a graph full of ghosts.

Column counts against live INFORMATION_SCHEMA is the one people skip, and it's the
one that matters most. It's the difference between a graph that describes the warehouse and
a graph that describes a warehouse someone had once. Mine reconciled 30 for 30 — which is
how I know the nodes are real. It's also how I found that two documented tables didn't
exist in this environment at all.

The payoff I didn't design for

I built the graph for humans. Then it turned out to be the right shape for an LLM too.

We're building a natural-language-to-SQL agent over this warehouse, and it needs schema
knowledge in context. The standard RAG approach is: dump documentation into a vector store,
chunk it at 512 tokens, retrieve top-k.

That fails badly on schema documentation, and the graph explains why. Fixed-size chunking
cuts a table note mid-Columns-table. The chunk that matches the query then contains
column names with no grain, no join list, no gotchas. The agent gets the vocabulary and
none of the semantics. It writes confident, broken SQL.

Whereas one node = one chunk preserves the unit of meaning by construction. The largest
node is 22 KB — comfortably inside any embedding limit worth using. No chunking strategy
required, because the graph already segmented the knowledge correctly.

Three more graph properties turned into retrieval config:

• domain — the partition. Retrieve n per domain instead of n overall, and cross-domain questions stop returning nothing.
• Node degree — the four hub nodes get pinned into every retrieval. They're needed by nearly every query and matched by nearly no business-language question. Nobody asks about "the state mapping table"; every query needs it.
• The synthesis nodes — _Query Rules and _Value Sets don't go in the vector store at all. They go in the system prompt, always present. If retrieval misses a non-negotiable rule, the agent emits SQL that returns zero rows and reports "no results found." A silent wrong answer. Anything that must never be missed cannot depend on top-k.

That last point is the one I'd tattoo on something. Graph structure tells you what to
retrieve and what to hardcode. A flat document can't tell you that.

What I'd do differently

Model edge confidence from the first commit. I retrofitted verified / unverified /
rejected. It should have been in the template.

Put rows and active_rows in frontmatter immediately. The ratio is the single most
useful number about a dimension and it's invisible in prose.

Build the synthesis nodes early, not last. I wrote _Query Rules at the end, as a
summary. It should have existed from day one as a scratchpad — every trap would have landed
there the moment I hit it, instead of being reconstructed later from memory.

Validate from the first build, not the last. Broken links and empty sections are cheap
to catch continuously and miserable to fix in bulk.

Resist the graph database. Really. The value was in the model — typed nodes, annotated
edges, reverse traversal, queryable properties — not the storage engine. Thirty-six markdown
files in git delivered all four, and the team actually reads them.

The one-line version

A schema without foreign keys is a graph whose edges exist only as knowledge. Materialise
them somewhere queryable, annotate them with how much you trust them, and validate the
result against the live database — or you don't have documentation, you have folklore with
syntax highlighting.