Synthetic relational data: keys, chronology and integrity

Last updated: 5 Oct 20265 min read

tutorial

A generated table collection must satisfy the relationships its consumers expect, not just the type of each column.

Model the graph of records

A receipt has one merchant and multiple line items; a refund refers to an existing payment. Define primary keys, foreign keys, cardinality, uniqueness and required fields before generation. Generate parent entities first and child rows second, or use a joint method with explicit constraints. Source contracts help identify the fields downstream joins rely on.

Check event order

Receipt creation, payment, settlement and refund form a temporal sequence, with exceptions such as delayed settlement. Use explicit event time and recorded time when simulating retries or corrections. A refund before any payment can be a deliberate bad record in a quarantine test, but should be labeled as such; otherwise the fixture quietly teaches a pipeline to accept impossible histories.

Preserve enough correlation

Independent random columns often produce implausible combinations even when each marginal distribution looks correct. A large basket may correlate with price and tax; a scanner source may correlate with image quality. Encode relationships required by the test and measure whether they hold. Be careful: fitting those relationships from sensitive real records can create disclosure risk, so the privacy gate remains separate.

Break it on purpose

Create 47 payments, 52 receipt rows and six refunds. Insert one orphan refund, one duplicate payment ID and one refund timestamp before its payment. The integrity gate should reject exactly those deliberate exceptions. Save a clean version as a parser test and the broken version as a quarantine test, with scenario labels explaining why each bad record exists.

Implementation

def invalid_refunds(payments, refunds):
    payment_time = {payment["payment_id"]: payment["paid_at"] for payment in payments}
    return [refund["refund_id"] for refund in refunds
            if refund["payment_id"] not in payment_time
            or refund["refunded_at"] < payment_time[refund["payment_id"]]]

Performance and operating cost

Building a payment index costs O(P) expected time and space; validating R refunds costs O(R) expected time and O(B) space for B invalid IDs. Real databases should enforce keys transactionally as well as in fixture checks.

Common Mistakes

Do not generate child rows before their referenced parents exist.
Do not confuse a deliberate corrupt fixture with a clean training record.
Do not trust individually plausible columns to form plausible rows.