Step 1: define the schema and expected result
Use two customers and three orders. Customer C1 owns O1 and O2; C2 owns O3. Let O1 be open, O2 cancelled and O3 open. An open-orders query must return O1 and O3 only. Keep these identifiers and states in a reviewed fixture definition. This gives you a business oracle: three syntactically valid rows are not enough if the query returns a cancelled order.
Step 2: choose the smallest suitable generator
A values library can supply names while test code owns relationships. A schema generator can produce files or mock responses. A reusable model is worth evaluating when scenarios span entities or systems and must be maintained as project logic. Confirm the target database, exporter, license and runtime from the linked product profiles. Do not select an enterprise platform merely to fill a single test table.
Step 3: generate, assert, repeat and clean up
Assert unique customer and order identifiers, zero orphan orders, the exact open-order set and the expected row counts. Repeat with controlled inputs. For a seeded generator, also control runtime versions and any reference date before promising replay. Test cleanup and a failed insertion. Keep the fixture definition and expected output in version control so a change to either is reviewable.
How do you test relationships and invalid cases?
Create a customer, an account belonging to that customer and a transaction referencing that account. Add one transaction with a nonexistent account as a negative case. Your test must report or reject that record; merely generating three tables is not proof of referential integrity. The downloadable reference scenario demonstrates the expectation in SQLite.
How can a team repeat the same scenario?
Store input and expected output together. Capture the tool version, operating environment, seed and any clock or external lookup used. Run the same assertion after generation and after cleanup. If the result changes, inspect the recorded inputs and tool-specific replay guarantees before updating expected values.
References and further reading
Choose your next step
- Which tool should you use for test fixtures and mock APIs?
- When should you choose model-driven test data generation?
- Which test data generator fits your database?
- What is test data? Types, examples and acceptance checks
- Synthetic test data: rules, learned patterns and hybrid workflows
- Secure test data workflows: access, encryption and dependencies
- Test data without production data: choices and privacy
- Kafka test data: events, ordering and acceptance
- Open source or enterprise test data tools?