Handling Complex Data Scenarios

LOAD CSV WITH HEADERS FROM 'file:///employees.csv' AS row
CREATE (e:Employee {
  employeeID: toInteger(row.employee_id),
  firstName: row.first_name,
  lastName: row.last_name
})

LOAD CSV WITH HEADERS FROM 'file:///employees.csv' AS row
MATCH (e:Employee {employeeID: toInteger(row.employee_id)})
MATCH (m:Employee {employeeID: toInteger(row.reports_to)})
CREATE (e)-[:REPORTS_TO]->(m)

:auto LOAD CSV WITH HEADERS FROM 'file:///large_file.csv' AS row
CALL {
  WITH row
  CREATE (n:Node {id: row.id, name: row.name})
} IN TRANSACTIONS OF 10000 ROWS

USING PERIODIC COMMIT 10000
LOAD CSV WITH HEADERS FROM 'file:///large_file.csv' AS row
CREATE (n:Node {id: row.id})

// Create all constraints first
CREATE CONSTRAINT customer_id IF NOT EXISTS
FOR (c:Customer) REQUIRE c.customerID IS UNIQUE

CREATE CONSTRAINT order_id IF NOT EXISTS
FOR (o:Order) REQUIRE o.orderID IS UNIQUE

CREATE CONSTRAINT product_id IF NOT EXISTS
FOR (p:Product) REQUIRE p.productID IS UNIQUE

// Bad: Creates duplicates on re-run
LOAD CSV WITH HEADERS FROM 'file:///customers.csv' AS row
CREATE (c:Customer {customerID: row.customer_id, companyName: row.company_name})

// Good: Idempotent - safe to re-run
LOAD CSV WITH HEADERS FROM 'file:///customers.csv' AS row
MERGE (c:Customer {customerID: row.customer_id})
SET c.companyName = row.company_name,
    c.contactName = row.contact_name,
    c.city = row.city,
    c.country = row.country

// Bad: May fail silently or create unexpected results
LOAD CSV WITH HEADERS FROM 'file:///customers.csv' AS row
MERGE (c:Customer {customerID: row.customer_id})
SET c.region = row.region

// Good: Explicit NULL handling
LOAD CSV WITH HEADERS FROM 'file:///customers.csv' AS row
MERGE (c:Customer {customerID: row.customer_id})
SET c.companyName = row.company_name,
    c.region = CASE WHEN row.region IS NOT NULL AND row.region <> ''
                    THEN row.region
                    ELSE null END

// Bad: Silently fails if customer does not exist
LOAD CSV WITH HEADERS FROM 'file:///orders.csv' AS row
MATCH (c:Customer {customerID: row.customer_id})
MATCH (o:Order {orderID: toInteger(row.order_id)})
CREATE (c)-[:PLACED]->(o)

// Good: Log missing references
LOAD CSV WITH HEADERS FROM 'file:///orders.csv' AS row
OPTIONAL MATCH (c:Customer {customerID: row.customer_id})
WITH row, c
WHERE c IS NULL
RETURN row.order_id AS orderWithMissingCustomer, row.customer_id AS missingCustomerID

// Check for orphan foreign keys before import
LOAD CSV WITH HEADERS FROM 'file:///orders.csv' AS row
WITH row.customer_id AS custId
WHERE NOT EXISTS ((c:Customer {customerID: custId}))
RETURN DISTINCT custId AS missingCustomer

// Import order and its details in one transaction
:auto LOAD CSV WITH HEADERS FROM 'file:///orders_with_details.csv' AS row
CALL {
  WITH row
  MERGE (o:Order {orderID: toInteger(row.order_id)})
  SET o.orderDate = date(row.order_date)

  WITH o, row
  MATCH (p:Product {productID: toInteger(row.product_id)})
  MERGE (o)-[r:CONTAINS]->(p)
  SET r.quantity = toInteger(row.quantity),
      r.unitPrice = toFloat(row.unit_price)
} IN TRANSACTIONS OF 1000 ROWS

// Before import: Record starting counts
MATCH (n)
RETURN labels(n)[0] AS label, COUNT(*) AS beforeCount

// After import: Compare counts
MATCH (n)
RETURN labels(n)[0] AS label, COUNT(*) AS afterCount

// Check for import issues
MATCH (o:Order)
WHERE NOT EXISTS ((:Customer)-[:PLACED]->(o))
RETURN COUNT(o) AS ordersWithoutCustomer

// Create import metadata node
CREATE (i:ImportLog {
  timestamp: datetime(),
  source: 'northwind',
  customersImported: 91,
  ordersImported: 830,
  status: 'COMPLETED'
})

// Remove partially imported data (be careful!)
// First, identify what was imported in the failed batch
MATCH (n)
WHERE n.importBatch = 'batch_2024_01_15'
DETACH DELETE n

// Or remove nodes without expected relationships
MATCH (o:Order)
WHERE NOT EXISTS ((:Customer)-[:PLACED]->(o))
DETACH DELETE o