Validity benchmark

The realism benchmark asks whether generated data looks real. This one asks something more basic: does it obey its own schema? A seed that breaks a CHECK constraint, a composite key or a foreign key fails on INSERT, and test data that cannot be loaded is not test data.

Five SQL schemas, written the way production databases declare their rules:

Each schema is generated two ways:

• misata_from_ddl: misata.from_ddl(ddl) then generate_from_schema, with the row counts set and nothing else configured.
• faker_script: the script people usually write instead. One Faker or NumPy call per column, foreign keys drawn uniformly from the parent ids, CHECK clauses not read.
• sdv_hma: SDV 1.38's multi-table model (HMA), used the way it is meant to be: fitted on a valid sample of the same schema (Misata's output under another seed, checked to have zero violations), told every primary and foreign key, and sampled at the same size. It is not given the CHECK clauses: SDV enforces those only when each one is re-declared as an SDV constraint, which is not what most users do. Optional; it runs only when sdv is installed.

The checker restates every rule in plain pandas and shares no code with Misata, so a pass is not Misata grading itself. It counts duplicate primary keys, orphaned foreign keys, NULLs in NOT NULL columns, duplicate UNIQUE values (single and composite), values outside CHECK enums and ranges, violated column-to-column CHECKs, and rows that sit on a cycle of a self-reference.

Results#

Misata 0.9.7, seed 7. Reproduce with

python benchmarks/validity_bench.py

Misata reports zero violations on every schema. SDV's multi-table model, trained on valid data, still breaks rules it was never told: composite keys (5,292 repeated (order_id, line_no) pairs), check_out > check_in (2,651 stays) and the manager hierarchy (4,736 manager_id values pointing at no employee, because HMA does not model self-references). Its open-source version refuses two of the five schemas outright (more than five tables, or foreign keys more than two levels deep), and took 8 to 11 minutes to fit the two it could, where Misata takes under two seconds.

Two things Misata's result costs, stated plainly:

• Composite keys can shorten a table. Where a composite key cannot be made unique by renumbering (a junction table of two foreign keys), repeated pairs are dropped, so product_tags delivers 5,045 of the 6,000 rows asked for. A sequence column in a composite key (line_no) is renumbered within its group instead, and only rows past the CHECK maximum are dropped.
• Durations come from the column names, not your data. A date pair ordered by a CHECK is generated as the earlier date plus a duration that fits the names: stays (check_out > check_in) skew short with a median of three nights, shipping takes one to ten days, a contract's end_date runs one month to two years. Other pairs repaired by the constraint move by a gap drawn from the rows that already satisfied it.

What fixing this found#

This benchmark was written for 0.9.7 and failed on its first run. The fixes it forced are in the release:

• from_ddl dropped every self-referencing foreign key, so manager_id was random integers that looped back on themselves. It now keeps them, and the generator builds the hierarchy as a forest.
• Table-level CHECK (a > b) between two columns was ignored; it is now an inequality constraint.
• Composite PRIMARY KEY (a, b) and UNIQUE (a, b) were ignored, and once declared they were enforced per batch, which the union of batches broke. They now hold over the whole table.
• An inequality repair set the violating value equal to the other column, which breaks a strict > and piles rows up at a zero gap.
• UNIQUE text columns skipped semantic generation, so a UNIQUE sku was filled with sentences and a regions.name with people's names.

Related#

• Seed a dev database from its DDL: the same guarantees on a schema you can run in a second
• SQL DDL to schema: which DDL rules Misata reads
• Realism benchmark: whether the data looks real, not only whether it is valid
• Benchmarks overview