Database Test Data Quality Checklist Before Automation Runs
Database Test Data Quality Checklist Before Automation Runs
When a test suite starts to flake, the first place to look is the data that feeds it. A well‑designed automation framework can only be as reliable as the rows, columns, and relationships it exercises. The checklist below is meant to be printed, shared in a Confluence page, or stored as a markdown file in the repo so every stakeholder—QA engineers, test‑automation engineers, QA leads, developers, and engineering managers—can run through it before a new test run is triggered.
1. Why a Data‑Quality Gate Matters
| Symptom | Typical Root Cause in Test Data |
|---|---|
| Intermittent “row not found” failures | Missing or stale reference rows (lookup tables, config) |
| Constraint‑violation errors on INSERT | Duplicate primary keys, FK violations, NOT‑NULL breaches |
| Performance spikes that disappear after a DB restart | Statistics out of date, fragmented indexes, unrealistic volume |
| False‑positive security alerts | Production‑like PII left in non‑prod environments |
| Tests that pass locally but fail in CI | Different seed data, schema drift, or environment‑specific defaults |
A data‑quality gate catches these problems before the automation suite spends minutes (or hours) executing. The gate is a short, repeatable checklist that can be run manually or scripted as a pre‑flight step in CI/CD.
2. Checklist Overview
| # | Area | Owner | Frequency | Automation‑Ready? |
|---|---|---|---|---|
| 1 | Schema Alignment | DB‑Admin / Dev | Every schema change | ✅ (schema diff) |
| 2 | Referential Integrity | QA Lead | Each data refresh | ✅ (FK validation scripts) |
| 3 | Data Freshness & Volume | Data Engineer | Daily / per sprint | ✅ (row‑count & age queries) |
| 4 | Constraint & Business Rule Coverage | QA Engineer | Each new test case | ✅ (rule‑engine checks) |
| 5 | PII / Sensitive Data Sanitisation | Security / DBA | Every environment provision | ✅ (masking verification) |
| 6 | Index & Statistics Health | DBA | Weekly | ✅ (stats freshness query) |
| 7 | Deterministic Seed & Versioning | Test‑Automation Engineer | Every test‑run | ✅ (seed hash) |
| 8 | Clean‑up / Rollback Plan | DevOps | Each deployment | ✅ (teardown scripts) |
Tip: Treat each row as a gate; the pipeline should stop if any gate returns FAIL. The table above also serves as a RACI matrix for ownership discussions.
3. Detailed Gate Walk‑Through
3.1 Schema Alignment
Goal: Ensure the test database schema matches the version the automation code expects.
Decision Points
| Question | Yes → Action | No → Action |
|---|---|---|
Does db/migration/ contain a migration that has not been applied to the test DB? | Apply migration, then re‑run gate | Investigate drift; rollback or create a migration |
| Are there any orphan objects (tables, views, functions) not tracked in version control? | Add to version control or drop | Document as known exception (rare) |
Worked Example
-- Run in CI before test suite
SELECT
obj_type,
obj_name,
CASE WHEN vc.tracked THEN 'OK' ELSE 'MISSING_IN_VC' END AS status
FROM (
SELECT 'TABLE' AS obj_type, tablename AS obj_name
FROM pg_tables WHERE schemaname = 'public'
UNION ALL
SELECT 'VIEW', viewname FROM pg_views WHERE schemaname = 'public'
) o
LEFT JOIN (
SELECT 'TABLE' AS obj_type, filename AS obj_name, true AS tracked
FROM vc_schema_objects WHERE type = 'TABLE'
UNION ALL
SELECT 'VIEW', filename, true FROM vc_schema_objects WHERE type = 'VIEW'
) vc USING (obj_type, obj_name);
If any row returns MISSING_IN_VC, the gate fails.
Automation Hook – Add the query as a pre-test step in GitHub Actions, GitLab CI, or Azure Pipelines. Fail the job on non‑zero rows.
3.2 Referential Integrity
Goal: All foreign‑key relationships resolve to existing parent rows.
Checklist Items
- Run
SELECT * FROM pg_constraint WHERE contype = 'f' AND NOT convalidated;– any unvalidated FK? - Execute a FK‑coverage script that joins each child table to its parent and counts orphans.
- Verify that lookup tables (e.g.,
status_codes,country_codes) contain the full set of values referenced by test cases.
Sample Orphan Query
SELECT
c.relname AS child_table,
p.relname AS parent_table,
conname AS fk_name,
COUNT(*) AS orphan_rows
FROM pg_constraint con
JOIN pg_class c ON c.oid = con.conrelid
JOIN pg_class p ON p.oid = con.confrelid
JOIN pg_attribute ca ON ca.attrelid = c.oid AND ca.attnum = ANY(con.conkey)
JOIN pg_attribute pa ON pa.attrelid = p.oid AND pa.attnum = ANY(con.confkey)
WHERE con.contype = 'f'
GROUP BY c.relname, p.relname, conname
HAVING COUNT(*) > 0;
Ownership – QA Lead owns the script; DB‑Admin owns the constraint definitions. The script lives in qa/sql/fk_integrity_check.sql and is version‑controlled.
3.3 Data Freshness & Volume
Goal: Test data reflects realistic cardinalities and age distributions.
| Metric | Target (example) | Query |
|---|---|---|
| Row count per core table | ≥ 10 k rows for orders, ≥ 5 k for customers | SELECT relname, n_live_tup FROM pg_stat_user_tables WHERE schemaname='public'; |
Max created_at age | ≤ 30 days for transactional tables | SELECT max(created_at) FROM orders; |
Distribution of status values | Roughly matches production histogram (e.g., 70 % completed, 20 % pending, 10 % canceled) | SELECT status, count(*) FROM orders GROUP BY status; |
Decision Points
- If row counts fall below thresholds → trigger a data‑generation job (see § 3.9).
- If
max(created_at)> 30 days → refresh the snapshot or run a time‑travel script.
Automation – Wrap the queries in a small Python/Go binary that exits non‑zero on any violation. Schedule it as a nightly job and also as a pre‑flight step.
3.4 Constraint & Business Rule Coverage
Goal: Every business rule that the automation asserts has a corresponding data condition.
| Rule | Data Condition | Verification |
|---|---|---|
| “Order total must be > 0” | CHECK (total_amount > 0) on orders | SELECT COUNT(*) FROM orders WHERE total_amount <= 0; |
| “Customer email unique” | UNIQUE (email) on customers | SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1; |
| “Discount cannot exceed 30 %” | Application‑level rule (no DB constraint) | Unit test + integration test that inserts a 35 % discount and expects rejection |
Checklist
- All
CHECKconstraints exercised by at least one test case. - All
UNIQUEindexes have a negative test that attempts a duplicate insert. - Business‑logic rules without DB constraints are covered by contract tests (e.g., Pact, Postman).
Ownership – QA Engineer writes the negative tests; Developer adds missing constraints where feasible.
3.5 PII / Sensitive Data Sanitisation
Goal: No production‑grade personally identifiable information (PII) lives in non‑prod environments.
Verification Steps
- Masking Audit – Run a regex scan on columns flagged as sensitive (
email,ssn,phone,address). - Tokenisation Check – Verify that tokenised columns contain only surrogate keys (e.g., UUIDs) and no reversible encryption.
- Data‑Subset Confirmation – Ensure the test DB contains only the minimum rows needed for coverage (e.g., 5 % of production volume).
Sample Scan (PostgreSQL)
SELECT
table_name,
column_name,
COUNT(*) AS rows_scanned,
SUM(CASE WHEN column_value ~* '^\d{3}-\d{2}-\d{4}$' THEN 1 ELSE 0 END) AS ssn_like,
SUM(CASE WHEN column_value ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$' THEN 1 ELSE 0 END) AS email_like
FROM (
SELECT
c.relname AS table_name,
a.attname AS column_name,
a.attnum,
pg_get_expr(ad.adbin, ad.adrelid) AS column_value
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_attrdef ad ON ad.adrelid = a.attrelid AND ad.adnum = a.attnum
WHERE a.attnum > 0
AND NOT a.attisdropped
AND c.relkind = 'r'
AND a.attname IN ('email','ssn','phone','address')
) sub
GROUP BY table_name, column_name;
If any ssn_like or email_like > 0, the gate fails.
Ownership – Security / DBA owns the masking policy; QA Lead ensures the policy is applied before each test‑run.
3.6 Index & Statistics Health
Goal: Query plans in the test environment resemble production enough to surface performance regressions.
| Check | Query | Pass Criteria |
|---|---|---|
pg_stat_clear_snapshot age | SELECT now() - stats_reset FROM pg_stat_database WHERE datname = current_database(); | < 24 h |
| Index bloat | SELECT * FROM pgstattuple_approx('orders') WHERE index_bloat > 0.2; | No index > 20 % bloat |
| Missing indexes on FK columns | SELECT * FROM pg_indexes WHERE tablename = 'orders' AND indexdef NOT LIKE '%customer_id%'; | All FK columns indexed |
Action – If stats are stale, run ANALYZE; if bloat > threshold, schedule REINDEX CONCURRENTLY in a maintenance window.
Ownership – DBA runs the health check weekly; CI runs a lightweight version (stats age only) before each test suite.
3.7 Deterministic Seed & Versioning
Goal: Every test run starts from a known, reproducible data snapshot.
Implementation Options
| Approach | Pros | Cons |
|---|---|---|
SQL dump + pg_restore | Simple, full fidelity | Slow for > 10 GB |
| Flyway/Liquibase baseline + seed scripts | Versioned, incremental | Requires discipline |
Snapshot‑as‑code (e.g., dbt seed) | Declarative, diffable | Limited to static data |
| Test‑data generator (QA3 free tool) | Fast, configurable, can produce realistic distributions | Needs schema metadata |
Recommended Pattern
- Store a seed manifest (
seed_manifest.json) that lists:- Table name
- Row count target
- Generation strategy (static CSV, synthetic, copy‑from‑prod‑masked)
- CI step runs
qa3-test-data-generator --manifest seed_manifest.json --output /tmp/seed.sql(see the free generator at/tools/test-data-generator). - The generated SQL is applied in a transaction; the transaction hash (
sha256(seed.sql)) is stored as an artifact. - Subsequent test runs compare the stored hash; mismatch → gate failure.
Checklist
- Manifest versioned in repo (
db/seed/manifest.json). - Generator run produces identical output for same manifest (deterministic RNG seed).
- Hash stored as CI artifact and compared on each run.
Ownership – Test‑Automation Engineer owns the manifest and CI integration; Data Engineer reviews the generation strategies quarterly.
3.8 Clean‑up / Rollback Plan
Goal: After a test run (especially a failed one) the test DB returns to a clean baseline without manual intervention.
Artifacts
| Artifact | Location | Trigger |
|---|---|---|
teardown.sql | qa/sql/teardown.sql | Post‑test CI step (always runs) |
snapshot_before_test | PG pg_basebackup or logical snapshot | Pre‑test CI step (optional for heavy suites) |
rollback.sh | scripts/rollback.sh | Manual trigger or on‑call runbook |
Teardown Example
-- qa/sql/teardown.sql
TRUNCATE TABLE orders, order_items, customers, payments RESTART IDENTITY CASCADE;
-- Re‑apply seed
\i /tmp/seed.sql
Checklist
-
teardown.sqlruns in < 30 s for the current data volume. - No leftover temporary tables or sequences.
- Rollback script tested in a staging environment at least once per sprint.
Ownership – DevOps owns the CI pipeline steps; QA Lead validates the teardown timing.
4. Putting It All Together – A Pre‑Flight Pipeline
# .github/workflows/preflight.yml
name: Pre‑Flight Data Quality Gate
on:
workflow_dispatch:
pull_request:
types: [opened, synchronize, reopened]
jobs:
data-quality-gate:
runs-on: ubuntu-latest
timeout-minutes: 15
steps:
- uses: actions/checkout@v4
- name: Spin up test DB (Docker)
run: |
docker run -d --name testdb -e POSTGRES_PASSWORD=secret -p 5432:5432 postgres:16
- name: Apply schema migrations
run: |
./gradlew flywayMigrate -Pdb.url=jdbc:postgresql://localhost:5432/postgres
- name: Generate deterministic seed
id: seed
run: |
qa3-test-data-generator \
--manifest db/seed/manifest.json \
--output /tmp/seed.sql \
--seed 0xC0FFEE
echo "hash=$(sha256sum /tmp/seed.sql | cut -d' ' -f1)" >> $GITHUB_OUTPUT
- name: Load seed
run: |
psql -h localhost -U postgres -d postgres -f /tmp/seed.sql
- name: Run gate checks
env:
SEED_HASH: ${{ steps.seed.outputs.hash }}
run: |
./scripts/run_gates.sh # exits non‑zero on any FAIL
- name: Verify seed hash unchanged
run: |
CURRENT=$(sha256sum /tmp/seed.sql | cut -d' ' -f1)
if [ "$CURRENT" != "$SEED_HASH" ]; then
echo "Seed hash drift detected"
exit 1
fi
All gate scripts (run_gates.sh) are thin wrappers around the SQL queries shown in Sections 3.1‑3.7.
Result: The pipeline fails fast, prints a concise table of which gates passed/failed, and surfaces the exact SQL that caused a failure. No test code runs until the data gate is green.
5. Common Pitfalls & Mitigations
| Pitfall | Symptom | Mitigation |
|---|---|---|
| Schema drift between environments | Gate passes locally, fails in |
Read more
GraphQL Test Data Generation for Queries and Mutations
A practical guide to “GraphQL Test Data Generation for Queries and Mutations,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.
Postman Test Data Generation with CSV and JSON Files
A practical guide to “Postman Test Data Generation with CSV and JSON Files,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.
Generate API Test Data Directly from an OpenAPI Specification
A practical guide to “Generate API Test Data Directly from an OpenAPI Specification,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.