How to Generate Realistic Test Data with AI Without Leaking PII

Test data is the silent bottleneck in most QA pipelines. You need volume, variety, and referential integrity—but you also need to sleep at night knowing production PII never touched a staging database. AI promises to solve the volume-and-variety problem. It also introduces new leakage vectors if you treat it like a black box.

This guide walks through a practical, auditable workflow for generating realistic synthetic test data with AI while keeping PII out of your models, logs, and outputs. It assumes you're working in a regulated or privacy-conscious environment (GDPR, CCPA, HIPAA, SOC 2) and that "just use Faker.js" isn't sufficient for your domain complexity.

---

The Core Problem: Realism vs. Risk

Production data has texture. Email domains cluster by geography. Phone numbers follow carrier-specific formats. Transaction amounts respect business rules (no $0.00 refunds on $500 purchases). Names correlate with locales. Replicating that texture by hand is brittle; copying production directly is illegal in many contexts.

AI models trained on public code and text know these patterns. But they also memorize. A prompt like "generate 100 realistic user records for a US fintech app" can surface real SSNs, real email addresses, or real transaction IDs the model saw during training. Worse, if you feed production samples to a hosted API for few-shot prompting, you've just exported PII to a third party.

The goal isn't "zero risk." It's quantifiable, documented risk with controls you can show an auditor.

---

Prerequisites: What You Need Before Generating Anything

Before you write a prompt or spin up a model, establish these foundations.

1. Data Classification Inventory

You cannot protect what you haven't cataloged. For each target table or API payload, document:

Field	PII Type	Regulation	Retention Requirement	Synthetic Strategy
email	Direct identifier	GDPR Art. 4(1), CCPA	30 days post-test	Deterministic hash + synthetic domain
phone	Direct identifier	TCPA, GDPR	90 days	Format-preserving encryption (FPE)
ssn	Sensitive PII	HIPAA, GLBA	Never in test	Tokenized placeholder (XXX-XX-####)
address	Quasi-identifier	GDPR	30 days	Geocode → synthetic within same census tract
transaction_amount	Non-PII but sensitive	PCI DSS	1 year	Distribution matching (log-normal)
user_agent	Indirect identifier	GDPR	30 days	Sample from real UA corpus, no IPs

Checklist: Inventory Complete?

• Every column in target schema classified
• Regulation mapping documented
• Synthetic strategy assigned per field
• Reviewed by privacy/legal (signed off)

2. Threat Model for the Generation Pipeline

Draw the data flow. Where does PII enter the system? Where could it exit?

[Production DB] → (ETL/Export) → [Staging Area] → [AI Generator] → [Synthetic Output] → [Test Environments]
                      ↑                    ↑                  ↑
                 Risk: plaintext      Risk: prompt       Risk: model weights,
                 CSV on disk          leakage to API     memorization, logs

Mitigations per stage:

• Export: Never write raw PII to disk. Use streaming transforms (e.g., pg_dump | python transform.py | gpg -e).
• Prompting: If using a hosted API, redact before the request leaves your network. Use a local sidecar (see Implementation Choices).
• Model weights: Prefer local models or dedicated instances. Avoid shared multi-tenant endpoints for sensitive domains.
• Logs: Disable request/response logging on the inference server. Audit this quarterly.

3. Acceptance Criteria for "Realistic Enough"

Define measurable targets before generation. Examples:

Metric	Target	Validation Method
Email format validity	100% RFC 5322	Regex + MX lookup on 1% sample
Referential integrity (orders → users)	100% FK match	SQL constraint check
SSN format	100% XXX-XX-####	Pattern scan
Geographic distribution (zip → state)	χ² p-value > 0.05 vs prod	Statistical test
Name-ethnicity correlation	Within 5% of census	Join on surname tables
Transaction amount distribution	KS test D < 0.05 vs prod	Two-sample KS test

If you can't measure it, you can't prove it to an auditor.

---

Implementation Choices: Three Architectural Patterns

Choose based on your risk tolerance, infra maturity, and domain complexity.

Pattern A: Local Model + Deterministic Post-Processing (Highest Control)

Stack: Llama 3 / Mistral / Phi-3 running on GPU (on-prem or VPC), Python post-processors, FPE library.

Flow:

1. Export production schema + statistics only (counts, distincts, histograms, correlation matrix)—no rows.
2. Feed stats + schema to local model as structured prompt (JSON/YAML).
3. Model outputs synthetic rows as JSONL.
4. Deterministic post-processor enforces hard rules: FPE on SSN, synthetic domains on email, FK integrity.
5. Validate against acceptance criteria. Reject/regenerate on failure.

Pros: Zero data leaves your network. Full audit trail. Reproducible with fixed seed. Cons: Requires GPU infra. Model may hallucinate invalid combinations (hence post-processor). Limited context window for wide tables.

When to use: Regulated environments (finance, health), air-gapped networks, teams with ML ops capability.

Pattern B: Hosted API + Local Redaction Sidecar (Balanced)

Stack: OpenAI / Anthropic / Cohere API + local proxy (e.g., pii-redaction-proxy OSS or custom Flask/FastAPI service).

Flow:

1. Export production samples (if allowed by DPA) or hand-crafted few-shot examples with already-synthetic data.
2. Request passes through local proxy: detects PII (Presidio, spaCy, regex), redacts/replaces with tokens (<EMAIL_1>, <PHONE_2>).
3. Redacted prompt sent to API.
4. Response passes back through proxy: tokens replaced with fresh synthetic values from local generators (Faker, Mimesis, custom).
5. Validate.

Pros: Stronger model capabilities. No GPU infra. Redaction logic version-controlled and testable. Cons: Prompt structure (not content) leaves network. Vendor retains logs per their policy (check DPA). Latency + cost per 1k rows.

When to use: Lower-regulation domains, teams without GPU budget, need for complex reasoning (e.g., "generate clinical notes with consistent ICD-10 codes").

Pattern C: Specialized Synthetic Data Platforms (Fastest Time-to-Value)

Stack: Gretel, Mostly AI, Syntheticus, Tonic, or QA3's free test data generator at /tools/test-data-generator.

Flow:

1. Connect to source DB (read-only) or upload schema + stats.
2. Configure per-field generators (categorical, PII, numerical, temporal, relational).
3. Train/generate. Platform handles referential integrity, distributions, privacy guarantees (DP, k-anonymity).
4. Export to target test DB or CSV/Parquet.

Pros: Built-in privacy math (differential privacy, synthetic privacy metrics). Handles relational schemas natively. UI for non-engineers. Audit reports generated automatically. Cons: Vendor lock-in. Cost at scale. Less control over "weird" business logic. May not support niche domains (e.g., HL7 v2 messages, proprietary telemetry).

When to use: Relational DBs, standard PII types, need SOC 2 Type II vendor, want to move fast.

---

Worked Example: Fintech User + Transaction Dataset (Pattern A)

We'll generate 50,000 users and 500,000 transactions for a US neobank. Target: PostgreSQL test DB. Regulations: CCPA, GLBA, internal SOC 2.

Step 1: Extract Schema + Statistics (No Rows)

-- stats_extractor.sql
-- Run on production read-replica. Output: JSON.
SELECT json_build_object(
  'tables', json_object_agg(t.table_name, t.stats)
)
FROM (
  SELECT
    table_name,
    json_build_object(
      'row_count', (SELECT reltuples::bigint FROM pg_class WHERE relname = c.relname),
      'columns', json_object_agg(column_name, col_stats)
    ) AS stats
  FROM information_schema.columns c
  JOIN (
    SELECT
      attrelid,
      attname AS column_name,
      json_build_object(
        'data_type', atttypid::regtype::text,
        'null_frac', null_frac,
        'avg_width', avg_width,
        'n_distinct', n_distinct,
        'most_common_vals', most_common_vals,
        'most_common_freqs', most_common_freqs,
        'histogram_bounds', histogram_bounds,
        'correlation', correlation
      ) AS col_stats
    FROM pg_stats
  ) s ON c.table_name = s.attrelid::regclass::text AND c.column_name = s.column_name
  WHERE c.table_schema = 'public'
    AND c.table_name IN ('users', 'transactions', 'accounts')
  GROUP BY c.table_name
) t;

Save output as prod_stats.json. Verify: No most_common_vals contain real PII (they shouldn't for high-cardinality fields, but check).

Step 2: Define Generation Config (Version-Controlled)

# generation_config.yaml


version: "1.2"
seed: 20241115  # reproducible
target_rows:
  users: 50000
  transactions: 500000


tables:
  users:
    primary_key: user_id
    columns:
      user_id:
        type: uuid
        generator: uuid_v4
      email:
        type: string
        pii_type: direct_identifier
        strategy: deterministic_hash
        params:
          salt: "qa3-email-salt-v1"
          domain: "testbank.example"
      phone:
        type: string
        pii_type: direct_identifier
        strategy: format_preserving_encryption
        params:
          key: "{{ env.FPE_KEY_PHONE }}"
          format: "###-###-####"
      full_name:
        type: string
        pii_type: quasi_identifier
        strategy: synthetic_correlated
        params:
          locale_distribution: "us_census_2020"
          name_source: "census_surnames_2010,ssa_given_names_2023"
      date_of_birth:
        type: date
        pii_type: quasi_identifier
        strategy: distribution_match
        params:
          dist: "prod_histogram"
          min_age: 18
          max_age: 95
      ssn:
        type: string
        pii_type: sensitive
        strategy: tokenized_placeholder
        params:
          format: "XXX-XX-####"
          last4_source: "uniform"
      kyc_status:
        type: enum
        values: [pending, verified, rejected, expired]
        strategy: categorical_match
      created_at:
        type: timestamp
        strategy: temporal_correlated
        params:
          correlate_with: "date_of_birth"
          lag_distribution: "prod_histogram"


transactions:
    primary_key: txn_id
    foreign_keys:
      - column: user_id
        references: users.user_id
      - column: account_id
        references: accounts.account_id
    columns:
      txn_id:
        type: uuid
        generator: uuid_v4
      user_id:
        type: uuid
        strategy: fk_sample
        params:
          table: users
          column: user_id
      account_id:
        type: uuid
        strategy: fk_sample
        params:
          table: accounts
          column: account_id
      amount_cents:
        type: integer
        pii_type: sensitive
        strategy: distribution_match
        params:
          dist: "lognormal"
          mu: 5.2
          sigma: 1.8
          min: 1
          max: 5000000
      currency:
        type: enum
        values: [USD]
        strategy: constant
      merchant_category:
        type: string
        strategy: categorical_match
        params:
          top_k: 500
      merchant_name:
        type: string
        strategy: synthetic_merchant
        params:
          locale: "US"
      status:
        type: enum
        values: [pending, posted, declined, refunded]
        strategy: categorical_match
      created_at:
        type: timestamp
        strategy: temporal_correlated
        params:
          correlate_with: "users.created_at"
          lag_distribution: "prod_histogram"

Step 3: Local Model Prompt Construction

We don't ask the model to generate rows directly. We ask it to write the generation script.

# prompt_builder.py


import json, yaml


with open("prod_stats.json") as f:
    stats = json.load(f)
with open("generation_config.yaml") as f:
    config = yaml.safe_load(f)


prompt = f"""
You are a senior test data engineer. Write a Python script using pandas, numpy, faker, and cryptography.fernet
that generates synthetic data matching the following specification.


CONFIG:
{yaml.dump(config, sort_keys=False)}


PRODUCTION STATISTICS (for distribution matching only):
{json.dumps(stats, indent=2)}


REQUIREMENTS:
1. Use the seeded RNG (seed={config['seed']}) for ALL randomness.
2. Implement each strategy in config as a pure function: generate_<strategy>(params, rng, context).
3. Enforce referential integrity: transactions.user_id must exist in users.user_id.
4. Output: users.parquet, transactions.parquet (pyarrow, snappy compression).
5. No external network calls. No file reads except config.
6. Include a validate() function that runs the acceptance criteria from the config.
7. Print validation results as JSON to stdout.


Return ONLY the complete Python script. No markdown, no explanation.
"""


print(prompt)

Run this, pipe to your local model (e.g., ollama run llama3:70b-instruct < prompt.txt > generate.py).

Step 4: Review, Harden, Execute

Do not run the generated script blindly. Review for:

• Hardcoded secrets (should use os.environ)
• Missing imports
• Incorrect FK logic (batch inserts need ordering)
• Performance: 500k rows with pure Python loops will be slow. Vectorize with numpy/pandas.

Harden (example fixes):

# Generated script used list comprehension for FK sampling. Replace with:


user_ids = users_df['user_id'].to_numpy()
txn_user_ids = rng.choice(user_ids, size=n_transactions, replace=True)

Execute:

python generate.py 2>&1 | tee generation.log


# Output: validation_results.json, users.parquet, transactions.parquet

Step 5: Automated Validation

# validate.py (run in CI)


import json, pandas as pd
from scipy import stats


def validate():
    users = pd.read_parquet("users.parquet")
    txns = pd.read_parquet("transactions.parquet")
    results = {}


# 1. Row counts
    results['users_count'] = len(users) == 50000
    results['txns_count'] = len(txns) == 500000


# 2. PK uniqueness
    results['users_pk_unique'] = users['user_id'].is_unique
    results['txns_pk_unique'] = txns['txn_id'].is_unique


# 3. FK integrity
    results['fk_user_id'] = txns['user_id'].isin(users['user_id']).all()
    results['fk_account_id'] = txns['account_id'].isin(accounts['account_id']).all()


# 4. PII patterns
    results['email_format'] = users['email'].str.match(r'^[^@]+@testbank\.example$').all()
    results['phone_format'] = users['phone'].str.match(r'^\d{3}-\