Retail analytics warehouse — building a DQ-validation-ready dataset at scale¶
Use case: you want to validate a data-quality framework end-to-end. You need a dataset that is (a) large enough that detection thresholds are statistically meaningful, (b) carries the same six failure modes a real warehouse hits, and (c) ships with a ground-truth manifest that names exactly which rows and columns were corrupted — so your DQ framework's recall and precision are evaluable, not handwaved.
What this notebook does: start from the bundled retail template, scale it up to roughly 5 million cells, lift the cell-budget soft cap, expand the quality block to cover all six issue types the engine ships, generate, then show the manifest payloads that act as your DQ oracle. The notebook stops at dataset hand-off — no DuckDB, no Great Expectations. Once you have the dataset and the oracle, plug it into whatever DQ framework you prefer.
Expected run time: 1–3 minutes end-to-end, depending on hardware. Most of the time is spent in the vectorized fact builder; the quality injection step is sub-second.
Setup¶
import json
from collections import Counter
from importlib.resources import files
from pathlib import Path
import pandas as pd
import yaml
import plotsim
print(f"plotsim {plotsim.__version__}")
What the retail template ships¶
plotsim.load_template('retail') returns a ready-to-generate config for an omnichannel retail warehouse. Before customizing, walk through what it carries.
stock_cfg = plotsim.load_template("retail")
print(
f"entities: {len(stock_cfg.entities)} across {len({e.archetype for e in stock_cfg.entities})} archetypes"
)
print(f"periods: {stock_cfg.time_window.period_count()} ({stock_cfg.time_window.granularity})")
print(f"metrics: {[m.name for m in stock_cfg.metrics]}")
print(f"tables: {[t.name for t in stock_cfg.tables]}")
print(f"bridges: {[b.name for b in stock_cfg.bridges]}")
print(f"locale: {stock_cfg.locale}")
print(f"quality: {[q.type for q in stock_cfg.quality.quality_issues]}")
What you got, in one paragraph each:
- Metrics. Order volume, cart value, loyalty score, conversion rate, return rate, NPS, repeat purchase rate.
- Segments. Six customer cohorts: loyal repeat, holiday shopper (seasonal archetype), bargain hunter, churning, new customer, VIP.
- Lifecycle stages.
loyalty_scoredrives abrowser → first_purchase → returning → loyalband. - Loyalty tier (SCD2).
dim_customerversions aloyalty_tiercolumn (bronze / silver / gold / platinum) over time — each tier change emits a new dim row withvalid_from/valid_to/is_current. - Variable-grain orders + per-parent-row items.
fct_ordersis row-count-driven byorder_volume(one row per discrete order). Each order spawns 1–5fct_order_itemsline-item rows. - Cross-fact FK.
fct_returnsreferencesfct_orders.order_id— returns are traceable back to the order they originated from. - CDC audit.
fct_orderscarries_inserted_at/_updated_at/_opaudit columns for refund-processing workflows. - Geo bundle on customers.
dim_customercarries a coherent country / region / city / country_code bundle (the four fields land row-coherent, drawn from a shipped geo dataset). - Multi-locale faker. Customer names land across
en_US,en_GB,fr_FR,de_DE— reflects international operations. - Bridge.
bridge_customer_promotionconnects customers to promotions M:N with cardinality 1–4. - Quality (template default). Three of the engine's six issue types: a null injection on
conversion_rate, duplicate rows onevt_session, late arrivals onfct_orders. We'll expand this to all six in the next step.
Customize for DQ-framework validation¶
Load the template's YAML as a dict, edit the dict, then pass it back through plotsim.create(**dict). Three changes: scale up the segment counts, extend the window, expand the quality block, and lift the cell-budget soft cap.
template_path = files("plotsim.configs.templates") / "retail.yaml"
spec = yaml.safe_load(template_path.read_text(encoding="utf-8"))
print("top-level keys:", sorted(spec))
Scale up: 2,000 customers × 36 periods¶
DQ detection thresholds need volume to be statistically meaningful — null-rate flags on a 100-row table fire on the second null cell, which is useless for tuning. Multiply each segment's count by 20 (total: 2,000 customers) and extend the window from 24 to 36 monthly periods.
for segment in spec["segments"]:
segment["count"] = segment["count"] * 20
spec["window"]["end"] = "2025-12" # was 2024-12
total_entities = sum(s["count"] for s in spec["segments"])
print(f"total entities: {total_entities}")
for seg in spec["segments"]:
print(f" {seg['name']:20s} {seg['count']:>5d}")
Lift the cell-budget soft cap¶
Plotsim ships with a 2-million-cell soft budget that raises at config load when crossed — the gate prevents accidental five-hour-runtime configs. For this exercise the dataset deliberately lands at roughly 5 million cells; lift the soft cap via output.cell_budget. The hard ceiling stays at 50 million regardless (non-configurable). The CLI-equivalent escape hatch is the --allow-large-dataset flag.
Quality injection that grows rows (duplicate_rows, volume_anomaly mode=spike) counts against the post-quality cell total — the engine accounts for it when checking the budget.
spec["output"] = {"format": "csv", "directory": "output_retail", "cell_budget": 10_000_000}
Expand quality to all six issue types¶
The template ships three issue types (null injection, duplicate rows, late arrival). The engine supports six total — adding the missing three gives your DQ framework one of every flavour to detect. Each issue takes a distinct seed_offset so its RNG draws don't collide with the others.
Quality and holdout can run on the same config — the engine slices the corrupted fact tables by date_key, so both _train and _holdout files carry their proportional share of injected issues. This notebook focuses on DQ validation and skips the holdout block; the credit risk modeling tutorial shows holdout in action.
spec["quality"] = [
# 1. null_injection — column-level, sets a fraction of cells to null.
{
"table": "fct_customer_activity",
"issue": "null_injection",
"column": "conversion_rate",
"rate": 0.03,
"seed_offset": 0,
},
# 2. duplicate_rows — row-level, inserts exact copies at random positions.
{"table": "evt_session", "issue": "duplicate_rows", "rate": 0.015, "seed_offset": 1},
# 3. late_arrival — row-level, appends an _arrival_period column with a 1-5 period offset.
{"table": "fct_orders", "issue": "late_arrival", "rate": 0.02, "seed_offset": 2},
# 4. type_mismatch — column-level, casts a fraction of numeric values to strings.
{
"table": "fct_customer_activity",
"issue": "type_mismatch",
"column": "nps",
"rate": 0.01,
"seed_offset": 3,
},
# 5. schema_drift — column-level, copies the value to <col>_v2 and nulls the original.
{
"table": "fct_customer_activity",
"issue": "schema_drift",
"column": "cart_value",
"rate": 0.02,
"seed_offset": 4,
},
# 6. volume_anomaly — row-level, duplicates (spike) or drops (drop) the rows at the named period(s).
{
"table": "evt_session",
"issue": "volume_anomaly",
"mode": "spike",
"period": 18,
"rate": 0.3,
"seed_offset": 5,
},
]
print(f"quality issues configured: {len(spec['quality'])}")
for q in spec["quality"]:
print(f" {q['issue']:18s} on {q['table']:24s} rate={q['rate']}")
Build and generate¶
plotsim.run(cfg, output_dir) runs the full pipeline in one call: generate → validate → build the ground-truth manifest → apply the quality issues → write the CSVs and manifest.json. This is the longest step — the vectorized engine does most of the work in the fact builder. On modern hardware, expect 30–90 seconds for the table generation plus a fraction of a second for the quality-injection layer.
cfg = plotsim.create(**spec)
out_dir = Path("output_retail")
plotsim.run(cfg, out_dir)
csv_files = [p for p in out_dir.iterdir() if p.suffix == ".csv"]
print(f"wrote {len(csv_files)} CSV files")
print()
for path in sorted(csv_files):
df = pd.read_csv(path, nrows=0)
n_rows = sum(1 for _ in open(path, encoding="utf-8")) - 1
print(f" {path.name:34s} {n_rows:>9,d} rows × {len(df.columns):>3d} cols")
The manifest is your DQ oracle¶
Three manifest payloads matter for DQ-framework validation: every quality injection (with original row indices and clean values), every SCD band crossing (the ground-truth versioning trail), and every parent → child fact relation (the ground-truth lineage). Your framework's recall and precision on each are now measurable.
Quality injections — every corrupted cell, named¶
Each record fingerprints one issue × table × column with the row indices it touched and the clean values it overwrote. A DQ framework that catches the row gets one true positive; a framework that flags any row not in the record gets one false positive.
manifest_obj = json.loads((out_dir / "manifest.json").read_text())
rows = []
for qi in manifest_obj["quality_injections"]:
rows.append(
{
"issue": qi["issue_type"],
"table": qi["table"],
"column": qi.get("column"),
"rows_touched": len(qi["row_indices"]),
"clean_values_recorded": len(qi["clean_values"]),
}
)
pd.DataFrame(rows)
SCD events — every loyalty-tier band crossing¶
Each record names the entity, the dim, the from / to label pair, and the period at which the crossing happened. A DQ framework validating SCD2 history can compare its detected band transitions to this list.
scd_events = manifest_obj["scd_events"]
print(f"SCD band crossings recorded: {len(scd_events):,}\n")
if scd_events:
label_counter = Counter((e["old_label"], e["new_label"]) for e in scd_events)
print("transition counts (old → new):")
for (old, new), n in label_counter.most_common():
print(f" {old:>10s} → {new:<10s} {n:>5d}")
Parent-child relations — every parent/child fact lineage¶
Each record names one (parent_table, child_table) pair and reports the engine's row counts and the configured children_per_row bounds. A DQ framework validating warehouse lineage can compare its observed parent / child row counts to these ground-truth totals.
pcr = manifest_obj["parent_child_relations"]
print(f"parent → child relation records: {len(pcr)}\n")
for r in pcr:
avg = r["child_row_count"] / max(r["parent_row_count"], 1)
print(f" {r['parent_table']} → {r['child_table']}")
print(f" parent rows: {r['parent_row_count']:>9,d}")
print(
f" child rows: {r['child_row_count']:>9,d} (avg {avg:.2f} per parent, configured [{r['children_per_row_min']}, {r['children_per_row_max']}])"
)
print()
Hand-off¶
Your dataset is ready. The output_retail/ directory carries:
- The normalized star schema (one CSV per dim, fact, event, bridge).
- CDC audit columns on
fct_orders. - SCD2-expanded
dim_customerwith one row per (customer, tier-band-version). - Six flavours of injected quality issues spread across the fact and event tables.
manifest.json— the ground-truth oracle for every corrupted cell, every SCD crossing, and every parent → child relation.
Hand this to your DQ framework. The manifest tells you exactly what should be detected — so your framework's recall and precision become measurable rather than asserted.