Advanced Database Testing & Data Integrity

1 The Hook — Why This Matters

A major NZ bank pushed a schema migration to production: renaming a column from account_balance_nzd to balance. The migration ran. Tests passed. But the mobile app was still calling the old column name in its API calls. For three hours, the API returned NULL for every balance check. Customer support received 14,000 calls. The bank's reputation took a hit.

A senior data integrity tester would have tested the migration by: (1) running it against a production-like replica, (2) verifying both the old and new column names worked during a transition period, (3) checking for cascading deletes or constraint violations, (4) confirming rollback was reversible, and (5) validating API contracts matched the schema change.

Database testing at senior level is about protecting data contracts, preventing silent data loss, and ensuring the entire system adapts to schema changes. It's not just "did the data save"—it's "is the data intact, consistent, and accessible across all systems?"

2 The Rule — The One-Sentence Version

Data integrity is a system property, not a UI property. It spans schema design, triggers, stored procedures, cascading changes, rollback paths, and API compatibility. A single weak link silently corrupts data for thousands of users.

3 The Analogy — Think Of It Like...

4 Watch Me Do It — Step by Step

Here is a complete advanced database testing workflow for a real NZ scenario: testing a payment processing system migration.

1. Document the schema contract Before testing, understand the schema: table structure, column types, constraints, triggers, and stored procedures. Create a schema diagram or document showing relationships.
   -- Example: payments table contract
CREATE TABLE payments (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  amount_nzd DECIMAL(10,2) NOT NULL CHECK (amount_nzd > 0),
  currency VARCHAR(3) DEFAULT 'NZD',
  customer_id BIGINT NOT NULL FOREIGN KEY REFERENCES customers(id),
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE KEY (id, created_at)
);
2. Test constraint enforcement Verify that constraints are actually enforced in the database, not just the application. Try inserting invalid data:
   -- Should FAIL: negative amount violates CHECK
INSERT INTO payments (amount_nzd, customer_id) VALUES (-50.00, 123);
-- Expected: Error "Check constraint violated"

-- Should FAIL: duplicate unique key
INSERT INTO payments (id, amount_nzd, customer_id, created_at)
VALUES (999, 100.00, 123, '2026-04-24 10:00:00');
-- Expected: Error "Duplicate entry"
3. Test transactions and rollback A transaction must be all-or-nothing. If a payment is split across two inserts (payment record + audit log), both must succeed or both must fail.
   START TRANSACTION;
INSERT INTO payments (amount_nzd, customer_id, status)
VALUES (500.00, 123, 'pending');
INSERT INTO payment_audit_log (payment_id, action)
VALUES (LAST_INSERT_ID(), 'created');

-- Simulate error: try to insert invalid audit log
INSERT INTO payment_audit_log (payment_id, action)
VALUES (LAST_INSERT_ID(), NULL); -- This will fail (NOT NULL constraint)

-- Rollback: both inserts should be undone
ROLLBACK;

-- Verify: payment should NOT exist
SELECT * FROM payments WHERE amount_nzd = 500.00;
4. Test cascading deletes and orphaned records If a customer is deleted, what happens to their payments? Are they deleted (cascade), marked as orphaned, or do they block the delete?
   -- Check constraint definition
SHOW CREATE TABLE payments\G

-- If CASCADE DELETE is defined:
DELETE FROM customers WHERE id = 123;
-- Expected: all payments for customer 123 are ALSO deleted

-- Verify no orphans exist
SELECT p.* FROM payments p
LEFT JOIN customers c ON p.customer_id = c.id
WHERE c.id IS NULL; -- Should return 0 rows
5. Test stored procedures and business logic Many systems enforce critical logic in stored procedures, not the application. Test them directly:
   -- Example: stored procedure to process payment
DELIMITER $$
CREATE PROCEDURE process_payment(IN p_payment_id BIGINT)
BEGIN
  DECLARE p_amount DECIMAL(10,2);
  SELECT amount_nzd INTO p_amount FROM payments WHERE id = p_payment_id;
  IF p_amount IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Payment not found';
  END IF;
  UPDATE payments SET status = 'completed' WHERE id = p_payment_id;
  INSERT INTO payment_audit_log (payment_id, action) VALUES (p_payment_id, 'processed');
END$$
DELIMITER ;

-- Test it: call with invalid payment_id
CALL process_payment(99999); -- Should error: "Payment not found"
6. Test schema migrations and reversibility Before running a migration in production, test it on a replica. Verify: it succeeds, it is reversible, data is not corrupted, and all APIs still work.
   -- Migration: rename column amount_nzd to amount
ALTER TABLE payments RENAME COLUMN amount_nzd TO amount;

-- Test 1: Data is intact
SELECT COUNT(*) FROM payments; -- Should match before count

-- Test 2: Old query fails (breaking change for apps using old name)
SELECT amount_nzd FROM payments WHERE id = 123; -- Should error

-- Test 3: New query works
SELECT amount FROM payments WHERE id = 123; -- Should work

-- Test 4: Can rollback if needed
ALTER TABLE payments RENAME COLUMN amount TO amount_nzd;
7. Test NZ Privacy Act 2020 compliance for data retention NZ law requires deletion of personal data when no longer needed. Verify your deletion logic works:
   -- Test: soft delete (mark as deleted, keep for audit)
UPDATE customers SET deleted_at = NOW() WHERE id = 123;

-- Test: queries exclude soft-deleted rows
SELECT * FROM customers WHERE deleted_at IS NULL;
-- Verify: customer 123 is NOT returned

-- Test: hard delete (actually remove)
-- First, archive to backup table
INSERT INTO customers_archive SELECT * FROM customers WHERE id = 123;
-- Then delete
DELETE FROM customers WHERE id = 123;

-- Test: orphaned payments are also deleted or archived
SELECT * FROM payments WHERE customer_id = 123;
-- Should return 0 rows (cascade delete worked) or be in payments_archive
8. Test concurrent access and eventual consistency If multiple processes update the same record, verify they don't corrupt data:
   -- Session 1: Start transaction
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- Lock the row
-- ... some processing ...
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Session 2 (blocked until session 1 commits):
SELECT balance FROM accounts WHERE id = 1; -- Waits for lock

-- Verify: read is consistent, no lost updates
COMMIT;

Common data integrity test matrix:

5 When to Use It / When NOT to Use It

6 Common Mistakes — Don't Do This

7 Now You Try — Payment System Integrity

8 Self-Check — Can You Actually Do This?

Click each question. If you answer all three correctly, you can test data integrity at senior level.

9 Interview Prep — Senior Questions