Pokémon TCG — Dataset, Oracle and ETL Contract

Pipeline shape

The repository's data documents describe a staged transformation, not a single “dataset download” step:

flowchart LR
    Z[Raw replay ZIPs] --> J[JSON member extraction]
    J --> S[Semantic validation]
    S --> A[Episode/action realignment]
    A --> O[Engine-backed oracle]
    O --> R[Telescoping rewards and targets]
    R --> P[Day-partitioned Parquet]
    P --> T[MLX training / TBPTT]
    Z --> D[SQLite catalog and tournament metadata]
    D --> Q[Quality audit and provenance joins]

Every transition needs an auditable count, source identity and failure policy. A row that is dropped, shifted, imputed or synthesized must not silently look like an observed replay row.

Current snapshot used by the August 14 audit

The direct local read and the Antigravity survey disagree in ways that matter for later ETL work:

Layer	Direct/current observation	Provenance note
Replay ZIPs	30 archives, from 2026-07-14 through 2026-08-12	Direct filesystem inventory
ZIP JSON members	138,138	Direct re-read; not accepted as the only corpus count
Antigravity surveyed members	140,511	Survey artifact; unresolved delta of 2,373
SQLite schema	version 2.0.0	Direct database read
SQLite matches	139,783	Includes a history that is not identical to current ZIP members
Parquet partitions	30 files, 30 manifests	Direct dataset inventory
Parquet schema	90 columns	Direct schema read
Parquet rows	24,177,852	Direct row-count observation
Latest Parquet day	2026-08-12, 806,653 rows	Direct row-count observation
Direct remote/local split	138,023 / 1,760	Current source metadata; not a complete provenance proof

These numbers are not interchangeable. A larger SQLite match count does not prove that missing ZIP members were recovered, and a Parquet row count does not prove every row is semantically valid.

Semantic and temporal normalization

The compiler performs several different kinds of repair or interpretation:

• Semantic validation: required fields, action legality and episode shape are checked before materialization.
• Off-by-one realignment: replay observations and decisions are aligned to the state at which an action was selected. This is a transformation with a failure count, not a harmless index adjustment.
• Engine oracle: the rules engine computes would-KO and related prospective labels from a state/action context. The oracle is a label generator, not an observation source.
• Telescoping rewards: per-step rewards are derived so that the sum reflects terminal or prize changes without inventing independent outcomes.
• Parquet materialization: day partitions and row groups provide streaming units for the trainer and hierarchical cache.

The current and target schema proposals add fields such as match_step, meta_epoch, time_remaining, inferred opponent Elo, team hash and vehicle deck-card IDs. These fields are useful for RoPEND/MoE and data augmentation only if their temporal availability is explicit. The distinction between replay-derived auxiliary targets and future synthetic vehicle drafting is maintained in pokemon_tcg_data_augmentation_vehicle_draft.

Data-quality hazards

Day metadata versus imported data

The August 14 audit found divergent declared, database and ZIP counts on several days:

Day	Declared metadata	SQLite observation	ZIP observation
2026-08-05	6,229	4,729	4,740
2026-08-06	10,451	4,629	4,631
2026-08-07	8,716	4,636	4,639
2026-08-10	5,139	4,599	4,603
2026-08-11	7,632	4,621	4,622
2026-08-12	20,325	6,361	4,604

The differences must remain as reconciliation work. Selecting the largest number would turn metadata into false evidence.

Foreign-key integrity

The current snapshot exposed 2,946,336 child rows violating declared foreign-key relationships. The largest groups include orphaned match_steps and their dependent options, events, board snapshots and field rows, plus orphaned match-card usage. No purge, destructive rebuild or silent repair was run during the audit.

This is why the ETL design emphasizes zero trust: the database is a catalog and evidence source, not automatically the ground truth for every replay lineage.

Target validity and sparsity

The Parquet audit recorded complete deck-list shape in the sampled schema and current auxiliary statistics including approximately 100% valid auxiliary rows, 9.39% KO labels, 1.52% terminal labels, 8.91% non-zero prize outcomes, 99.99% return coverage and 27.31% attack coverage. These are descriptive snapshot metrics. They do not establish that the sparse-head denominators were used correctly by the trainer, which is the separate Stage 3/4 incident.

Zero-trust ETL rules

The ETL architecture documents establish several operational rules:

• physical ZIP contents and SQLite imports are reconciled rather than assumed equivalent;
• rebuild_db=false is an offline promise and must not unexpectedly invoke network or destructive rebuild behavior;
• download and import vectors are separate from local idempotent delta builders;
• build_card_stats and replay population should be safe to rerun as local deltas;
• source enums, receipts and manifests must preserve where every row came from;
• entity-resolution candidates must remain additive mappings with their rule, episode distance and confidence; the original team/agent observation is not overwritten;
• no future-derived Elo, tournament result or generated deck may enter ordinary replay rows without an explicit source and timestamp.

Data augmentation contract

The future vehicle-draft design extends this ETL rather than bypassing it. A generated deck or synthetic trajectory must carry:

source = synthetic_vehicle_draft
generator_checkpoint
generator_version
sampling_policy
deck_legality_result
engine_version
rating_snapshot
tournament_cohort
seed

Synthetic examples may be valuable for exploration, but they must remain separable from replay-derived behavior cloning until a controlled experiment demonstrates that their inclusion improves a declared target without hiding source drift.

Audit sequence for the next ETL pass

1. Recount raw ZIP members by archive and day.
2. Reconcile ZIP, SQLite, Parquet, manifest and declared metadata counts.
3. Validate episode and action alignment with explicit drop/shift counts.
4. Recompute oracle coverage and sparse target denominators.
5. Check foreign keys and orphan-root lineage without deleting anything.
6. Verify train/validation/tournament split identity and future-field availability.
7. Only then add synthetic vehicle-draft records to a separate source cohort.

This sequence is analysis and documentation work. It does not authorize implementation of the target schema or a database rebuild.

Primary sources

• docs/dataset_compilation_and_oracle_pipeline.md — compiler stages and future context fields.
• docs/etl_architecture_and_auditing.md — zero-trust database and import behavior.
• docs/database_schema.md — current physical table topology.
• docs/schema-evolution.md — target relational design under review.
• docs/normalization_heuristics.md — entity resolution and residual ghosts.
• docs/technical_handoff_rfc.md — database, replay and run inventory assertions.

Cross-references

• pokemon_tcg_aug14_data_etl_database_audit — current read-only counts and integrity findings.
• pokemon_tcg_stage3_training_failure_postmortem — target denominator and validation failure.
• pokemon_tcg_parquet_dataset — live Parquet format.
• pokemon_tcg_sqlite_schema_current — current schema snapshot.
• pokemon_tcg_ropend_moe_blueprint — future synthetic vehicle augmentation.
• pokemon_tcg_data_augmentation_vehicle_draft — dedicated provenance and legality contract for that augmentation.
• pokemon_tcg_cross_harness_provenance — Antigravity survey provenance.
• pokemon_tcg_entity_normalization — saved-probe methodology, the L1/L2 discrepancy and residual identity boundary.