Multi-table Databases

A single Faker table answers "what does a row look like?". A database also has to answer "which rows belong to which": every order points at an existing customer, each customer has a plausible number of orders, only shipped orders have a shipment, and the payment equals the sum of the order's lines.

Passing tables= to fit() switches the FakerSynthesizer to schema mode. One schema describes several tables and the relationships between them. sample() generates them in dependency order and returns a MultiDataset in which every foreign key resolves. It is the same class as single-table mode, and fit(metadata, ...) behaves exactly as before.

from ydata.synthesizers import FakerSynthesizer

synth = FakerSynthesizer()
synth.fit(tables="shop.yaml", calculated_features=features)   # path, dict or FakerSchema
db = synth.sample(sample_size={"customers": 2_000, "products": 300}, random_state=42)

db["orders"].to_pandas()      # one Dataset per table
synth.error_labels_           # one row per injected error

A schema with one table returns a Dataset, and a schema with several returns a MultiDataset.

The schema

The layout follows the LLM synthesizer's schema where both engines mean the same thing (tables, primary_key, foreign_keys, scaffold, table_errors). Each table keeps the single-table Faker configuration you already know: columns, distributions, conditions, sequence, totals, derived_tables and errors. Every Faker configuration loads into one type, FakerSchema (load_faker_schema(path), or fit(tables=path) directly). The reserved top-level tables: key marks a database; a file without it is a single table, loaded as a one-table schema named data.

tables:
  customers:
    primary_key: customer_id
    key_format: "CUS-{n:06d}"
    columns:
      customer_id: {datatype: string, vartype: string}
      segment:     {datatype: categorical, vartype: string, categories: {consumer: 80, business: 20}}
      city:        {datatype: string, vartype: string, provider: city}

  orders:
    primary_key: order_id
    foreign_keys:
      - column: customer_id
        referenced_table: customers
        cardinality: {distribution: poisson, lam: 2.5, min: 1, max: 12}
        context_columns: [segment]
    columns:
      order_id:        {datatype: numerical, vartype: int}
      customer_id:     {datatype: string, vartype: string}
      shipping_method: {datatype: categorical, vartype: string,
                        categories: {standard: 70, express: 25, pickup: 5}}
    conditions:
      - column: shipping_method
        rules: [{when: {customers.segment: business}, values: {express: 0.7, standard: 0.3}}]

Table keys

Key	Meaning
primary_key	The table's key column. Integer keys are generated as 1..N; string keys follow key_format.
key_format	str.format template with one n field for string keys, e.g. "OP-{n:08d}". Defaults to the table's initial plus five digits.
foreign_keys	Relationships to other tables (below).
scaffold	A structural grid, as in single-table mode. from_table may name another table of the schema, and it is resolved against the generated data.
table_errors	referential_integrity: [{column, rate}] — foreign key values replaced by keys that do not exist in the parent.
description	Free text documenting the table. It is not used for generation.
columns, distributions, conditions, sequence, totals, derived_tables, errors	The single-table configuration, unchanged.

Foreign keys

Key	Meaning
column, referenced_table	The child column and the parent table. The parent must have a primary key, and the column types must match.
cardinality	Children per parent. Either a distribution spec ({distribution: poisson, lam: 2, min: 1, max: 8}, clipped by min/max) or integer bounds ({min: 1, max: 3}, uniform). With cardinality the foreign key drives the table's row count, and at most one foreign key per table may declare it.
where	Parent filter: column → value or list of values, ANDed across columns. Only matching parents get children, or can be sampled.
unique	For sampled foreign keys: each parent is used at most once.
context_columns	Parent columns available to the child during generation, usable in conditions and calculated features, and not copied into the output. Each entry is a column of the parent, or a path to an ancestor's column through the parent's foreign keys (country_id.iso_code). See Parent context below.

A foreign key without cardinality is sampled: each child row picks one of the (filtered) parents.

Schema-mode column keys

Key	Meaning
provider	Any Faker provider by name (city, state_abbr, company, street_name, ...) for string columns not covered by characteristic.
_-prefixed column names	Helper columns. They are generated and usable by conditions and calculated features, then dropped from the output. Use them for mixtures, hidden segments or rare anomaly flags. A declared primary or foreign key is always kept, whatever its name (e.g. _id).

Prompts are not part of a Faker schema. Use description to document intent.

Parent context

context_columns makes parent values available to the child during generation. Each requested column can be addressed through the foreign key it comes from, as <fk column>.<column>. A column requested through only one foreign key to its parent can also be addressed with the short form <Parent>.<column>, as in the customers.segment example above.

The foreign key form matters when a table references the same parent twice. A flight has an origin and a destination airport, and its fare depends on both countries:

  flights:
    primary_key: flight_id
    foreign_keys:
      - {column: origin_airport,      referenced_table: airports, context_columns: [country]}
      - {column: destination_airport, referenced_table: airports, context_columns: [country]}
    columns:
      flight_id:           {datatype: numerical, vartype: int}
      origin_airport:      {datatype: string, vartype: string}
      destination_airport: {datatype: string, vartype: string}
      fare:                {datatype: numerical, vartype: float, min: 50, max: 2000}
    conditions:
      - column: fare
        rules:
          - when: {origin_airport.country: US, destination_airport.country: CA}
            distribution: {distribution: uniform, low: 1500, high: 2000}
        default: {distribution: {distribution: uniform, low: 50, high: 600}}

Here airports.country would not say which airport is meant, so fit() rejects it wherever it is read and names the two foreign key forms to use instead.

An entry can also follow foreign keys further up. Each segment before the last is a foreign key of the table reached so far, and the last segment is a column of the table it ends on. If the country is its own table (airports.country_id references countries), the same rule reads the country's code two hops away:

      - {column: origin_airport,      referenced_table: airports, context_columns: [country_id.iso_code]}
      - {column: destination_airport, referenced_table: airports, context_columns: [country_id.iso_code]}
    ...
          - when: {origin_airport.country_id.iso_code: US, destination_airport.country_id.iso_code: CA}

Calculated features read the same names with the table in front, e.g. flights.origin_airport.country_id.iso_code. The last segment may be a declared column, a _ helper column or a calculated feature of the table it reaches. Every other segment must be a foreign key.

Which rows exist

Each table's rows come from exactly one driver, checked in this order:

Driver	When	Rows
Supplied data	The table is in existing_data	The supplied rows, with their keys kept
Parent seed	The parent of a supplied table	One row per distinct foreign key value used by the supplied table
Scaffold	scaffold: is declared	The materialized grid
1:1 extension	The primary key is also a foreign key	One row per (filtered) parent, sharing its key
Driving foreign key	A foreign key declares cardinality	The sum of the drawn counts over the (filtered) parents
Root	None of the above	sample_size

sample_size sizes root tables only: an int applies to every root table, and {table: rows} sizes them individually. Sizing a table whose rows come from its relationships raises an error that names the root tables.

Calculated features across tables

Calculated features use table.column addressing. Features within one table run inside that table's generation, exactly as in single-table mode. A feature that reads another table declares reference_keys:

{
    "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()),
    "calculated_from": ["order_items.line_total"],
    "reference_keys": {"source": "order_items.order_id", "target": "payments.order_id"},
}

The function receives the source columns, then the source key. It returns either a DataFrame with the source key and the outputs (an aggregate, as above) or values aligned with the source rows (a copy). The result is merged onto the target by the key before the target is generated, so the target's conditions can use it. A cross-table feature also orders generation (source before target), and a cycle is reported with its path.

Errors and labels

Each table's errors block works as in single-table mode. It may be written unkeyed ({columns: ..., table: ...}) or keyed by table name, as in a single-table file. Errors are applied once every table exists, so children and calculated features are always derived from clean parents, and the corruption only lands in the delivered data.

synth.error_labels_ lists every error injected by the last sample(): table, row, pk_value, column, error_type, original_value, injected_value. In single-table mode it is set as well when errors are configured (without pk_value). After schema-mode sampling it is an empty DataFrame when no errors are configured; single-table mode leaves it as None. Every fit() resets it to None, so labels from a previous model never carry over.

Before errors are injected, the clean data is checked against the schema's keys and relationships; a violation there means a contradictory schema and is raised as a warning.

Checking referential integrity

To check a generated database, run the ReferentialIntegrityValidator from ydata-report on the MultiDataset. It reports primary key violations (duplicates, nulls), orphan foreign keys, parent coverage and children per parent, per table and in total:

from ydata.metadata import MultiMetadata
from ydata.report import ReferentialIntegrityValidator

results = ReferentialIntegrityValidator(synthetic_data=db, metadata=MultiMetadata(db)).validate()
results["summary"]["total_fk_violations"]           # orphans across all tables
results["tables"]["shipments"]["foreign_keys"]["order_id"]["orphan_count"]

With table_errors.referential_integrity, the orphans it finds are exactly the rows labelled referential_integrity in error_labels_, as the worked example below asserts.

Supplied tables

existing_data={"orders": df} keeps the rows and keys of a real table and generates only the schema columns it lacks. Parents it references are seeded from the foreign key values it actually uses, so the generated database stays consistent with the supplied rows. anchor_tables=["orders"] additionally requires the supplied table to carry a unique, non-null primary key.

Worked example

An online shop: customers, products, orders, order lines, payments, shipments and reviews. It uses every relationship type, cross-table calculated features, errors and labels, and asserts each guarantee.

# 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: 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.")

Notes

Volume

String columns backed by provider, characteristic or regex (and neither unique nor the target of a condition) are drawn from a pool of generated values: 5,000 by default, set with fit(pool_size=...) to any positive integer. pool_size=None generates every value individually. With pooling, a six-table database with 100,000+ rows per table generates in seconds. A unique column is never pooled. A unique provider column gets one value per row without repeats, which costs at least one provider call per row; if the provider cannot produce enough distinct values (e.g. state_abbr for more than 50 rows), a warning is logged and duplicates are allowed, as in single-table mode.

Single-table arguments

In schema mode, scaffold, distributions, conditions, sequence, totals, derived_tables, errors, reference_data and table_name belong inside each table's block. Passing them to fit() together with tables= raises an error.

Starting from an LLM synthesizer schema

FakerSchema.from_llm_schema(tables) converts an LLM synthesizer schema into a Faker schema skeleton: tables, keys, relationships and categorical values are copied and prompts become description. It also returns the columns that still need ranges.

Not supported yet

Self-referencing foreign keys (for example manager_id → employees) and composite keys.

Related Materials

• Structural Scaffolds
• Distributions and Conditional Generation
• Error Injection
• Calculated Features (Business Rules)
• Faker Synthesizer overview