Skip to content

Systems and records

Dataset unit test package: a validator spec for schema and key integrity

By SourceX Editorial · Updated

10 sections

Short answer

A delivered dataset validator should test every constraint the package declares: files present at relative paths with matching hashes, fields and types as declared, required and allowed values, unique primary and unique keys, and foreign keys that resolve. Ship each check as a test with a passing and a failing fixture, and report failures by test ID with counts and sample keys.

Checklist

Dataset unit test package

This page covers dataset unit test package: a validator spec for schema and key integrity.Illustrative

Key takeaways

  • Generate validator tests from a declaration shipped in the package, not from the supplier's memory of the data.
  • Test composite keys as tuples and treat null optional foreign keys according to the declaration.
  • Every test needs a fixture it must fail; a suite whose mutation fixtures pass is broken.
  • Reports should carry test IDs, counts, denominators and surrogate sample keys, never personal data.
  • Decide before delivery how de-identification removals affect referential integrity, and declare it.

What should a delivered dataset validator check against its declared structure?

A delivered dataset validator should check every constraint the package declares, and nothing it does not: that each declared file exists at its relative path with a matching hash, that headers and types match the field list, that required fields are filled and coded fields use allowed values, that primary and unique keys hold, and that every foreign key resolves to a row in the table it names.

The second half of the answer is what makes the difference in practice. Each of those checks should ship as a test with a known-good fixture it passes and a known-bad fixture it fails, and the validator should report failures by test ID with counts and sample keys. Then a buyer is not trusting a supplier's statement that the data is clean; they are rerunning a test suite whose own correctness they can see.

This page specifies that package: where the rules come from, the test catalogue, the fixtures, the report format and an illustrative valid and broken record. It is format-neutral and works for CSV, Parquet or JSON Lines deliveries.

Why ship a test package instead of a checklist?

A checklist says what should be true; a unit test package proves, on every run, that the checks still catch what they are meant to catch. The distinction matters because validators decay quietly. A schema gains a field, a key becomes composite, a script is refactored, and a check that used to fail on duplicates now passes everything.

Analytics engineering already works this way. In dbt, data tests are select statements that look for failing records: a uniqueness test selects duplicates, a not-null test selects nulls, and a test passes when it returns zero rows. dbt ships four generic tests, unique, not_null, accepted_values and relationships, the last being a referential integrity check that each value exists in another table. A dataset delivery can borrow the same idea: every assertion is a query or function that returns failing rows, so a failure always comes with evidence.

What a delivery adds is the outside reader. The buyer did not write the schema, does not have the source systems and may run the tests months later. So the package has to carry its rules, its test cases and its expected results with it.

Where the declared structure should come from

Tests should be generated from a machine-readable declaration that ships in the package, never hard-coded from the supplier's memory of the data. If the declaration and the tests disagree, the declaration wins and the test is a bug.

Several open specifications already express the constraints a validator needs. The Frictionless Table Schema defines a primaryKey whose fields cannot be null, uniqueKeys modeled on SQL unique constraints with null values excluded from the uniqueness check, foreignKeys with fields and a reference to fields in the same or another resource, and missingValues listing the strings to treat as missing. The Data Package standard requires a datapackage.json descriptor at the top level of the package, and a Data Resource locates its data with a path that is either a fully qualified URL or a relative POSIX path, optionally with a hash.

Croissant, the MLCommons metadata format for machine learning datasets, offers the same ideas in JSON-LD: a RecordSet can declare a key, one or more fields whose values are unique across records, and a field's references property is described in the spec as the equivalent of a foreign key. For record-level shape in JSON or JSON Lines, JSON Schema 2020-12 supplies required and enum. Pick one declaration per package and generate every structural test from it.

Whichever format you choose, ship the declaration's own version with it and check, as the first test, that the declaration validates against that format's published schema. A malformed descriptor makes every later test meaningless.

The schema and referential integrity validation specification

The specification below is the asset. Each row is one test with a stable ID, the place in the declaration it reads, the assertion, what it reports on failure and its default severity. IDs never change meaning across versions; a changed rule gets a new ID.

Composite keys are tested as tuples, not column by column, and a null in an optional foreign key is not a failure unless the declaration marks the field required. Those two rules account for most false passes and false alarms in hand-written validators.

Tests R02 and R03 only run when the declaration states the rule. Most standard descriptors have no place for a minimum child count or a timestamp ordering rule, so add them as a small, documented extension file and version it with the schema rather than burying them in code.

Order the run so that early failures explain later ones. If P02 finds a missing file, the key and relationship tests on that resource should report as skipped with a reason, not as thousands of orphans that bury the real cause.

The schema and referential integrity validation specification
Test IDReads from the declarationAssertionReports on failureDefault severity
P01 descriptordatapackage.json or Croissant fileDescriptor parses and names a schema versionParse error and lineBlocking
P02 files and hashesResource path and hashEach file exists at its relative path inside the package and its hash matchesMissing paths, mismatched digestsBlocking
S01 fieldsField list per resourceHeader or record keys match the declared fields, in declared order where requiredExtra, missing or renamed fieldsBlocking
S02 types and missing valuesField type, format, missingValuesEvery non-missing value casts to its declared typeCount and sample keys per fieldBlocking
S03 required and allowed valuesRequired constraints, categories or enumRequired fields are filled; coded fields use declared valuesCount per field and the unexpected valuesBlocking
K01 primary keyprimaryKey or Croissant keyKey tuple is unique and never nullDuplicate tuples with row countsBlocking
K02 unique keysuniqueKeysEach declared combination is unique, nulls excludedDuplicate tuplesBlocking
R01 foreign keysforeignKeys or referencesEvery non-null key tuple exists in the referenced resourceOrphan count and sample keys per relationshipBlocking
R02 cardinalityDeclared minimum children, if anyEach parent has at least the declared number of child rowsParents below the minimumWarning unless agreed
R03 cross-table orderDeclared temporal rules, if anyChild timestamps are not earlier than the parent's creationViolations with both timestampsWarning unless agreed

How each test proves it can fail: the fixture set

Every test ships with at least one fixture that must fail it, and the suite itself fails if any such fixture passes. That is how a buyer knows K01 really detects duplicates rather than trusting a green report.

Keep fixtures small, invented and readable, and store them in the package beside the tests; the list below describes a good set.

Run the fixture suite in the supplier's build before every delivery, not only when the tests change. A schema edit that silently disables a check shows up as a mutation fixture that suddenly passes, which is far cheaper to catch before shipping than after a buyer finds the defect in production data.

  • One valid mini package, a few rows per table, that passes every test. It doubles as a worked example of the declared structure.
  • One mutation fixture per test: the valid package with exactly one defect, such as a duplicated key tuple for K01 or an orphan child row for R01.
  • An expected-results file listing, for each fixture, which test IDs must fail and the failing count each must report.
  • Edge fixtures for the rules people get wrong: a null in an optional foreign key that must pass, a composite key where each column repeats but the tuple does not, and a value that equals a declared missing-value string.
  • A rule that fixtures never contain real records. They are synthetic by construction, so they can be published, shared with a buyer before contract and kept in version control without a privacy review.

What the validator's report must tell the buyer

The report should let a buyer decide without opening the data: one entry per test per resource, with the counts, the denominator and enough sample keys to find the rows. Write it as JSON next to a short human-readable summary.

Recommended fields, a convention this guide proposes rather than an external standard: test_id, spec_version, dataset_version, resource, status (pass, fail, error, skipped), failing_count, checked_count, sample_keys (up to a fixed number, keys only), severity and message. Use distinct exit codes for passed, failed at blocking severity and could not run, so a pipeline never mistakes a crashed validator for a clean dataset.

Sample keys must be surrogate identifiers, never names, emails or free text. The report travels further than the data, often into tickets and email threads, so it should contain nothing that would need de-identification.

Include the versions of the declaration, the test suite and the dataset in every report. A buyer comparing two deliveries needs to know whether a new failure came from the data or from a stricter test.

Illustrative worked record: a maintenance request package, valid and broken

This example is illustrative and invented; it does not describe any SourceX supplier's data. A property maintenance package has four resources: buildings, units, requests and visits. units declares a foreign key building_id to buildings; requests declares unit_id to units; visits declares request_id to requests and a composite primary key of request_id and visit_seq. status on requests uses the categories open, scheduled, completed and cancelled.

The valid record is request R-1042 for unit U-207 in building B-12, status completed, with two visits numbered 1 and 2, both dated after the request was opened. Every test passes.

The broken record is the same request after a careless re-export. It points at unit U-911, which de-identification removed; its status reads Closed; and a second visit 2 was appended for the same request. The table shows which tests fire. Note what does not fire: R02 passes, because the request still has visits, which is why a single check is never enough.

Illustrative worked record: a maintenance request package, valid and broken
ElementValid recordBroken recordTest that fires
requests.unit_idU-207, present in unitsU-911, removed during de-identificationR01 foreign keys
requests.statuscompletedClosedS03 allowed values
visits key(R-1042, 1) and (R-1042, 2)(R-1042, 2) appears twiceK01 primary key
visits.visited_atBoth after the request openedBoth after the request openedNone, R03 passes
Visit countTwo visitsThree visit rowsNone, R02 passes

Can a buyer run the tests without the supplier's systems?

Yes, if the package is built for it: the declaration, data, tests, fixtures and expected results all sit inside one folder, every path is relative, every file has a hash and the test runner has pinned dependencies. Nothing should call back to the supplier's database, ERP or helpdesk.

For JSON Lines resources, the format's own rules help: files are UTF-8, each line is one valid JSON value, and lines end with a newline, so a test can report failures by line number. For tabular files, report by row key rather than row number, because sorting or partitioning changes row numbers.

The hard case is integrity broken by preparation itself. De-identification often removes parent rows, such as a unit or a customer, while leaving children that reference them. Decide before delivery whether to drop the children, keep them with a null key, or keep the parent as a stub, write the choice into the declaration and let R01 enforce it. A buyer who finds silent orphans will reasonably distrust the rest.

Pin the runtime as well as the libraries. A lock file or a container image digest in the package means the buyer runs the same code the supplier ran, and a difference in results points at the data rather than the environment.

Which failures block acceptance and which become disclosed exceptions?

Structural failures should block acceptance by default: a missing file, a broken key or an orphaned child makes joins unreliable for everyone downstream. Failures that only limit use, such as a cardinality shortfall in some parents, can be accepted if the supplier discloses them with counts and the buyer agrees in writing.

Agree the severity of each test before delivery, in the same document that sets acceptance criteria, and version that agreement with the specification. Then a failing test is a contractual fact rather than a debate, and the supplier knows exactly which defects must be fixed before shipping and which may be documented instead.

A useful rule of thumb is that anything breaking a join is blocking and anything reducing coverage is negotiable. Joins fail silently downstream, while a coverage gap can be measured and priced into the decision. Severity in the specification should follow that line unless the agreement says otherwise.

Asking for a dataset that arrives with its own tests

If you are buying operational data, put the test package in the request: the declaration format you can read, the test IDs you expect, fixtures and a report you can rerun. With SourceX nothing is pulled from stock: each dataset is found at U.S. businesses to match a request that states scope, volume, language, format, timing and licensing needs, and that request form is where a validation specification like this one belongs.

When the dataset arrives, run the fixtures before the data. If the mutation fixtures do not fail as the expected-results file says, fix or reject the test suite first; a validator you cannot trust on synthetic defects tells you nothing about real ones.

Frequently asked questions

What should a schema and referential integrity validation specification include?

Stable test IDs, the part of the declaration each test reads, the assertion, what each failure reports and a default severity. Cover the descriptor, files and hashes, fields, types and missing values, required and allowed values, primary and unique keys, foreign keys and any declared cardinality or ordering rules.

Can the tests run on a buyer's machine with no access to the supplier?

Yes, when the declaration, data, tests, fixtures and expected results ship in one folder with relative paths, file hashes and pinned test dependencies. If any check needs a call to the supplier's database or application, the package is not yet self-contained.

Which fixtures show that each validator test actually works?

A small synthetic package that passes every test, plus one mutation fixture per test with exactly one defect, such as a duplicated key or an orphaned child row. An expected-results file says which tests each fixture must fail, so the suite proves its own checks work.

Is a null foreign key a referential integrity failure?

Not by itself. Under most declarations a foreign key check applies to non-null values, so a null passes unless the field is declared required. Decide and declare which relationships are optional, especially where de-identification removed parent rows.

How is this different from dbt tests or a JSON Schema?

It uses the same ideas, assertions that return failing rows and declared required fields and allowed values, but packages them for someone outside your warehouse. The tests, fixtures and report travel with the dataset so a buyer can rerun them anywhere.

Sources

  • The Frictionless Table Schema defines primary keys that cannot be null, unique keys with nulls excluded from the check, foreign keys and declared missing values. Source
  • A Frictionless Data Resource locates data with a path and can carry a hash, MD5 by default or another algorithm by prefix. Source
  • A Data Package descriptor must be named datapackage.json and placed at the top level, and resource paths are URLs or relative POSIX paths. Source
  • dbt data tests select failing records and pass at zero rows; the four generic tests include relationships, a referential integrity check. Source
  • Croissant lets a RecordSet declare a key of unique fields and a field reference that is equivalent to a foreign key. Source
  • JSON Schema 2020-12 validation defines the required and enum keywords for object and value constraints. Source
  • JSON Lines files are UTF-8 with one valid JSON value per line, separated by newlines. Source

Tell us what data you need

Share scope, volume, format, timing and licensing needs. Datasets are sourced on request.

See if your company qualifies