Database & Data Testing
Testing the data layer — SQL a tester actually needs, what data integrity means, verifying migrations and bulk operations, stored procedures, referential integrity, index changes, concurrent edits, and proving an export matches its source.
9 questions
JuniorTheoryVery commonWhat is data integrity, and which classes of data defect does a tester look for?
What is data integrity, and which classes of data defect does a tester look for?
Data integrity means stored data still obeys its own rules and still matches reality — correct values and types, no orphan rows, no duplicates, no silent truncation. A tester hunts lost or half-written records, broken parent-child links, constraint violations, and the same fact disagreeing between two tables.
Common mistakes
- ✗Confusing integrity (data is correct) with availability (data is reachable)
- ✗Assuming UI validation makes bad rows impossible in the database
- ✗Never checking for orphan rows or duplicates after a write
Follow-up questions
- →How does a partially applied write produce an integrity defect?
- →Which integrity defects can only be seen by querying the database directly?
MiddleDesignCommonA release migrates 40 million customer rows into a new schema, splitting one address column into four. The script runs once, inside the release window, straight against production. A week beforehand you are handed the script and a restored copy of the production database. How do you validate the migration, and what has to exist before you sign it off?
A release migrates 40 million customer rows into a new schema, splitting one address column into four. The script runs once, inside the release window, straight against production. A week beforehand you are handed the script and a restored copy of the production database. How do you validate the migration, and what has to exist before you sign it off?
Run it against the restored production copy, never synthetic data. Prove the result three ways: row counts per table, checksums or sums over the key columns, and a reconciliation query that re-derives the new columns from the old one and diffs. Time the run, spot-check nulls and unicode, and demand a tested rollback.
Common mistakes
- ✗Validating on a synthetic dataset that lacks production's dirty edge cases
- ✗Trusting a row count while never comparing the values themselves
- ✗Signing off with no rehearsed, timed rollback
Follow-up questions
- →How do you build the expected result without reusing the migration code?
- →What makes the rollback harder than the migration itself?
MiddleTheoryCommonHow do you prove that referential integrity is enforced by the database itself?
How do you prove that referential integrity is enforced by the database itself?
Write the bad data straight to the database, bypassing the app: insert a child row whose foreign key points nowhere, delete a parent that still has children, and change a parent key. The database must reject the write or apply its declared ON DELETE rule. If only the interface complains, the constraint does not exist — app validation is not proof.
Common mistakes
- ✗Testing through the UI, which proves only that the app validates
- ✗Treating a declared constraint as proof it is actually enforced
- ✗Ignoring the ON DELETE rule (cascade, restrict, set null) the schema declares
Follow-up questions
- →How does ON DELETE CASCADE change what your test must assert?
- →Why can a constraint exist in the schema and still not be enforced?
JuniorCodeOccasionalProve with SQL that a bulk price update did exactly what was intended
Prove with SQL that a bulk price update did exactly what was intended
Three queries, not one. Count the rows in the target category and compare with the reported count; join the table to the pre-run snapshot and assert no row inside the category holds a price other than old times 1.10; then assert that no row outside the category changed at all.
Open full question →Common mistakes
- ✗Trusting the affected-row count as the only evidence
- ✗Checking only the rows in scope and never that nothing else changed
- ✗Deriving the expected values with the same statement that made the change
Follow-up questions
- →How would you verify the update if no snapshot table had been taken?
- →Why wrap the whole verification in a transaction you roll back?
MiddleDesignOccasionalSupport reports that two agents occasionally overwrite each other's changes: both open the same customer record, both save, and one agent's edits vanish with no warning. The team suspects a lost-update race condition. You are asked to reproduce it deliberately and to specify what correct behaviour should be. Describe how you would force the race in a test, how you would confirm data was actually lost rather than just believing the report, and what mechanisms the fix could use so the second save is either merged or rejected instead of silently clobbering the first.
Support reports that two agents occasionally overwrite each other's changes: both open the same customer record, both save, and one agent's edits vanish with no warning. The team suspects a lost-update race condition. You are asked to reproduce it deliberately and to specify what correct behaviour should be. Describe how you would force the race in a test, how you would confirm data was actually lost rather than just believing the report, and what mechanisms the fix could use so the second save is either merged or rejected instead of silently clobbering the first.
Force the interleaving on purpose: load the same record in two sessions, submit both so the second lands after the first, then check the stored row against both inputs — a lost update shows the first change gone. Prove it at the database, not the UI. Correct behaviour is optimistic concurrency (a version check rejecting the stale write) or a row lock, so the loser gets an error or a merge.
Common mistakes
- ✗Believing a race cannot be reproduced deliberately
- ✗Confirming the report through the UI instead of the database row
- ✗Accepting silent last-write-wins as correct instead of reject-or-merge
Follow-up questions
- →How does an optimistic version check turn a lost update into a visible error?
- →Why must you confirm the lost update at the database rather than the screen?
MiddleDesignOccasionalA reporting feature exports a customer dataset to a CSV that finance loads into their own system. Before release you must prove the export matches the source data exactly. The export applies filters (only active customers), joins several tables, formats dates and money, and can run to millions of rows. Describe how you would verify the exported file is a faithful representation of the source — row counts, values, and formatting — and how you would catch subtle corruptions such as a truncated field, a wrong timezone, a delimiter sitting inside a value, or a changed character encoding. State what a pass actually requires.
A reporting feature exports a customer dataset to a CSV that finance loads into their own system. Before release you must prove the export matches the source data exactly. The export applies filters (only active customers), joins several tables, formats dates and money, and can run to millions of rows. Describe how you would verify the exported file is a faithful representation of the source — row counts, values, and formatting — and how you would catch subtle corruptions such as a truncated field, a wrong timezone, a delimiter sitting inside a value, or a changed character encoding. State what a pass actually requires.
Reconcile against the source, not by eyeballing the file. Re-derive the expected set with an independent query using the same filters and joins, then compare row count and a checksum or sorted diff of the values. Re-parse the file and assert types — dates, timezone, money precision, encoding — and that a delimiter or newline inside a value is quoted, not splitting a row. A pass is exact count-and-value reconciliation plus a clean re-parse.
Common mistakes
- ✗Eyeballing the file in a spreadsheet instead of reconciling against the source
- ✗Checking row count while never comparing the field values
- ✗Missing quoting bugs when a delimiter or newline sits inside a value
Follow-up questions
- →How do you build the expected result without re-running the export's own query?
- →What breaks when a value contains the CSV delimiter and is not quoted?
MiddleTheoryOccasionalHow do you test a stored procedure, and why do API tests alone not cover it?
How do you test a stored procedure, and why do API tests alone not cover it?
Call it directly from SQL against a seeded dataset: run it with valid, boundary and invalid parameters, and assert both what it returns and the rows it actually changed. Wrap each run in a transaction and roll back. An API test only sees the endpoint response, so branches, error paths and side effects inside the procedure stay unexercised.
Common mistakes
- ✗Assuming a green API test proves the procedure's internal branches ran
- ✗Asserting only the return value and never the rows the procedure changed
- ✗Leaving written data behind so the next run starts from a polluted state
Follow-up questions
- →How would you assert a procedure's behaviour when it is called concurrently?
- →What do you check when a procedure swallows an error and returns success?
SeniorDesignOccasionalLeadership wants confidence that the business can survive losing its primary database. There is a nightly backup and a documented restore runbook, but no one has ever restored it under realistic conditions. You are asked to design a disaster-recovery verification. Describe how you would test that a restore actually works end to end: that the backup is complete and not silently corrupt, that the restored data is consistent and matches what was live at the backup point, how you measure recovery time against the target, and what would make you declare the DR plan unproven despite a backup file existing.
Leadership wants confidence that the business can survive losing its primary database. There is a nightly backup and a documented restore runbook, but no one has ever restored it under realistic conditions. You are asked to design a disaster-recovery verification. Describe how you would test that a restore actually works end to end: that the backup is complete and not silently corrupt, that the restored data is consistent and matches what was live at the backup point, how you measure recovery time against the target, and what would make you declare the DR plan unproven despite a backup file existing.
A backup that was never restored is unproven. Restore it to an isolated environment via the runbook, and time the full recovery against the recovery-time objective (RTO). Verify integrity: row counts and checksums against the source, referential integrity intact, and smoke flows passing on the restored data. Probe the point-in-time boundary and a corrupt-backup path. A failed restore, a missed RTO, or lost data means unproven.
Common mistakes
- ✗Trusting a green backup job without ever restoring the file
- ✗Restoring but never checking integrity against the source
- ✗Treating a read replica as a substitute for a tested backup
Follow-up questions
- →How would you detect a backup that restores but is silently missing rows?
- →Why does a successful backup job not prove the recovery-time target is met?
MiddleDesignRareA developer adds a new composite index and rewrites a slow report query to use it, aiming to cut a 12-second query to under a second. The change ships in the same release as a small data-model tweak, and you own the sign-off. You have a restored copy of production plus the old and new query plans. Describe how you would verify two things: that the indexed query still returns exactly the same rows as before, so correctness is not sacrificed for speed, and that the promised speed-up is real and not a fluke of a warm cache. State what evidence would let you approve or reject the change.
A developer adds a new composite index and rewrites a slow report query to use it, aiming to cut a 12-second query to under a second. The change ships in the same release as a small data-model tweak, and you own the sign-off. You have a restored copy of production plus the old and new query plans. Describe how you would verify two things: that the indexed query still returns exactly the same rows as before, so correctness is not sacrificed for speed, and that the promised speed-up is real and not a fluke of a warm cache. State what evidence would let you approve or reject the change.
Correctness first: run the old and new query on the same restored copy and diff the full result sets — same rows and order under the same ORDER BY, no duplicates or dropped rows. An index that changes the output is a bug, not an optimisation. Then measure speed on a cold cache with production-sized data across several runs. Approve only when results match exactly and the speed-up holds.
Common mistakes
- ✗Timing the new query while never diffing its rows against the old one
- ✗Trusting EXPLAIN uses the index as proof the results are equivalent
- ✗Measuring the speed-up on a warm cache with undersized data
Follow-up questions
- →How could a new partial or filtered index silently drop rows from the result?
- →Why measure on a cold cache rather than the developer's warm run?
