Building an investing knowledge graph, part 2: the query SQL can't answer

Here is a Cypher query I can now run against the investing knowledge graph:

MATCH path = (source:Company {name: "ASML"})
             -[:SUPPLY_CUT|EXPORT_RESTRICTION|PRODUCTION_DELAY*1..4]->
             (downstream:Company)
RETURN 
  downstream.name AS company,
  length(path) AS hops,
  [r IN relationships(path) | r.article_id] AS evidence_articles,
  [r IN relationships(path) | r.event_date] AS event_dates
ORDER BY hops, event_dates[0]
LIMIT 30;

It returns paths. Not rows. Each result in the output is a chain from the source company to a downstream company, with the article ID and event date at every link. I can see whether NVIDIA appears in the results, how many steps away, and what sequence of articles establishes the connection. The depth bound is a parameter I tune at query time.

I didn't write this first. This is where I ended up after trying two other things that didn't work.

The search index

The article database sits in Elasticsearch, index called economic_news_articles_en, currently 9,414 documents. Starting with what I had:

GET economic_news_articles_en/_search
{
  "query": {
    "bool": {
      "must": [
        { "match": { "text": "chip packaging export restriction" } },
        { "range": { "published_at": { "gte": "2023-01-01" } } }
      ]
    }
  },
  "_source": ["title", "published_at", "companies_mentioned"],
  "size": 15
}

Fourteen results. Some are on the specific restriction. A few match incidentally. The NVIDIA articles are nowhere in these results because NVIDIA isn't mentioned in any of them. This is not a deficiency in Elasticsearch. It returned exactly what it should. The question I wanted to ask is a different kind of question: it requires following edges between things, and the search index doesn't model edges. Relevance tuning would not have helped here.

The SQL attempt

I had generated a relational structure during the build process. An article_events table, each row an event type, a subject company, an affected company, and a date. The first query:

SELECT c.name, ae.event_type, ae.event_date
FROM article_events ae
JOIN companies c ON c.id = ae.object_id
WHERE ae.subject_id = (SELECT id FROM companies WHERE name = 'ASML')
  AND ae.event_type IN ('supply_cut', 'export_restriction', 'production_delay')
ORDER BY ae.event_date;

That gives first-hop results: companies directly named in events where ASML is the subject. Going one hop further:

WITH first_hop AS (
  SELECT ae.object_id AS affected_id, ae.event_date
  FROM article_events ae
  WHERE ae.subject_id = (SELECT id FROM companies WHERE name = 'ASML')
    AND ae.event_type IN ('supply_cut', 'export_restriction', 'production_delay')
)
SELECT DISTINCT c.name, ae2.event_type, ae2.event_date
FROM first_hop fh
JOIN article_events ae2 ON ae2.subject_id = fh.affected_id
JOIN companies c ON c.id = ae2.object_id
WHERE ae2.event_date >= fh.event_date
ORDER BY ae2.event_date;

I had to know upfront how many hops I was looking for. Two CTEs for two hops. A three-hop query needs three. If the connection I care about happens to be five steps deep, a three-hop query misses it quietly. Recursive CTEs handle variable depth but bring their own performance questions, and by that point I was building something that wasn't really a SQL use case anymore.

The schema itself was losing information. Events in articles don't always have clean subject-object pairs. An article might describe an event that involves three or four companies in ways I couldn't reduce to one row without discarding something.

Why the graph answered it

The Cypher query at the top of this post returned paths. The evidence traveled with the result. For each chain in the output I had the article IDs and the event dates at each intermediate link, not just the endpoint. The *1..4 depth parameter meant I could check four hops without rewriting anything.

The graph at this point holds 84,962 nodes and 275,293 relationships, built from those same 9,414 articles. On the Cypher query, response time is acceptable. When a path doesn't exist in the data, the query returns nothing, which is a correct answer. It means the graph doesn't have evidence for that connection, not that the connection doesn't exist in the world.

What the graph assumed

For the traversal to be useful, the same company needed to map to the same node regardless of how different articles referred to it.

That didn't happen automatically. Articles about ASML might say "ASML Holding NV" in a formal announcement, then a wire service shortens it to just "ASML," then a blog post never names the company and calls it "the Dutch lithography equipment maker" instead. If those land as three separate nodes in the graph, the traversal silently breaks. None of the three nodes accumulates enough edges to connect to anything interesting.

The flip side caused different problems. ASML Cymer is a subsidiary. It makes excimer laser light sources, not the lithography systems the parent is known for. Merging that name into the ASML parent node because the strings share characters would misattribute events. A supply disruption that hit only the subsidiary's production line would look, in the graph, like it hit ASML's main business.

Getting entity mentions to resolve correctly was the bottleneck I hadn't expected. That's what part 3 covers: how string similarity fails in both directions at once, and what probabilistic matching over multiple signals looks like in practice for 47,853 resolved entities.

---

Built on Splink for probabilistic record linkage. Part 1 is here.