Cookbook / Data quality · Advanced

Rehearse a migration against a golden graph

A migration can keep every node count and every intended change right and still destroy data. Fingerprint the graph before the migration runs, write down what it is allowed to change, and compare afterwards. Here a probe on each organization's relationships catches a bug that deletes 352 city links across 61 organizations, and the fingerprint puts the links back.

The graph is a company directory: (:Organization)-[:HAS_CEO]->(:Person), (:Organization)-[:HAS_CATEGORY]->(:IndustryCategory), and (:Organization)-[:IN_CITY]->(:City)-[:IN_COUNTRY]->(:Country). The migration renames HAS_CEO to LED_BY, adds an industry property, drops summaries from organizations without a CEO, and removes duplicate city links. One of its four statements has a bug.

Data model: the company directory; the migration renames HAS_CEO to LED_BY and adds industry

The companion runs the migration on a copy of a committed golden file and diffs the two files from Python. Here everything happens in one database: the fingerprint is stored in the graph itself, on Probe and Fingerprint nodes that the migration never matches.

The rehearsal, in one database

Load the golden graph

Every node has an id, and uniqueness constraints keep them unique. The fingerprint identifies relationships by their target's id.

Step 1

Show the setup step

CREATE CONSTRAINT org_id IF NOT EXISTS FOR (o:Organization) REQUIRE o.id IS UNIQUE;
CREATE CONSTRAINT person_id IF NOT EXISTS FOR (p:Person) REQUIRE p.id IS UNIQUE;
CREATE CONSTRAINT category_id IF NOT EXISTS
  FOR (c:IndustryCategory) REQUIRE c.id IS UNIQUE;
CREATE CONSTRAINT city_id IF NOT EXISTS FOR (c:City) REQUIRE c.id IS UNIQUE;
CREATE CONSTRAINT country_id IF NOT EXISTS FOR (c:Country) REQUIRE c.id IS UNIQUE

Recorded result

(no rows) · constraints added: 5

Rows for organizations without a summary have no summary key, so r.summary is null and CREATE sets no property.

Step 2

Show the setup step

UNWIND $countries AS r
CREATE (:Country {id: r.id, name: r.name});

UNWIND $cities AS r
MATCH (k:Country {id: r.country})
CREATE (:City {id: r.id, name: r.name})-[:IN_COUNTRY]->(k);

UNWIND $categories AS r
CREATE (:IndustryCategory {id: r.id, name: r.name});

UNWIND $organizations AS r
CREATE (:Organization {id: r.id, name: r.name, founded: r.founded,
                       employees: r.employees, isPublic: r.isPublic,
                       summary: r.summary});

UNWIND $organizations AS r
WITH r WHERE r.ceo IS NOT NULL
MATCH (o:Organization {id: r.id})
CREATE (o)-[:HAS_CEO]->(:Person {id: r.ceo.id, name: r.ceo.name});

UNWIND $organizations AS r
MATCH (o:Organization {id: r.id})
UNWIND r.categories AS id
MATCH (c:IndustryCategory {id: id})
CREATE (o)-[:HAS_CATEGORY]->(c);

UNWIND $organizations AS r
MATCH (o:Organization {id: r.id})
UNWIND r.cities AS id
MATCH (c:City {id: id})
CREATE (o)-[:IN_CITY]->(c);

RETURN COUNT { MATCH (:Organization) } AS organizations,
       COUNT { MATCH (:Person) } AS ceos,
       COUNT { MATCH (:City) } AS cities,
       COUNT { MATCH (:Organization)-[:IN_CITY]->(:City) } AS city_links

UNWIND $countries AS r
CREATE (:Country {id: r.id, name: r.name});

UNWIND $cities AS r
MATCH (k:Country {id: r.country})
CREATE (:City {id: r.id, name: r.name})-[:IN_COUNTRY]->(k);

UNWIND $categories AS r
CREATE (:IndustryCategory {id: r.id, name: r.name});

UNWIND $organizations AS r
CREATE (:Organization {id: r.id, name: r.name, founded: r.founded,
                       employees: r.employees, isPublic: r.isPublic,
                       summary: r.summary});

UNWIND $organizations AS r
WITH r WHERE r.ceo IS NOT NULL
MATCH (o:Organization {id: r.id})
CREATE (o)-[:HAS_CEO]->(:Person {id: r.ceo.id, name: r.ceo.name});

UNWIND $organizations AS r
MATCH (o:Organization {id: r.id})
UNWIND r.categories AS id
MATCH (c:IndustryCategory {id: id})
CREATE (o)-[:HAS_CATEGORY]->(c);

UNWIND $organizations AS r
MATCH (o:Organization {id: r.id})
UNWIND r.cities AS id
MATCH (c:City {id: id})
CREATE (o)-[:IN_CITY]->(c);

RETURN COUNT { MATCH (:Organization) } AS organizations,
       COUNT { MATCH (:Person) } AS ceos,
       COUNT { MATCH (:City) } AS cities,
       COUNT { MATCH (:Organization)-[:IN_CITY]->(:City) } AS city_links

Loads golden.json (26 KB)

Recorded result

organizations	ceos	cities	city_links
90	68	26	438

1 row · nodes created: 201, relationships created: 676, properties set: 718, labels added: 201

| organizations | ceos | cities | city_links |
|---------------|------|--------|------------|
| 90            | 68   | 26     | 438        |
1 row · nodes created: 201, relationships created: 676, properties set: 718, labels added: 201

One organization, Nimbus Labs, as it is before anything changes: every node it links to, and every city in the directory, linked or not. The same statement runs again after the migration, so the two results can be compared.

Step 3

MATCH (o:Organization {id: $org}), (x)
WHERE x:City OR EXISTS { (o)-->(x) }
OPTIONAL MATCH (o)-[r]->(x)
RETURN o, r, x
ORDER BY x.id

Parameters

Recorded result

// 29 rows
[ | (n53:Organization {employees: 80, founded: 2013, id: "org-11", isPublic: false, name: "Nimbus Labs"}), (n53)-[r282:IN_CITY]->(n11), (n11:City {id: "austin", name: "Austin"})]
[ | (n53), (n53)-[r291:IN_CITY]->(n30), (n30:City {id: "bangalore", name: "Bangalore"})]
[ | (n53), (n53)-[r290:IN_CITY]->(n18), (n18:City {id: "berlin", name: "Berlin"})]
[ | (n53), (n53)-[r280:IN_CITY]->(n9), (n9:City {id: "boston", name: "Boston"})]
[ | (n53), (n53)-[r281:IN_CITY]->(n17), (n17:City {id: "cambridge", name: "Cambridge"})]
[ | (n53), (n53)-[r293:IN_CITY]->(n12), (n12:City {id: "chicago", name: "Chicago"})]
[ | (n53), (n53)-[r271:IN_CITY]->(n13), (n13:City {id: "denver", name: "Denver"})]
[ | (n53), (n53)-[r288:IN_CITY]->(n16), (n16:City {id: "edinburgh", name: "Edinburgh"})]
[ | (n53), (n53)-[r111:HAS_CATEGORY]->(n42), (n42:IndustryCategory {id: "edtech", name: "Education Technology Companies"})]
[ | (n53), (n53)-[r289:IN_CITY]->(n20), (n20:City {id: "hamburg", name: "Hamburg"})]
[ | (n53), (n53)-[r269:IN_CITY]->(n31), (n31:City {id: "hyderabad", name: "Hyderabad"})]
[ | (n53), (n53)-[r270:IN_CITY]->(n14), (n14:City {id: "london", name: "London"})]
[ | (n53), (n53)-[r292:IN_CITY]->(n22), (n22:City {id: "lyon", name: "Lyon"})]
[ | (n53), (n53)-[r294:IN_CITY]->(n15), (n15:City {id: "manchester", name: "Manchester"})]
[ | (n53), (n53)-[r276:IN_CITY]->(n25), (n25:City {id: "montreal", name: "Montreal"})]
[ | (n53), (n53)-[r275:IN_CITY]->(n19), (n19:City {id: "munich", name: "Munich"})]
[ | (n53), (n53)-[r272:IN_CITY]->(n8), (n8:City {id: "new-york", name: "New York"})]
[ | (n53), (n53)-[r287:IN_CITY]->(n29), (n29:City {id: "osaka", name: "Osaka"})]
[ | (n53), (n53)-[r285:IN_CITY]->(n21), (n21:City {id: "paris", name: "Paris"})]
[ | (n53), (n53)-[r31:HAS_CEO]->(n138), (n138:Person {id: "person-06", name: "Kavya Ueda"})]
[ | (n53), (n53)-[r278:IN_CITY]->(n32), (n32:City {id: "pune", name: "Pune"})]
[ | (n53), (n53)-[r112:HAS_CATEGORY]->(n35), (n35:IndustryCategory {id: "robotics", name: "Robotics Companies"})]
[ | (n53), (n53)-[r284:IN_CITY]->(n7), (n7:City {id: "san-francisco", name: "San Francisco"})]
[ | (n53), (n53)-[r279:IN_CITY]->(n10), (n10:City {id: "seattle", name: "Seattle"})]
[ | (n53), (n53)-[r273:IN_CITY]->(n28), (n28:City {id: "tokyo", name: "Tokyo"})]
[ | (n53), (n53)-[r286:IN_CITY]->(n24), (n24:City {id: "toronto", name: "Toronto"})]
[ | (n53), (n53)-[r277:IN_CITY]->(n23), (n23:City {id: "toulouse", name: "Toulouse"})]
[ | (n53), (n53)-[r283:IN_CITY]->(n26), (n26:City {id: "vancouver", name: "Vancouver"})]
[ | (n53), (n53)-[r274:IN_CITY]->(n27), (n27:City {id: "waterloo", name: "Waterloo"})]

Fingerprint it before anything changes

The migration's contract is written down before it runs. Its one structural change is a relationship rename, passed as a map from old type to new type:

The fingerprint has two parts:

Count probes: one Probe node per label and per relationship type, holding the count now (before) and the count the migration must leave (expected). Counts stay the same, except that a renamed type drops to 0 and its new name inherits the old count. $renamed[type] looks the type up in the map, and is null for a type that is not renamed.
Per-organization fingerprints: one Fingerprint node per organization, holding its property keys, its properties as key=value strings, and its relationships as TYPE:targetId strings. expected is the relationship list with the rename applied: the relationships the organization must have afterwards.

Step 4

MATCH (n)
WITH labels(n)[0] AS label, count(*) AS n
CREATE (:Probe {kind: 'nodes', key: label, name: 'nodes :' + label, before: n,
                expected: n});

MATCH ()-[r]->()
WITH type(r) AS type, count(*) AS n
CREATE (:Probe {kind: 'rels', key: type, name: 'rels :' + type, before: n,
                expected: CASE WHEN $renamed[type] IS NULL THEN n ELSE 0 END});

UNWIND keys($renamed) AS old
MATCH (p:Probe {kind: 'rels', key: old})
MERGE (q:Probe {kind: 'rels', key: $renamed[old]})
  ON CREATE SET q.name = 'rels :' + $renamed[old], q.before = 0, q.expected = 0
SET q.expected = q.expected + p.before;

MATCH (o:Organization)
WITH o, COLLECT { MATCH (o)-[r]->(x) RETURN type(r) + ':' + x.id } AS rels
CREATE (:Fingerprint {
  org: o.id, name: o.name,
  propKeys: keys(o),
  props: [k IN keys(o) | k + '=' + toString(o[k])],
  rels: rels,
  expected: [e IN rels |
    coalesce($renamed[split(e, ':')[0]], split(e, ':')[0]) + ':' + split(e, ':')[1]],
  hadCeo: EXISTS { (o)-[:HAS_CEO]->() },
  hadCategory: EXISTS { (o)-[:HAS_CATEGORY]->() }
});

MATCH (p:Probe)
RETURN p.name AS probe, p.before AS before, p.expected AS expected
ORDER BY probe

MATCH (n)
WITH labels(n)[0] AS label, count(*) AS n
CREATE (:Probe {kind: 'nodes', key: label, name: 'nodes :' + label, before: n,
                expected: n});

MATCH ()-[r]->()
WITH type(r) AS type, count(*) AS n
CREATE (:Probe {kind: 'rels', key: type, name: 'rels :' + type, before: n,
                expected: CASE WHEN $renamed[type] IS NULL THEN n ELSE 0 END});

UNWIND keys($renamed) AS old
MATCH (p:Probe {kind: 'rels', key: old})
MERGE (q:Probe {kind: 'rels', key: $renamed[old]})
  ON CREATE SET q.name = 'rels :' + $renamed[old], q.before = 0, q.expected = 0
SET q.expected = q.expected + p.before;

MATCH (o:Organization)
WITH o, COLLECT { MATCH (o)-[r]->(x) RETURN type(r) + ':' + x.id } AS rels
CREATE (:Fingerprint {
  org: o.id, name: o.name,
  propKeys: keys(o),
  props: [k IN keys(o) | k + '=' + toString(o[k])],
  rels: rels,
  expected: [e IN rels |
    coalesce($renamed[split(e, ':')[0]], split(e, ':')[0]) + ':' + split(e, ':')[1]],
  hadCeo: EXISTS { (o)-[:HAS_CEO]->() },
  hadCategory: EXISTS { (o)-[:HAS_CATEGORY]->() }
});

MATCH (p:Probe)
RETURN p.name AS probe, p.before AS before, p.expected AS expected
ORDER BY probe

Parameters

Recorded result

probe	before	expected
"nodes :City"	26	26
"nodes :Country"	7	7
"nodes :IndustryCategory"	10	10
"nodes :Organization"	90	90
"nodes :Person"	68	68
"rels :HAS_CATEGORY"	144	144
"rels :HAS_CEO"	68	0
"rels :IN_CITY"	438	438
"rels :IN_COUNTRY"	26	26
"rels :LED_BY"	0	68

10 rows · nodes created: 100, properties set: 771, labels added: 100

| probe                     | before | expected |
|---------------------------|--------|----------|
| "nodes :City"             | 26     | 26       |
| "nodes :Country"          | 7      | 7        |
| "nodes :IndustryCategory" | 10     | 10       |
| "nodes :Organization"     | 90     | 90       |
| "nodes :Person"           | 68     | 68       |
| "rels :HAS_CATEGORY"      | 144    | 144      |
| "rels :HAS_CEO"           | 68     | 0        |
| "rels :IN_CITY"           | 438    | 438      |
| "rels :IN_COUNTRY"        | 26     | 26       |
| "rels :LED_BY"            | 0      | 68       |
10 rows · nodes created: 100, properties set: 771, labels added: 100

LED_BY does not exist yet, so its probe starts at 0 and expects the 68 HAS_CEO relationships.

Run the migration

These are the four statements the migration's author wants to ship. Step 4 has a bug. Try to spot it before the probes do.

Step 5

// 1. Rename HAS_CEO -> LED_BY (relationship types are immutable: copy + delete).
MATCH (o:Organization)-[r:HAS_CEO]->(p:Person)
CREATE (o)-[:LED_BY]->(p)
DELETE r;

// 2. Backfill a denormalised primary industry (alphabetically first category).
MATCH (o:Organization)
WHERE EXISTS { (o)-[:HAS_CATEGORY]->() }
SET o.industry = COLLECT { MATCH (o)-[:HAS_CATEGORY]->(c)
                           RETURN c.name ORDER BY c.name LIMIT 1 }[0];

// 3. Summaries are only shown on company pages with a CEO; drop the rest.
MATCH (o:Organization)
WHERE NOT EXISTS { (o)-[:LED_BY]->() }
REMOVE o.summary;

// 4. Dedupe parallel IN_CITY edges (same org, same city).
MATCH (o:Organization)-[r:IN_CITY]->(c:City)
WITH o, collect(r) AS rels
WHERE size(rels) > 1
FOREACH (r IN tail(rels) | DELETE r)

Recorded result

(no rows) · relationships created: 68, relationships deleted: 420, properties set: 95

The counters are the first hint: 68 relationships were created for the rename, but far more than 68 were deleted.

Ask for a verdict

One read turns the fingerprint into a pass or fail, the way a CI gate would. It recounts every probe, compares each organization's relationships with its expected list, and checks every property change against the contract: industry may appear on an organization with a category (and must), summary may disappear from an organization without a CEO, and nothing else may change.

Step 6

CALL () {
  MATCH (p:Probe)
  WITH p, CASE p.kind
            WHEN 'nodes' THEN COUNT { MATCH (n) WHERE p.key IN labels(n) }
            ELSE COUNT { MATCH ()-[r]->() WHERE type(r) = p.key }
          END AS after
  WITH p, after
  WHERE after <> p.expected
  RETURN collect(p.name) AS failing_probes
}
CALL () {
  MATCH (f:Fingerprint)
  MATCH (o:Organization {id: f.org})
  WITH f, COLLECT { MATCH (o)-[r]->(x) RETURN type(r) + ':' + x.id } AS now
  WITH f, now
  WHERE any(e IN f.expected WHERE NOT e IN now)
     OR any(e IN now WHERE NOT e IN f.expected)
  RETURN count(f) AS orgs_with_changed_relationships
}
CALL () {
  MATCH (f:Fingerprint)
  MATCH (o:Organization {id: f.org})
  WITH f, o, [k IN keys(o) | k + '=' + toString(o[k])] AS now
  WITH f, o, now
  WHERE any(e IN f.props WHERE NOT e IN now
                               AND NOT (e STARTS WITH 'summary=' AND NOT f.hadCeo))
     OR any(k IN keys(o) WHERE NOT k IN f.propKeys
                               AND NOT (k = 'industry' AND f.hadCategory))
     OR (f.hadCategory AND o.industry IS NULL)
  RETURN count(f) AS orgs_with_unexpected_properties
}
RETURN CASE WHEN size(failing_probes) = 0 AND orgs_with_changed_relationships = 0
                 AND orgs_with_unexpected_properties = 0
            THEN 'PASS' ELSE 'FAIL' END AS verdict,
       failing_probes, orgs_with_changed_relationships, orgs_with_unexpected_properties

CALL () {
  MATCH (p:Probe)
  WITH p, CASE p.kind
            WHEN 'nodes' THEN COUNT { MATCH (n) WHERE p.key IN labels(n) }
            ELSE COUNT { MATCH ()-[r]->() WHERE type(r) = p.key }
          END AS after
  WITH p, after
  WHERE after <> p.expected
  RETURN collect(p.name) AS failing_probes
}
CALL () {
  MATCH (f:Fingerprint)
  MATCH (o:Organization {id: f.org})
  WITH f, COLLECT { MATCH (o)-[r]->(x) RETURN type(r) + ':' + x.id } AS now
  WITH f, now
  WHERE any(e IN f.expected WHERE NOT e IN now)
     OR any(e IN now WHERE NOT e IN f.expected)
  RETURN count(f) AS orgs_with_changed_relationships
}
CALL () {
  MATCH (f:Fingerprint)
  MATCH (o:Organization {id: f.org})
  WITH f, o, [k IN keys(o) | k + '=' + toString(o[k])] AS now
  WITH f, o, now
  WHERE any(e IN f.props WHERE NOT e IN now
                               AND NOT (e STARTS WITH 'summary=' AND NOT f.hadCeo))
     OR any(k IN keys(o) WHERE NOT k IN f.propKeys
                               AND NOT (k = 'industry' AND f.hadCategory))
     OR (f.hadCategory AND o.industry IS NULL)
  RETURN count(f) AS orgs_with_unexpected_properties
}
RETURN CASE WHEN size(failing_probes) = 0 AND orgs_with_changed_relationships = 0
                 AND orgs_with_unexpected_properties = 0
            THEN 'PASS' ELSE 'FAIL' END AS verdict,
       failing_probes, orgs_with_changed_relationships, orgs_with_unexpected_properties

Recorded result

verdict	failing_probes	orgs_with_changed_relationships	orgs_with_unexpected_properties
"FAIL"	["rels :IN_CITY"]	61	0

1 row

| verdict | failing_probes    | orgs_with_changed_relationships | orgs_with_unexpected_properties |
|---------|-------------------|---------------------------------|---------------------------------|
| "FAIL"  | ["rels :IN_CITY"] | 61                              | 0                               |
1 row

Note Each subquery repeats its new variables in a second WITH (WITH p, after) before filtering. Inside a CALL (…) {} subquery, Relate currently rejects a WHERE that reads a variable its own WITH just introduced (UndefinedVariable). The same WITH … WHERE works outside the subquery.

Which probes failed

The same recount, probe by probe. Every node count matches, and so does the rename: HAS_CEO went to 0 and LED_BY to 68. A check that stopped at node counts would pass. IN_CITY did not survive.

Step 7

MATCH (p:Probe)
WITH p, CASE p.kind
          WHEN 'nodes' THEN COUNT { MATCH (n) WHERE p.key IN labels(n) }
          ELSE COUNT { MATCH ()-[r]->() WHERE type(r) = p.key }
        END AS after
RETURN p.name AS probe, p.before AS before, after, p.expected AS expected,
       after = p.expected AS ok
ORDER BY probe

Recorded result

probe	before	after	expected	ok
"nodes :City"	26	26	26	true
"nodes :Country"	7	7	7	true
"nodes :IndustryCategory"	10	10	10	true
"nodes :Organization"	90	90	90	true
"nodes :Person"	68	68	68	true
"rels :HAS_CATEGORY"	144	144	144	true
"rels :HAS_CEO"	68	0	0	true
"rels :IN_CITY"	438	86	438	false
"rels :IN_COUNTRY"	26	26	26	true
"rels :LED_BY"	0	68	68	true

10 rows

| probe                     | before | after | expected | ok    |
|---------------------------|--------|-------|----------|-------|
| "nodes :City"             | 26     | 26    | 26       | true  |
| "nodes :Country"          | 7      | 7     | 7        | true  |
| "nodes :IndustryCategory" | 10     | 10    | 10       | true  |
| "nodes :Organization"     | 90     | 90    | 90       | true  |
| "nodes :Person"           | 68     | 68    | 68       | true  |
| "rels :HAS_CATEGORY"      | 144    | 144   | 144      | true  |
| "rels :HAS_CEO"           | 68     | 0     | 0        | true  |
| "rels :IN_CITY"           | 438    | 86    | 438      | false |
| "rels :IN_COUNTRY"        | 26     | 26    | 26       | true  |
| "rels :LED_BY"            | 0      | 68    | 68       | true  |
10 rows

The intended changes happened

Property changes, grouped, each checked against the contract. Only the two intended changes appear, both allowed.

Step 8

MATCH (f:Fingerprint)
MATCH (o:Organization {id: f.org})
WITH f, o, [k IN keys(o) | k + '=' + toString(o[k])] AS now
UNWIND [k IN keys(o) WHERE NOT k IN f.propKeys |
          {change: k + ' added', allowed: k = 'industry' AND f.hadCategory}]
     + [k IN f.propKeys WHERE NOT k IN keys(o) |
          {change: k + ' removed', allowed: k = 'summary' AND NOT f.hadCeo}]
     + [e IN f.props WHERE split(e, '=')[0] IN keys(o) AND NOT e IN now |
          {change: split(e, '=')[0] + ' changed', allowed: false}] AS c
RETURN c.change AS change, c.allowed AS allowed, count(*) AS organizations
ORDER BY change

Recorded result

change	allowed	organizations
"industry added"	true	85
"summary removed"	true	10

2 rows

| change            | allowed | organizations |
|-------------------|---------|---------------|
| "industry added"  | true    | 85            |
| "summary removed" | true    | 10            |
2 rows

Who lost relationships

For each organization, lost is what its expected list has and the graph no longer does, and gained is the reverse. The ten largest losses:

Step 9

MATCH (f:Fingerprint)
MATCH (o:Organization {id: f.org})
WITH f, COLLECT { MATCH (o)-[r]->(x) RETURN type(r) + ':' + x.id } AS now
WITH f, [e IN f.expected WHERE NOT e IN now] AS lost,
        [e IN now WHERE NOT e IN f.expected] AS gained
WHERE size(lost) + size(gained) > 0
RETURN f.name AS organization, size(lost) AS lost, size(gained) AS gained,
       COLLECT { UNWIND lost AS e
                 RETURN DISTINCT split(e, ':')[0] AS type ORDER BY type } AS lost_types
ORDER BY lost DESC, organization
LIMIT 10

Recorded result

organization	lost	lost_types
"Nimbus Labs"	25	["IN_CITY"]
"Cinder Vision"	22	["IN_CITY"]
"Fathom Analytics"	19	["IN_CITY"]
"Meridian AI"	19	["IN_CITY"]
"Cobalt Analytics"	18	["IN_CITY"]
"Lumen Robotics"	18	["IN_CITY"]
"Keel AI"	16	["IN_CITY"]
"Brindle AI"	14	["IN_CITY"]
"Meridian Vision"	14	["IN_CITY"]
"Yarrow Vision"	13	["IN_CITY"]

10 rows

| organization       | lost | gained | lost_types  |
|--------------------|------|--------|-------------|
| "Nimbus Labs"      | 25   | 0      | ["IN_CITY"] |
| "Cinder Vision"    | 22   | 0      | ["IN_CITY"] |
| "Fathom Analytics" | 19   | 0      | ["IN_CITY"] |
| "Meridian AI"      | 19   | 0      | ["IN_CITY"] |
| "Cobalt Analytics" | 18   | 0      | ["IN_CITY"] |
| "Lumen Robotics"   | 18   | 0      | ["IN_CITY"] |
| "Keel AI"          | 16   | 0      | ["IN_CITY"] |
| "Brindle AI"       | 14   | 0      | ["IN_CITY"] |
| "Meridian Vision"  | 14   | 0      | ["IN_CITY"] |
| "Yarrow Vision"    | 13   | 0      | ["IN_CITY"] |
10 rows

Every loss is an IN_CITY link, and nothing was gained. The histogram of city links per organization shows the pattern: every organization that was in two or more cities is now in exactly one.

Step 10

MATCH (f:Fingerprint)
MATCH (o:Organization {id: f.org})
WITH size([e IN f.rels WHERE e STARTS WITH 'IN_CITY:']) AS before,
     COUNT { MATCH (o)-[:IN_CITY]->() } AS after
UNWIND [['before', before], ['after', after]] AS pair
WITH pair[0] AS phase,
     CASE WHEN pair[1] = 0 THEN '0' WHEN pair[1] = 1 THEN '1'
          WHEN pair[1] <= 5 THEN '2-5' WHEN pair[1] <= 20 THEN '6-20'
          ELSE '>20' END AS cities
RETURN cities,
       sum(CASE WHEN phase = 'before' THEN 1 ELSE 0 END) AS before,
       sum(CASE WHEN phase = 'after' THEN 1 ELSE 0 END) AS after
ORDER BY cities

Recorded result

cities	before	after
"0"	4	4
"1"	25	86
"2-5"	45	0
"6-20"	14	0
">20"	2	0

5 rows

| cities | before | after |
|--------|--------|-------|
| "0"    | 4      | 4     |
| "1"    | 25     | 86    |
| "2-5"  | 45     | 0     |
| "6-20" | 14     | 0     |
| ">20"  | 2      | 0     |
5 rows

Nimbus Labs had an office in all 26 cities. The statement from before the migration, run again: one city link is left.

Step 11

MATCH (o:Organization {id: $org}), (x)
WHERE x:City OR EXISTS { (o)-->(x) }
OPTIONAL MATCH (o)-[r]->(x)
RETURN o, r, x
ORDER BY x.id

Parameters

Recorded result

// 29 rows
[{r: null} | (n53:Organization {employees: 80, founded: 2013, id: "org-11", industry: "Education Technology Companies", isPublic: false, name: "Nimbus Labs"}), (n11:City {id: "austin", name: "Austin"})]
[{r: null} | (n53), (n30:City {id: "bangalore", name: "Bangalore"})]
[{r: null} | (n53), (n18:City {id: "berlin", name: "Berlin"})]
[{r: null} | (n53), (n9:City {id: "boston", name: "Boston"})]
[{r: null} | (n53), (n17:City {id: "cambridge", name: "Cambridge"})]
[{r: null} | (n53), (n12:City {id: "chicago", name: "Chicago"})]
[{r: null} | (n53), (n13:City {id: "denver", name: "Denver"})]
[{r: null} | (n53), (n16:City {id: "edinburgh", name: "Edinburgh"})]
[ | (n53), (n53)-[r111:HAS_CATEGORY]->(n42), (n42:IndustryCategory {id: "edtech", name: "Education Technology Companies"})]
[{r: null} | (n53), (n20:City {id: "hamburg", name: "Hamburg"})]
[{r: null} | (n53), (n31:City {id: "hyderabad", name: "Hyderabad"})]
[{r: null} | (n53), (n14:City {id: "london", name: "London"})]
[{r: null} | (n53), (n22:City {id: "lyon", name: "Lyon"})]
[{r: null} | (n53), (n15:City {id: "manchester", name: "Manchester"})]
[{r: null} | (n53), (n25:City {id: "montreal", name: "Montreal"})]
[{r: null} | (n53), (n19:City {id: "munich", name: "Munich"})]
[{r: null} | (n53), (n8:City {id: "new-york", name: "New York"})]
[{r: null} | (n53), (n29:City {id: "osaka", name: "Osaka"})]
[{r: null} | (n53), (n21:City {id: "paris", name: "Paris"})]
[ | (n53), (n53)-[r681:LED_BY]->(n138), (n138:Person {id: "person-06", name: "Kavya Ueda"})]
[{r: null} | (n53), (n32:City {id: "pune", name: "Pune"})]
[ | (n53), (n53)-[r112:HAS_CATEGORY]->(n35), (n35:IndustryCategory {id: "robotics", name: "Robotics Companies"})]
[ | (n53), (n53)-[r284:IN_CITY]->(n7), (n7:City {id: "san-francisco", name: "San Francisco"})]
[{r: null} | (n53), (n10:City {id: "seattle", name: "Seattle"})]
[{r: null} | (n53), (n28:City {id: "tokyo", name: "Tokyo"})]
[{r: null} | (n53), (n24:City {id: "toronto", name: "Toronto"})]
[{r: null} | (n53), (n23:City {id: "toulouse", name: "Toulouse"})]
[{r: null} | (n53), (n26:City {id: "vancouver", name: "Vancouver"})]
[{r: null} | (n53), (n27:City {id: "waterloo", name: "Waterloo"})]

The two results side by side, nodes matched by their id: the rename removed HAS_CEO and added LED_BY, step 2 changed the organization, and step 4 removed 25 of the 26 city links. The cities themselves are still there.

The graph diff compares what cell nimbus returned with what cell victim returned, as recorded. A node is matched by its id property, and a relationship by its type and the id of the node at each end; element ids are not compared, since a store need not keep them from one run to the next.

Nodes: 1 changed, 29 unchanged. Relationships: 26 removed, 1 added, 3 unchanged. − marks what was removed, + what was added and ~ what changed; an unchanged element carries no mark.

Change	Element	What changed
~ changed	Nimbus Labs (:Organization, id "org-11")	industry added: "Education Technology Companies"
− removed	Nimbus Labs -[:HAS_CEO]-> Kavya Ueda	-
− removed	Nimbus Labs -[:IN_CITY]-> Austin	-
− removed	Nimbus Labs -[:IN_CITY]-> Bangalore	-
− removed	Nimbus Labs -[:IN_CITY]-> Berlin	-
− removed	Nimbus Labs -[:IN_CITY]-> Boston	-
− removed	Nimbus Labs -[:IN_CITY]-> Cambridge	-
− removed	Nimbus Labs -[:IN_CITY]-> Chicago	-
− removed	Nimbus Labs -[:IN_CITY]-> Denver	-
− removed	Nimbus Labs -[:IN_CITY]-> Edinburgh	-
− removed	Nimbus Labs -[:IN_CITY]-> Hamburg	-
− removed	Nimbus Labs -[:IN_CITY]-> Hyderabad	-
− removed	Nimbus Labs -[:IN_CITY]-> London	-
− removed	Nimbus Labs -[:IN_CITY]-> Lyon	-
− removed	Nimbus Labs -[:IN_CITY]-> Manchester	-
− removed	Nimbus Labs -[:IN_CITY]-> Montreal	-
− removed	Nimbus Labs -[:IN_CITY]-> Munich	-
− removed	Nimbus Labs -[:IN_CITY]-> New York	-
− removed	Nimbus Labs -[:IN_CITY]-> Osaka	-
− removed	Nimbus Labs -[:IN_CITY]-> Paris	-
− removed	Nimbus Labs -[:IN_CITY]-> Pune	-
− removed	Nimbus Labs -[:IN_CITY]-> Seattle	-
− removed	Nimbus Labs -[:IN_CITY]-> Tokyo	-
− removed	Nimbus Labs -[:IN_CITY]-> Toronto	-
− removed	Nimbus Labs -[:IN_CITY]-> Toulouse	-
− removed	Nimbus Labs -[:IN_CITY]-> Vancouver	-
− removed	Nimbus Labs -[:IN_CITY]-> Waterloo	-
+ added	Nimbus Labs -[:LED_BY]-> Kavya Ueda	-

What changed between cells nimbus and victim, as recorded

Live: the diff is drawn in a graph view beside the table of changes, and Compare runs the page's cells again, from the first to the after cell and with any edit you made to them, on a separate in-memory database in your browser, apart from the page's database, then draws the new diff; choosing a row of the table marks its element in the view.

The bug

Step 4 groups the city links by organization only, so tail(rels) deletes all but one link per organization, not all but one per city. The intended statement groups by organization and city:

 MATCH (o:Organization)-[r:IN_CITY]->(c:City)
-WITH o, collect(r) AS rels
+WITH o, c, collect(r) AS rels
 WHERE size(rels) > 1
 FOREACH (r IN tail(rels) | DELETE r);

Repair from the fingerprint

The companion fixes the migration and reruns it on a fresh copy of the golden file. A single database cannot rewind, but the fingerprint recorded every city link, so the lost ones can be put back. The victims query showed that IN_CITY is the only type that lost anything. IN_CITY links carry no properties, so a recreated link matches the lost one in everything but its internal id.

Step 12

MATCH (f:Fingerprint)
MATCH (o:Organization {id: f.org})
UNWIND f.rels AS e
WITH o, split(e, ':') AS part
WHERE part[0] = 'IN_CITY'
MATCH (c:City {id: part[1]})
WHERE NOT EXISTS { (o)-[:IN_CITY]->(c) }
CREATE (o)-[:IN_CITY]->(c)

Recorded result

(no rows) · relationships created: 352

Now the fixed step 4. The golden graph has no duplicate city links, so it deletes nothing, which is what a dedupe of clean data should do:

Step 13

MATCH (o:Organization)-[r:IN_CITY]->(c:City)
WITH o, c, collect(r) AS rels
WHERE size(rels) > 1
FOREACH (r IN tail(rels) | DELETE r)

Recorded result

(no rows)

Steps 1 to 3 already did what the contract asks, so they need no rerun. The verdict, unchanged:

Step 14

CALL () {
  MATCH (p:Probe)
  WITH p, CASE p.kind
            WHEN 'nodes' THEN COUNT { MATCH (n) WHERE p.key IN labels(n) }
            ELSE COUNT { MATCH ()-[r]->() WHERE type(r) = p.key }
          END AS after
  WITH p, after
  WHERE after <> p.expected
  RETURN collect(p.name) AS failing_probes
}
CALL () {
  MATCH (f:Fingerprint)
  MATCH (o:Organization {id: f.org})
  WITH f, COLLECT { MATCH (o)-[r]->(x) RETURN type(r) + ':' + x.id } AS now
  WITH f, now
  WHERE any(e IN f.expected WHERE NOT e IN now)
     OR any(e IN now WHERE NOT e IN f.expected)
  RETURN count(f) AS orgs_with_changed_relationships
}
CALL () {
  MATCH (f:Fingerprint)
  MATCH (o:Organization {id: f.org})
  WITH f, o, [k IN keys(o) | k + '=' + toString(o[k])] AS now
  WITH f, o, now
  WHERE any(e IN f.props WHERE NOT e IN now
                               AND NOT (e STARTS WITH 'summary=' AND NOT f.hadCeo))
     OR any(k IN keys(o) WHERE NOT k IN f.propKeys
                               AND NOT (k = 'industry' AND f.hadCategory))
     OR (f.hadCategory AND o.industry IS NULL)
  RETURN count(f) AS orgs_with_unexpected_properties
}
RETURN CASE WHEN size(failing_probes) = 0 AND orgs_with_changed_relationships = 0
                 AND orgs_with_unexpected_properties = 0
            THEN 'PASS' ELSE 'FAIL' END AS verdict,
       failing_probes, orgs_with_changed_relationships, orgs_with_unexpected_properties

Recorded result

verdict	failing_probes	orgs_with_changed_relationships	orgs_with_unexpected_properties
"PASS"	[]	0	0

1 row

| verdict | failing_probes | orgs_with_changed_relationships | orgs_with_unexpected_properties |
|---------|----------------|---------------------------------|---------------------------------|
| "PASS"  | []             | 0                               | 0                               |
1 row

Nimbus Labs is back in all 26 cities:

Step 15

MATCH (o:Organization {id: $org}), (x)
WHERE x:City OR EXISTS { (o)-->(x) }
OPTIONAL MATCH (o)-[r]->(x)
RETURN o, r, x
ORDER BY x.id

Parameters

Recorded result

// 29 rows
[ | (n53:Organization {employees: 80, founded: 2013, id: "org-11", industry: "Education Technology Companies", isPublic: false, name: "Nimbus Labs"}), (n53)-[r776:IN_CITY]->(n11), (n11:City {id: "austin", name: "Austin"})]
[ | (n53), (n53)-[r768:IN_CITY]->(n30), (n30:City {id: "bangalore", name: "Bangalore"})]
[ | (n53), (n53)-[r769:IN_CITY]->(n18), (n18:City {id: "berlin", name: "Berlin"})]
[ | (n53), (n53)-[r778:IN_CITY]->(n9), (n9:City {id: "boston", name: "Boston"})]
[ | (n53), (n53)-[r777:IN_CITY]->(n17), (n17:City {id: "cambridge", name: "Cambridge"})]
[ | (n53), (n53)-[r766:IN_CITY]->(n12), (n12:City {id: "chicago", name: "Chicago"})]
[ | (n53), (n53)-[r787:IN_CITY]->(n13), (n13:City {id: "denver", name: "Denver"})]
[ | (n53), (n53)-[r771:IN_CITY]->(n16), (n16:City {id: "edinburgh", name: "Edinburgh"})]
[ | (n53), (n53)-[r111:HAS_CATEGORY]->(n42), (n42:IndustryCategory {id: "edtech", name: "Education Technology Companies"})]
[ | (n53), (n53)-[r770:IN_CITY]->(n20), (n20:City {id: "hamburg", name: "Hamburg"})]
[ | (n53), (n53)-[r789:IN_CITY]->(n31), (n31:City {id: "hyderabad", name: "Hyderabad"})]
[ | (n53), (n53)-[r788:IN_CITY]->(n14), (n14:City {id: "london", name: "London"})]
[ | (n53), (n53)-[r767:IN_CITY]->(n22), (n22:City {id: "lyon", name: "Lyon"})]
[ | (n53), (n53)-[r765:IN_CITY]->(n15), (n15:City {id: "manchester", name: "Manchester"})]
[ | (n53), (n53)-[r782:IN_CITY]->(n25), (n25:City {id: "montreal", name: "Montreal"})]
[ | (n53), (n53)-[r783:IN_CITY]->(n19), (n19:City {id: "munich", name: "Munich"})]
[ | (n53), (n53)-[r786:IN_CITY]->(n8), (n8:City {id: "new-york", name: "New York"})]
[ | (n53), (n53)-[r772:IN_CITY]->(n29), (n29:City {id: "osaka", name: "Osaka"})]
[ | (n53), (n53)-[r774:IN_CITY]->(n21), (n21:City {id: "paris", name: "Paris"})]
[ | (n53), (n53)-[r681:LED_BY]->(n138), (n138:Person {id: "person-06", name: "Kavya Ueda"})]
[ | (n53), (n53)-[r780:IN_CITY]->(n32), (n32:City {id: "pune", name: "Pune"})]
[ | (n53), (n53)-[r112:HAS_CATEGORY]->(n35), (n35:IndustryCategory {id: "robotics", name: "Robotics Companies"})]
[ | (n53), (n53)-[r284:IN_CITY]->(n7), (n7:City {id: "san-francisco", name: "San Francisco"})]
[ | (n53), (n53)-[r779:IN_CITY]->(n10), (n10:City {id: "seattle", name: "Seattle"})]
[ | (n53), (n53)-[r785:IN_CITY]->(n28), (n28:City {id: "tokyo", name: "Tokyo"})]
[ | (n53), (n53)-[r773:IN_CITY]->(n24), (n24:City {id: "toronto", name: "Toronto"})]
[ | (n53), (n53)-[r781:IN_CITY]->(n23), (n23:City {id: "toulouse", name: "Toulouse"})]
[ | (n53), (n53)-[r775:IN_CITY]->(n26), (n26:City {id: "vancouver", name: "Vancouver"})]
[ | (n53), (n53)-[r784:IN_CITY]->(n27), (n27:City {id: "waterloo", name: "Waterloo"})]

Clean up

The probes and fingerprints were scaffolding for the rehearsal. Delete them before the graph is used for anything else:

Step 16

MATCH (n)
WHERE n:Probe OR n:Fingerprint
DELETE n

Recorded result

(no rows) · nodes deleted: 100

About the data

Dataset: A synthetic company graph of 90 organizations, 68 CEOs, 10 industry categories, and 26 cities in 7 countries; 201 nodes and 676 relationships (26 KB)
Source: Synthetic, generated with a fixed seed for this recipe. The graph's shape and the migration follow the examples project's 08-migration-regression, whose golden data is not reproduced here
License: Apache-2.0

Every data block is published beside this page as data/<name>.json, and the whole recipe as markdown.

Agent memory

Search and retrieval

Data quality

Graph analytics

Geospatial

Rehearse a migration against a golden graph

Load the golden graph

Fingerprint it before anything changes

Run the migration

Ask for a verdict

Which probes failed

The intended changes happened

Who lost relationships

The bug

Repair from the fingerprint

Clean up

About the data

More in Data quality