Multi-table database with the Faker Synthesizer
This section generates a relational database from a schema alone, with no source data. One YAML file declares the tables, their keys and relationships (orders per customer, lines per order, which orders get a payment or a shipment) and each table's columns. FakerSynthesizer.fit(tables=...) turns it into a MultiDataset where every foreign key resolves. Calculated features (Python callables) copy the product price onto the order lines and sum them into the payment.
The schema reference is in Multi-table Databases.
Configuration file
# Multi-table FakerSynthesizer configuration: an online shop database.
#
# The top-level `tables:` key makes this a database: several tables in one
# FakerSchema (load_faker_schema). Each table carries the single-table blocks you already
# know (columns, distributions, conditions, errors, ...) plus its relational
# declarations: primary_key, key_format, foreign_keys, table_errors.
#
# customers ──< orders ──< order_items >── products
# │
# ├── payments (1:1, every non-cancelled order)
# └── shipments (1 per shipped / delivered order)
# └── reviews (sampled: at most one per delivered order)
tables:
customers:
primary_key: customer_id
key_format: "CUS-{n:06d}"
columns:
customer_id: {datatype: string, vartype: string}
name: {datatype: string, vartype: string, characteristic: name}
email: {datatype: string, vartype: string, characteristic: email}
city: {datatype: string, vartype: string, provider: city} # any Faker provider
segment: {datatype: categorical, vartype: string,
categories: {consumer: 80, business: 20}}
signup_date: {datatype: date, vartype: date, min: "2023-01-01", max: "2025-12-31",
format: "%Y-%m-%d"}
products:
primary_key: sku
key_format: "SKU-{n:05d}"
columns:
sku: {datatype: string, vartype: string}
category: {datatype: categorical, vartype: string,
categories: {books: 35, electronics: 20, home: 30, toys: 15}}
list_price: {datatype: numerical, vartype: float, min: 2, max: 2500}
conditions:
- column: list_price
rules:
- {when: {category: electronics}, distribution: {distribution: lognormal, mean: 5.0, sigma: 0.7}}
- {when: {category: books}, distribution: {distribution: lognormal, mean: 2.8, sigma: 0.3}}
default: {distribution: {distribution: lognormal, mean: 3.5, sigma: 0.6}}
orders:
primary_key: order_id # integer key -> 1..N
foreign_keys:
- column: customer_id
referenced_table: customers
cardinality: {distribution: poisson, lam: 2.5, min: 1, max: 12} # orders per customer
context_columns: [segment] # -> customers.segment
columns:
order_id: {datatype: numerical, vartype: int}
customer_id: {datatype: string, vartype: string}
order_date: {datatype: date, vartype: datetime, min: "2026-01-01 00:00:00",
max: "2026-06-30 23:59:59", format: "%Y-%m-%d %H:%M:%S"}
status: {datatype: categorical, vartype: string,
categories: {placed: 10, shipped: 25, delivered: 55, cancelled: 10}}
shipping_method: {datatype: categorical, vartype: string,
categories: {standard: 70, express: 25, pickup: 5}}
conditions:
- column: shipping_method # business customers favour express
rules:
- {when: {customers.segment: business}, values: {express: 0.7, standard: 0.3}}
default: {values: {standard: 0.72, express: 0.2, pickup: 0.08}}
order_items:
primary_key: item_id
key_format: "ITM-{n:08d}"
foreign_keys:
- column: order_id
referenced_table: orders
cardinality: {distribution: poisson, lam: 1.8, min: 1, max: 10} # lines per order
- column: sku # no cardinality: sampled from products
referenced_table: products
columns:
item_id: {datatype: string, vartype: string}
order_id: {datatype: numerical, vartype: int}
sku: {datatype: string, vartype: string}
quantity: {datatype: numerical, vartype: int, min: 1, max: 20}
_discount_tier: {datatype: categorical, vartype: string, # helper: dropped
categories: {none: 80, promo: 20}}
distributions:
quantity: {distribution: poisson, lam: 1.5, min: 1}
# unit_price (copied from the product) and line_total are calculated features
errors:
columns:
quantity: {out_of_range: {rate: 0.005, factor: 50}} # rogue bulk lines
payments:
primary_key: order_id # the PK is also the FK: a 1:1 extension
foreign_keys:
- column: order_id
referenced_table: orders
where: {status: [placed, shipped, delivered]} # no cancelled orders
columns:
order_id: {datatype: numerical, vartype: int}
method: {datatype: categorical, vartype: string,
categories: {card: 70, paypal: 20, bank_transfer: 10}}
# amount = sum of the order's line totals (cross-table calculated feature)
shipments:
primary_key: shipment_id
key_format: "SHP-{n:07d}"
foreign_keys:
- column: order_id
referenced_table: orders
cardinality: {min: 1, max: 1}
where: {status: [shipped, delivered], shipping_method: [standard, express]}
context_columns: [shipping_method]
columns:
shipment_id: {datatype: string, vartype: string}
order_id: {datatype: numerical, vartype: int}
carrier: {datatype: categorical, vartype: string,
categories: {UPS: 40, FedEx: 35, DHL: 25}}
transit_days: {datatype: numerical, vartype: int, min: 1, max: 10}
conditions:
- column: transit_days
rules:
- {when: {orders.shipping_method: express},
distribution: {distribution: triangular, left: 1, mode: 1, right: 2}}
default: {distribution: {distribution: triangular, left: 2, mode: 4, right: 8}}
table_errors:
referential_integrity: [{column: order_id, rate: 0.01}] # orphan shipments
reviews:
primary_key: review_id
foreign_keys:
- column: order_id
referenced_table: orders
unique: true # sampled without replacement
where: {status: delivered}
columns:
review_id: {datatype: numerical, vartype: int}
order_id: {datatype: numerical, vartype: int}
rating: {datatype: categorical, vartype: int,
categories: {1: 5, 2: 5, 3: 15, 4: 35, 5: 40}}
Example Code
"""
Example: a multi-table database with the FakerSynthesizer.
Passing `tables=` to `fit()` switches the FakerSynthesizer to schema mode: one
schema describes several tables and the relationships between them, and
`sample()` returns a MultiDataset in which every foreign key resolves.
The schema (faker_multitable_database.yaml) is an online shop:
customers ──< orders ──< order_items >── products
├── payments 1:1 extension, only non-cancelled orders
├── shipments one per shipped/delivered order, with orphans injected
└── reviews sampled, at most one per delivered order
It shows each relationship type:
- driving foreign keys: `cardinality` sets how many orders each customer has
and how many lines each order has
- sampled foreign keys: each order line picks a product; `unique` makes each
review pick a different order
- `where`: only matching parents get children (or can be sampled)
- 1:1 extension: the payments primary key is also its foreign key
- `context_columns`: parent columns (customers.segment, orders.shipping_method)
drive the child's conditions without being copied into it
- cross-table calculated features: the unit price is copied from the product,
and the payment amount is the sum of the order's lines
- errors and `table_errors.referential_integrity`, with every injected cell
recorded in `synth.error_labels_`
- checking the result with ydata-report's ReferentialIntegrityValidator
Run with: python faker_multitable_database.py
"""
from pathlib import Path
import pandas as pd
from ydata.metadata import MultiMetadata
from ydata.report import ReferentialIntegrityValidator
from ydata.synthesizers import FakerSynthesizer
HERE = Path(__file__).parent
# ---------------------------------------------------------------------------
# Calculated features: `table.column` addressing. Cross-table features declare
# reference_keys; the function receives the source columns, then the source key.
# ---------------------------------------------------------------------------
DISCOUNT = {"none": 1.0, "promo": 0.85}
calculated_features = [
{ # copy each product's list price onto the order lines that reference it
"calculated_features": "order_items.unit_price",
"function": lambda price, sku: price, # row-aligned with products
"calculated_from": ["products.list_price"],
"reference_keys": {"source": "products.sku", "target": "order_items.sku"},
},
{ # intra-table: quantity x price, with the helper column's discount
"calculated_features": "order_items.line_total",
"function": lambda qty, price, tier: (qty * price * tier.map(DISCOUNT)).round(2),
"calculated_from": ["order_items.quantity", "order_items.unit_price",
"order_items._discount_tier"],
},
{ # aggregate: the payment is the sum of the order's lines
"calculated_features": "payments.amount",
"function": lambda line_total, order_id: (
pd.DataFrame({"order_id": order_id, "amount": line_total})
.groupby("order_id", as_index=False).sum().round(2)),
"calculated_from": ["order_items.line_total"],
"reference_keys": {"source": "order_items.order_id", "target": "payments.order_id"},
},
]
synth = FakerSynthesizer(locale="en")
synth.fit(tables=HERE / "faker_multitable_database.yaml",
calculated_features=calculated_features)
# only ROOT tables are sized; every other table follows from its relationships
db = synth.sample(
sample_size={"customers": 2_000, "products": 300, "reviews": 1_000},
random_state=42,
)
t = {name: db[name].to_pandas() for name in db.keys()}
customers, products, orders = t["customers"], t["products"], t["orders"]
items, payments, shipments, reviews = t["order_items"], t["payments"], t["shipments"], t["reviews"]
for name, frame in t.items():
print(f"{name:12s} {len(frame):>7,} rows {list(frame.columns)}")
# ---------------------------------------------------------------------------
# What the schema guarantees
# ---------------------------------------------------------------------------
# keys: key_format for strings, 1..N for integers
assert customers["customer_id"].str.fullmatch(r"CUS-\d{6}").all()
assert orders["order_id"].tolist() == list(range(1, len(orders) + 1))
# driving FK: every customer has between 1 and 12 orders
per_customer = orders.groupby("customer_id").size()
assert set(per_customer.index) == set(customers["customer_id"])
assert per_customer.between(1, 12).all()
# context: business customers get express or standard shipping, never pickup
business = set(customers.loc[customers["segment"] == "business", "customer_id"])
assert not ((orders["customer_id"].isin(business)) & (orders["shipping_method"] == "pickup")).any()
assert "customers.segment" not in orders.columns # context is not copied
# sampled FK + cross-table copy: the line price is the product's list price
merged = items.merge(products, on="sku")
assert len(merged) == len(items)
assert (merged["unit_price"] == merged["list_price"]).all()
assert "_discount_tier" not in items.columns # helper column dropped
# 1:1 extension with where: one payment per non-cancelled order, nothing else
paid = set(orders.loc[orders["status"] != "cancelled", "order_id"])
assert set(payments["order_id"]) == paid and payments["order_id"].is_unique
# aggregate: the payment equals the order's line totals (computed on clean data,
# so it still holds where `quantity` was later corrupted by out_of_range)
expected = items.groupby("order_id")["line_total"].sum().round(2)
assert (payments.set_index("order_id")["amount"] - expected.reindex(payments["order_id"])
).abs().max() < 0.011
# where + cardinality {1, 1}: one shipment per shipped/delivered, non-pickup order
eligible = orders[orders["status"].isin(["shipped", "delivered"])
& orders["shipping_method"].isin(["standard", "express"])]
labels = synth.error_labels_
orphans = labels[labels["error_type"] == "referential_integrity"]
clean_shipments = shipments[~shipments["shipment_id"].isin(orphans["pk_value"])]
assert set(clean_shipments["order_id"]) <= set(eligible["order_id"])
assert len(shipments) == len(eligible)
# express shipments take 1-2 days
express = shipments.merge(orders[["order_id", "shipping_method"]], on="order_id")
assert express.loc[express["shipping_method"] == "express", "transit_days"].le(2).all()
# unique + where: each review is for a different delivered order
delivered = set(orders.loc[orders["status"] == "delivered", "order_id"])
assert reviews["order_id"].is_unique and set(reviews["order_id"]) <= delivered
# ---------------------------------------------------------------------------
# Ground truth of the injected errors
# ---------------------------------------------------------------------------
print("\nInjected errors:")
print(labels.groupby(["table", "column", "error_type"]).size().to_string())
assert len(orphans) == int(0.01 * len(shipments))
assert not set(orphans["injected_value"]) & set(orders["order_id"]) # orphans point nowhere
# ---------------------------------------------------------------------------
# Referential integrity of the delivered database (ydata-report): the only
# violations it finds are exactly the orphans injected on purpose
# ---------------------------------------------------------------------------
integrity = ReferentialIntegrityValidator(synthetic_data=db, metadata=MultiMetadata(db)).validate()
summary = integrity["summary"]
print(f"\nPK violations: {summary['total_pk_violations']}, "
f"FK violations: {summary['total_fk_violations']}")
assert summary["total_pk_violations"] == 0
assert summary["total_fk_violations"] == len(orphans)
assert integrity["tables"]["shipments"]["foreign_keys"]["order_id"]["orphan_count"] == len(orphans)
# reproducibility: the same random_state gives the same database
again = synth.sample(sample_size={"customers": 2_000, "products": 300, "reviews": 1_000},
random_state=42)
assert again["orders"].to_pandas().equals(orders)
print("All relationship, calculated-feature and error checks passed.")