The data consistency versus data integrity distinction is direct: consistency means agreement everywhere. Data integrity means data remains valid, accurate, complete, and protected from improper modification throughout its lifecycle. A dataset can therefore be consistent without having integrity: every system can agree, and every system can still be wrong.
This distinction affects the transformations, ad hoc queries, and analysis that analytics teams build on governed data already ingested through ETL. AI-powered self-service analytics gives analysts a practical way to validate the data workflows they own while engineering maintains ingestion, governance, and platform controls.
What is data consistency?
Data consistency is the uniformity of data across systems, replicas, or records. As a data-quality characteristic, ISO/IEC 25012 describes consistency as freedom from contradiction and coherence with other data in a specific context of use.
A consistency check asks whether two representations agree. It does not necessarily establish whether either representation matches the real-world fact or business rule it is meant to represent.
Three meanings of consistency
The word has three related but distinct meanings across the data stack:
- Atomicity, consistency, isolation, durability (ACID): A transaction preserves the database's declared rules and moves it from one valid state to another. The ACID consistency model concerns transaction and constraint validity.
- Consistency, availability, partition tolerance (CAP): Operations on distributed data appear to behave as if they execute against one copy. The formal CAP definition concerns replica behavior, not business accuracy.
- Cross-system reconciliation: Two systems return matching values for the same field, record, or aggregate.
CAP consistency and ACID consistency describe different properties. Replicas can agree on a value that violates a unique key, foreign key, or business rule. Cross-system reconciliation can likewise confirm agreement without confirming correctness.
What is data integrity?
Data integrity is the trustworthiness of data across storage, processing, and transit. NIST defines integrity as protection against improper information modification or destruction. From a user's perspective, NIST also connects integrity with attributes such as accuracy and completeness.
In this guide's practical model, consistency is one component of integrity, not a synonym for it. Data can agree across systems but still be incomplete, invalid, stale, assigned to the wrong entity, or changed improperly.
Types of data integrity rules
Relational and analytics systems express integrity through several rule types:
- Entity integrity uses primary keys (PKs) to identify rows uniquely and prevent null or duplicate identifiers
- Referential integrity uses foreign keys (FKs) to prevent child records from referencing missing parent records
- Domain integrity limits values to expected data types, formats, ranges, or sets
- NOT NULL and CHECK constraints reject missing values or values that violate declared predicates
- Business-rule validation tests semantic requirements that storage constraints cannot fully express
For example, a valid foreign key proves that a referenced customer exists. It does not prove that an order was assigned to the correct customer. Structural integrity and business correctness both need validation.
Data consistency vs data integrity: what's the difference?
The main difference in data consistency versus data integrity is purpose. Consistency tests agreement between representations, while integrity tests whether data remains trustworthy against constraints, business rules, and known facts.
| Dimension | Data consistency | Data integrity |
|---|
| Purpose | Confirm that systems or replicas agree | Confirm that data is valid, accurate, complete, and properly maintained |
| Scope | Values across records, systems, or replicas | The full data lifecycle and its technical and business rules |
| Typical failure | Two systems report different values | A value is missing, duplicated, invalid, improperly changed, or semantically wrong |
| How you test it | Reconcile records, totals, versions, or replica reads | Apply constraints, domain checks, completeness tests, and business-rule validation |
| How you fix it | Refresh stale copies and correct synchronization or propagation | Correct source data, transformation logic, constraints, or business rules |
Consistent and correct
A source system records a $10,000 transaction. The general ledger, analytics table, and report all show $10,000. The systems agree, and the value matches the business event. Both consistency and integrity hold.
Consistent but wrong
A faulty upstream transformation changes the transaction to $8,000 before downstream systems receive it. The ledger, analytics table, and report all show $8,000. The systems are consistent, but integrity has failed.
Correct but inconsistent
The source and validated analytics table contain the correct $10,000 value, but a stale dashboard still shows $8,000. The current source value has integrity, but the systems are inconsistent because one representation was not refreshed.
Neither property alone guarantees trustworthy analytics. Teams need integrity checks for correctness and consistency checks for propagation.
Why the distinction matters in practice
The distinction determines which failures a validation strategy can detect. Reconciliation finds disagreement, but it cannot detect an error that has propagated uniformly or data that never arrived.
Consistency can mask an integrity violation
Consider a financial firm whose ETL logic fails to apply a required calculation. The wrong value propagates to the general ledger, internal computation records, and regulatory filings. Cross-system reconciliation returns zero discrepancies because each system received the same incorrect value.
The firm must first correct the computation logic and affected records, restoring integrity. It must then verify that the corrected values reach every downstream system, restoring consistency. A reconciliation report alone cannot complete the first task.
Completeness violations are an integrity problem
When required records don't exist, there may be nothing contradictory to compare. Every downstream system can contain the same incomplete dataset.
This is why row counts, expected record checks, and source-to-target completeness tests matter. They test whether all required data arrived, not merely whether the available data agrees.
Cloud platforms don't apply every relational constraint in the same way. As of September 9, 2026, standard Snowflake tables, Databricks Delta tables, and BigQuery all leave PK and FK enforcement to users or their data workflows. Snowflake hybrid tables are the important exception.
| Platform and table type | PK | FK | NOT NULL constraint | CHECK |
|---|
| Snowflake standard tables | Standard PK not enforced | Standard FK not enforced | Enforced | Standard CHECK enforced |
| Snowflake hybrid tables | Required and enforced | Hybrid FK enforced | Enforced | Hybrid CHECK unsupported |
| Databricks Delta Lake with Unity Catalog keys | Informational primary key | Informational foreign key | Enforced | Enforced |
| BigQuery | BigQuery PK not enforced | BigQuery FK not enforced | BigQuery REQUIRED enforced | BigQuery CHECK unsupported |
An informational constraint documents an expected relationship but does not reject a violating write. If neither engineering's ETL nor analytics-owned data workflows validate uniqueness and referential integrity, duplicate keys and orphaned records can reach analysis without a platform error.
The risk also extends to optimization. Snowflake, Databricks, and BigQuery can use declared relationships to improve query plans. Their documentation warns that relying on violated constraints can produce unexpected or incorrect results: Snowflake constraint risks, Databricks constraint risks, and BigQuery constraint risks. The practical rule is clear: only declare optimizer-trusted relationships when your data workflows continuously validate them.
Building validation that catches both failure types
Validation works best when each check runs near the stage that can correct the failure. Data engineers own ingestion and platform controls. Analytics teams own automating data transformation logic for the questions and outputs they create.
A three-layer architecture divides that responsibility:
- Ingestion gate: Engineering validates formats, encoding, and schemas before malformed input enters the platform
- Transformation gate: Analytics workflows test uniqueness, completeness, joins, domains, calculations, and other business rules
- Pre-load gate: Final checks compare aggregates, totals, and reference relationships with the destination's expected state
The Bronze, Silver, and Gold pattern follows a similar progression. Databricks defines bronze as raw data, silver as validated data, and gold as enriched data with final business logic and quality rules in its medallion architecture.
Analysts can query prepared data directly in Prophecy. BI tools and other applications are possible downstream consumers of data stored on the cloud platform, not the required destination of a Prophecy workflow.
Data consistency, data integrity, and data quality
A useful working model places consistency inside integrity and integrity inside fitness for use. Under this model, agreement supports integrity, while integrity supports a broader judgment about whether data is suitable for a particular analysis.
This is a practical model, not a universal standards hierarchy. ISO/IEC 25012 and other data-quality frameworks may list consistency, accuracy, completeness, and related characteristics as coordinate dimensions. Wang and Strong's foundational framework defines data quality through fitness for use, which depends on the consumer and context.
A dataset can satisfy every declared constraint and agree across every system but still be wrong for the question. A complete table of booked revenue, for example, is not fit for an analysis that requires recognized revenue. Validation therefore belongs inside the analytics workflow as well as in platform constraints.
How Prophecy catches both failure modes
Prophecy is an agentic data preparation platform for AI-assisted, governed data workflows. Analysts work on data already landed in Databricks, Snowflake, or BigQuery while engineering retains control of platform access and deployment standards.
Self-service without the engineering queue
Prophecy uses a Generate → Refine → Deploy lifecycle. Analysts describe a need in plain language, and AI agents generate a first-draft visual data workflow with underlying code. Analysts inspect joins, calculations, and validation rules on the canvas, refine the workflow with their domain knowledge, and deploy the validated result as native platform code.
For example, a credit risk analyst can generate a workflow that joins applications with account history, then correct the business definition of delinquency before deployment instead of queuing behind engineering bottlenecks.
Prophecy workflows run natively on the customer's cloud data platform. The generated SQL, Python, or Scala uses the platform's existing compute, identity, permissions, and governance controls. Prophecy's security model keeps data in place and passes the user's identity through to supported platform controls.
A finance analyst preparing a close report, for example, can only query data already available under that analyst's permissions. Git versioning, audit records, and existing access controls remain part of the governed workflow rather than moving into a parallel analytics runtime.
Built-in data quality checks
The documented DataQualityCheck Gem includes completeness, row count, distinct count, uniqueness, data type, min-max length, total sum, mean, standard deviation, and column-to-column comparisons. The current DataQualityCheck Gem documentation shows warning-level checks but does not document distinct blocking and non-blocking modes.
A retail operations analyst preparing order data can test that order IDs are unique, required customer fields are complete, quantities use the expected type, and total revenue matches an expected control value. Referential relationships and broader business rules can be implemented through workflow joins, comparisons, and custom transformation logic rather than presented as named built-in check types.
Catch silent data failures with Prophecy
Consistency checks confirm that values propagated correctly. Integrity checks confirm that the values and records satisfy technical and business requirements. Analytics teams need both before results reach decision-makers or downstream consumers.
Prophecy brings those checks into governed, visual data workflows that analysts can generate, refine, and deploy on their existing cloud platform. Book a demo to see it on your own data and catch silent data failures before deployment.
FAQ
What is the main difference between data consistency and data integrity?
Consistency means systems or replicas agree. Integrity means data is valid, accurate, complete, and protected against improper modification throughout its lifecycle.
Can data be consistent without integrity?
Yes. A faulty transformation can send the same incorrect value to every downstream system. The systems agree, but the data still violates integrity.
Is consistency part of data integrity?
In this guide's practical model, yes. Consistency supports integrity, but major data-quality frameworks may list consistency and other dimensions as coordinate characteristics rather than a formal hierarchy.
What is an example of data consistency?
A source table, analytics table, and report all show the same $10,000 transaction value. They are consistent because their representations agree.
What is an example of data integrity?
An order has a unique ID, references an existing customer, includes every required field, and contains an amount that follows the organization's business rules.
