Derived Tables and Aggregations
A warehouse rarely contains one table. It contains a detail table and the summaries built from it — and the summaries must agree with the detail. The daily total is the sum of that day's rows, or the pipeline reading them has a bug.
Generating the detail and the summary independently guarantees they disagree, which makes the pair useless for testing anything that reconciles. derived_tables computes the extra tables from the generated table, after everything else, so reconciliation holds by construction.
The configuration block
derived_tables=[
{"name": "daily_sales", "type": "aggregate",
"group_by": ["order_date", "department"],
"aggregations": {
"net_revenue": {"column": "net_amount", "agg": "sum"},
"n_lines": {"column": "net_amount", "agg": "count"},
}},
{"name": "accounting_legs", "type": "custom",
"function": to_accounting_legs}, # DataFrame -> DataFrame
]
type: "aggregate"
Declarative and validated at fit() time.
| Key | Meaning |
|---|---|
name |
Table name in the result. Must be unique. |
group_by |
Grouping columns, from the post-calculated-features schema. |
aggregations |
Output column → {"column": <source>, "agg": <name>}. |
Whitelisted aggregations: sum, mean, min, max, count, nunique, first, last. The order-aware first and last require a declared sequence — without one, "the last row of the group" has no meaning.
type: "custom"
An escape hatch for shapes the declarative form does not cover: exploding rows into double-entry legs, pivoting, building a snapshot. The callable receives the finished base table and returns any DataFrame. Declared order is execution order.
The return type
With derived tables configured, sample() returns a dict of table name to Dataset rather than a single Dataset. The base table appears under table_name (default "data"):
synth.fit(metadata, table_name="order_lines", derived_tables=[...])
data = synth.sample(random_state=42)
lines = data["order_lines"].to_pandas()
daily = data["daily_sales"].to_pandas()
Worked example
An e-commerce order-line table with two aggregate summaries — one of them using the order-aware last — and a custom table exploding every line into balancing accounting legs.
"""
Example: derived tables — aggregates that reconcile with the detail.
A warehouse rarely contains one table. It contains a detail table and the
summaries built from it, and the summaries must AGREE with the detail: the
daily total is the sum of that day's rows, or the whole thing is a bug.
Generating detail and summary independently guarantees they disagree.
`derived_tables` computes the extra tables FROM the generated table, after
everything else, so reconciliation holds by construction. Two kinds:
- `type: "aggregate"` — declarative `group_by` + `aggregations`.
Whitelisted aggs: sum, mean, min, max, count, nunique, first, last.
`first`/`last` are order-aware and therefore require a `sequence`.
- `type: "custom"` — any DataFrame -> DataFrame callable, for shapes the
declarative form does not cover (here: exploding each order line into
double-entry accounting legs).
With derived tables configured, `sample()` returns a DICT of table name to
Dataset — the base table under `table_name` (default `"data"`).
Run with: python faker_derived_tables.py
"""
import pandas as pd
from ydata.metadata import Metadata
from ydata.metadata.builder import MetadataConfigurationBuilder
from ydata.synthesizers import FakerSynthesizer
VAT_RATE = 0.23
catalog = pd.DataFrame({
"sku": ["SKU-100", "SKU-200", "SKU-300", "SKU-400"],
"department": ["outdoor", "outdoor", "kitchen", "kitchen"],
})
columns = {
"channel": {"datatype": "categorical", "vartype": "string",
"categories": {"web": 60, "marketplace": 25, "store": 15}},
"units": {"datatype": "numerical", "vartype": "int", "min": 1, "max": 12},
"unit_price": {"datatype": "numerical", "vartype": "float",
"min": 4.99, "max": 249.0},
}
metadata = Metadata(configuration_builder=MetadataConfigurationBuilder(columns))
def to_accounting_legs(order_lines: pd.DataFrame) -> pd.DataFrame:
"""Explode every order line into a revenue leg and a VAT leg.
A `custom` derived table receives the finished base table and returns any
DataFrame — the escape hatch for shapes the declarative form cannot express.
"""
revenue = pd.DataFrame({
"order_date": order_lines["order_date"],
"sku": order_lines["sku"],
"account": "4000-revenue",
"amount": (-order_lines["net_amount"]).round(2),
})
vat = pd.DataFrame({
"order_date": order_lines["order_date"],
"sku": order_lines["sku"],
"account": "2400-vat-payable",
"amount": (-order_lines["vat_amount"]).round(2),
})
receivable = pd.DataFrame({
"order_date": order_lines["order_date"],
"sku": order_lines["sku"],
"account": "1100-receivable",
"amount": order_lines["gross_amount"].round(2),
})
return (pd.concat([revenue, vat, receivable], ignore_index=True)
.sort_values(["order_date", "sku", "account"], kind="stable")
.reset_index(drop=True))
synth = FakerSynthesizer(locale="en")
synth.fit(
metadata,
table_name="order_lines", # the base table's name in the result
scaffold={
"dimensions": [
{"column": "sku", "from_table": "catalog", "from_column": "sku",
"include": ["department"]},
{"column": "order_date",
"date_range": {"start": "2026-03-01", "end": "2026-03-07", "freq": "D"}},
{"column": "line_no", "values": [1, 2, 3]},
]
},
reference_data={"catalog": catalog},
# `last` below is order-aware, so an explicit ordering is required
sequence={"order_by": ["order_date", "line_no"], "partition_by": ["sku"]},
calculated_features=[
{"calculated_features": "net_amount",
"function": lambda units, price: (units * price).round(2),
"calculated_from": ["units", "unit_price"]},
{"calculated_features": "vat_amount",
"function": lambda net: (net * VAT_RATE).round(2),
"calculated_from": ["net_amount"]},
{"calculated_features": "gross_amount",
"function": lambda net, vat: (net + vat).round(2),
"calculated_from": ["net_amount", "vat_amount"]},
{"calculated_features": "cumulative_net",
"function": lambda net: net.cumsum().round(2),
"calculated_from": ["net_amount"],
"per_partition": True},
],
derived_tables=[
{"name": "daily_department_sales", "type": "aggregate",
"group_by": ["order_date", "department"],
"aggregations": {
"net_revenue": {"column": "net_amount", "agg": "sum"},
"gross_revenue": {"column": "gross_amount", "agg": "sum"},
"n_lines": {"column": "net_amount", "agg": "count"},
"units_sold": {"column": "units", "agg": "sum"},
"avg_line_value": {"column": "net_amount", "agg": "mean"},
"largest_line": {"column": "net_amount", "agg": "max"},
"distinct_skus": {"column": "sku", "agg": "nunique"},
}},
{"name": "sku_running_totals", "type": "aggregate",
"group_by": ["sku", "department"],
"aggregations": {
# order-aware: the LAST value in the declared sequence
"closing_cumulative_net": {"column": "cumulative_net", "agg": "last"},
"total_net": {"column": "net_amount", "agg": "sum"},
}},
{"name": "accounting_legs", "type": "custom",
"function": to_accounting_legs},
],
)
# a dict of Datasets, keyed by table name
data = synth.sample(random_state=42)
print("Tables returned:", sorted(data))
lines = data["order_lines"].to_pandas()
daily = data["daily_department_sales"].to_pandas()
running = data["sku_running_totals"].to_pandas()
legs = data["accounting_legs"].to_pandas()
# ---------------------------------------------------------------------------
# Reconciliation — the property the whole feature exists for
# ---------------------------------------------------------------------------
# 1. the aggregate is the detail, grouped: every group, every measure
recomputed = (lines.groupby(["order_date", "department"])
.agg(net_revenue=("net_amount", "sum"),
n_lines=("net_amount", "count"),
units_sold=("units", "sum"))
.reset_index())
merged = daily.merge(recomputed, on=["order_date", "department"],
suffixes=("", "_check"))
assert len(merged) == len(daily)
assert (merged["net_revenue"].round(2) == merged["net_revenue_check"].round(2)).all()
assert (merged["n_lines"] == merged["n_lines_check"]).all()
assert (merged["units_sold"] == merged["units_sold_check"]).all()
assert daily["n_lines"].sum() == len(lines), "no row is lost or double counted"
# 2. `last` respects the sequence, so the closing cumulative equals the total
assert (running["closing_cumulative_net"].round(2)
== running["total_net"].round(2)).all()
# 3. the custom table balances: every order line becomes three legs summing to 0
assert len(legs) == 3 * len(lines)
assert abs(legs["amount"].sum()) < 0.01, "double-entry must balance"
assert abs(legs[legs["account"] == "1100-receivable"]["amount"].sum()
- lines["gross_amount"].sum()) < 0.01
print()
print(daily.head(6).to_string(index=False))
print()
print(running.to_string(index=False))
print()
print(legs.head(6).to_string(index=False))
print(f"\n{len(lines)} order lines -> {len(daily)} daily rows, "
f"{len(running)} sku rows, {len(legs)} accounting legs")
print("All aggregates reconcile with the detail, and the ledger balances.")
Notes
Reconciliation is the point
Because the aggregate is computed from the delivered detail, summary.total.sum() == detail.amount.sum() holds exactly — no tolerance, no drift. That is what makes the pair a usable fixture for reconciliation and data-quality pipelines.
custom tables cannot live in YAML
aggregate entries are pure data and can be declared under the reserved derived_tables key in a configuration file. custom entries carry a Python callable and are rejected there — pass them in code.
Break the reconciliation on purpose
A derived table can also be targeted by error injection. Corrupting the summary after it has been derived produces detail and summary that no longer agree — the unresolved mismatch a reconciliation engine is supposed to catch.
Related Materials
