Database & Backend Testing

1 The Hook

An Auckland insurance company migrates 500,000 policy records from a legacy system to Salesforce Financial Services Cloud. The project runs for eight months. Go-live day: the UI shows all policies loaded. The migration team signs off. Everyone goes home.

A month later, renewals start failing randomly. Customer services are getting calls. Root cause investigation takes two weeks. The migration script had mapped the renewal_date column correctly for policies with a January start date — but incorrectly for every other month. The formula treated month numbers as sequential offsets rather than calendar months. Policies with an April start date showed a renewal date in January of the following year. 487,000 of the 500,000 records were wrong.

The tester had validated the migration through the UI. Policy details looked correct on screen. Had she run SELECT * FROM policies WHERE renewal_date < GETDATE() AND renewal_status = 'Active' and cross-checked ten rows against the legacy extract, she would have found the mismatch before go-live. The rework cost exceeded the original migration budget.

2 The Rule

3 The Analogy

4 Watch Me Do It

SQL test cases for verifying KiwiSaver contributions in a payroll system. These queries run against the test database after a payroll run has been processed.

-- 1. Verify contribution amounts match the calculated expected value
SELECT
  e.employee_id,
  e.ird_number,
  e.kiwisaver_rate,
  p.gross_salary,
  p.kiwisaver_contribution,
  ROUND(p.gross_salary * e.kiwisaver_rate, 2) AS expected_contribution,
  CASE
    WHEN p.kiwisaver_contribution = ROUND(p.gross_salary * e.kiwisaver_rate, 2)
    THEN 'PASS'
    ELSE 'FAIL'
  END AS result
FROM payroll p
JOIN employees e ON p.employee_id = e.employee_id
WHERE p.pay_period = '2026-06'
ORDER BY result DESC;

-- 2. Check for orphaned payroll records (no matching employee)
SELECT p.employee_id
FROM payroll p
LEFT JOIN employees e ON p.employee_id = e.employee_id
WHERE e.employee_id IS NULL;

-- 3. Verify referential integrity on policy table
SELECT COUNT(*) AS orphaned_policies
FROM policies pol
LEFT JOIN customers cust ON pol.customer_id = cust.customer_id
WHERE cust.customer_id IS NULL;

Run the first query. Any row showing FAIL is a defect. The CASE statement does the assertion work for you — you're not eyeballing numbers, you're running a query that produces a verdict.

Run the second query. Any row returned means a payroll record exists with no corresponding employee. That's referential integrity failure. If the count is zero, you pass.

Reading a query plan. Add EXPLAIN before any SELECT and the database shows you how it will execute the query. Look for "Seq Scan" or "Full Table Scan" — these read every row in the table and will be slow on large datasets. "Index Seek" or "Index Scan" means the database is using an index, which is fast. In test environments with small data sets, a full table scan won't hurt. On a table with 10 million rows in production, it can lock the table for minutes.

5 When to Use It

Use database testing when: validating any data migration; verifying calculated or derived fields (totals, rates, dates, balances); confirming batch processing results; checking referential integrity after an import; when the UI shows correct data but a business report is showing wrong figures.

Triggers to go directly to the database: the developer says "the data is correct"; a business report doesn't match the UI; a batch job ran overnight and you need to verify outputs; you're testing a data migration and need to compare source with destination.

Don't replace UI testing. Database queries tell you the data is right. UI tests tell you the data is presented correctly and users can interact with it. Both layers can independently fail. A correct database record can still display incorrectly in the UI.

6 Common Mistakes

7 Now You Try

8 Self-Check

Click each question to reveal the answer.

9 ISTQB Mapping

10 Next Steps