Hospital Referral Network: Graph DML (INSERT, UPDATE, DELETE)

Mutate a live graph via standard DML on its backing Delta tables, verify changes with Cypher, and re-run algorithms on the modified topology.

Syntax

-- Setup: 30 physicians, 75 referrals
CREATE GRAPH external.hospital_referrals.hospital_referrals
  VERTEX TABLE external.hospital_referrals.physicians
    ID COLUMN id NODE TYPE COLUMN specialty NODE NAME COLUMN name
  EDGE TABLE external.hospital_referrals.referrals
    SOURCE COLUMN src TARGET COLUMN dst
    WEIGHT COLUMN weight EDGE TYPE COLUMN referral_type
  DIRECTED;
CREATE GRAPHCSR external.hospital_referrals.hospital_referrals;

-- 1. INSERT a new vertex
INSERT INTO external.hospital_referrals.physicians
VALUES (31, 'Dr. Rivera_31', 'Emergency', 'Memorial', 15, true);

-- Verify the new vertex is visible via Cypher
USE external.hospital_referrals.hospital_referrals
MATCH (n) WHERE n.id = 31
RETURN n.id AS id, n.name AS name, n.specialty AS specialty,
       n.hospital AS hospital, n.years_exp AS years_exp;

-- 2. INSERT a new edge connecting the new vertex to the existing graph
INSERT INTO external.hospital_referrals.referrals
VALUES (9999, 31, 1, 0.9, 'consult', '2025-06-15', 'active');

-- 3. UPDATE: escalate Dr. Chen_1's outgoing referrals to urgent
UPDATE external.hospital_referrals.referrals
SET weight = 1.0, status = 'urgent'
WHERE src = 1;

-- Verify the update via Cypher
USE external.hospital_referrals.hospital_referrals
MATCH (a)-[r]->(b) WHERE a.id = 1
RETURN b.id AS dst_id, r.weight AS weight, r.status AS status
ORDER BY b.id;

-- 4. DELETE completed referrals
DELETE FROM external.hospital_referrals.referrals
WHERE status = 'completed';

-- Verify deletion
ASSERT ROW_COUNT = 0
USE external.hospital_referrals.hospital_referrals
MATCH (a)-[r]->(b) WHERE r.status = 'completed'
RETURN a.id AS src, b.id AS dst;

-- 5. Algorithms on the mutated graph
USE external.hospital_referrals.hospital_referrals
CALL algo.degree() YIELD node_id, in_degree, out_degree, total_degree
RETURN node_id, in_degree, out_degree, total_degree
ORDER BY total_degree DESC;

USE external.hospital_referrals.hospital_referrals
CALL algo.pageRank({dampingFactor: 0.85, iterations: 20})
YIELD node_id, score, rank
RETURN node_id, score, rank ORDER BY score DESC;

Description

## When to Use When your graph changes over time, new vertices appear, edges are reweighted, stale edges are purged, and you need to understand how DeltaForge propagates table-level DML into live Cypher results. The hospital referral dataset (30 physicians, 75 referral edges initially) is mutated by adding a new Emergency Medicine physician (Dr. Rivera_31), connecting her to the existing network, escalating a set of referrals to urgent priority, and purging completed cases. Every mutation is verified via Cypher before moving on. ## What You Will Learn 1. Graph mutations happen at the **Delta table** layer: `INSERT INTO physicians ...`, `UPDATE referrals SET weight = 1.0 WHERE src = 1`, `DELETE FROM referrals WHERE status = 'completed'`. There is no separate `CREATE VERTEX` / `CREATE EDGE` syntax. 2. The graph definition is logical, not a snapshot: Cypher queries read through to the current state of the backing Delta tables, so DML results are visible immediately on the next MATCH. 3. Verify each mutation with a pattern query: after `INSERT`, `MATCH (n) WHERE n.id = 31 RETURN ...`; after `UPDATE`, `MATCH (a)-[r]->(b) WHERE a.id = 1 RETURN r.weight, r.status`; after `DELETE`, assert `ROW_COUNT = 0` for the predicate you just removed. 4. Algorithm results (PageRank, degree) reflect the post-mutation graph, a newly inserted vertex with a single outgoing edge shows `in_degree = 0, out_degree = 1, total_degree = 1`. 5. CSR cache invalidation: after significant DML, re-run `CREATE GRAPHCSR <graph>` to refresh the on-disk topology; otherwise the next cold load rebuilds it automatically from Delta. ## Prerequisites - A graph defined on Delta-backed vertex and edge tables (not external/Parquet files). - Understanding that the vertex and edge tables are independent Delta tables: FK integrity is not enforced by the graph definition.

Pitfalls

• DML targets the Delta tables, not the graph. There is no `CREATE VERTEX (n:Person {...})` syntax, use `INSERT INTO physicians VALUES (...)`.
• FK integrity between edge src/dst and vertex IDs is not enforced by the graph definition. Inserting an edge with a src/dst that points to a non-existent vertex will silently produce a dangling edge that Cypher skips. Validate FK invariants in setup with a `WHERE NOT EXISTS` SELECT.
• The CSR topology is cached in a .dcsr sidecar that becomes stale after a large batch of edge inserts/deletes. The engine rebuilds it on next cold load, but you can force a refresh with `CREATE GRAPHCSR <graph>` after bulk DML to avoid a slow first query.
• Graph mutations do not participate in Cypher transactions: INSERT/UPDATE/DELETE commit to Delta through the SQL transaction, and Cypher reads always see the committed table state.
• UPDATE and DELETE require the edge table to be Delta (not a read-only external source). If you built a graph on a CSV-backed external table, mutations are not supported.
• PageRank on the mutated graph reports 31 rows after the INSERT, not 30, any row count asserts written against the pre-mutation graph need to be updated after DML.

See Also

• demo_graph_karate_club_basics
• demo_graph_advanced_cypher_research
• demo_graph_sql_cypher_mix