Quality is not optional. It's our standard. Free QA tools for testers and developers.

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

SymptomTypical Root Cause in Test Data
Intermittent “row not found” failuresMissing or stale reference rows (lookup tables, config)
Constraint‑violation errors on INSERTDuplicate primary keys, FK violations, NOT‑NULL breaches
Performance spikes that disappear after a DB restartStatistics out of date, fragmented indexes, unrealistic volume
False‑positive security alertsProduction‑like PII left in non‑prod environments
Tests that pass locally but fail in CIDifferent 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

#AreaOwnerFrequencyAutomation‑Ready?
1Schema AlignmentDB‑Admin / DevEvery schema change✅ (schema diff)
2Referential IntegrityQA LeadEach data refresh✅ (FK validation scripts)
3Data Freshness & VolumeData EngineerDaily / per sprint✅ (row‑count & age queries)
4Constraint & Business Rule CoverageQA EngineerEach new test case✅ (rule‑engine checks)
5PII / Sensitive Data SanitisationSecurity / DBAEvery environment provision✅ (masking verification)
6Index & Statistics HealthDBAWeekly✅ (stats freshness query)
7Deterministic Seed & VersioningTest‑Automation EngineerEvery test‑run✅ (seed hash)
8Clean‑up / Rollback PlanDevOpsEach 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

QuestionYes → ActionNo → Action
Does db/migration/ contain a migration that has not been applied to the test DB?Apply migration, then re‑run gateInvestigate drift; rollback or create a migration
Are there any orphan objects (tables, views, functions) not tracked in version control?Add to version control or dropDocument 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.

MetricTarget (example)Query
Row count per core table≥ 10 k rows for orders, ≥ 5 k for customersSELECT relname, n_live_tup FROM pg_stat_user_tables WHERE schemaname='public';
Max created_at age≤ 30 days for transactional tablesSELECT max(created_at) FROM orders;
Distribution of status valuesRoughly 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.

RuleData ConditionVerification
“Order total must be > 0”CHECK (total_amount > 0) on ordersSELECT COUNT(*) FROM orders WHERE total_amount <= 0;
“Customer email unique”UNIQUE (email) on customersSELECT 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 CHECK constraints exercised by at least one test case.
  • All UNIQUE indexes 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

  1. Masking Audit – Run a regex scan on columns flagged as sensitive (email, ssn, phone, address).
  2. Tokenisation Check – Verify that tokenised columns contain only surrogate keys (e.g., UUIDs) and no reversible encryption.
  3. 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.

CheckQueryPass Criteria
pg_stat_clear_snapshot ageSELECT now() - stats_reset FROM pg_stat_database WHERE datname = current_database();< 24 h
Index bloatSELECT * FROM pgstattuple_approx('orders') WHERE index_bloat > 0.2;No index > 20 % bloat
Missing indexes on FK columnsSELECT * 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

ApproachProsCons
SQL dump + pg_restoreSimple, full fidelitySlow for > 10 GB
Flyway/Liquibase baseline + seed scriptsVersioned, incrementalRequires discipline
Snapshot‑as‑code (e.g., dbt seed)Declarative, diffableLimited to static data
Test‑data generator (QA3 free tool)Fast, configurable, can produce realistic distributionsNeeds schema metadata

Recommended Pattern

  1. Store a seed manifest (seed_manifest.json) that lists:
    • Table name
    • Row count target
    • Generation strategy (static CSV, synthetic, copy‑from‑prod‑masked)
  2. CI step runs qa3-test-data-generator --manifest seed_manifest.json --output /tmp/seed.sql (see the free generator at /tools/test-data-generator).
  3. The generated SQL is applied in a transaction; the transaction hash (sha256(seed.sql)) is stored as an artifact.
  4. 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

ArtifactLocationTrigger
teardown.sqlqa/sql/teardown.sqlPost‑test CI step (always runs)
snapshot_before_testPG pg_basebackup or logical snapshotPre‑test CI step (optional for heavy suites)
rollback.shscripts/rollback.shManual 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.sql runs 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

PitfallSymptomMitigation
Schema drift between environmentsGate 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.