Keeping a Knowledge Graph Fresh

A knowledge graph is only as useful as its worst-updated corner. The engineering is not in the initial load — that is a weekend — but in the steady state, where four sources push changes at different cadences, in the wrong order, and occasionally disagree.

Three ways to get changes

Most real systems run two: CDC or polling for latency, and a snapshot diff weekly as the backstop. The backstop is not optional, because every incremental mechanism drifts — a consumer restart with a lost offset, a watermark advanced past a batch that failed, a source that updates rows without touching updated_at.

One trap specific to watermark polling: use a strictly monotonic watermark, and read >= rather than > with deduplication downstream. Rows committed within the same clock tick as your watermark are otherwise skipped, and that class of loss is silent and unrecoverable without a snapshot.

Idempotent writes are the foundation

Every message will be delivered more than once. Retries, consumer rebalances and replays guarantee it, so the writer has to be safe under repetition rather than the delivery being safe under failure.

// idempotent: keyed MERGE, then SET
MERGE (c:Company {id: $canonical_id})
ON CREATE SET c.created_at = datetime()
SET c.name    = $name,
    c.country = $country,
    c.updated_at = datetime(),
    c.source_version = $source_version

Note that $canonical_id is the resolved internal id, not the source’s primary key. The lookup from source key to canonical id goes through the alias table described in canonical entity ids, and doing it in the writer rather than in the extractor is what lets a merge decision change without replaying the whole stream.

Relationships need the same treatment and one extra decision. A CDC event saying “this company’s supplier list is now A, B and C” requires removing D, which a MERGE cannot do. Handle collection-valued updates as a diff within a transaction:

MATCH (c:Company {id: $canonical_id})
OPTIONAL MATCH (c)-[r:SUPPLIES]->(old:Company)
WHERE NOT old.id IN $current_supplier_ids
DELETE r
WITH c
UNWIND $current_supplier_ids AS sid
MATCH (s:Company {id: sid})
MERGE (c)-[:SUPPLIES]->(s)

Out-of-order events

Partitioned queues guarantee ordering within a partition and nothing across partitions. A company updated twice in one second can arrive in either order, and last-write-wins on arrival time will happily apply the older value.

The fix is a version on every write and a comparison in the writer. Use the source’s own monotonic marker — a log sequence number, a commit timestamp, a row version — and never your own clock:

MERGE (c:Company {id: $canonical_id})
ON CREATE SET c.source_version = -1
WITH c
WHERE $source_version > c.source_version
SET c.name = $name,
    c.country = $country,
    c.source_version = $source_version

A stale event now matches nothing after the WHERE and applies no writes. Count those rejections; a rate that climbs means the consumer is falling behind or a partition key is wrong.

Keep the version per source, not one global version. Two sources writing different fields of the same node each have their own sequence, and a shared counter makes them fight.

Deletes, which are never deletes

A delete in a source system means one of at least four things, and they need different handling:

• The record was created in error. The fact was never true. Retract it.
• The thing ceased to exist. The company dissolved. The fact was true and stopped being true — that is an end date, not a deletion. See temporal knowledge.
• The record moved. It was merged into another record in the source. Follow the merge; do not delete.
• A retention policy fired. The source deleted it for legal reasons, and you may be obliged to do the same — which is the one case where a hard delete in your graph is correct, and it should be a deliberate, logged, separate path.

The default for the first three is a soft delete: set status = 'withdrawn' with a reason and a timestamp, exclude it from the read views, and keep the node so that every id still resolves. A hard DETACH DELETE in a CDC consumer is the single most destructive line of code in a graph pipeline, because it silently removes edges nothing else knew about.

The conflict rules, written down

Two sources will assert different values for one field. This is not an edge case; it happens on day one with addresses, names and country codes. Write the rule as configuration rather than as code, so that a data steward can read it:

# field-level resolution policy
company.legal_name:
  precedence: [company_register, erp, crm, extracted]
  on_tie:     most_recent
  never_overwrite_if: steward_verified

company.email:
  precedence: [crm, erp]
  on_tie:     most_recent

company.country:
  precedence: [company_register]
  on_conflict: flag          # do not pick; raise for review

company.employee_count:
  precedence: [erp, extracted]
  on_tie:     most_recent
  max_change_per_day: 0.5    # a 50% jump is a bad load, not a hiring spree

The last line is a plausibility guard and it catches more real incidents than any of the precedence rules. A field that changes by an implausible amount is nearly always a unit change, a column shift, or a truncated load. Bound the change, and route violations to review rather than applying them.

The reconciliation sweep

Run a full comparison against every source on a fixed cadence and report differences without fixing them automatically. Three categories, and each means something different:

1. In the source, not in the graph. A missed create, or an entity-resolution decision that attached it elsewhere. Usually safe to auto-repair.
2. In the graph, not in the source. A missed delete — or a legitimate record from another source. Never auto-repair this one; scope it by source and review.
3. In both, different. Either a missed update or a conflict rule doing its job. The count is the useful signal: a gradual rise means a consumer is dropping messages.

Publish the three counts as a dashboard and alert on the derivative rather than the level. Every graph has a steady-state discrepancy count; what matters is when it starts climbing.

Measuring freshness

Freshness is a distribution, not a number. Record, per fact, the source commit time and the graph write time, and report the lag as percentiles per source:

SELECT source_id,
       count(*)                                          AS facts,
       percentile_disc(0.50) WITHIN GROUP (ORDER BY lag) AS p50_seconds,
       percentile_disc(0.95) WITHIN GROUP (ORDER BY lag) AS p95_seconds,
       max(lag)                                          AS worst_seconds
FROM (
  SELECT source_id,
         EXTRACT(EPOCH FROM (graph_written_at - source_committed_at)) AS lag
  FROM assertion
  WHERE graph_written_at > now() - interval '24 hours'
) t
GROUP BY source_id
ORDER BY p95_seconds DESC;

A mean hides the case you care about, in exactly the way described in latency percentiles: one source stalled for six hours barely moves the mean and dominates p95. Set the alert on p95 per source, and set the target from the consumer — a pricing query needs minutes, an analytics rollup can live with a day, and pretending both need seconds is how a pipeline becomes expensive for no reason.