---
name: synthetic_test_data
description: Generate realistic synthetic test data in CSV, JSON, or SQL with believable distributions, referential integrity across tables, row coherence, and PII-safe defaults. Use when seeding a database, building fixtures, making dummy datasets, demo data, load test data, or replacing production data in tests.
---

# Synthetic Test Data

## Purpose
Build dummy datasets that behave like real data under test: skewed amounts, a few heavy users, valid foreign keys, dates in the right order, and totals that add up. Ships a tested stdlib generator, so the output is code you can run and verify, not a format template.

## Workflow
1. Define the tables first. List each table, its primary key, its foreign keys, and which fields must stay coherent (tier affects amount, order date follows signup date, total equals the sum of items).
2. Order generation by dependency. Parents first, then children. Keep the issued parent keys in a list and sample child foreign keys only from that list.
3. Pick a distribution per field using references/distributions.md. Do not use uniform random for money, counts, popularity, or time gaps unless the real field is truly uniform.
4. Apply PII-safe defaults. Emails use example.com, example.net, or example.org, which RFC 2606 reserves for documentation and examples. North American phone numbers use 555-0100 to 555-0199, the range reserved for fictitious use. Never copy real records, real emails, or real phone numbers into a fixture. Names come from a small invented list or from Faker with a fixed seed.
5. Seed everything. Use one seed value, a private random.Random(seed) instance in Python, and Faker.seed(seed) if Faker is used. Record the seed in the output notes. Pin the Faker version if a test hardcodes generated values, because seeded output can change across Faker versions.
6. Generate with the bundled script or adapt it:
   python3 scripts/generate.py --out /tmp/demo --customers 100 --orders 400 --seed 42
   python3 scripts/generate.py --out /tmp/demo --format sql --seed 42
   python3 scripts/generate.py --out /tmp/demo --format json --seed 7 --edge-rate 0.02
7. Validate before handing the data over. Check every foreign key resolves, unique fields are unique, totals match their items, child dates are not before parent dates, and amounts fall in the stated domain. The bundled script runs these checks and prints validation_errors=0 on success.
8. Add edge cases on purpose, not by accident. Use --edge-rate for a small share of rows with an empty note field, and add named cases separately: a refunded order, a customer with no orders, a product with no sales. Keep edge cases valid unless the test is specifically about invalid data, and label invalid fixtures as invalid.

## Output Contract
Deliver:
- The data files, one per table, in one format: CSV, JSON, or SQL INSERT statements.
- The seed, row counts per table, and the generator script or the exact command used.
- A validation report: foreign key orphans found, uniqueness checks, domain checks, and date order checks, each with a pass or fail count.
- A short note on distribution choices for the main numeric fields and on any edge cases included.

SQL output loads parents before children in file order: customers, products, orders, order_items. Keep that order when you add tables.

## Operating Rules
- Generate from scratch by default. A generator built from real data is a different job with privacy risk: NIST SP 800-188 treats model-built synthetic data as a de-identification problem, not a free pass. Do not claim privacy safety for data derived from real records without a stated method.
- Referential integrity is a generation rule and a check. Sampling a foreign key from issued parent keys prevents orphans, and the validation step still proves it afterwards.
- Totals, counts, and derived fields are computed from their parts. Never generate a total independently from its line items.
- Popularity is skewed. Use Zipf style weighting for orders per customer or views per item, so load tests and pagination tests meet realistic hot spots.
- Reproducibility beats variety in tests. Same seed, same commands, same output. Change the seed when a test needs a different dataset, and say so.
- Keep the generator stdlib only where possible. If you bring in Faker for names and addresses, use its locales deliberately, use fake.unique for unique fields, and seed it from the same seed as the rest of the run.
- If validation fails, fix the generator and rerun. Do not patch the output rows by hand.