Synthetic Data Generation: Faker, SDV, Gretel & Mimesis for Safe Test Pipelines

Synthetic data generation is how a team gets realistic, safe data to build and test with when the real thing is either forbidden, too sensitive, or simply not there yet — fabricating rows that look and behave like production without carrying a single byte of production's personal data. The problem it solves is one every data engineer eventually hits: you cannot copy the customer table into staging because it is full of names, emails, and card numbers; a masked dump still leaks structure and edge cases and is a compliance liability the moment it leaves the vault; and a hand-written fixture of three rows never exercises the null, the duplicate, the leap-year date, or the ten-thousand-row join that breaks in production. Synthetic data is the answer to all three — data you are allowed to spread across every laptop and CI runner, reproducible from a seed, and rich enough to catch the bugs that only appear at scale.

This guide is the data-engineering walkthrough of the four tools that matter — framed the way the work actually splits: rule-based generation with Faker and Mimesis when you need believable fields, locales, and related tables from a seed; statistical/ML generation with SDV when the synthetic data must preserve the distributions and correlations of a real dataset; Gretel when you need privacy guarantees — differential-privacy synthetics with quality and privacy reports you can show an auditor; and the connective tissue nobody teaches — keeping referential integrity across a multi-table synthetic dataset and wiring the whole thing into a test data pipeline that runs in CI behind a quality gate. Each section pairs a teaching block with a worked answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works — so the patterns transfer straight into your own fixtures and pipelines.

When you want hands-on reps immediately after reading, drill the data processing practice library →, harden your fixtures on the data validation practice library →, and wire it into pipelines on the ETL practice library →.

---

On this page

• Why generate synthetic data
• Rule-based generation — Faker and Mimesis
• Gretel — differential-privacy synthetics
• Referential integrity across multi-table synthetic data
• Wiring synthetic data into a test pipeline
• Cheat sheet — synthetic data generation
• Frequently asked questions
• Practice on PipeCode

---

1. Why generate synthetic data

The three problems it solves — no production PII, reproducible fixtures, and edge-case coverage

The one-sentence invariant: synthetic data generation fabricates data that is statistically or structurally like production but contains none of production's real records, so you can develop, test, demo, and share freely — and the reason it exists is that the three ways teams get test data otherwise all fail: copying production leaks PII and violates policy, masking it still leaks structure and edge cases while staying a compliance liability, and hand-writing fixtures never covers the volume or the pathological rows that break code — so the whole discipline is about choosing the right generator (rule-based, statistical, or privacy-preserving) for the fidelity and safety a given use demands. Point your test suite at a masked prod dump and you inherit its blind spots and its liability; generate the data instead and you own its shape, its seed, and its safety.

The three failure modes of "just use production data."

• PII and compliance. Real rows carry names, emails, addresses, and card numbers. Copying them into staging, a laptop, or a CI runner spreads regulated PII into low-trust environments — a GDPR/HIPAA/PCI violation waiting to happen, and a breach surface with no upside. Synthetic data has no data subject, so there is nothing to leak.
• Masking is not safety. Redacting or hashing columns still preserves row counts, join structure, value distributions, and rare combinations — enough to re-identify or to leak business secrets — and a masked dataset is still derived from real people, so it never fully escapes the compliance perimeter. Synthetic data is generated from a model or rules, not from individuals.
• Hand-written fixtures don't scale or cover. Three tidy rows in a test file never contain the null in the middle, the duplicate key, the unicode name, the 29-Feb date, or the million-row table that surfaces the N+1 or the memory blow-up. A generator produces volume and controllable edge cases on demand.

The four axes that decide which generator you reach for.

• Fidelity. Do you need only plausible field values (a name, an email, a date), or must the data preserve the real dataset's distributions and correlations (age vs. income, purchase frequency vs. region)? Plausible fields → rule-based (Faker/Mimesis); faithful distributions → statistical/ML (SDV).
• Privacy guarantee. Is "no real records" enough, or do you need a provable privacy bound — differential privacy — because the synthetic data is trained on sensitive real data and shared externally? A provable bound → Gretel with differential privacy.
• Referential integrity. Is it one flat table, or a related schema where child foreign keys must point at real parent primary keys? Multi-table → SDV multi-table or careful key-linked rule-based generation.
• Reproducibility. Must the same run produce the same data (fixtures, golden tests) or is fresh randomness fine? Reproducible → seed every generator; fresh → don't.

The 2026 toolbox — four tools, four jobs.

• Faker — the workhorse rule-based generator: hundreds of providers (name, address, email, company), locales, and a seed for reproducibility. Fast, dependency-light, perfect for fixtures and volume where you only need believable fields.
• Mimesis — a Faker-alternative rule-based generator built for speed and typed, locale-first APIs; the same job as Faker with a different ergonomics and a large performance edge for bulk generation.
• SDV (Synthetic Data Vault) — statistical/ML generation that learns a real dataset and samples new rows preserving its distributions and correlations; single-table (GaussianCopula, CTGAN) and multi-table with referential integrity.
• Gretel — a platform for privacy-preserving synthetics: train a model on sensitive data with differential privacy, generate, and get quality and privacy reports quantifying utility and re-identification risk.

What interviewers listen for.

• Do you say you never copy production PII into test environments and explain why masking is not enough? — required answer.
• Do you match the generator to the fidelity need — rule-based for fields, statistical for distributions? — senior signal.
• Do you name differential privacy as the thing that makes trained-on-real synthetics safe to share, not just "it's fake data"? — senior signal.
• Do you treat reproducibility (seeding) and referential integrity as first-class, not afterthoughts? — required answer.

Worked example — the generator decision table

Detailed explanation. The most useful artifact for a synthetic-data discussion is a memorised mapping of need → generator. Every real decision converges on it: given a fidelity requirement, a privacy requirement, and a schema shape, which tool do you reach for? Walk through building the table for a team that needs test data for a payments service.

• The needs. A CI fixture of believable customers (fields only), a load-test dataset that must match real purchase distributions, and an external partner dataset that must be provably private.
• The tension. More fidelity and stronger privacy cost more setup and compute; plausible-fields generation is nearly free but preserves no real distribution.
• The rule. Match the generator to the weakest requirement that still satisfies the use — do not train a privacy model to produce a login fixture.

Question. For each use case, name the generator and why, in one line each.

Input.

Use case	Fidelity need	Privacy need	Generator
CI fixture (believable fields)	plausible fields	none (no real data)	Faker / Mimesis
Load test (real distributions)	distributions + correlations	none (internal)	SDV (statistical)
External share (trained on real)	distributions	provable (differential privacy)	Gretel
Multi-table staging dataset	referential integrity	none (internal)	SDV multi-table

Code.

# A tiny decision helper — encode the mapping so the choice is explicit, not vibes.
def choose_generator(fidelity: str, privacy: str, tables: str) -> str:
    if privacy == "provable":              # trained on real data + shared out
        return "Gretel (differential privacy)"
    if fidelity == "distributions":        # must match real distributions/correlations
        return "SDV multi-table" if tables == "related" else "SDV single-table"
    return "Faker / Mimesis"               # plausible fields only, no real data

print(choose_generator("fields", "none", "single"))          # Faker / Mimesis
print(choose_generator("distributions", "none", "related"))  # SDV multi-table
print(choose_generator("distributions", "provable", "single"))  # Gretel

Step-by-step explanation.

1. The first branch checks the privacy requirement, because it dominates: if the synthetic data is trained on real sensitive data and shared outside the trust boundary, you need a provable bound (differential privacy via Gretel) regardless of the fidelity ask.
2. The second branch checks fidelity: if the data must preserve real distributions and correlations — a load test, a model-training set — a purely rule-based generator will not do, because Faker's ages and incomes are independent random draws with no real relationship.
3. Within the statistical branch, the schema shape picks single- vs. multi-table SDV: one flat table uses a single-table synthesizer, a related schema needs the multi-table synthesizer that maintains foreign keys.
4. The default is rule-based (Faker/Mimesis): when you only need plausible fields and there is no real data involved, it is the fastest, cheapest, safest option — no model to train, no privacy risk, trivially reproducible from a seed.
5. The mistake the table prevents is over-engineering (training a DP model to produce a login-form fixture) and under-engineering (using Faker's independent draws for a load test that then behaves nothing like production).

Output.

Requirement pattern	Right generator	Wrong generator (common mistake)
Plausible fields, no real data	Faker / Mimesis	training an ML/DP model
Real distributions, internal	SDV	Faker (loses correlations)
Related tables, integrity	SDV multi-table	flat single-table generation
Trained on real, shared out	Gretel (DP)	"it's fake, ship it" (re-id risk)

Rule of thumb. State the fidelity, privacy, and schema requirements first, then let them pick the generator: rule-based for plausible fields, statistical for real distributions, multi-table for related schemas, and differential privacy whenever synthetic data trained on real records leaves the trust boundary.

Worked example — why masking a production dump is not safe test data

Detailed explanation. A tempting shortcut is "just mask the sensitive columns of a prod copy and use that." It feels safe and it is not — masking preserves everything except the literal masked values, and what it preserves is often enough to re-identify or to leak. Contrast masking with generation on the properties that matter.

• What masking keeps. Row count, join structure, value distributions, rare combinations, correlations, and the fact that every row is a real person with a masked name.
• The re-identification risk. A "masked" row with a rare ZIP + birth date + gender combination re-identifies an individual even with the name removed.
• The generation alternative. Synthetic rows have no underlying data subject, so a rare combination points at nobody.

Question. Show why a masked dataset still carries re-identification risk that synthetic data does not.

Input.

Property	Masked prod dump	Synthetic data
Underlying data subject	real person (name hidden)	none
Rare-combo re-identification	possible	impossible (no subject)
Distribution leakage	full (unchanged)	controlled
Compliance perimeter	still inside it	outside it

Code.

import pandas as pd

# A "masked" dump: names redacted, but quasi-identifiers left intact.
masked = pd.DataFrame({
    "name": ["***", "***", "***"],
    "zip": ["94123", "10001", "94123"],
    "birth_date": ["1991-02-14", "1975-11-02", "1991-02-14"],
    "gender": ["F", "M", "F"],
    "diagnosis": ["asthma", "diabetes", "asthma"],
})

# Quasi-identifier = (zip, birth_date, gender). Count how many rows are UNIQUE on it.
qi = ["zip", "birth_date", "gender"]
group_sizes = masked.groupby(qi)[qi[0]].transform("size")
unique_rows = (group_sizes == 1).sum()
print(f"{unique_rows} of {len(masked)} rows are re-identifiable via quasi-identifiers")
# -> 1 of 3 rows is a unique (zip, dob, gender) — the mask on `name` did not protect it.

Step-by-step explanation.

1. Masking replaces the direct identifier (name) with ***, which feels like anonymization but leaves the quasi-identifiers — ZIP, birth date, gender — completely intact.
2. The group-by on the quasi-identifier tuple counts how many rows share each combination; any row that is unique on (zip, birth_date, gender) can be matched back to a single real individual using an external voter or public record — the classic re-identification attack.
3. The masked dataset therefore still is real people, one masking-bypass away from a breach, and it never leaves the compliance perimeter because it is derived from real records.
4. Synthetic data breaks this chain: a synthetic row with the same rare (zip, dob, gender) combination corresponds to no real person, so there is nothing to re-identify — the risk is structurally absent, not merely reduced.
5. The senior framing: anonymization is a spectrum of risk you must prove you are on the safe end of; generation removes the data subject entirely, which is why it is the stronger default for anything leaving production.

Output.

Question	Masked dump	Synthetic data
Can a rare row be re-identified?	yes	no (no subject)
Safe to put on every CI runner?	no (still PII)	yes
Safe to share with a partner?	no	yes (with DP if trained on real)
In the compliance perimeter?	yes	no

Rule of thumb. Treat masking as risk reduction, not removal — a masked dump is still derived from real people and can re-identify on quasi-identifiers, so it stays inside the compliance perimeter. Prefer generated data for anything that leaves production; add differential privacy when the generator is trained on real records.

Senior data-engineering question on choosing a synthetic-data strategy

A senior interviewer often opens with: "Your team keeps copying a masked slice of the production customer and orders tables into staging and onto laptops for testing. Security has flagged it. Design a synthetic-data strategy: how you replace the copy for CI fixtures, for a realistic load test, and for a dataset you must share with an external analytics partner — and justify the generator you pick for each and why masking the prod copy is not an acceptable answer."

Solution Using rule-based, statistical, and differentially-private generation matched to each need

# 1. CI fixtures — plausible fields only, no real data, reproducible. Rule-based (Faker).
from faker import Faker
Faker.seed(42)                      # reproducible fixtures across every run/runner
fake = Faker()
ci_customer = {"name": fake.name(), "email": fake.email(), "city": fake.city()}

# 2. Load test — must match REAL distributions/correlations. Statistical (SDV).
from sdv.single_table import GaussianCopulaSynthesizer
from sdv.metadata import Metadata
metadata = Metadata.detect_from_dataframe(real_orders)   # learn column types/keys
synth = GaussianCopulaSynthesizer(metadata)
synth.fit(real_orders)              # learns distributions + correlations, not rows
load_test_orders = synth.sample(1_000_000)   # a million faithful-but-fake orders

# 3. External partner share — trained on real data + leaves the trust boundary.
#    => needs a PROVABLE privacy bound: differential privacy (Gretel).
from gretel_client import Gretel
gretel = Gretel(api_key="prompt")
trained = gretel.submit_train(
    "tabular-actgan",
    data_source=real_customers,
    config={"privacy_filters": {"outliers": "high"},
            "differential_privacy": {"enabled": True, "epsilon": 5.0}},  # DP bound
)
partner_data = trained.fetch_report_synthetic_data()
report = trained.fetch_report()      # quality (SQS) + privacy metrics for the auditor

Step-by-step trace.

Need	Real data involved?	Leaves trust boundary?	Generator
CI fixtures	no	yes (laptops/CI)	Faker (rule-based, seeded)
Load test	learns from real	no (internal)	SDV (statistical)
Partner share	learns from real	yes (external)	Gretel (differential privacy)
Masked prod copy	is real (masked)	yes	rejected — still PII

After the rollout, the masked copy is deleted from staging and laptops; CI uses seeded Faker fixtures that carry no data subject and reproduce byte-for-byte; the load test uses an SDV synthesizer that learned the real orders' distributions and correlations and sampled a million faithful-but-fake rows internally; and the partner dataset is generated by a Gretel model trained with differential privacy, shipped alongside a quality and privacy report. No real record ever leaves production.

Output:

Metric	Masked prod copy	Matched synthetic strategy
PII on laptops/CI	yes (masked ≠ safe)	none (no data subject)
Load-test realism	high (it's real)	high (learned distributions)
Partner-share risk	re-identifiable	bounded (differential privacy)
Reproducible fixtures	no (dump drifts)	yes (seeded)
Compliance perimeter	staging + laptops	production only

Why this works — concept by concept:

• Generator matched to need — plausible-field fixtures use rule-based Faker, distribution-faithful data uses statistical SDV, and externally-shared data uses differentially-private Gretel, so each use pays exactly the fidelity and privacy cost it requires and no more.
• No data subject — generated rows correspond to no real individual, so PII on a CI runner or a partner's disk is structurally absent rather than merely masked, taking those environments out of the compliance perimeter.
• Differential privacy for trained-on-real data — when a generator learns from real records and the output is shared, a DP bound (epsilon) provably limits how much any single real row can influence the output, which masking cannot offer.
• Seeding for reproducibility — a seeded generator produces identical fixtures on every run and every machine, so tests are deterministic and a failure reproduces instead of flaking on fresh randomness.
• Cost — Faker fixtures are near-free, an SDV fit is a one-time training cost amortised over unlimited sampling, and Gretel adds a training/privacy budget only where it is needed — versus the unbounded liability cost of real PII spread across environments. The eliminated cost is a breach surface — O(1) generation versus O(records) of regulated exposure.

Data validation
Topic — data-validation
Data validation problems on PII, masking, and safe test data

Practice →

Design Topic — design Design problems on test-data strategy and environments

Practice →

---

2. Rule-based generation — Faker and Mimesis

Providers, locales, and a seed turn believable fields — and whole related tables — out of thin air

The mental model in one line: rule-based generators (Faker and Mimesis) build data field-by-field from providers — small functions that emit a believable value for a type (name, email, address, date_of_birth) — parameterised by a locale so the values look right for a region, and pinned by a seed so a run is reproducible; SDV's statistical models sit one tier up for when you must preserve real distributions, but the vast majority of test data only needs plausible fields, and for that rule-based generation is the fastest, safest, most controllable option — you compose providers into rows, link keys by hand to build related tables, and get infinite reproducible volume with no model to train and no real data anywhere near it. Reach for statistics only when independence between fields is the thing that breaks your test.

Faker — the workhorse.

• Providers. fake.name(), fake.email(), fake.address(), fake.company(), fake.date_of_birth() — hundreds of built-in providers, plus community and custom ones. Each call emits one believable value; you compose them into a row.
• Locales. Faker('ja_JP') produces Japanese names and addresses; Faker(['en_US', 'fr_FR']) mixes locales. Locale is what makes the data look real for the market you are testing.
• Seeding. Faker.seed(42) (class-level) makes every subsequent call deterministic, so the same fixture is produced on every machine — essential for golden tests and reproducible CI.
• Uniqueness and constraints. fake.unique.email() guarantees no repeats within a run; you enforce your own domain constraints (a total >= 0, a valid state code) by drawing from a controlled set.

Mimesis — the fast alternative.

• Same job, different ergonomics. Person(Locale.EN).full_name(), Address(Locale.DE).city() — typed, locale-first providers grouped into classes (Person, Address, Finance, Datetime).
• Speed. Mimesis generates bulk data markedly faster than Faker because it avoids some of Faker's per-call overhead — the reason to pick it for millions of rows.
• Schema-first generation. Mimesis's Field + Schema API describes a row shape once and produces N rows, which reads cleanly for structured fixtures.
• Seeding. Person(seed=42) (or a seeded Field) pins reproducibility the same way Faker does.

Building related tables by hand.

• Generate parents first, capture their keys. Create customers, keep the list of customer_ids, then generate orders whose customer_id is drawn from that list — so every child FK points at a real parent PK.
• Control the fan-out. Decide how many children per parent (fixed, random-in-range, or a realistic long-tail) so the synthetic data exercises one-to-many joins the way production does.
• Keep derived fields consistent. If an order has total = sum(line_items.amount), compute it — do not draw it independently, or your test data violates the invariant your code assumes.

The failure modes engineers pre-empt.

• Unseeded "reproducible" fixtures. Forgetting to seed means every run differs, so a failing test can't be reproduced and golden files churn. Mitigation: seed at the top of every fixture/generator.
• Independent fields where correlation matters. Faker draws age and income independently; a test that depends on their real relationship will pass on synthetic data that production would fail. Mitigation: use SDV when correlations matter (section covered below), or encode the correlation as a rule.
• Orphan foreign keys. Drawing customer_id from randint instead of the real parent list creates children pointing at nonexistent parents — a referential-integrity bug. Mitigation: always draw child keys from the captured parent keys.

Common probes on rule-based generation.

• "How do you make Faker output reproducible?" — Faker.seed(n) at class level before generating.
• "Faker vs Mimesis?" — same rule-based job; Mimesis is faster for bulk and typed/locale-first; Faker has the larger provider ecosystem.
• "How do you generate related tables?" — generate parents, capture keys, draw child FKs from those keys.
• "When is rule-based not enough?" — when the test depends on real distributions/correlations; then use SDV.

Worked example — seeded Faker providers for a reproducible customer fixture

Detailed explanation. The canonical fixture: a seeded Faker instance emitting a batch of customers with unique emails and locale-correct fields, reproducible on every run. Build a 5-row customer fixture that is byte-identical across machines.

• Seed. Faker.seed(42) before any generation.
• Providers. name, unique email, city, seeded date of birth.
• Reproducibility. the same seed → the same rows, everywhere.

Question. Generate a reproducible list of customers with unique emails using seeded Faker providers.

Input.

Field	Provider	Constraint
customer_id	sequential	unique PK
name	fake.name()	—
email	fake.unique.email()	unique in run
birth_date	fake.date_of_birth()	adult

Code.

from faker import Faker

Faker.seed(42)                     # class-level seed → deterministic across machines
fake = Faker("en_US")

def make_customers(n: int) -> list[dict]:
    fake.unique.clear()            # reset the uniqueness pool for a fresh, repeatable run
    return [
        {
            "customer_id": i,
            "name": fake.name(),
            "email": fake.unique.email(),          # guaranteed unique within the run
            "birth_date": fake.date_of_birth(minimum_age=18, maximum_age=90).isoformat(),
        }
        for i in range(1, n + 1)
    ]

customers = make_customers(5)
for c in customers:
    print(c["customer_id"], c["name"], c["email"])

Step-by-step explanation.

1. Faker.seed(42) seeds the class-level shared random generator before any instance is used, so every fake.* call downstream is deterministic — run this on any machine and the rows are identical.
2. fake.unique.email() draws from a uniqueness-tracking proxy that refuses to repeat a value within the run, so the fixture never violates a UNIQUE(email) constraint your schema might enforce.
3. fake.unique.clear() resets the uniqueness pool at the start of make_customers, so calling the factory twice reproduces the same sequence instead of exhausting or drifting — the detail that makes the fixture repeatable, not just unique.
4. date_of_birth(minimum_age=18, maximum_age=90) bakes a domain constraint (adults only) into the provider call, so the synthetic data respects the invariant the code under test assumes.
5. Because the seed is fixed, the "random" names and emails are a stable sequence: a test asserting on customers[0] is deterministic, and a golden file regenerated later does not churn.

Output.

customer_id	name (seed=42)	email	note
1	deterministic	unique	same every run
2	deterministic	unique	same every run
…	…	…	…
5	deterministic	unique	reproducible fixture

Rule of thumb. Seed Faker at the class level (Faker.seed(n)) and reset fake.unique at the start of each factory so fixtures are both unique and byte-reproducible. Bake domain constraints (age range, valid codes) into the provider calls so the synthetic data can never violate the invariants the code under test relies on.

Worked example — locale-aware bulk generation with Mimesis

Detailed explanation. When you need a million locale-correct rows fast, Mimesis's schema-first API is the tool: describe the row once and generate N, seeded, in a fraction of Faker's time. Generate German-locale customers in bulk.

• Locale. Locale.DE → German names, cities, and formats.
• Schema. describe the row shape once with Field + Schema.
• Speed. bulk generation without per-row Python overhead per field.

Question. Generate a batch of German-locale customers with Mimesis's schema API, seeded for reproducibility.

Input.

Aspect	Faker	Mimesis
API style	per-call providers	typed, locale-first classes
Bulk speed	slower	markedly faster
Schema generation	manual loop	Schema produces N rows
Seeding	Faker.seed(n)	Field(seed=n)

Code.

from mimesis import Field, Schema
from mimesis.locales import Locale

# Field is locale + seed aware; Schema describes the row ONCE and yields N rows.
field = Field(Locale.DE, seed=42)          # German locale, reproducible

def customer(f: Field) -> dict:
    return {
        "customer_id": f("increment"),      # 1, 2, 3, ... deterministic PK
        "name": f("full_name"),             # locale-correct German name
        "email": f("email"),
        "city": f("city"),                  # German city
    }

schema = Schema(schema=lambda: customer(field), iterations=1_000_000)
rows = schema.create()                      # a million German-locale rows, fast + seeded
print(rows[0]["name"], "—", rows[0]["city"])

Step-by-step explanation.

1. Field(Locale.DE, seed=42) binds both the locale and the seed to a single callable, so every field it emits is German-locale and deterministic — the locale is what makes the data look right for the market under test.
2. The customer function describes the row shape once as a dict of field calls; f("increment") gives a deterministic sequential key while f("full_name"), f("email"), f("city") emit locale-correct values.
3. Schema(schema=..., iterations=1_000_000) declares that this row shape should be produced a million times; create() materialises them in one bulk pass that avoids much of the per-call overhead Faker incurs — the reason Mimesis wins for volume.
4. Because the Field is seeded, the million rows are reproducible: the same seed regenerates the same dataset, so a bulk fixture or a load-test input is deterministic.
5. The trade-off vs. Faker is ecosystem for speed: Faker has more providers and community add-ons, Mimesis has a typed locale-first API and a large throughput advantage — pick Mimesis when you are generating a lot of rows.

Output.

Rows	Faker (relative)	Mimesis (relative)
1k	fast	fast
100k	slower	faster
1M	slow	markedly faster
seeded?	yes	yes

Rule of thumb. Use Mimesis's Field + Schema for large, locale-correct, seeded batches where throughput matters, and Faker when you need its wider provider ecosystem. Either way, bind the seed and the locale explicitly so the bulk dataset is both region-correct and reproducible.

Worked example — generating related tables with consistent foreign keys

Detailed explanation. The step that turns believable fields into a usable dataset is linking tables: generate parents, capture their keys, then generate children whose foreign key is drawn from the parent keys — so every child points at a real parent. Build customers and orders with valid FKs and a realistic fan-out.

• Order. parents (customers) first, capture customer_ids.
• Link. each order.customer_id is sampled from the captured keys.
• Fan-out. a random number of orders per customer.

Question. Generate customers and a related orders table so every orders.customer_id references an existing customer.

Input.

Table	Key	FK source
customers	customer_id (PK)	—
orders	order_id (PK)	customer_id ∈ customer PKs
fan-out	0–5 orders/customer	random-in-range

Code.

import random
from faker import Faker

Faker.seed(7); random.seed(7)              # seed BOTH RNGs used below
fake = Faker()

# 1. Parents first — capture the primary keys we will reference.
customers = [{"customer_id": i, "name": fake.name()} for i in range(1, 51)]
customer_ids = [c["customer_id"] for c in customers]

# 2. Children — every FK is DRAWN FROM the real parent keys (never randint).
orders, oid = [], 1
for cid in customer_ids:
    for _ in range(random.randint(0, 5)):           # realistic 0..5 fan-out per customer
        n_items = random.randint(1, 4)
        orders.append({
            "order_id": oid,
            "customer_id": cid,                     # guaranteed to exist in customers
            "total_cents": sum(random.randint(200, 9000) for _ in range(n_items)),
        })
        oid += 1

# 3. Integrity holds by construction: every child FK is a real parent PK.
assert all(o["customer_id"] in set(customer_ids) for o in orders)
print(f"{len(customers)} customers, {len(orders)} orders, 0 orphans")

Step-by-step explanation.

1. Both random sources are seeded (Faker.seed and random.seed), because the fan-out and totals use Python's random while the fields use Faker — a fixture is only reproducible if every RNG it touches is pinned.
2. Parents are generated first and their primary keys captured into customer_ids; this list is the only legal source of customer_id values for children.
3. Each child's customer_id is set to cid from the captured parent keys as we iterate, so a foreign key can never reference a customer that does not exist — the orphan-FK bug is impossible by construction.
4. total_cents is derived from the generated line-item amounts rather than drawn independently, so the synthetic order respects the total = sum(items) invariant real code assumes — independent draws would violate it.
5. The closing assert proves referential integrity on the generated data; in a real generator this becomes a validation step (covered later) that fails the build if any orphan slips in.

Output.

Approach	Orphan FKs	Integrity
customer_id = randint(1, 1e6)	many	broken
draw FK from captured parent keys	zero	holds by construction
derive total from items	—	invariant preserved
draw total independently	—	invariant violated

Rule of thumb. Generate parents first, capture their primary keys, and draw every child foreign key from that captured set so referential integrity holds by construction — never from a raw random integer. Seed every RNG the generator touches, and derive dependent fields rather than drawing them independently.

Senior data-engineering question on rule-based multi-table fixtures

A senior interviewer might ask: "Build a reproducible, privacy-safe fixture for a customers/orders/order_items schema using rule-based generation. Cover how you keep it deterministic across machines, how you make it locale-correct, how you guarantee every foreign key references a real parent, and how you keep derived fields like order totals consistent — and when you'd abandon rule-based generation for a statistical model."

Solution Using seeded providers, captured parent keys, and derived fields

import random
from faker import Faker

Faker.seed(2026); random.seed(2026)        # deterministic across every run/machine
fake = Faker("en_US")

# 1. Parents: customers. Capture PKs — the ONLY legal FK source for children.
customers = [{"customer_id": i, "name": fake.name(), "email": fake.unique.email()}
             for i in range(1, 101)]
customer_ids = [c["customer_id"] for c in customers]

# 2. Children: orders, FK drawn from captured parent keys; items nested under orders.
orders, items, oid, iid = [], [], 1, 1
for cid in customer_ids:
    for _ in range(random.randint(0, 6)):          # fan-out per customer
        line_total = 0
        for _ in range(random.randint(1, 5)):      # items per order
            amt = random.randint(200, 9000)
            items.append({"item_id": iid, "order_id": oid, "amount_cents": amt})
            line_total += amt
            iid += 1
        orders.append({"order_id": oid, "customer_id": cid,     # valid FK by construction
                       "total_cents": line_total})              # DERIVED, not drawn
        oid += 1

# 3. Referential-integrity + invariant checks — the fixture fails loudly if violated.
cust_pks = {c["customer_id"] for c in customers}
order_pks = {o["order_id"] for o in orders}
assert all(o["customer_id"] in cust_pks for o in orders), "orphan order FK"
assert all(it["order_id"] in order_pks for it in items), "orphan item FK"
# total invariant: each order total equals the sum of its items
from collections import defaultdict
sums = defaultdict(int)
for it in items:
    sums[it["order_id"]] += it["amount_cents"]
assert all(o["total_cents"] == sums[o["order_id"]] for o in orders), "total mismatch"
print("fixture OK: deterministic, locale-correct, integrity + invariants hold")

Step-by-step trace.

Step	Action	Guarantee
Seed both RNGs	Faker.seed + random.seed	byte-reproducible across machines
Parents first	capture customer_ids	only legal FK source
Children	FK ∈ captured keys	no orphan orders
Items	order_id ∈ order PKs	no orphan items
Totals	derive from items	total = sum(items) holds
Validate	asserts on FKs + totals	build fails on any violation

After construction, the fixture is deterministic (both RNGs seeded), locale-correct (en_US providers), and referentially sound: every order references a real customer, every item references a real order, and every order total equals the sum of its items — all proven by asserts that fail the build on violation. No production data is involved, so the fixture is safe on every laptop and CI runner.

Output:

Property	Naive fixture	Rule-based solution
Reproducible	no (unseeded)	yes (both RNGs seeded)
Orphan foreign keys	common	zero (captured keys)
Derived-field consistency	violated	preserved (derived)
PII exposure	if copied from prod	none (generated)
Locale correctness	ad hoc	explicit (en_US)

Why this works — concept by concept:

• Seed every RNG — pinning both Faker's class RNG and Python's random makes the whole multi-table fixture deterministic, so tests reproduce failures instead of flaking and golden files stay stable.
• Captured parent keys — drawing every child foreign key from the parents' captured primary keys makes referential integrity hold by construction, eliminating the orphan-FK bug a raw random integer would introduce.
• Derived dependent fields — computing total_cents from the generated items rather than drawing it independently preserves the domain invariant the code under test assumes, which independent draws would silently break.
• Validation asserts — checking foreign keys and invariants at generation time turns a silent data bug into a loud build failure, so a broken fixture never reaches a test as a false pass/fail.
• Cost — rule-based generation is O(rows) of cheap provider calls with no model to train and no real data anywhere, versus the liability of a masked prod copy. The eliminated cost is both the training compute of a statistical model (unneeded when only fields matter) and the compliance exposure of real PII — O(rows) generation for O(1) risk.

Data processing
Topic — data-processing
Data processing problems on generating and shaping records

Practice →

Defensive coding Topic — defensive-coding Defensive coding problems on seeding and deterministic fixtures

Practice →

---

3. Gretel — differential-privacy synthetics

Train on real data, generate safe data, and prove it with quality and privacy reports

The mental model in one line: Gretel is a platform for privacy-preserving synthetic data — you train a generative model (a tabular GAN/transformer) on real sensitive data, optionally under **differential privacy, then generate new rows that preserve the data's utility while provably limiting how much any single real record influenced the output — and, crucially, it returns a quality report (how faithfully the synthetic data matches the real distributions and correlations) and a privacy report (re-identification and membership-inference risk), so you can hand an auditor evidence that the data is both useful and safe.** This is the tool for when synthetic data is trained on real records and must leave the trust boundary — the one case rule-based generation and plain statistical sampling cannot make provably safe.

What Gretel does that rule-based tools cannot.

• Learns real distributions. Like SDV, Gretel's models learn the joint distribution of the real data — correlations, conditional relationships — so the synthetic output behaves like production, not like independent random draws.
• Differential privacy. With DP enabled, training adds calibrated noise so that whether any single real record was in the training set is provably hard to detect (bounded by a privacy budget, epsilon) — the mathematical guarantee that makes trained-on-real data safe to share.
• Quality report. A synthetic-data quality score (often called an SQS) plus per-field distribution and correlation comparisons quantify utility — how faithfully the synthetic data mirrors the real data.
• Privacy report. Membership-inference and re-identification metrics quantify risk, so "is this safe to share?" becomes a number, not a hope.

The privacy-versus-utility trade-off (epsilon).

• Epsilon is the dial. A smaller epsilon means more noise, stronger privacy, and lower utility; a larger epsilon means less noise, weaker privacy, and higher utility. There is no free lunch — you choose the point on the curve your use and policy allow.
• Outlier handling. Privacy filters can drop or smooth outliers, because a lone extreme value is exactly what leaks an individual; this trades a little tail fidelity for a lot of privacy.
• The auditor's question. "What epsilon did you train at, and what does the privacy report say?" — a defensible synthetic-data pipeline can answer both.

Where Gretel fits vs. SDV.

• SDV — open-source, local, great for internal synthetic data where "no real records" is enough and you do not need a provable privacy bound; you own the compute.
• Gretel — a platform (SDK + hosted training) that adds differential privacy and standardized quality/privacy reporting; reach for it when synthetic data trained on real data must be shared externally or must satisfy a formal privacy requirement.
• Not either/or. Many pipelines use rule-based generation for fields, SDV for internal distribution-faithful data, and Gretel for the external, must-be-provably-private slice.

The failure modes engineers pre-empt.

• "It's synthetic, so it's private." Synthetic data trained on real records without DP can still memorise and leak outliers — synthesis alone is not a privacy guarantee. Mitigation: enable differential privacy and read the privacy report when the output leaves the boundary.
• Chasing utility off a cliff. Cranking epsilon high for a better quality score can erase the privacy benefit. Mitigation: pick epsilon from policy, then report the resulting utility — not the other way around.
• Shipping without the reports. Generating data and not checking the quality/privacy reports means you cannot defend either its usefulness or its safety. Mitigation: gate the pipeline on both a minimum quality score and a maximum risk score.

Common probes on Gretel and privacy.

• "How is Gretel different from Faker?" — Gretel learns real distributions and adds differential privacy + reports; Faker emits independent plausible fields.
• "What does differential privacy give you?" — a provable bound (epsilon) on how much any single real record affects the output.
• "How do you prove synthetic data is safe to share?" — the privacy report (membership-inference/re-id risk) plus the epsilon you trained at.
• "What's the trade-off?" — privacy vs. utility; smaller epsilon = safer but noisier.

Worked example — train a differentially-private synthesizer and generate

Detailed explanation. The core Gretel loop: submit real data to train a tabular model with differential privacy enabled, then fetch the synthetic data. Train a DP synthesizer on a real customers table and generate a safe replacement.

• Train. a tabular model with differential_privacy enabled at a chosen epsilon.
• Generate. fetch synthetic rows that preserve utility under the DP bound.
• Privacy. the epsilon is the provable knob.

Question. Train a differentially-private tabular synthesizer on real customer data and generate a synthetic dataset safe to share externally.

Input.

Piece	Value
Model	tabular-actgan (tabular GAN)
Privacy	differential privacy, epsilon = 5.0
Outliers	filtered high
Output	synthetic dataframe + reports

Code.

from gretel_client import Gretel

gretel = Gretel(api_key="prompt", project_name="safe-test-data")

# Train a tabular model WITH differential privacy → provable per-record bound.
trained = gretel.submit_train(
    "tabular-actgan",
    data_source=real_customers,                 # a pandas DataFrame of REAL data
    config={
        "params": {"epochs": 100},
        "privacy_filters": {"outliers": "high", "similarity": "high"},  # drop leaky tails
        "differential_privacy": {"enabled": True, "epsilon": 5.0},      # the DP budget
    },
)

# Generate synthetic rows that preserve utility under the DP bound.
synthetic = trained.fetch_report_synthetic_data()
print(synthetic.shape, "synthetic rows, trained at epsilon=5.0")

Step-by-step explanation.

1. submit_train("tabular-actgan", data_source=real_customers, ...) trains a tabular generative model on the real data — the model learns the joint distribution (correlations and conditional structure) that rule-based generators cannot reproduce.
2. differential_privacy: {enabled: True, epsilon: 5.0} makes training add calibrated noise so that the presence or absence of any single real record is provably hard to detect — epsilon 5.0 is the budget, the formal privacy guarantee.
3. privacy_filters drop high-similarity rows and extreme outliers, because a lone extreme value or a near-duplicate of a real record is exactly what leaks an individual even under DP — belt-and-braces on top of the epsilon bound.
4. fetch_report_synthetic_data() returns the generated rows; they preserve the real data's utility (distributions, correlations) but correspond to no real person and are bounded by the DP budget, so they are safe to share externally.
5. The senior point: the epsilon is chosen first from policy, and the resulting utility is measured after — not tuned upward to chase a better score, which would erode the very guarantee you enabled DP to get.

Output.

Aspect	Rule-based	Gretel + DP
Preserves real distributions	no	yes
Provable privacy bound	n/a	yes (epsilon)
Safe to share externally	yes (no real data)	yes (trained-on-real, bounded)
Compute cost	~none	model training

Rule of thumb. When synthetic data is trained on real records and will leave the trust boundary, enable differential privacy and set epsilon from policy — not from the utility you wish you had. Add privacy filters to drop leaky outliers, and remember that synthesis without DP is not, by itself, a privacy guarantee.

Worked example — reading the quality and privacy reports

Detailed explanation. Generating the data is half the job; the other half is proving it is useful and safe with the two reports Gretel produces. Read a quality score and a privacy score and gate on both. Evaluate a generated dataset before shipping.

• Quality (SQS). how faithfully synthetic matches real distributions/correlations.
• Privacy. membership-inference / re-identification risk.
• The gate. ship only if quality ≥ threshold and risk ≤ threshold.

Question. Fetch the quality and privacy reports and decide, programmatically, whether the synthetic dataset is safe and useful enough to ship.

Input.

Metric	Meaning	Gate
Quality score (SQS)	utility vs. real	≥ 80
Privacy score	protection level	≥ "good"
Membership-inference risk	re-id risk	≤ threshold
Decision	ship / block	both must pass

Code.

report = trained.fetch_report()          # standardized quality + privacy report

quality = report.quality_score           # synthetic-data quality score (utility)
privacy = report.privacy_score           # privacy protection level

def ship_decision(quality: float, privacy_level: str) -> str:
    # Gate on BOTH: useful enough AND safe enough. Either failing blocks the release.
    quality_ok = quality >= 80
    privacy_ok = privacy_level in {"Good", "Very Good", "Excellent"}
    if quality_ok and privacy_ok:
        return "SHIP"
    reasons = []
    if not quality_ok: reasons.append(f"quality {quality} < 80")
    if not privacy_ok: reasons.append(f"privacy {privacy_level} too low")
    return "BLOCK: " + ", ".join(reasons)

print(ship_decision(quality, privacy))

Step-by-step explanation.

1. fetch_report() returns the standardized report; quality_score quantifies utility — how closely the synthetic data's distributions and correlations match the real data — while privacy_score quantifies protection against re-identification and membership inference.
2. The gate checks both independently: a dataset can be high-utility but leaky (quality passes, privacy fails) or very private but useless (privacy passes, quality fails), and either failure must block the release.
3. quality >= 80 encodes a minimum-utility bar so a dataset that no longer behaves like production — because too much privacy noise destroyed the signal — is rejected rather than silently shipped.
4. The privacy-level check rejects anything below "Good," so a model that memorised too much (weak epsilon, no outlier filtering) cannot be released even if its utility looks great.
5. The senior discipline: this gate lives in the pipeline, so "is it safe and useful?" is answered by numbers on every run, and the auditor gets the report, not a promise.

Output.

Quality	Privacy	Decision
88	Good	SHIP
92	Poor	BLOCK (leaky)
71	Excellent	BLOCK (unusable)
85	Very Good	SHIP

Rule of thumb. Gate every privacy-preserving synthetic dataset on both a minimum quality score and a maximum re-identification risk — a dataset must be useful and safe, and either failing must block the release. Keep the gate in the pipeline so every run produces defensible evidence, not just data.

Senior data-engineering question on privacy-preserving synthetic data

A senior interviewer might ask: "You must give an external analytics partner a dataset derived from real, regulated customer records. Explain why plain synthetic generation isn't automatically safe, how differential privacy changes that, how you'd pick the privacy budget, and how you'd prove to an auditor that the released data is both useful and safe — with the pipeline that enforces it."

Solution Using a DP-trained synthesizer, an epsilon chosen from policy, and a two-sided report gate

from gretel_client import Gretel

gretel = Gretel(api_key="prompt", project_name="partner-share")

# 1. Epsilon is chosen from POLICY first (not tuned up for utility).
POLICY_EPSILON = 3.0            # stricter budget for an EXTERNAL, regulated share

# 2. Train on real data WITH differential privacy + outlier filters.
trained = gretel.submit_train(
    "tabular-actgan",
    data_source=real_customers,
    config={
        "privacy_filters": {"outliers": "high", "similarity": "high"},
        "differential_privacy": {"enabled": True, "epsilon": POLICY_EPSILON},
    },
)
synthetic = trained.fetch_report_synthetic_data()

# 3. Prove utility AND safety, then gate the release on BOTH.
report = trained.fetch_report()

def gate(report, min_quality=80, allowed_privacy=("Good", "Very Good", "Excellent")):
    q_ok = report.quality_score >= min_quality           # useful enough?
    p_ok = report.privacy_score in allowed_privacy        # safe enough?
    return q_ok and p_ok, {
        "epsilon": POLICY_EPSILON,                        # what we trained at
        "quality_score": report.quality_score,            # utility evidence
        "privacy_score": report.privacy_score,            # safety evidence
    }

ok, evidence = gate(report)
if not ok:
    raise SystemExit(f"BLOCK release — evidence: {evidence}")
# 4. Ship the data AND the evidence bundle the auditor will ask for.
publish(synthetic, evidence)
print("released with evidence:", evidence)

Step-by-step trace.

Step	Action	Why
Pick epsilon	POLICY_EPSILON = 3.0 from policy	privacy budget decided first
Train w/ DP	differential_privacy.enabled	provable per-record bound
Filter outliers	privacy_filters	drop rows that leak individuals
Fetch reports	fetch_report()	quantify utility + risk
Gate on both	quality ≥ 80 AND privacy ok	useful and safe
Publish evidence	epsilon + scores	auditor-ready proof

After the run, the partner dataset is generated by a model trained under an epsilon chosen from policy, with outlier filters stripping the rows most likely to leak; the pipeline blocks the release unless the quality score clears the utility bar and the privacy score clears the risk bar; and the shipped bundle includes the epsilon and both scores, so the auditor gets evidence, not assurances. No real record leaves the boundary — only a bounded, measured synthetic derivative.

Output:

Concern	Plain synthesis	DP synthesis + gate
Outlier memorisation	possible	filtered + DP-bounded
Privacy guarantee	none (hope)	provable (epsilon)
Utility evidence	none	quality score
Safety evidence	none	privacy score
Auditor answer	"trust us"	epsilon + reports

Why this works — concept by concept:

• Differential privacy — calibrated training noise provably bounds how much any single real record can influence the output (epsilon), converting "it's synthetic" from a hope into a guarantee that survives an audit.
• Epsilon from policy — choosing the privacy budget from requirements first, then measuring utility, keeps the guarantee intact instead of quietly trading it away to chase a better quality score.
• Outlier/similarity filters — dropping extreme and near-duplicate rows removes the records most likely to leak an individual, hardening privacy on top of the DP bound where it is weakest.
• Two-sided report gate — requiring both a minimum quality score and an acceptable privacy score means a release is provably useful and provably safe, and either failing blocks the ship.
• Cost — a one-time DP training run plus report generation, versus the unbounded liability of sharing regulated data or a re-identifiable masked derivative. The eliminated cost is a regulatory breach — a bounded epsilon and an evidence bundle in exchange for O(model) training, paid once per dataset.

Data validation
Topic — data-validation
Data validation problems on privacy checks and quality gates

Practice →

Design Topic — design Design problems on privacy-preserving data sharing

Practice →

---

4. Referential integrity across multi-table synthetic data

FK-consistent multi-table generation — capture keys by hand, or let SDV learn the relationships

The mental model in one line: generating a single table is easy; generating a related schema where every foreign key must point at a real primary key — and where distributions across tables stay consistent — is the hard part of synthetic data generation, and there are two disciplined answers: the rule-based way (generate parents, capture their keys, draw child FKs from that captured set, as in section 2, extended across the whole graph) and the statistical way (SDV's multi-table synthesizer, which learns each table and the relationships between them from metadata and samples a whole related dataset whose foreign keys are valid by construction) — and whichever you use, you finish with an integrity check that proves no child references a missing parent before the data is trusted. A synthetic dataset that violates referential integrity is worse than none — it makes tests pass on data the database would reject.

Why multi-table is the hard case.

• Foreign keys must resolve. Every orders.customer_id must exist in customers.customer_id; a synthetic dataset with orphan FKs fails a real FOREIGN KEY constraint and gives false test results.
• Cross-table distributions must be consistent. If real high-value customers place more orders, independently generating each table loses that relationship — the multi-table data no longer behaves like production.
• Cardinality/fan-out must be realistic. The number of children per parent (and its skew) is part of the schema's behaviour; uniform fan-out where production is long-tailed hides bugs.

The rule-based way — captured keys across the graph.

• Topological order. Generate tables parent-before-child following the FK graph, capturing each table's PKs as you go.
• Draw FKs from captured keys. Every child FK is sampled from the parent's captured PKs — never a raw random integer — so integrity holds by construction (the section-2 pattern, applied to the whole schema).
• Encode cross-table rules. If a relationship matters (VIP customers order more), encode it as a sampling weight rather than leaving it to chance.

The statistical way — SDV multi-table.

• Metadata describes the schema. A MultiTableMetadata (or Metadata) records each table's columns, its primary key, and the relationships (parent table/key → child table/key).
• HMASynthesizer learns and samples. The Hierarchical Modeling Algorithm synthesizer fits every table and the relationships, then sample() produces a whole related dataset whose foreign keys are valid and whose cross-table distributions are learned from the real data.
• Scale. A scale factor samples a larger or smaller synthetic dataset than the original while keeping the relationships intact.

The non-negotiable — an integrity check.

• Validate before trusting. After generation (rule-based or SDV), verify every child FK resolves to a parent PK; fail loudly on any orphan.
• SDV's own validation. SDV can validate generated data against the metadata; a custom check is still worth having in the pipeline as a hard gate.
• Cardinality sanity. Optionally assert the fan-out distribution is realistic (no parent with a million children unless production has that).

The failure modes engineers pre-empt.

• Orphan foreign keys. Independently generating each table, or drawing FKs randomly, produces children pointing at nonexistent parents. Mitigation: captured keys (rule-based) or a relationship-aware synthesizer (SDV), plus an integrity check.
• Lost cross-table correlation. Table-at-a-time generation destroys the relationships between tables. Mitigation: SDV multi-table (learns them) or encoded sampling rules.
• Wrong metadata. SDV can only preserve relationships you declare; a missing relationship in the metadata means an unmodeled, possibly-orphaned FK. Mitigation: declare every relationship and validate.

Common probes on multi-table generation.

• "How do you keep FKs valid in synthetic data?" — captured parent keys (rule-based) or SDV multi-table (learned relationships), then an integrity check.
• "What does SDV's HMASynthesizer do?" — fits each table and the relationships from metadata, samples a whole valid related dataset.
• "How do you preserve cross-table correlations?" — a relationship-aware model (SDV), not independent per-table generation.
• "How do you prove integrity?" — validate every child FK resolves to a parent PK before trusting the data.

Worked example — SDV multi-table synthesis with metadata

Detailed explanation. The canonical SDV multi-table setup: describe the schema and its relationships in metadata, fit an HMASynthesizer, and sample a whole related dataset whose foreign keys are valid by construction. Synthesize customers + orders preserving their relationship.

• Metadata. columns, primary keys, and the customer→order relationship.
• Fit. HMASynthesizer learns tables and relationship.
• Sample. a valid related dataset, scaled as needed.

Question. Use SDV's multi-table synthesizer to generate a related customers/orders dataset with valid foreign keys and learned cross-table structure.

Input.

Piece	Value
Tables	customers (PK customer_id), orders (PK order_id)
Relationship	customers.customer_id → orders.customer_id
Synthesizer	HMASynthesizer
Scale	1.0 (same size as real)

Code.

from sdv.metadata import Metadata
from sdv.multi_table import HMASynthesizer

# 1. Describe the schema: tables, primary keys, and the relationship (the FK).
metadata = Metadata.detect_from_dataframes(
    {"customers": real_customers, "orders": real_orders}
)
metadata.set_primary_key("customers", "customer_id")
metadata.set_primary_key("orders", "order_id")
metadata.add_relationship(
    parent_table_name="customers", parent_primary_key="customer_id",
    child_table_name="orders",     child_foreign_key="customer_id",
)

# 2. Fit the hierarchical synthesizer: learns EACH table AND the relationship.
synth = HMASynthesizer(metadata)
synth.fit({"customers": real_customers, "orders": real_orders})

# 3. Sample a whole related dataset — FKs valid by construction, distributions learned.
synthetic = synth.sample(scale=1.0)
syn_customers, syn_orders = synthetic["customers"], synthetic["orders"]

Step-by-step explanation.

1. Metadata.detect_from_dataframes infers column types for both tables; set_primary_key and add_relationship then declare the schema's keys and the customer→order foreign key — SDV can only preserve relationships you declare.
2. HMASynthesizer(metadata) is the hierarchical modeling algorithm: it models each table conditioned on its parent, so the synthetic orders depend on the synthetic customer they belong to — the mechanism that keeps cross-table correlations intact.
3. fit(...) learns both the per-table distributions and the parent-child structure from the real data — not the rows themselves, the statistics — so no real record is copied.
4. sample(scale=1.0) produces a whole related dataset; because generation follows the declared relationship, every orders.customer_id references a customer that exists in the sampled customers — referential integrity holds by construction, not by luck.
5. scale lets you sample a bigger synthetic dataset than the original (e.g. scale=10 for a load test) while keeping the learned relationships and fan-out realistic — something independent per-table generation cannot do.

Output.

Property	Independent per-table	SDV multi-table
FK validity	orphans likely	valid by construction
Cross-table correlation	lost	learned + preserved
Fan-out realism	uniform/guessed	learned
Scale up/down	breaks relationships	scale= keeps them

Rule of thumb. For a related schema, declare every table's primary key and every relationship in the metadata, then let HMASynthesizer fit and sample — it preserves foreign-key validity and cross-table correlations that per-table generation destroys. Use scale to resize while keeping the relationships intact.

Worked example — a referential-integrity check as a hard gate

Detailed explanation. Whether data came from rule-based generation or SDV, the pipeline must prove integrity before trusting it — a cheap check that fails loudly on any orphan foreign key. Write a reusable integrity validator over a synthetic dataset.

• The check. every child FK value must be in the parent PK set.
• The gate. raise (fail the build) on any orphan.
• Reusable. a table of (child table, FK, parent table, PK) rules.

Question. Validate that a generated multi-table dataset has zero orphan foreign keys, failing the pipeline if any exist.

Input.

Relationship	Child.FK	Parent.PK
orders → customers	orders.customer_id	customers.customer_id
items → orders	order_items.order_id	orders.order_id
result	pass / fail	zero orphans required

Code.

import pandas as pd

# Declarative FK rules: (child_df, fk_col, parent_df, pk_col) — the schema's edges.
def check_referential_integrity(rules: list[tuple]) -> None:
    problems = []
    for child_df, fk_col, parent_df, pk_col in rules:
        parent_keys = set(parent_df[pk_col])
        # rows whose FK is non-null but NOT present in the parent key set = orphans
        orphan_mask = child_df[fk_col].notna() & ~child_df[fk_col].isin(parent_keys)
        n_orphans = int(orphan_mask.sum())
        if n_orphans:
            problems.append(f"{fk_col}: {n_orphans} orphan(s)")
    if problems:
        raise AssertionError("referential integrity FAILED — " + "; ".join(problems))
    print("referential integrity OK — zero orphans across all relationships")

check_referential_integrity([
    (syn_orders, "customer_id", syn_customers, "customer_id"),
    (syn_items,  "order_id",    syn_orders,    "order_id"),
])

Step-by-step explanation.

1. The rules are declared as a list of (child, fk, parent, pk) tuples — one per foreign key in the schema — so the check is data-driven and covers the whole graph, not one hard-coded relationship.
2. For each rule, set(parent_df[pk_col]) is the set of legal key values; a child FK is valid only if it appears in that set.
3. child_df[fk_col].notna() & ~child_df[fk_col].isin(parent_keys) flags rows whose FK is present but not a real parent key — a genuine orphan — while allowing legitimately null FKs (an order without a coupon, say).
4. Any non-zero orphan count is collected into problems, and a non-empty problems list raises AssertionError, which in a pipeline fails the build — the synthetic data is rejected before any test trusts it.
5. This check is the safety net behind both generation methods: SDV should produce valid FKs and captured-key rule-based generation should too, but a metadata mistake or a code bug can still slip an orphan through, and this gate catches it deterministically.

Output.

Dataset	Orphans found	Gate
SDV multi-table (correct metadata)	0	PASS
rule-based (captured keys)	0	PASS
FK drawn from randint	many	FAIL (build stops)
missing relationship in metadata	some	FAIL (build stops)

Rule of thumb. Always finish multi-table generation with a declarative referential-integrity check that fails the build on any orphan foreign key — treat it as a hard gate, not an optional test. It is the safety net that catches a metadata mistake or a generation bug before broken synthetic data produces false test results.

Worked example — preserving cross-table cardinality and correlation

Detailed explanation. Valid foreign keys are necessary but not sufficient — the shape of the relationships (how many children per parent, and which parents get more) must resemble production or the data hides bugs. Compare independent generation with a relationship-aware approach on fan-out.

• The risk. uniform fan-out where production is long-tailed.
• The fix. learn the fan-out (SDV) or encode a realistic distribution (rule-based).
• The proof. compare the children-per-parent distribution to real.

Question. Show why independent per-table generation distorts fan-out and how a relationship-aware approach preserves it.

Input.

Aspect	Independent	Relationship-aware
Children per parent	uniform/random	learned or weighted
Correlation (VIP orders more)	lost	preserved
Long tail	flattened	preserved
Hidden bugs	yes	exposed like prod

Code.

import numpy as np

# Independent: every customer gets the SAME expected orders — flat, unrealistic.
def independent_fanout(customer_ids, mean=3):
    return {cid: np.random.poisson(mean) for cid in customer_ids}

# Relationship-aware: fan-out DEPENDS on a customer attribute (VIPs order more) →
# preserves the real long-tailed, correlated cardinality.
def correlated_fanout(customers):
    out = {}
    for c in customers:
        base = 12 if c["tier"] == "vip" else 2       # VIPs skew the tail
        out[c["customer_id"]] = np.random.poisson(base)
    return out

# The distribution of children-per-parent is what a downstream JOIN/agg test depends on.

Step-by-step explanation.

1. independent_fanout draws each customer's order count from the same Poisson mean, producing a flat, symmetric distribution — every customer looks average, and the long tail of heavy buyers that stresses joins and aggregations disappears.
2. correlated_fanout makes the fan-out depend on a customer attribute (tier), so VIP customers draw from a much higher mean — recreating the skewed, correlated cardinality real data has.
3. This matters because downstream code is often tested by its behaviour on the tail: the customer with 500 orders is what surfaces the N+1 query, the memory spike, or the pagination bug — and independent generation erases exactly that customer.
4. SDV's HMASynthesizer learns this fan-out from the real data automatically; the rule-based approach reproduces it by encoding the attribute-dependent weight, as shown — either way, the relationship is modeled, not left to a uniform draw.
5. The senior point: referential integrity (valid FKs) and referential realism (correct cardinality/correlation) are different bars; passing the first while failing the second gives data that is structurally valid but behaviourally misleading.

Output.

Metric	Independent	Relationship-aware
Mean children/parent	correct	correct
Distribution shape	flat	long-tailed (real)
Tail (heavy parents)	missing	present
Surfaces tail bugs	no	yes

Rule of thumb. Match not just foreign-key validity but the relationship's shape — its cardinality distribution and cross-table correlations — because production bugs usually live in the long tail a uniform fan-out erases. Let SDV learn it, or encode an attribute-dependent weight in rule-based generation.

Senior data-engineering question on multi-table synthetic data

A senior interviewer might ask: "Generate a synthetic replacement for a related customers/orders/order_items dataset that (a) has zero orphan foreign keys, (b) preserves the real cross-table distributions and fan-out, and (c) can be scaled up for a load test — and explain how you'd prove integrity before anyone trusts the data. Compare doing it rule-based versus with SDV."

Solution Using SDV multi-table metadata, HMASynthesizer, scaling, and an integrity gate

from sdv.metadata import Metadata
from sdv.multi_table import HMASynthesizer

# 1. Declare the whole schema graph: PKs + every relationship (both FKs).
tables = {"customers": real_customers, "orders": real_orders, "order_items": real_items}
metadata = Metadata.detect_from_dataframes(tables)
metadata.set_primary_key("customers", "customer_id")
metadata.set_primary_key("orders", "order_id")
metadata.set_primary_key("order_items", "item_id")
metadata.add_relationship("customers", "orders", "customer_id", "customer_id")
metadata.add_relationship("orders", "order_items", "order_id", "order_id")

# 2. Fit once (learns tables + BOTH relationships), sample scaled for a load test.
synth = HMASynthesizer(metadata)
synth.fit(tables)
big = synth.sample(scale=10.0)          # 10x data, relationships + fan-out preserved

# 3. Prove integrity BEFORE trusting the data — a hard gate over the whole graph.
def check_ri(rules):
    bad = []
    for child, fk, parent, pk in rules:
        keys = set(parent[pk])
        orphans = int((child[fk].notna() & ~child[fk].isin(keys)).sum())
        if orphans: bad.append(f"{fk}:{orphans}")
    if bad: raise AssertionError("RI FAILED: " + ", ".join(bad))

check_ri([
    (big["orders"],      "customer_id", big["customers"], "customer_id"),
    (big["order_items"], "order_id",    big["orders"],    "order_id"),
])
print("multi-table synthetic dataset: 10x scale, RI proven, distributions learned")

Step-by-step trace.

Step	Action	Guarantee
Declare schema	PKs + both relationships	SDV models the FK graph
Fit HMASynthesizer	learn tables + relationships	cross-table correlation kept
Sample scale=10	10x related dataset	fan-out realism preserved
Integrity gate	check every FK resolves	zero orphans, or build fails
Compare rule-based	captured keys + same gate	same integrity, less realism

After the run, SDV has learned the three tables and their two relationships from the real data and sampled a dataset ten times larger for the load test, with fan-out and cross-table correlations preserved; the declarative integrity gate then proves every order references a real customer and every item references a real order, failing the build on any orphan. The rule-based alternative (captured keys, section 2) achieves the same integrity but must encode the correlations SDV learns — the trade-off between control and fidelity.

Output:

Requirement	Independent per-table	SDV multi-table + gate
Orphan foreign keys	many	zero (proven)
Cross-table correlation	lost	learned
Fan-out realism	flat	preserved
Scale for load test	breaks FKs	scale= keeps them
Integrity proof	none	hard gate in pipeline

Why this works — concept by concept:

• Declared metadata graph — recording every primary key and relationship lets SDV model the schema as a hierarchy, so generation follows the foreign-key edges and cross-table structure is preserved rather than lost to per-table draws.
• HMASynthesizer — modeling each child table conditioned on its parent keeps foreign keys valid by construction and reproduces learned cross-table correlations, the two things independent generation destroys.
• Scale factor — sampling at scale=10 resizes the dataset for a load test while keeping relationships and fan-out realistic, which naive multiplication of rows cannot do.
• Referential-integrity gate — a declarative check that fails the build on any orphan FK is the deterministic safety net behind both generation methods, catching metadata or code mistakes before broken data reaches a test.
• Cost — one SDV fit amortised over unlimited scaled sampling plus an O(rows) integrity check, versus the false-result cost of testing on data the database would reject. The eliminated cost is a class of bugs that only appear when synthetic data violates constraints — O(fit) once for O(valid) forever.

ETL
Topic — etl
ETL problems on multi-table pipelines and foreign keys

Practice →

Data validation Topic — data-validation Data validation problems on referential integrity checks

Practice →

---

5. Wiring synthetic data into a test pipeline

Seed a factory, gate on quality, and generate fresh fixtures in CI — never a prod copy

The mental model in one line: the payoff of synthetic data generation is a test data pipeline — a seeded fixture factory your tests call for deterministic data, a generation step your CI runs to build realistic datasets on demand, and a quality/integrity gate that fails the build if the synthetic data drifts from what the tests assume — so every developer and every CI runner gets safe, reproducible, edge-case-rich data without a single production copy, and the data is versioned and validated like code rather than a stale dump someone SCP'd into staging last quarter. Fixtures that reproduce, data that scales, and a gate that proves it: that is what turns synthetic generation from a script into infrastructure.

The three pieces of a synthetic test-data pipeline.

• Seeded fixture factories. A function/pytest fixture that returns deterministic synthetic data for a test — seeded so a failure reproduces, parameterisable so one factory serves many scenarios (empty, single, bulk, edge cases).
• On-demand generation. A CI step that builds a realistic dataset (rule-based for fields, SDV for distributions) at the scale a given test suite needs — small for unit tests, large for load/perf tests.
• A quality/integrity gate. A check that the generated data meets the tests' assumptions — referential integrity, distribution sanity, required edge cases present — that fails the build when violated, so tests never run on bad data.

Fixtures as factories, not files.

• Deterministic per test. Seed inside the fixture (or from the test id) so each test gets stable data; a shared unseeded generator makes flaky tests.
• Edge-case parameterisation. A factory that takes size, include_nulls, include_duplicates, locale produces the empty-table, single-row, and pathological cases from one place.
• No I/O in the hot path. Generating in-memory fixtures is faster and hermetic vs. loading a checked-in CSV that drifts and hides its provenance.

Generation in CI.

• Build artifacts, not commits. Generate the dataset as a pipeline artifact from a seeded config, so it is reproducible from the config rather than a big binary in git.
• Match scale to the job. Unit tests need tens of rows; a nightly perf test needs millions via SDV scale — the same generator, different scale.
• Cache by seed+config. If the seed and config are unchanged, the dataset is identical, so CI can cache it instead of regenerating.

Quality gates in the pipeline.

• Referential integrity. The section-4 check as a build step — zero orphans or fail.
• Distribution/drift sanity. Assert key statistics (row counts, null rates, value ranges, category coverage) are within expected bounds, so a generator change that silently breaks the data is caught.
• Required edge cases. Assert the dataset actually contains the null, the duplicate, the boundary date the tests need — a generator that stops producing them would make tests pass vacuously.

The failure modes engineers pre-empt.

• Flaky fixtures from unseeded generation. Fresh randomness per run means a test that passes today fails tomorrow with no code change. Mitigation: seed every fixture; make the seed part of the fixture contract.
• Checked-in CSV rot. A static fixture file drifts from the schema, hides its origin, and cannot scale. Mitigation: generate from a seeded config as an artifact.
• Silent generator drift. A dependency bump changes what the generator emits and tests quietly change meaning. Mitigation: a quality/drift gate that pins expected statistics and required edge cases.

Common probes on synthetic test-data pipelines.

• "How do you make test fixtures reproducible?" — seed the generator inside the fixture; part of the contract.
• "Where does synthetic data fit in CI?" — a generation step producing a seeded artifact, gated by quality/integrity before tests run.
• "How do you stop bad synthetic data from giving false results?" — a build-failing gate: referential integrity, distribution sanity, required edge cases.
• "Unit vs. load test data?" — same generator, different scale (tens of rows vs. SDV scale to millions).

Worked example — a seeded pytest fixture factory

Detailed explanation. The unit-test workhorse: a parameterisable pytest fixture that returns deterministic synthetic data, seeded so failures reproduce and flexible enough to produce edge cases. Build a customers fixture factory.

• Seeded. deterministic per test run.
• Parameterised. size, include_edge_cases.
• Hermetic. in-memory, no file I/O.

Question. Write a pytest fixture factory that yields deterministic synthetic customers and can inject edge cases on demand.

Input.

Parameter	Effect
size	number of rows
include_edge_cases	add null/boundary rows
seed	fixed → reproducible
return	in-memory list of dicts

Code.

import pytest
from faker import Faker

@pytest.fixture
def customer_factory():
    """Returns a builder → tests call it with the shape they need, deterministically."""
    def _build(size: int = 10, include_edge_cases: bool = False, seed: int = 42):
        Faker.seed(seed)                    # deterministic per call → reproducible tests
        fake = Faker()
        fake.unique.clear()
        rows = [{"customer_id": i, "name": fake.name(), "email": fake.unique.email()}
                for i in range(1, size + 1)]
        if include_edge_cases:              # the rows that break naive code
            rows += [
                {"customer_id": size + 1, "name": "",      "email": None},   # null/empty
                {"customer_id": size + 2, "name": "O'Hara-李", "email": "[e-mail]"},  # unicode
            ]
        return rows
    return _build

def test_handles_empty_name(customer_factory):
    customers = customer_factory(size=3, include_edge_cases=True)
    assert any(c["name"] == "" for c in customers)     # edge case is present
    # ... exercise code-under-test against the edge-case-rich, deterministic fixture

Step-by-step explanation.

1. The fixture returns a builder function rather than a fixed dataset, so each test calls customer_factory(...) with exactly the size and edge-case mix it needs — one fixture serves the empty, single-row, bulk, and pathological cases.
2. Faker.seed(seed) inside the builder makes each call deterministic; a test that fails does so reproducibly, and re-running it locally yields the identical data the CI runner saw.
3. fake.unique.clear() resets the uniqueness pool per build so repeated calls are stable rather than drifting or exhausting — the same detail that made section-2 fixtures repeatable.
4. include_edge_cases injects the rows that break naive code — an empty name, a null email, a unicode name — so the default path is realistic and the edge path is opt-in and explicit.
5. Because everything is in-memory, the fixture is hermetic and fast: no checked-in CSV to rot, no file I/O to slow the suite, and the data's provenance is the seeded code itself.

Output.

Test needs	Factory call	Deterministic
happy path, 10 rows	customer_factory()	yes
bulk, 10k rows	customer_factory(size=10_000)	yes
edge cases	include_edge_cases=True	yes
checked-in CSV	—	drifts, avoid

Rule of thumb. Ship fixtures as seeded factories that build in-memory data parameterised by size and edge-case mix, not as checked-in files that rot. Seed inside the factory so every test is deterministic and every failure reproduces, and make edge cases an explicit opt-in so the default path stays realistic.

Worked example — a CI generation step with a quality gate

Detailed explanation. In CI, synthetic data is generated as an artifact from a seeded config and then gated before any test trusts it. Wire a generation-plus-gate step so a bad dataset fails the build. Sketch the CI job and the gate.

• Generate. from a seeded config → a reproducible artifact.
• Gate. referential integrity + distribution sanity + required edge cases.
• Fail fast. the build stops if the gate fails.

Question. Write a CI step that generates the synthetic dataset and fails the build unless it passes an integrity-and-quality gate.

Input.

Gate check	Rule	On fail
Referential integrity	zero orphan FKs	fail build
Row counts	within expected range	fail build
Null rate	≤ configured max	fail build
Required edge cases	present	fail build

Code.

# .github/workflows/test.yml — generate synthetic data, gate it, THEN run tests.
jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: pip install -r requirements.txt
      - name: Generate synthetic fixtures (seeded → reproducible artifact)
        run: python scripts/generate_fixtures.py --seed 42 --scale 1 --out data/
      - name: Quality + integrity gate (fails the build on bad data)
        run: python scripts/gate_fixtures.py --data data/     # exits non-zero on failure
      - name: Run tests (only reached if the gate passed)
        run: pytest -q

# scripts/gate_fixtures.py — the build-failing gate.
import sys, pandas as pd

def gate(data_dir: str) -> list[str]:
    customers = pd.read_parquet(f"{data_dir}/customers.parquet")
    orders    = pd.read_parquet(f"{data_dir}/orders.parquet")
    problems = []
    # 1. Referential integrity: zero orphan order → customer FKs.
    keys = set(customers["customer_id"])
    if (orphans := int((~orders["customer_id"].isin(keys)).sum())):
        problems.append(f"{orphans} orphan order FKs")
    # 2. Distribution sanity: row counts + null rate within expected bounds.
    if not (900 <= len(orders) <= 1100):
        problems.append(f"order count {len(orders)} out of range")
    if orders["total_cents"].isna().mean() > 0.0:
        problems.append("unexpected null totals")
    # 3. Required edge case present (a zero-total order the tests rely on).
    if not (orders["total_cents"] == 0).any():
        problems.append("missing zero-total edge case")
    return problems

if (found := gate(sys.argv[sys.argv.index("--data") + 1])):
    print("GATE FAILED:", "; ".join(found)); sys.exit(1)   # non-zero → build stops
print("GATE PASSED")

Step-by-step explanation.

1. The CI job generates the dataset from a seeded config (--seed 42), so the artifact is reproducible: the same commit produces the same data, and a failure can be reproduced locally with the same seed.
2. The generation step is separate from the test step, so the data is an explicit artifact built from config — not a binary committed to git that drifts and hides its provenance.
3. gate_fixtures.py runs before the tests and checks three things: referential integrity (zero orphan FKs), distribution sanity (row counts and null rates within bounds), and the presence of the required edge cases the tests depend on.
4. The gate sys.exit(1) on any problem, which makes the CI step fail and stops the build — tests never run on data that would give false results, because the pipeline refuses to proceed.
5. The required-edge-case check is the subtle one: a generator change that silently stopped emitting zero-total orders would make the tests that cover that path pass vacuously; asserting the edge case exists keeps the suite honest.

Output.

Dataset state	Gate	Tests run?
valid, in-range, edge cases present	PASS	yes
orphan FKs	FAIL	no (build stops)
row count out of range	FAIL	no
missing required edge case	FAIL	no

Rule of thumb. Generate synthetic data as a seeded CI artifact and put a build-failing gate between generation and the tests — referential integrity, distribution sanity, and required-edge-case presence. A gate that fails the build is what stops silently-broken synthetic data from turning your test suite into a source of false confidence.

Worked example — detecting generator drift with a statistics baseline

Detailed explanation. A dependency bump or a config change can silently alter what a generator emits, changing what your tests actually cover. A drift check pins expected statistics and fails when the new data strays. Baseline the synthetic dataset's key stats and compare.

• Baseline. store expected row count, null rate, value ranges, category coverage.
• Compare. on each run, assert current stats are within tolerance.
• Fail on drift. a large deviation stops the build for review.

Question. Write a drift check that fails when a regenerated dataset's key statistics deviate beyond tolerance from a stored baseline.

Input.

Statistic	Baseline	Tolerance
row count	1000	±10%
null rate (email)	0.05	±0.02
mean total_cents	4200	±15%
distinct regions	5	exact

Code.

BASELINE = {"rows": 1000, "email_null_rate": 0.05, "mean_total": 4200, "regions": 5}

def check_drift(df, baseline=BASELINE) -> list[str]:
    drift = []
    rows = len(df)
    if abs(rows - baseline["rows"]) / baseline["rows"] > 0.10:
        drift.append(f"row count {rows} drifted >10% from {baseline['rows']}")
    null_rate = df["email"].isna().mean()
    if abs(null_rate - baseline["email_null_rate"]) > 0.02:
        drift.append(f"email null rate {null_rate:.3f} drifted from {baseline['email_null_rate']}")
    mean_total = df["total_cents"].mean()
    if abs(mean_total - baseline["mean_total"]) / baseline["mean_total"] > 0.15:
        drift.append(f"mean total {mean_total:.0f} drifted >15%")
    if df["region"].nunique() != baseline["regions"]:
        drift.append(f"region coverage {df['region'].nunique()} != {baseline['regions']}")
    return drift

if (d := check_drift(synthetic_orders)):
    raise AssertionError("GENERATOR DRIFT: " + "; ".join(d))   # stop the build for review

Step-by-step explanation.

1. BASELINE captures the expected shape of the synthetic data — row count, a specific null rate, a mean, and exact category coverage — so "what the tests assume" is written down, not implicit.
2. On each run, check_drift recomputes those statistics on the freshly generated data and compares each to the baseline within a per-statistic tolerance (percentage for magnitudes, absolute for rates, exact for coverage).
3. Row-count and mean checks use a relative tolerance because their acceptable range scales with size; the null-rate check uses an absolute tolerance because a small rate's meaningful drift is a few points; region coverage is exact because a missing category silently drops test coverage.
4. Any deviation beyond tolerance is collected and raised, failing the build so a human reviews why the generator's output changed — a legitimate config change updates the baseline; an accidental dependency bump is caught.
5. This closes the last hole: seeding makes data reproducible within a generator version, but a version bump can still change the output — the drift gate is what notices, so tests never silently change meaning under you.

Output.

Change	Drift check	Build
no change (same version)	no drift	pass
dependency bump alters output	drift detected	fail (review)
intentional config change	drift → update baseline	pass after review
category silently dropped	coverage mismatch	fail

Rule of thumb. Pin a statistics baseline (row counts, null rates, ranges, category coverage) and fail the build when a regenerated dataset drifts beyond tolerance — seeding guarantees reproducibility within a generator version, but only a drift gate catches a version or config change that silently alters what your tests cover.

Senior data-engineering question on a synthetic test-data pipeline

A senior interviewer might ask: "Replace a team's habit of copying masked production data with a synthetic test-data pipeline. Cover how developers get deterministic fixtures, how CI generates realistic data at the right scale, how you stop bad or drifted synthetic data from producing false test results, and how the whole thing stays reproducible and free of production PII — end to end."

Solution Using seeded factories, an on-demand CI generation step, and integrity + drift gates

# 1. Deterministic fixtures for developers/unit tests — seeded factory, in-memory.
import pytest
from faker import Faker

@pytest.fixture
def dataset_factory():
    def _build(size=100, scale=1, seed=42, edge_cases=True):
        Faker.seed(seed); fake = Faker(); fake.unique.clear()
        customers = [{"customer_id": i, "name": fake.name(),
                      "email": fake.unique.email()} for i in range(1, size + 1)]
        # ... orders/items via captured keys (section 2) or SDV sample(scale) for bulk
        return build_related(customers, scale=scale, edge_cases=edge_cases)
    return _build

# 2. CI generates a SEEDED artifact at the right scale, then gates before tests.
steps:
  - run: python scripts/generate_fixtures.py --seed 42 --scale 1 --out data/   # unit scale
  - run: python scripts/gate_fixtures.py --data data/   # RI + distribution + edge-case gate
  - run: pytest -q                                        # only if the gate passed
  # nightly perf job reuses the SAME generator at --scale 50 for a large dataset

# 3. The gate: referential integrity + drift baseline — both fail the build.
def ci_gate(tables) -> None:
    problems = check_referential_integrity([                 # zero orphans (section 4)
        (tables["orders"], "customer_id", tables["customers"], "customer_id"),
        (tables["items"],  "order_id",    tables["orders"],    "order_id"),
    ]) or []
    problems += check_drift(tables["orders"])                # stats within tolerance
    if problems:
        raise SystemExit("CI GATE FAILED: " + "; ".join(problems))   # build stops

Step-by-step trace.

Layer	Component	Guarantee
Dev/unit	seeded fixture factory	deterministic, edge-case-rich, no PII
CI generate	seeded artifact --scale	reproducible, right size per job
Integrity gate	zero orphan FKs	data the DB would accept
Drift gate	stats within baseline	tests keep their meaning
Perf job	same generator --scale 50	large dataset, same code
Everywhere	generated, never copied	zero production PII

After adoption, developers get deterministic, edge-case-rich fixtures from a seeded factory; CI generates a reproducible artifact at unit scale and a nightly job reuses the same generator at 50x for perf; a two-part gate (referential integrity + drift baseline) fails the build before tests ever run on bad or drifted data; and because everything is generated from seeds and configs, no production record ever touches a laptop, a runner, or an artifact store. The masked-copy habit is gone, replaced by data versioned and validated like code.

Output:

Property	Masked-copy habit	Synthetic pipeline
PII in CI/laptops	yes	none (generated)
Reproducible fixtures	no (dump drifts)	yes (seeded)
Right scale per job	one fixed dump	--scale per job
Bad-data false results	possible	blocked (gates)
Silent generator drift	undetected	caught (baseline)
Data provenance	opaque dump	seed + config

Why this works — concept by concept:

• Seeded fixture factories — building deterministic, parameterised data in-memory gives developers reproducible, edge-case-rich fixtures whose provenance is the seeded code, so failures reproduce and no file rots in git.
• On-demand scaled generation — one generator driven by a seed and a scale produces unit-sized data for tests and million-row data for perf jobs, so the same reproducible code serves every scale instead of a fixed dump.
• Referential-integrity gate — failing the build on any orphan FK means tests only ever run on data the database itself would accept, eliminating a class of false results.
• Drift baseline gate — pinning expected statistics catches a generator or dependency change that would silently alter what the tests cover, keeping the suite's meaning stable across versions.
• Cost — cheap seeded generation and O(rows) gates on every run, versus the liability of copied PII and the debugging cost of flaky or vacuously-passing tests. The eliminated cost is production data in low-trust environments and the false confidence of tests running on unvalidated data — O(rows) generation for O(1) risk and honest coverage.

Defensive coding
Topic — defensive-coding
Defensive coding problems on fixtures, gates, and reproducibility

Practice →

ETL
Topic — etl
ETL problems on test-data pipelines and quality gates

Practice →

---

Cheat sheet — synthetic data generation

• Why synthetic, not masked prod. Copying production spreads PII into low-trust environments; masking still preserves structure, distributions, and quasi-identifiers (re-identifiable) and stays inside the compliance perimeter. Synthetic data has no data subject — nothing to re-identify — so it is the stronger default for anything leaving production.
• Pick the generator by need. Plausible fields, no real data → rule-based (Faker/Mimesis). Must preserve real distributions/correlations → statistical (SDV). Trained on real data and shared externally → differential privacy (Gretel). Related schema → SDV multi-table or captured-key rule-based. Match the generator to the weakest requirement that still satisfies the use.
• Faker template. Faker.seed(n) (class-level) for reproducibility; fake.unique.email() + fake.unique.clear() per factory; Faker('ja_JP') for locale; compose providers into rows; draw child FKs from captured parent keys; derive dependent fields (totals) rather than drawing them.
• Mimesis template. Field(Locale.DE, seed=42) + Schema(schema=..., iterations=N).create() for fast, seeded, locale-correct bulk generation. Reach for Mimesis over Faker when throughput matters; Faker for the wider provider ecosystem.
• SDV single-table. Metadata.detect_from_dataframe(df) → GaussianCopulaSynthesizer (fast, correlations) or CTGANSynthesizer (deep, complex distributions) → fit(df) → sample(n). It learns statistics, not rows, so no real record is copied.
• SDV multi-table. Declare PKs + every relationship in Metadata; HMASynthesizer fits tables and relationships; sample(scale=k) resizes while keeping FKs valid and cross-table correlations intact. Only relationships you declare are preserved.
• Gretel + differential privacy. Train on real data with differential_privacy: {enabled: True, epsilon: e} + privacy_filters (drop leaky outliers); pick epsilon from policy first, measure utility after. Gate the release on both a quality score (utility) and a privacy score (re-id risk); ship the epsilon + reports as auditor evidence. Synthesis without DP is not a privacy guarantee.
• Referential integrity. Rule-based: generate parents first, capture PKs, draw child FKs from them. SDV: declare the relationship graph. Always finish with a declarative check — every child FK ∈ parent PK set — that fails the build on any orphan. Integrity (valid FKs) and realism (correct fan-out/correlation) are different bars; hit both.
• Fixtures as seeded factories. Return a builder parameterised by size/edge_cases, seeded inside so tests are deterministic and failures reproduce. Prefer in-memory generation over checked-in CSVs that rot and hide provenance.
• Gates in CI. Generate a seeded artifact (reproducible from config), then gate before tests: referential integrity (zero orphans), distribution sanity (counts/null rates/ranges), required edge cases present, and a drift baseline (catches a generator/dependency change that silently alters coverage). A build-failing gate is what stops bad synthetic data from producing false results.
• Reproducibility. Seed every RNG the generator touches (Faker's class RNG, random, numpy, SDV). A fixture is only reproducible if every source of randomness is pinned. Cache datasets by seed+config so CI regenerates only on change.
• Scale by the job. Same generator, different scale: tens of rows for unit tests, SDV scale to millions for perf — never a fixed dump. Generated data is versioned and validated like code, not SCP'd into staging.

Frequently asked questions

What is synthetic data generation and when should a data engineer use it?

Synthetic data generation is the practice of fabricating data that looks and behaves like real production data but contains none of production's actual records — produced from rules (Faker, Mimesis), from a statistical/ML model that learned a real dataset (SDV), or from a privacy-preserving platform (Gretel). You should reach for it whenever you need data to build, test, demo, or share and the real data is sensitive, restricted, or simply not available: CI fixtures that must run on every laptop without leaking PII, realistic load-test datasets, edge-case coverage a tidy hand-written fixture never provides, and datasets you can hand to a partner. The core wins are safety (no data subject to leak), reproducibility (seed the generator and every run is identical), and coverage (generate the null, the duplicate, the boundary date, and the million-row table on demand) — none of which a masked production dump gives you.

Faker vs Mimesis — which rule-based generator should I pick?

They do the same job — emit believable field values from providers, parameterised by locale and pinned by a seed — so the choice is about ecosystem versus throughput. Pick Faker when you want the largest provider library and community add-ons, or when your team already knows its API; it is the de facto standard for fixtures. Pick Mimesis when you are generating a lot of rows and speed matters: its typed, locale-first Field + Schema API generates bulk data markedly faster than Faker because it avoids some per-call overhead. Both seed for reproducibility (Faker.seed(n) vs. Field(seed=n)) and both are purely rule-based — neither preserves the relationships between fields in real data, so if your test depends on age correlating with income, you want SDV, not either of these. Many teams use Faker by default and switch to Mimesis for the bulk generation paths.

When do I need SDV instead of Faker or Mimesis?

Use SDV when the synthetic data must preserve the statistical properties of a real dataset — the distributions of individual columns and, critically, the correlations between them — which rule-based generators cannot do because they draw each field independently. A load test, a model-training set, or any analysis that depends on realistic relationships (high-value customers ordering more, older accounts having more history) needs SDV: you fit a synthesizer (GaussianCopulaSynthesizer for speed and correlations, CTGANSynthesizer for complex distributions) on the real data and sample new rows that mirror its statistics without copying any actual record. SDV also handles the multi-table case with HMASynthesizer, learning relationships across tables so foreign keys stay valid and cross-table correlations survive. If you only need plausible field values with no real relationships, Faker/Mimesis is simpler, faster, and needs no training data.

Is synthetic data automatically private and safe to share?

No — and this is the most common mistake. Synthetic data that is purely rule-based (Faker/Mimesis) is safe because it was never derived from real records, but synthetic data trained on real data (SDV, or Gretel without differential privacy) can memorise and leak outliers or near-duplicates of real individuals, so "it's synthetic" is not by itself a privacy guarantee. To make trained-on-real synthetic data provably safe to share, use differential privacy (Gretel's differential_privacy with an epsilon budget), which bounds how much any single real record can influence the output, and add outlier/similarity filters to drop the rows most likely to leak. Then prove it: read the privacy report (membership-inference / re-identification risk) alongside the quality report (utility), and gate the release on both. The auditor's question is "what epsilon did you train at, and what does the privacy report say?" — a defensible pipeline can answer both with numbers.

How do I keep foreign keys valid across a multi-table synthetic dataset?

Two disciplined approaches, both finished with a check. The rule-based way: generate tables in parent-before-child order, capture each parent's primary keys, and draw every child foreign key from that captured set — never from a raw random integer — so integrity holds by construction. The statistical way: declare the schema's primary keys and relationships in SDV Metadata and let HMASynthesizer fit the tables and the relationships, then sample() a whole related dataset whose foreign keys are valid because generation follows the declared edges (and which also preserves cross-table correlations that per-table generation destroys). Whichever you use, always end with a declarative referential-integrity check — every child FK must appear in the parent PK set — that fails the build on any orphan, because a synthetic dataset with orphan keys is worse than none: it makes tests pass on data a real FOREIGN KEY constraint would reject. Remember that valid keys and realistic cardinality (fan-out, correlation) are separate bars — hit both.

How do I wire synthetic data into my test and CI pipeline without flaky tests?

Treat synthetic data as versioned, validated infrastructure, not a script. Give developers seeded fixture factories — functions (or pytest fixtures) that build deterministic in-memory data parameterised by size and edge-case mix, seeded inside so every test is reproducible and every failure reproduces (unseeded generation is the number-one cause of flaky data-driven tests). In CI, generate the dataset as a seeded artifact from a config rather than committing a CSV that rots, and match scale to the job — tens of rows for unit tests, SDV scale to millions for a nightly perf run, from the same generator. Then put a build-failing gate between generation and the tests: referential integrity (zero orphan FKs), distribution sanity (row counts, null rates, value ranges), required-edge-case presence, and a drift baseline that catches a generator or dependency change silently altering the data. Seeding guarantees reproducibility within a version; the drift gate catches changes across versions; the integrity and quality gates stop bad data from ever producing a false test result.

Practice on PipeCode

• Drill the data processing practice library → for the record-shaping, provider-composition, and bulk-generation problems that Faker and Mimesis make concrete.
• Harden your fixtures on the data validation practice library → for the PII, referential-integrity, and quality-gate checks that keep synthetic data safe and correct.
• Wire it into pipelines on the ETL practice library → for the multi-table generation, integrity, and CI-gate scenarios where synthetic test data earns its keep.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the seeding, referential-integrity, and privacy patterns against real graded inputs — Faker, SDV, Gretel, and Mimesis in practice.

Lock in synthetic-data muscle memory

Docs explain Faker, SDV, Gretel, and Mimesis. PipeCode drills explain the decision — when a masked prod copy is never safe, when `referential integrity` must hold by construction, when a per-record `differential privacy` bound is the only defensible answer, and when a seeded factory beats a checked-in fixture. Pipecode.ai is Leetcode for Data Engineering — test-data and pipeline practice tuned for the safety and reproducibility trade-offs data engineers actually face.

Practice data processing problems →
Practice data validation problems →