All jobs / Automation developer

Database Testing interview questions

Database Testing questions a Automation developer can be asked, from first job to senior. Open one to see the strongest answer and why it works.

1. Your test creates an order and then the assert fails. What still has to happen?

   The strongest answer: Cleanup in a finally / afterEach that runs on failure — leftover orders poison the next run

   Why it works: Exactly. Failures still leak data. Teardown is part of the test, not a courtesy.

   • Clean up even when you fail.
   • Hooks, not memory.
   • Logs are the evidence.
   • New users are not full isolation.

2. Your API test posts an order, gets 201 back, and stops there.

   How do you prove the order was really stored?

   The strongest answer: Read it back through the product’s own endpoint first, because that is the path users take; go to the database directly only for facts no endpoint exposes, such as an audit row or a soft-delete flag

   Why it works: Exactly — assert through the contract you ship, and drop to the database only where there is none.

   • Verify through the read path first.
   • The database is the fallback, not the default.
   • Use it for facts no API exposes.
   • Do not couple tests to columns.

3. A teammate wants to copy a production database into the test env “so data is real.” What do you say?

   The strongest answer: No — use fake-but-valid fixtures. A prod dump is a PII leak and a moving target, not a test oracle

   Why it works: Exactly. Realism comes from valid shapes, not from real people. Dumps leak and drift.

   • Fake-but-valid fixtures.
   • Prod dumps are leaks.
   • Partial masking is not enough.
   • Laptops are not an exception.

4. The suite uses user_id=1 for everything. What do you replace it with?

   The strongest answer: A factory that builds the user and the relations each test needs, then deletes or transactions them away

   Why it works: Exactly. Factories make the need explicit and keep tests from fighting over row 1.

   • Build what the test needs.
   • Tear it down.
   • No shared famous ids.
   • No god users.

5. A setup script creates “admin@test.com” and fails on the second run. What was missing?

   The strongest answer: Idempotence — upsert, delete-first, or unique names — setup must be safe to run twice

   Why it works: Exactly. Setup is a product. Twice must mean the same world, not a unique-constraint tantrum.

   • Setup must run twice safely.
   • Upsert or unique names.
   • Do not swallow seed errors.
   • Do not rely only on a wipe.

6. API tests share one database and run in parallel. What do you require?

   The strongest answer: Transaction-per-test, truncated tables you own, or isolated schemas — plus no tests that assume leftover rows from a sibling

   Why it works: Exactly. Isolation is a fixture problem. One worker is a surrender you can afford only briefly.

   • Isolate rows or schemas.
   • No sibling leftovers.
   • One worker is a bandage.
   • Do not sleep to dodge races.

7. Five squads invent their own “create order” helpers. What do you build?

   The strongest answer: A supported data service or library — one way to mint entities that match production rules — versioned, with owners, not a wiki of curl snippets

   Why it works: Exactly. Test data is a platform. Curl folklore does not scale to five squads.

   • One supported minting API.
   • Owners and versions.
   • No prod clones.
   • No folklore sheets.

8. Your API tests leave rows behind, and the suite has started failing on unique-constraint errors.

   Which cleanup strategy do you choose, and what decides it?

   The strongest answer: Unique data per test first — a generated email, a generated order reference — so nothing collides, then delete what the test created in teardown. Truncate and reseed only where the suite owns the whole database, never in a shared environment

   Why it works: Right — collision-free data removes most of the problem, and teardown handles the rest without a shared reset.

   • Generate unique data per test.
   • Tear down what you created.
   • Reset only what you own.
   • Rollback needs a shared connection.

9. A factory used a real customer email “to debug faster.” What do you do?

   The strongest answer: Treat it as a leak: purge, rotate if needed, replace with obviously fake addresses that still validate, and add a lint for prod-shaped PII

   Why it works: Exactly. Real emails are people. Fake-but-valid plus lint is the pattern.

   • Purge and replace with fake-but-valid.
   • Lint for prod-shaped PII.
   • Green is not a defence.
   • Private ≠ safe.

10. A test creates a record through the write API and immediately reads it back from a list endpoint served by a read replica. It passes locally and fails in staging perhaps one run in ten.

    What is going on, and what do you do?

    The strongest answer: Replication lag — the read replica has not caught up. The test should poll the read path until the record appears, within a bounded window, and the real question for the team is whether users are promised read-your-writes on that screen

    Why it works: Right — and turning it into a product question is what makes it a fix rather than a workaround.

    • Eventual consistency is not flake.
    • Poll the read path, with a bound.
    • Ask what the product promises.
    • Do not test a path users never take.

11. What does “test data as a service” mean in a grown company?

    The strongest answer: An owned API that mints and expires entities with production rules, with PII controls — not a shared staging tenant everyone clicks

    Why it works: Exactly. Mint, expire, obey rules, no real people. Shared tenants and dumps are how tests fight and leak.

    • Mint and expire via an API.
    • Obey product rules; no PII.
    • No prod restores.
    • No private dumps.