09-data-output.md

title	09 · Data Output

Data Output

Do not let CSV become the only output format, and do not let Parquet become an MVP burden.

MVP: JSON + CSV (the schema is the contract)

Format	Purpose	Contents
JSON	Metadata and conclusions	Experiment identity (seed / config_hash / schema_version / code version), configuration snapshot, metric summaries, constraint verdicts, candidate comparison, recommendations
CSV	Human inspection and exchange	Per-day metric time series, population distributions, cohort slices, A/B comparison tables — designers can open them directly in Excel

::: tip The MVP CSV / JSON column design is the Arrow contract Column names, column order, and types have been designed since the MVP to map directly onto an Arrow RecordBatch (day index as the first column, an explicit NaN convention, integer counts, UTF-8). The Arrow / Parquet carriers added in Phase 2 only change the packaging, not the semantics — existing consumers are not broken. :::

Output directory convention for every run (written directly by simulate --out, no run-NNNN subdirectories):

out/
├── report.json          # meta + results (per-replicate metrics and constraint verdicts; config_hash rides each result entry, not the top level)
├── days.csv             # per-day metric series
├── comparison.json      # compare subcommand: A/B means / paired-difference CI / effect size
├── day_stats.parquet    # ┐ added by the parquet feature
├── power_snapshots.parquet # ├ the three batches taken from replicate 1
└── cohort.parquet       # ┘

The meta field of report.json is generated by the run environment (generated_at_unix / git_sha / schema_version / model_version) and takes no part in the deterministic hash of the result summary — rerunning with the same seed must produce bit-identical results and days.csv (see Testing strategy).

The Web form (Web, Phase 6) exports under the same contract: days.csv is byte-for-byte identical to what the CLI's simulate --out writes to disk (rendered straight out of Rust, locked down by the web_parity test), and report.json is structurally isomorphic with value-identical results — drop the two files downloaded from the browser into one directory, and sandtable report / sandtable query can pick the analysis up directly.

Arrow: the data contract from simulation to analysis to visualization

Arrow is the uniform intermediate representation as data flows from the simulation to analysis:

Simulation (online aggregation)
     ↓
Result dataset (RecordBatch)
     ↓
┌────┼──────────┐
↓    ↓          ↓
Parquet  DuckDB   DuckDB-Wasm / polars / pandas

Arrow is chosen over wiring up any single analysis tool because it is a zero-copy cross-language contract: Rust (arrow-rs), DuckDB, Python (polars / pandas), and JS all read and write it natively; both DuckDB's official Rust client (duckdb-rs) and DuckDB-Wasm provide Arrow interoperability.

DuckDB: an analysis engine, not a simulation state store

::: warning Positioning red line DuckDB only reads result datasets; it never enters the simulation path. Simulation state lives in Rust structs and metrics aggregate online (KPI and metrics); INSERT-ing every event into DuckDB would turn an analytical database into a runtime database — at 10⁷-scale event counts that is both slow and pointless. :::

What it carries is after-the-fact exploration — experiments produce per-day aggregated metric series (the day_stats batch, whose columns correspond one-to-one with the days.csv header), and SQL is a stronger query layer than a hand-rolled Aggregator family:

-- Per-day win rate and churn (day_stats view; column names match days.csv)
SELECT day, win_rate, churn_rate, inflation
FROM day_stats WHERE day <= 7 ORDER BY day;

-- Economy income/expense gap and sink
SELECT day, gold_earned, gold_spent, sink_ratio
FROM day_stats ORDER BY day;

Note: the simulation does not produce player-level detail (the online-aggregation discipline, see online aggregation); the three batches day_stats / power_snapshots / cohort are the entire dataset, and per-cohort detail slices live in the cohort batch.

• native (Phase 3, CLI / desktop): duckdb-rs, in-memory or file-backed, reading Parquet / CSV directly;
• browser (Phase 6, Web): DuckDB-Wasm reads the Parquet / CSV carried alongside the results; SQL runs entirely locally.

Scale limits of the Web form

DuckDB-Wasm is single-threaded by default, and WASM memory has an official 4GB cap (browsers are often stricter in practice). The Web form therefore targets small-to-medium simulations (demos, teaching, parameter experiments, quick interaction); large sweeps at the scale of ten-thousand players × many replicates go through CLI / Desktop — the layered time model keeps a single simulation's event count in the 10⁷ range, so small and medium experiments in the browser are realistic (Time model).

Parquet: from Phase 2, behind a feature flag

Parquet suits large-scale time series, population detail, and notebook analysis, but the Arrow-family dependencies compile heavy and pull in a large dependency tree. The MVP does not enable it by default; Phase 2 offers the export behind a feature, and the DuckDB analysis layer takes it as its primary input.

HTML report

sandtable report (Phase 5) renders a result directory into a single-file HTML: inline styles and inline SVG charts, no external resources, no JS, opens offline with a double-click. It only reads artifacts already on disk and renders whatever it finds; sections without a source are skipped:

Source file	Section
report.json	Overview block (players / duration / replicates / seed / versions / config_hash); KPI summary (replicate-aggregated mean ± CI₉₅); cohort profiles (three-cohort aggregate table + bar chart); population quantile curves (p50/p90/p99 over days)
comparison.json	A/B means, paired difference with CI, effect size d, verdicts
sweep.json	Sweep summary table (candidates × metrics), constraint-verdict distribution, single-axis metric line charts (CI required)
rec.json	Sensitivity matrix and recommendation interval (the chapter 15 shape)
days.csv	Per-day metric line charts, one per column; economy flows (earned vs. spent, two lines + cumulative totals); growth curves (average power + cumulative level-ups)

The five sections — overview / cohorts / economy flows / growth / quantiles — fill in what the original plan's Phase 7 (Report / Web UI) called for (project record in the Roadmap); KPI / A-B / sweep / recommendation are the sections Phase 5 already shipped.

If not a single source file is found, it errors out (exit code 2); the page header lists the sources actually read, keeping the report traceable. The report never re-simulates — the same discipline as query: artifacts → report, outside the simulation path.

The page header always carries one line of model-boundary annotation (decided 2026-10-11): confidence intervals and difference verdicts are valid only within the simulation model — behavior probabilities are set by hand, conclusions serve relative comparison and balance exploration, and they do not predict the absolute behavior of real players. The wording keeps the chapter 01 stance of "relative comparison, not absolute prediction"; an end-to-end test locks in its presence, and the script consumer of comparison.json (scripts/templates/ab_summary.py) prints the same convention at the end of its table.

Project packaging: import / export

::: note Conceptual position A Project is a configuration-organization unit; it adds no simulation semantics — a Project packs several scenario and experiment definitions (optionally: the most recent artifacts) into a single exchangeable file that imports and exports between any of the forms (CLI / Web / Desktop). The seven core concepts are unchanged; Project is merely their container. :::

Finalized form (2026-10-09): a zip archive (deflate) plus a manifest, extension .sandtable. The Model is a section of the configuration tree rather than a standalone file, so a scenario file = schema_version + scenario + model, and an experiment file adds a sweep section:

mysim.sandtable (zip)
├── manifest.json      # schema_version '1', name, entries (path / kind / sha256)
├── scenarios/*.yaml   # scenarios (one level, non-recursive)
├── experiments/*.yaml # experiments (one level, non-recursive)
└── results/*          # optional: most recent experiment artifacts (report.json / days.csv / sweep.json …)

manifest.json (schema v1, machine-generated packaging metadata; byte-stable JSON serialization, parseable in the browser with zero dependencies, Web-side loading in Web):

{
  "schema_version": "1",
  "name": "my-sim",
  "entries": [
    { "path": "scenarios/base.yaml", "kind": "scenario", "sha256": "<64 hex digits>" },
    { "path": "experiments/sweep.yaml", "kind": "experiment", "sha256": "…" },
    { "path": "results/report.json", "kind": "result", "sha256": "…" }
  ]
}

Format rules:

• Three kind values: scenario (must pass the validate same-source load check) / experiment (same-source experiment validation, including the sweep section) / result (arbitrary artifact bytes, hash-checked but never parsed);
• Path discipline: relative paths, forward slashes, no .. / drive letters / absolute paths; scenario and experiment must be .yaml files under scenarios/ and experiments/ respectively, and result must be under results/; no duplicate paths;
• Packing requirements: at least one scenario or experiment inside the project directory; any file outside the manifest convention is an error (never silently dropped); manifest.json is generated by the packer — a stale manifest sitting in the directory takes no part in packing;
• Determinism: entries sorted by path, fixed timestamps inside the archive (the zip format's 1980 epoch) — packing the same directory twice yields byte-identical output; artifacts are diffable and auditable, the same discipline as kernel determinism;
• Import validates everything up front: manifest structure and schema_version, path discipline, per-entry sha256, the archive's file set strictly equal to the manifest (one extra or one missing is an error), and each scenario / experiment loaded and validated — bad entries are all reported before failing (same discipline as params import), never silently skipped.

CLI (chapter 10):

sandtable project pack   myproj --out myproj.sandtable   # directory → archive
sandtable project unpack myproj.sandtable --out restored # validate, then extract (target directory must be empty)
sandtable project check  myproj.sandtable                # validate only, nothing written

Complementary to the Web form: opening the project file in a browser reproduces the experiment — Web-side loading is scheduled with this finalized form, see Web.

Two-way in the browser (landed 2026-10-13): the Web can not only import .sandtable, it can also export — the toolbar's "Pack project (.sandtable)" action packs the two editors' contents + the current results under the same format rules (manifest v1, entries sorted by path, per-entry sha256 via WebCrypto, archive timestamps fixed at 1980-01-01, packed locally with fflate and zero external requests); packing the same content twice is byte-identical; the artifact is read directly by sandtable project check / unpack, closing the loop on configuration and results between the browser and the CLI (the guard apps/web/tests/project.test.mjs locks the pack→unpack loop, path discipline, byte determinism, and kernel validate).

Configuration intake: Excel / CSV import (evaluation conclusion)

Designers keep most of their numeric tables in Excel / CSV, so importing from existing config sheets may matter more for real adoption than hand-editing YAML. The Phase 5 evaluation concluded as follows (implementation scheduling pending):

Recommended form: two-way CSV, no Excel parsing library.

sandtable params export --out params.csv   # all numeric parameters in the registry → path,value template
sandtable params import params.csv         # per-line apply_numeric overrides → validate, errors reported per line

• Import validates immediately: unknown paths get the registry's edit-distance suggestions; invalid / out-of-range values are flagged line by line, never silently skipped;
• On the Excel side, "Save As CSV" is all it takes — no heavy dependency like rust_xlsx, the same discipline as "DuckDB ships as a feature, not a burden on the minimal build";
• Both directions use one flat table (path, value): the export template comes first, designers edit values on the template and re-import, with no column-name mapping to guess.

Not recommended: multi-sheet → config-tree mapping. Mapping Excel sheet structures onto nested YAML sections requires per-project conventions — heavy effort and easy to misalign — while the flat path table already covers the real need of "change a batch of numbers".

Known risks: misspelled paths (mitigated by suggestions); the encoding of Excel's Save-As-CSV (assumed UTF-8; GBK triggers an error prompting transcoding); formula cells become static values once saved — numeric tables are supposed to export final values anyway, so this is not a defect.

Conclusion: worth doing; the channel is small (template export + line-by-line import + reused validation). Implemented accordingly (Phase 5, sandtable params export / import; usage in CLI).

Export side: multi-sheet workbook

The two-way CSV channel above covers configuration intake. The mirror side — delivering result data to Excel / JMP / Origin / Power Query — is a multi-sheet .xlsx workbook, produced from the artifacts already on disk:

sandtable workbook experiment-out/                    # reads known artifacts → writes experiment-out/sandbox.xlsx
sandtable workbook experiment-out/ --out workflow.xlsx

The workbook is read-only data delivery, not a configuration entrance: it reads params.csv / candidates.csv / summary.csv / comparison.json / days.csv (the artifacts of params export, sweep, compare, simulate), plus cohort.parquet when built with the parquet feature. It writes one sheet per known artifact, plus manifest (self-description) and metric_dictionary (the kernel kpi_dict, same column names and semantics as the CSV / sweep.json constraint names). It recomputes nothing, renames no column, and pads no blank column — the field-mapping reuse is reading the same artifacts. The Parquet channel follows the same cell semantics as the CSV channel: nulls become blank cells, numeric Arrow values become real numeric cells, and unsupported Arrow types are rejected explicitly.

• Pivot / Power Query friendly: one sheet = one table, a single header row on the first row, no merged cells, no trailing blank rows — no multi-row-header inference;
• Real numeric cells, not text numbers: values go into xlsx numeric cells so pivot / sort / SUM carry no "text-form number" warning; precision is bounded by XLSX's 15 significant digits (a format limit, not a computation choice);
• Absent ≠ empty: a missing artifact is recorded in the manifest absent rows and gets no sheet — never an empty placeholder;
• Manifest is self-describing: model_version and metric_dict_version rows, the sheet list (including manifest itself), and the absent-artifact list;
• Deterministic: archive timestamps are fixed at 1980-01-01 (the same discipline as the .sandtable project archive) — the same directory packs to byte-identical output, so the workbook is diffable and auditable.

The workbook keeps the evaluation red line on the intake side unchanged: mapping sheet structures back onto the nested YAML config tree remains not recommended, and the flat params.csv channel stays the only configuration path. The reader is provided as a zero-dependency template — scripts/templates/workbook_sheets.py (list / dump <sheet> / check) reads the workbook with the Python standard library alone, relying on the single-header-row / real-numeric-cell contract above; it needs no pandas or openpyxl.

Roundtrip: export → edit → read back (calibration)

「Read-only delivery」 describes the export side — the pipeline reads artifacts without recomputation — not a ban on users editing what they receive. Observation data is allowed to flow back: the closed loop is sandtable workbook exports sandbox.xlsx → edit the observation value columns in Excel → read back through the MCP sandtable_calibrate tool with a series mapping pointing at the edited sheet. The mapped sheet keeps its header on row 1 (the calibration mapping reads row 1 and has no header-row option); the edited values land in the per-day anchors value-for-value (numeric equality through the shortest-roundtrip rendering, not preservation of the cell's original number format), and equivalence is locked by test: the same observation rows carried as .xlsx and as .csv produce byte-identical calibration output (see calibration design §2).

Editable boundary, stated precisely — the calibration loader reads only the sheet a series mapping names, matched by column names:

• daily observation value columns: editable. These are the observed KPIs being calibrated against (e.g. win_rate, income_per_active). The day axis column and the column set must stay untouched — columns are matched by name, so renaming or deleting a mapped column is a hard error, not a silent skip;
• manifest / metric_dictionary: never edit. Self-description and the kernel metric dictionary. They are not observation input; edits there have no calibration effect and only mislead the audit trail;
• Configuration intake stays CSV-only. The workbook gaining an observation read-back channel does not reopen multi-sheet → config-tree mapping; the flat params.csv channel remains the only configuration path.

Analysis-side interfaces

• Keep the quality of CSV / Parquet output (when the feature is enabled) so notebook analysis flows smoothly;
• MATLAB consumption: the artifacts (days.csv + report.json, and comparison.json from a compare run) are readable from base MATLAB alone. The self-service template scripts/templates/daily_analysis.m prints the KPI summary, the A/B verdict, and plots the day-granularity curves on separate axes (different units never share one). It uses only readtable / jsondecode / plot — no Toolbox and no MATLAB Engine, so the core simulation and the default charts never depend on a commercial license. The MATLAB Engine evaluation (calling the kernel from MATLAB, task state / logs / output directory / result reclamation) stays gated on a concrete local-call need;
• Python bindings have settled their carrier and phasing (decided 2026-10-08: Python carrier, extension module as the main path, design doc at scripts/scripting-design.md) — note that the sandtable name on PyPI is already taken, so the Python package will need a different name (name check).