Skip to content
Contact →

Handling Dependencies Between Data

Updated
25 Sept 2026
5 min read
7 sections
Page 29 of 29
Data Migration Masterclass

Section 5: Designing Data Migration Solutions. Lesson 29.

What is a dependency?

A dependency is a relationship where one thing cannot happen, or cannot be valid, until another thing exists or has finished. An everyday example: a bonus is only paid once the sales target is met, so the payment depends on the target.

Data works the same way. Handling dependencies means knowing which data must be migrated before which, so the target system keeps its integrity.

Table dependencies

In real databases, tables are connected: customers to orders, and orders to order items. You cannot safely migrate an order before its customer exists in the target. Move orders first and you get a broken system: orders pointing at nobody, reports that don't add up, and screens that fail to load.

Parent and child

The most common dependency is parent-child. The parent holds the main record (customers); the child depends on it (orders). In SQL this is enforced with key constraints, which Microsoft describes as the way to enforce integrity between tables:

  • Primary key: uniquely identifies each record in a table, such as customer_id in customers.
  • Foreign key: a column in another table that stores a reference to that primary key, such as customer_id in orders, which says which customer placed the order.
sql
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(customer_id)  -- foreign key
);

-- Fails if customer 9 doesn't exist yet:
INSERT INTO orders VALUES (103, 9);
-- ERROR: insert violates foreign key constraint "orders_customer_id_fkey"

If the target has foreign keys enabled, it rejects any child whose parent isn't there yet. That is why migration order matters.

The rule: parents first, then children

Always migrate the parent data first, then the data that depends on it: customers, then orders, then order items. Ignore the order and you get foreign-key errors, orphaned records, incomplete reports and broken application screens.

Beyond tables

  • Business dependencies: operations that rely on others. Invoices depend on orders, payments depend on invoices, and shipments depend on both orders and addresses.
  • System dependencies: broader structural ordering. Users come before permissions, departments before employees, and products and warehouses before inventory.

A good migration design identifies all of these before execution begins, usually by drawing the dependency graph from the schema's foreign keys and adding the business and system rules the schema doesn't show.

Working out the order: an example

An e-commerce schema with customers, products, orders and order items:

text
customers ──┐
            ├──▶ orders ──┐
            │             ├──▶ order_items
products ───┴─────────────┘

orders      depends on customers
order_items depends on orders AND products
  1. customers (parent)
  2. products (parent)
  3. orders (child of customers)
  4. order_items (child of orders and products)

When each child loads, all of its parents are already in place. Customers and products don't depend on each other, so they can load in either order, or in parallel. Working out an order like this from a dependency graph is called a topological sort.

sql
-- PostgreSQL: list foreign keys to build the dependency graph (child -> parent)
SELECT tc.table_name  AS child_table,
       ccu.table_name AS parent_table
FROM information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'public';

Harder cases

  • Self-references: employees.manager_id points to employees. Load employees with manager_id empty, then update it in a second pass; or load top-down by hierarchy level.
  • Cycles: A needs B and B needs A. Break the cycle by loading one side with the foreign key empty, then filling it in. Alternatively use deferrable constraints (PostgreSQL DEFERRABLE INITIALLY DEFERRED) so the check runs at commit time.
  • Disabling constraints for bulk loads: faster, but you must re-enable them with validation afterwards, for example ALTER TABLE ... VALIDATE CONSTRAINT in PostgreSQL, or WITH CHECK CHECK CONSTRAINT in SQL Server, or orphans slip in unnoticed.
  • New surrogate keys: if the target generates new IDs, keep an old-to-new ID map and use it to rewrite the foreign keys in child tables.
  • Phased migrations: each wave must include the parents its children need, or those parents must already be migrated (lesson 24).

Hands-on: a dependency-aware migration in Google Colab

The lecture builds three datasets in a Colab notebook: customers (parent), orders (its child) and order items (child of orders). It checks every relationship before loading, separates the invalid records, and prints the safe migration order. Reconstructed below and verified to run; the lecture ran it step by step and then as one cell.

python
import pandas as pd
from graphlib import TopologicalSorter

# Parent and children
customers = pd.DataFrame({"customer_id": [1, 2], "name": ["Ali Khan", "Sara Ahmed"]})
orders = pd.DataFrame({"order_id": [101, 102, 103], "customer_id": [1, 2, 9],
                       "total": [120.0, 80.5, 45.0]})
order_items = pd.DataFrame({"item_id": [1001, 1002, 1003], "order_id": [101, 102, 104],
                            "product": ["Mouse", "Monitor", "Keyboard"]})

# 1. Every order must reference an existing customer
orders_ok = orders["customer_id"].isin(customers["customer_id"])
bad_orders = orders[~orders_ok].assign(error="customer_id not found in customers")

# 2. Every item must reference a VALID order (checked after step 1, so failures cascade)
valid_orders = orders[orders_ok]
items_ok = order_items["order_id"].isin(valid_orders["order_id"])
bad_items = order_items[~items_ok].assign(error="order_id not found in valid orders")

# 3. Clean migration sets; failures go to an error file, not silently dropped
valid_items = order_items[items_ok]
pd.concat([bad_orders, bad_items]).to_csv("dependency_errors.csv", index=False)
print(f"{len(customers)} customers, {len(valid_orders)} valid orders, {len(valid_items)} valid items")

# 4. Migration order from the dependency graph: table -> the tables it depends on
depends_on = {"customers": set(), "orders": {"customers"}, "order_items": {"orders"}}
for step, table in enumerate(TopologicalSorter(depends_on).static_order(), 1):
    print(f"Step {step}: migrate {table}")
print("Dependency-aware migration completed.")
text
2 customers, 2 valid orders, 2 valid items
Step 1: migrate customers
Step 2: migrate orders
Step 3: migrate order_items
Dependency-aware migration completed.

dependency_errors.csv:
  order 103 (customer_id 9)  → customer_id not found in customers
  item 1003 Keyboard (order 104) → order_id not found in valid orders

As in the lecture, order 103 has no matching customer and the Keyboard item has no matching order. Both are held back in an error file rather than loaded or dropped silently. Checking items against valid orders, not all orders, matters: if any item belonged to order 103, it would be caught as well. TopologicalSorter in Python's standard library computes the load order from the graph, so adding products as another parent of order_items only needs one more line.

Key takeaways

  • A dependency means one record or task needs another to exist first.
  • Primary keys identify parents; foreign keys in children reference them, and the target enforces this.
  • Migrate parents before children, such as customers and products, then orders, then order items, by topologically sorting the dependency graph.
  • Consider business and system dependencies too, and plan for self-references, cycles and new keys.
  • Validate relationships before loading, and send orphans to error files instead of failing silently.

This completes Section 5: Designing Data Migration Solutions.