# Data-quality assertions A data-quality assertion is a declared rule about a dataset, such as a column that is never null or a key that is unique, which a pipeline checks against each run's output. A tool compiles each rule into a query over the dataset: a uniqueness rule becomes a query for repeated keys, and the rule fails when the query returns rows or a computed metric falls outside its declared bound. The rules range over whole tables, which is what separates them from [schema and boundary validation](https://quality.stereobooster.com/schema-and-boundary-validation.md). A validator judges one record as it enters a program, so it cannot tell whether a key repeats across rows or whether a reference points at a row that exists. ## What it catches - **Broken keys.** A duplicate or null primary key makes a join fan out or drop rows downstream. - **Orphaned references.** A foreign key can point at a row the referenced table does not hold. Warehouses such as Snowflake and BigQuery accept primary- and foreign-key declarations without enforcing them, so the check falls to the pipeline. - **Values outside the domain.** A status column can gain a value no consumer handles, or a price can turn negative. - **Partial or doubled loads.** A row count below a floor or above a ceiling is a cheap signal that a load lost or repeated rows. - **Type and format changes.** A column can arrive as text after an upstream change, or a date column can switch format. - **Departures from a published standard.** A health record can lack a field its FHIR profile requires, or a code can come from a code system the profile does not allow. What assertions do **not** catch: - a wrong value that satisfies every rule, such as a plausible but incorrect price; - a shift in distribution that keeps every value in range, which is the job of [data anomaly detection](https://quality.stereobooster.com/data-anomaly-detection.md). ## Where the check runs A check can run as a **gate** or as an **alert**. As a gate, it runs after the pipeline writes the dataset and before the dataset is published, and a failure stops publication; dbt's `build` command works this way, skipping every downstream model when a test of severity `error` fails. As an alert, the check runs after consumers can already read the data, so a failure reports damage that may have spread. The position is chosen per rule: a broken key belongs in the gate, while a soft bound such as a row count a little under last week's can go in the alert, where a false failure costs a notification rather than a stalled pipeline. ## Rules on a graph Data published as an RDF graph takes its rules in a shape language: SHACL, a W3C recommendation, or ShEx. A shape states what each node of a class must carry, such as the properties it needs, their datatypes and the classes of the nodes they point to, and a validator reports every node that breaks a shape. The axioms a vocabulary already publishes, such as the domain and range of a property, can be turned into tests without anyone writing them (Kontokostas et al. 2014)[^kontokostas2014]. ## Where the rules come from ### The team Most rules come from what the team knows about the data: business rules, the agreement with an upstream producer, and failures already seen. Such rules age: when a check fails, either the data or the rule is wrong, and a new valid value or a new column makes the rule the one to change. ### A profile of the data Deequ and TensorFlow Data Validation profile existing data and propose rules for an engineer to inspect and approve (Song and He 2021)[^song2021]. A proposed rule describes the sample it was profiled from, so it can encode an accident of that sample, such as a column that happened to hold no repeats, as a requirement. Profiling can also propose rules that span columns, such as a functional dependency in which a postcode determines the city, and the cost of finding them grows with the number of columns. Shapes extracted from a graph inherit its errors. Where DBpedia wrongly types some bands as cities, a shape extracted from its instance data allows a city a genre and former band members (Rabbani et al. 2023)[^rabbani2023]. ### The source the data was loaded from The source a load was copied from knows how many rows and what totals the target should hold. Source-to-target reconciliation computes the same quantities on both sides through the mapping between them, such as row counts, distinct keys, column sums and averages, and fails where they differ; a row-level comparison joins the two on a key and reports rows present on one side only and values that changed. A bound over the target alone accepts any row count inside it. Matching counts do not prove a complete load, since a lost row and a duplicated one cancel. A [parallel run](https://quality.stereobooster.com/parallel-run.md#data-pipelines) instead compares a changed pipeline's output with the current version's. ### A published standard Where data is exchanged under a published standard, the body that publishes it writes the rules rather than the team. A FHIR profile states which fields a health record carries and which code systems they use, CDISC standards state how trial data is structured for a regulatory submission, the OMOP common data model states the tables and vocabularies of observational health data, and XBRL taxonomies state how a financial filing reports its figures. Checking data against such a standard is what a harmonized data-quality terminology calls validation, which adds constraints imposed by external standards to the checks a team runs against its own expectations (Kahn et al. 2016)[^kahn2016]. A failure can mean a partner cannot use the data or a regulator rejects the submission; a pass shows the data has the standard's form, not that its values are right. ## Tools ### Tables and dataframes - **[dbt](https://github.com/dbt-labs/dbt-core)** — tests declared in YAML beside each SQL model; built-in `not_null`, `unique`, `accepted_values` and `relationships` tests, plus custom tests written as queries that return failing rows. - **[Great Expectations](https://github.com/great-expectations/great_expectations)** — Python library; rules ("expectations") grouped into suites and run against a batch, with rendered validation reports. - **[Soda Core](https://github.com/sodadata/soda-core)** — verifies YAML data contracts (schema, row count, missing and invalid values) against a warehouse table, from a CLI or Python. - **[Deequ](https://github.com/awslabs/deequ)** — Scala library on Apache Spark; incremental metrics for growing datasets and rule suggestion from a profile. - **[Pandera](https://github.com/unionai-oss/pandera)** — schemas and checks for dataframes (pandas, Polars, PySpark), declared in Python code. - **[TensorFlow Data Validation](https://github.com/tensorflow/data-validation)** — infers a schema from the statistics of training data and validates later batches against it; built for ML pipelines. ### RDF graphs - **[pySHACL](https://github.com/RDFLib/pySHACL)** — Python validator for SHACL shapes over RDF graphs. - **[Apache Jena](https://jena.apache.org/documentation/shacl/)** — Java RDF framework with SHACL and ShEx validation, from code or the command line. - **[TopBraid SHACL](https://github.com/TopQuadrant/shacl)** — open-source Java SHACL engine from TopQuadrant. ### Source-to-target reconciliation - **[dbt-utils](https://github.com/dbt-labs/dbt-utils)** — dbt package whose `equal_rowcount` and `equality` tests compare a model with another relation, such as its source. - **[Data Validation Tool](https://github.com/GoogleCloudPlatform/professional-services-data-validator)** — Google's open-source command-line tool for migrations; compares column aggregates, row hashes and schemas between a source and a target database across engines. ### Published standards - **[HL7 FHIR Validator](https://github.com/hapifhir/org.hl7.fhir.core)** — the reference Java validator for FHIR resources, against the base specification and profiles. - **[CDISC CORE](https://github.com/cdisc-org/cdisc-rules-engine)** — CDISC's open rules engine; executable conformance rules for trial data submitted to regulators. - **[OHDSI Data Quality Dashboard](https://github.com/OHDSI/DataQualityDashboard)** — R package running conformance, completeness and plausibility checks against the OMOP common data model. - **[Arelle](https://github.com/Arelle/Arelle)** — XBRL processor that validates financial filings against their taxonomy and a regulator's filing rules. ## When to use, when not **Use:** - On every table someone else reads. Keys, required columns and references are cheap to declare and catch the failures that break joins. - As a gate where a consumer acts on the data without a person in between: a model that retrains on it, a report that ships automatically. - Where a constraint is already known, from a business rule or an agreement with the producer. **Don't:** - As the only check on a feed whose failures nobody can foresee. A rule catches only what someone named; pair it with [data anomaly detection](https://quality.stereobooster.com/data-anomaly-detection.md). - With proposed rules accepted unread. A rule profiled from one sample fires on valid data the sample happened not to contain. ## Evidence ### Tables and dataframes - **Unreviewed inferred rules raise false alarms at scale.** On production string-valued columns at Microsoft, rules inferred without human review raised false alarms on over 90% of columns with TensorFlow Data Validation and on over 20% with Deequ (Song and He 2021)[^song2021]. - **Proposed rules can fail on the rest of the data.** Deequ proposed rules from a 10% sample of a Twitter-users dataset; one was a uniqueness rule for a column only 64% unique in the remaining 90% (Schelter et al. 2018)[^schelter2018]. - **Rule sets change with the data.** Across more than 700 production ML pipelines at Google, the schema that validates each batch was revised in most pipelines, in the majority of cases up to five times (Breck et al. 2019)[^breck2019]. - **Discovering rules across columns does not scale to wide tables.** All seven functional-dependency discovery algorithms in one evaluation scaled exponentially with the number of columns, and none scaled to datasets with hundreds of columns or millions of rows (Papenbrock et al. 2015)[^papenbrock2015]. ### RDF graphs - **Tests generated from published schemas find millions of violations, mostly from a few rules.** With a few tests written by hand, they found 28 million errors in the Spanish National Library's open bibliographic data and 9 million in the Library of Congress authority data, 99.9% of the latter from one domain rule. In the English DBpedia, two type statements the vocabularies require and DBpedia omits produced 33 million errors, 35% of its total. Domain and range violations were the most frequent in every dataset, and the authors note they cannot on their own classify a dataset as low quality (Kontokostas et al. 2014)[^kontokostas2014]. - **Pruned extracted shapes find real errors.** Pruning shapes extracted from a graph by their support and confidence filtered out up to 93% of invalid and spurious shapes, cutting DBpedia's from 11,916 constraints to 809. Ten of the remaining shapes found 20,916 missing and 155 erroneous triples in DBpedia (Rabbani et al. 2023)[^rabbani2023]. - **Not every graph rule fits core SHACL.** Of the 32 property-constraint types Wikidata uses, 26 could be written as SHACL-Core shapes; three only partially, one not reasonably and two not at all. SPARQL queries expressed all 32 (Ferranti et al. 2024)[^ferranti2024]. ### Source-to-target reconciliation - **Checks derived from the source-to-target mapping found faults sampling missed.** On a health data warehouse, count, average and value comparisons generated from the mapping found 11 faults in the extract-transform-load process that the testers' sampling had not. They detected 92% of 118 faults seeded into the target data; every fault missed was in a target column with no source counterpart (Homayouni et al. 2018)[^homayouni2018]. ### Published standards - **Most lab-value discrepancies across hospitals were simple to resolve.** Validating the laboratory data of 20 German university hospitals against shared FHIR profiles found discrepancies for 368 quantitative lab codes, and 298 of them (81%) could be resolved by simple measures (Rosenau et al. 2024)[^rosenau2024]. - **A fifth of drug submissions lacked required standard datasets.** In 2018, 21.6% of submissions to the FDA failed one of two checks for required standard datasets and definition files, against an earlier average failure rate of 32%, according to conference slides by FDA staff (Chen and Hussong 2019)[^chen2019]. ## Classification - **Data-quality characteristics:** Consistency (keys, references, types and accepted values hold across every row and between tables), Completeness (not-null rules and row-count bounds catch missing values and a load that silently dropped rows), Compliance (rules taken from a published data standard check that records follow it). - **Area:** Tables, files and partitions a data pipeline produces — warehouse models, ingested batches, feature tables — and knowledge graphs published as RDF, checked after each run, before downstream consumers read them. - **Guarantee:** Empirical — a pass shows this run's dataset met the declared rules; it says nothing about errors no rule names, or about the next run. ## Referenced by - [Data anomaly detection](https://quality.stereobooster.com/data-anomaly-detection.md) · Data quality - [Data error detection](https://quality.stereobooster.com/data-error-detection.md) · Data quality - [Data quality](https://quality.stereobooster.com/data-quality.md) · Data quality - [Duplicate-record detection](https://quality.stereobooster.com/duplicate-record-detection.md) · Data quality - [Testing machine-learning systems](https://quality.stereobooster.com/testing-ml-systems.md) · Methods ## References [^kontokostas2014]: Kontokostas, Dimitris, Patrick Westphal, Sören Auer, et al. 2014. "[Test-Driven Evaluation of Linked Data Quality](https://jens-lehmann.org/files/2014/www_test_driven_ld_quality.pdf)." *Proceedings of the 23rd International Conference on World Wide Web (WWW 2014)*, 747–58. . [^song2021]: Song, Jie, and Yeye He. 2021. "[Auto-Validate: Unsupervised Data Validation Using Data-Domain Patterns Inferred from Data Lakes](https://arxiv.org/pdf/2104.04659)." *Proceedings of the 2021 International Conference on Management of Data (SIGMOD '21)*. . [^rabbani2023]: Rabbani, Kashif, Matteo Lissandrini, and Katja Hose. 2023. "[Extraction of Validating Shapes from Very Large Knowledge Graphs](https://vbn.aau.dk/ws/files/678396868/3579075.3579078.pdf)." *Proceedings of the VLDB Endowment* 16 (5): 1023–32. . [^kahn2016]: Kahn, Michael G., Tiffany J. Callahan, Juliana Barnard, et al. 2016. "[A Harmonized Data Quality Assessment Terminology and Framework for the Secondary Use of Electronic Health Record Data](https://www.ebi.ac.uk/europepmc/webservices/rest/PMC5051581/fullTextXML)." *eGEMs (Generating Evidence & Methods to Improve Patient Outcomes)* 4 (1). . [^schelter2018]: Schelter, Sebastian, Dustin Lange, Philipp Schmidt, Meltem Celikel, Felix Biessmann, and Andreas Grafberger. 2018. "[Automating Large-Scale Data Quality Verification](https://www.vldb.org/pvldb/vol11/p1781-schelter.pdf)." *Proceedings of the VLDB Endowment* 11 (12): 1781–94. . [^breck2019]: Breck, Eric, Neoklis Polyzotis, Sudip Roy, Steven Euijong Whang, and Martin Zinkevich. 2019. "[Data Validation for Machine Learning](https://proceedings.mlsys.org/paper_files/paper/2019/file/928f1160e52192e3e0017fb63ab65391-Paper.pdf)." *Proceedings of Machine Learning and Systems (MLSys 2019)* 1: 334–47. [https://proceedings.mlsys.org/paper\\\_files/paper/2019/file/928f1160e52192e3e0017fb63ab65391-Paper.pdf](https://proceedings.mlsys.org/paper\_files/paper/2019/file/928f1160e52192e3e0017fb63ab65391-Paper.pdf). [^papenbrock2015]: Papenbrock, Thorsten, Jens Ehrlich, Jannik Marten, et al. 2015. "[Functional Dependency Discovery: An Experimental Evaluation of Seven Algorithms](https://www.vldb.org/pvldb/vol8/p1082-papenbrock.pdf)." *Proceedings of the VLDB Endowment* 8 (10). . [^ferranti2024]: Ferranti, Nicolas, Jairo Francisco de Souza, Shqiponja Ahmetaj, and Axel Polleres. 2024. "[Formalizing and Validating Wikidata's Property Constraints Using SHACL and SPARQL](https://repositum.tuwien.at/bitstream/20.500.12708/224960/1/Ferranti-2024-Semantic%20Web-vor.pdf)." *Semantic Web*, ahead of print. . [^homayouni2018]: Homayouni, Hajar, Sudipto Ghosh, and Indrakshi Ray. 2018. "[An Approach for Testing the Extract-Transform-Load Process in Data Warehouse Systems](https://doi.org/10.1145/3216122.3216149)." *Proceedings of the 22nd International Database Engineering & Applications Symposium (IDEAS 2018)*. . [^rosenau2024]: Rosenau, Lorenz, Paul Behrend, Joshua Wiedekopf, Julian Gruendner, and Josef Ingenerf. 2024. "[Uncovering Harmonization Potential in Health Care Data Through Iterative Refinement of Fast Healthcare Interoperability Resources Profiles Based on Retrospective Discrepancy Analysis: Case Study](https://www.ebi.ac.uk/europepmc/webservices/rest/PMC11303887/fullTextXML)." *JMIR Medical Informatics* 12: e57005. . [^chen2019]: Chen, Ethan, and Virginia Hussong. 2019. *[Technical Rejection Criteria for Study Data](https://www.fda.gov/media/124697/download)*. Slides, DIA Regulatory Submissions, Information, and Document Management Forum, North Bethesda, MD. . ## Acronyms - CDISC — Clinical Data Interchange Standards Consortium - FDA — U.S. Food and Drug Administration - FHIR — Fast Healthcare Interoperability Resources, HL7's standard for exchanging health records - HL7 — Health Level Seven International, the standards body behind FHIR - OHDSI — Observational Health Data Sciences and Informatics - OMOP — Observational Medical Outcomes Partnership, whose common data model OHDSI maintains - RDF — Resource Description Framework, the W3C graph data model - XBRL — eXtensible Business Reporting Language