Kindly fill up the following to try out our sandbox experience. We will get back to you at the earliest.
Data integrity means data stays complete, consistent and unchanged except by an authorized change. The 5 types, what breaks each one, and the controls that hold.
Updated on
Data integrity is the guarantee that a piece of data is complete, internally consistent and unchanged since it was recorded, apart from changes that were authorized and recorded. It is a property of the data and of the system holding it. It is not a judgment about whether the data is useful, and it is not a synonym for accuracy.
The security literature says the same thing in stricter words. The NIST glossary defines integrity, citing FIPS 200 and 44 U.S.C. Section 3542, as "guarding against improper information modification or destruction, and includes ensuring information nonrepudiation and authenticity", and gives a second definition drawn from NIST SP 800-57 as "a property whereby data has not been altered in an unauthorized manner since it was created, transmitted, or stored" (NIST Computer Security Resource Center glossary, read 6 September 2026). Strip the formality and both definitions come down to one test: can you state that this data is the data that was written, and can you show it.
That test has five parts, and they are the five types of data integrity. Every record can be told apart from every other record. Every reference points at something that exists. Every value sits inside its allowed set. Every business rule that spans several columns still holds. And the bytes underneath survived storage and transfer. Break any one and integrity is broken, even when the dashboard still looks reasonable.
The word that matters in the definition is "recorded". Integrity does not mean the data never changes. Orders get canceled, addresses get updated, prices move. It means no change happens that was not intended and no change happens that cannot be accounted for afterwards. A row that quietly became a different row overnight is an integrity failure whether a person, a script or a hardware fault did it.
Data integrity splits into one physical type and four logical types. Physical integrity is about the bytes surviving hardware, storage and transfer. The four logical types are about the data obeying its own rules once it is safely stored. Most teams are strong on the physical side because cloud storage handles it for them, and weak on the four logical types because those are the ones you have to declare yourself.
Physical integrity means the stored bytes are the bytes that were written. It covers disk faults, memory errors, half finished writes, network corruption in transit and anything else between the application and the platter.
A concrete example: a Parquet file written to object storage today reads back next year byte for byte identical, and the checksum computed after the copy matches the one computed before it. A concrete failure: a worker node dies part way through a write, the file is truncated, and the partition that should hold four million rows reads as forty thousand without any error being raised. The controls are checksums and hash comparison on transfer, write ahead logging, replication across zones, error correcting memory, and restore tests that are actually run rather than merely scheduled. A backup nobody has restored is a hypothesis, not a control.
Entity integrity means every row can be told apart from every other row. In relational terms, the primary key is present, unique and never null.
A concrete example: customer_id is the primary key of the customer dimension, so no two rows share a value and no row leaves it empty. A concrete failure: an ingestion job retries after a timeout, the same order arrives twice with the same order identifier, and revenue for the day is reported at double the real figure because nothing rejected the second copy. The controls are a PRIMARY KEY declaration combined with NOT NULL, a unique index, and idempotent loading, meaning the load merges on a business key rather than appending blindly. The test that proves it takes one query: group the table by its key and return only the groups with a count above one. A healthy table returns nothing.
Referential integrity means every reference points at something that exists. The PostgreSQL documentation puts it plainly: "A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables" (PostgreSQL 18 documentation, Constraints, read 6 September 2026).
A concrete example: every customer_id on an order row exists in the customer dimension, so the join between them cannot lose an order. A concrete failure: a support engineer hard deletes a duplicate customer record, twelve thousand orders are left pointing at a customer that no longer exists, the reporting join drops them all, and the revenue chart steps down with no incident anywhere. The control is a FOREIGN KEY declaration with an explicit ON DELETE action rather than the default. PostgreSQL offers NO ACTION, RESTRICT, CASCADE, SET NULL and SET DEFAULT, and choosing between them is a business decision, not a technical one: RESTRICT refuses the delete, CASCADE removes the children with the parent, SET NULL keeps the children and orphans them on purpose. In a warehouse where no foreign key is enforced, the equivalent control is an orphan check that counts child rows whose key is missing from the parent, run on a schedule with a threshold of zero.
Domain integrity means every value in a column sits inside the set of values that column is allowed to hold. Type, range, format, allowed list, and whether empty is permitted.
A concrete example: order_status accepts only placed, shipped, delivered or canceled; discount_pct sits between 0 and 100; country_code is a two letter ISO code; created_at is a timestamp, not a string that happens to look like one. A concrete failure: a new checkout service starts writing "Delivered " with a capital letter and a trailing space, every dashboard filtering on the lowercase value silently drops those orders, and the fulfillment rate appears to collapse overnight. The controls are the column data type itself, NOT NULL, CHECK constraints for ranges and formats, and a lookup table with a foreign key where the allowed set is long enough to deserve its own table. The habit that prevents most of it is refusing to store an enumerated value as free text.
User defined integrity covers the rules the first four types cannot express, because they span more than one column, more than one row or more than one table. This is where business logic lives.
A concrete example: an invoice header total equals the sum of its line items; a refund never exceeds the original payment; a subscription end date is never earlier than its start date; a shipment cannot be marked delivered before it is marked shipped. A concrete failure: a rounding bug in a partial refund routine issues refunds at 1.4 times the original payment on three hundred accounts, and nothing catches it because every single column is individually valid. Amount is a positive number, currency is a real currency, the account exists. Only the relationship between two columns is wrong. The controls are cross column CHECK constraints where the database supports them, triggers where it does not, and custom SQL monitors in a data quality tool for the rules that span tables.
| Type of data integrity | What it constrains | A failure you would recognize | How it is enforced |
|---|---|---|---|
| 1. Physical | The stored bytes match the written bytes | A truncated file after a node dies mid write | Checksums, replication, write ahead logs, tested restores |
| 2. Entity | Every row is uniquely identifiable | A retried load duplicates every order for one day | PRIMARY KEY with NOT NULL, unique index, idempotent merge |
| 3. Referential | Every reference points at a row that exists | Deleting a parent orphans twelve thousand child rows | FOREIGN KEY with an explicit ON DELETE action, or an orphan check |
| 4. Domain | Every value sits inside its allowed set | A status written as "Delivered " drops out of every filter | Data types, NOT NULL, CHECK constraints, lookup tables |
| 5. User defined | Business rules spanning columns, rows or tables | A refund larger than the payment it refunds | Cross column CHECK constraints, triggers, custom SQL monitors |
Three of the four logical types are enforced for you in an operational database and are not enforced for you in most cloud warehouses. Snowflake states it in its own constraints reference: on standard tables, PRIMARY KEY, FOREIGN KEY and UNIQUE constraints are "Optional, not enforced", while NOT NULL and CHECK constraints are "Optional, enforced" (Snowflake documentation, Constraints overview, read 6 September 2026). You can declare a primary key on a Snowflake table and load ten identical rows into it without an error.
This is the most useful thing to know about data integrity in a modern stack, and it is the thing the pages ranking above this one leave out. Constraints in the warehouse are documentation and query hints, not guarantees. Entity, referential and domain integrity stop being the engine's responsibility at the warehouse boundary and become yours, which means they have to be re expressed as tests that run on a schedule. The practical rule: for every constraint you would have declared in the source database, write the equivalent assertion in the warehouse and give it an owner.
Integrity is the property that makes every other data investment worth making. Accuracy, completeness and timeliness all assume the record you are looking at is the record that was written. Once that assumption fails, a more advanced model, a faster warehouse and a prettier dashboard all produce confident answers from data that quietly stopped being true.
Data integrity ensures superior data sets, forming the backbone of data quality and fostering user trust and confidence in outcomes.
Accuracy and integrity are close relatives and they are not the same thing. A value can be perfectly intact and perfectly wrong, and it can be perfectly right and duplicated four times. Integrity is the structural guarantee underneath; accuracy is the question of whether the recorded value matches the real world thing it describes. Decube covers the second question separately in the importance of data accuracy for business success.
Because every downstream process treats the data as settled. A finance close reconciles against it, a pricing engine reads it, a machine learning feature is computed from it, and an AI agent answers a question with it. None of those check whether the join fanned out last night. They inherit the result.
The practical case is easier to state as three things a team can do once integrity holds, and cannot do before. It can reconcile a number to a source and get the same answer twice. It can change a schema on purpose, because it knows what depends on the column. And it can hand a regulator evidence rather than a promise, because the checks that passed were recorded rather than remembered.
The reverse case is the one most teams meet first. Where integrity is not held, the data team spends its week arguing about whose number is right instead of building anything, and the business learns to keep a spreadsheet on the side. That spreadsheet is the real cost of broken integrity, and it is very hard to take back.
The five types describe what integrity is. This section describes how it breaks in real pipelines. Four failures cause most of it, and none of them raises a database error, which is why they are found by monitoring rather than by reading logs.
A join is written as many to one, but the right hand table is not actually unique on the join key, so every duplicate on the right multiplies the rows on the left. The symptom is a total that jumps by a clean multiple, or a slightly odd number if only some keys duplicate. The check is a row count comparison: for a many to one join, the output row count must equal the left input row count. The control is to assert uniqueness on the join key before the join runs, as a test on the dimension table rather than a comment in the model.
A pipeline writes six of nine partitions and then fails, or an incremental load overlaps a source outage and picks up nothing for two hours. The table exists, the query returns rows, and the next model in the chain runs happily on incomplete input. The check is a volume monitor with a band learned from the table's own history rather than a fixed number, plus a freshness monitor on the maximum timestamp. The control is to write to a staging table and swap atomically, so a reader sees either the previous complete state or the new complete state and never a half written one.
Someone runs an UPDATE in production to correct one record and the WHERE clause is wrong, or a backfill is re run and silently rewrites a month of history. This is the failure that leaves no trace at all, because the row simply holds a different value afterwards and nothing says it used to hold another. The control is structural: people do not hold write access to production tables, corrections ship as version controlled code, and every table that matters keeps an append only change log so the previous value can still be read. Where a manual fix genuinely cannot be avoided, it runs through a break glass role that logs who used it and why.
An upstream team renames a column, widens a type or drops a field. The pipeline does not fail, because most ingestion is tolerant by design. The column simply arrives full of nulls, or the values start truncating, and the first person to notice is a business user three weeks later. The check is a schema drift monitor that raises an incident on any added, removed or retyped column. The control is knowing what a change reaches before it ships, which is what column level data lineage is for: trace the column downstream, see the eleven models and four dashboards that read it, and tell those owners before the change lands rather than after.
| What breaks | The check that catches it | The control that prevents it |
|---|---|---|
| Join fans out on a non unique key | Output row count equals left input row count for a many to one join | Uniqueness test on the join key of the dimension table |
| Partial or interrupted load | Volume monitor on a learned band, freshness monitor on max timestamp | Write to staging and swap atomically, plus a load audit row count |
| Uncontrolled manual update | Row level change log diff, unexpected update counts | No human write access to production, corrections as reviewed code |
| Silent schema change upstream | Schema drift monitor on added, dropped or retyped columns | Column level lineage plus a data contract with the producing team |
| Duplicate records from a retried job | Group by the business key and return groups with a count above one | Idempotent merge on a business key rather than an append |
| Orphaned foreign keys after a delete | Count child rows whose key is missing from the parent, threshold zero | FOREIGN KEY with an explicit ON DELETE action, or soft deletes |
The four failures above are how integrity breaks. The five issues below are what it looks like once it has, which is the form most teams meet it in, because a data consumer reports a symptom rather than a cause.
The same thing is recorded two different ways in two different places, so any comparison between them is wrong. It shows up as two systems disagreeing about a customer count, or one report using a definition of active that another does not share.
Duplicates are the entity integrity failure, and they are the most common of all because almost every pipeline retries. They inflate counts, they multiply revenue through joins, and they are invisible in a chart until someone reconciles.
Corruption is the physical integrity failure: bytes changed between writing and reading, through hardware fault, an interrupted write, a bad transfer or a faulty conversion.
Missing data is the hardest failure to see, because an incomplete table looks exactly like a complete one until you know what should have been there. Nulls in a key column, a missing partition, a source that stopped sending, a filter that quietly excluded a region.
Inaccuracy is the case where the data is structurally sound and still describes the world wrongly. Every constraint passes, every check is green, and the address is still the wrong address. This is the boundary where integrity work ends and data quality work begins, and it is the reason the two disciplines are not interchangeable.
So ensuring the integrity of the data and integrity and validity of the connection is a very important element in any company's strategy that is moving towards a Web service paradigm." - John W. Thompson
The organizing principle is to put every rule as close to the write as it will go, and to test everything the engine will not enforce for you. A rule declared in the database runs on every write forever without anyone remembering it. A rule that lives in a scheduled test runs when the schedule runs. A rule that lives in a document runs never.
Validation works in three layers and most teams only build one of them. At write time the database enforces types, NOT NULL, primary keys, foreign keys and CHECK constraints. At load time a contract test rejects arriving data that does not match the agreed shape, before it lands in a table anyone reads. At rest a scheduled suite re tests the assertions the warehouse does not enforce.
Five checks cover most of the ground and all five are cheap to write: not null on key columns, unique on the business key, accepted values on every enumerated column, a relationship check that every foreign key exists in its parent, and row count parity between the source and the landed table. Give each one a severity, because a test that pages someone at three in the morning for a cosmetic issue will be muted within a month, and a muted test protects nobody.
Consistency is what stops two correct systems producing two different answers. It is mostly a definition problem rather than a technical one. One definition per metric, written down, with one owner and one place it is computed, and every consumer reading that computation rather than reimplementing it.
Where the same data genuinely lives in two systems, reconcile them on a schedule and store the result. A daily job that compares row counts and one control total per table, and records both, turns "the numbers do not match" from an argument into a query.
An audit is a periodic answer to a fixed list of questions, and its value comes from asking the same questions every time so the trend is visible. A workable list: which tables have a declared primary key and which do not, what the duplicate rate is on each business key, how many orphaned foreign keys exist, what the null rate is on every column flagged as required, when each table was last updated against its expected schedule, and which tables have no owner recorded.
Run it monthly on the tables that feed reporting and finance, and quarterly on everything else. Store each run rather than reading it and moving on. The stored history is what turns an audit into evidence when a regulator asks how long a control has been operating.
Access control belongs in an integrity discussion because the largest single cause of unexplained changes is a person with more permission than they need. The rule that removes most of the risk is to separate reading from writing: analysts read, pipelines write, and nobody holds both against a production table.
Underneath that, grant by role rather than by person so access can be reasoned about, classify sensitive columns so the policy is attached to the data rather than to a memory of it, log every use of an elevated role, and review the grants on a schedule instead of letting them accumulate. Decube handles the classification and policy side of this in its data governance platform, which keeps the classification, the owner and the policy on the asset itself.
Most integrity failures are introduced by competent people following a habit nobody ever questioned. Appending instead of merging. Adding a column without telling the consumers. Fixing one row by hand because it was faster. Training that changes behavior is specific about those habits rather than general about the importance of data.
The most useful thing to teach a new engineer is the review question: what happens to this if it runs twice. Idempotency is the single habit that prevents the largest share of duplicate and partial load failures, and it is a design choice made once per pipeline rather than a discipline sustained forever.
Tooling does not create integrity. Constraints, contracts and access controls do that. What tooling does is make the state of integrity visible across hundreds of tables at once, so a failure is found by a monitor within minutes rather than by a business user three weeks later. Three categories cover it, and a data integrity platform is simply one product that carries all three rather than three products that do not talk to each other.
Observability is the detection layer. It watches tables continuously and raises an incident when their behavior departs from their own history, which is how the four failures described earlier are actually caught. The monitors that matter for integrity are volume, freshness, schema drift, job failure, field health such as null and blank rates, and custom SQL for business rules that no generic monitor can express.
The short walkthrough below shows how those monitors are set up in Decube. Schema drift and job failure activate on their own as soon as a source is connected, which matters because those two catch the silent schema change failure. Volume, field health and custom SQL are configured per dataset, the freshness monitor learns each table's own update pattern rather than taking a fixed threshold, and the volume monitor uses machine learning to set its band from history.
A threshold learned from a table's own behavior is the difference between alerting that is still read in month six and alerting everybody muted in week two. The wider case for this layer is set out in Decube's guide to data observability, and the product itself sits at the Decube data observability platform.
Discovery is the inventory layer, and it comes first in practice, because you cannot assert integrity on tables you do not know exist. A catalog answers which tables are in use, which are abandoned, who owns each one, what each column means, and which assets carry sensitive data.
For integrity work specifically, the two things worth demanding from a catalog are column level lineage, so the reach of a change is a fact rather than a guess, and profiling, so the null rate, distinct count and value distribution of a column are visible before you write a constraint that will fail on day one.
Governance is the accountability layer. It records the classification of each asset, the policy that follows from that classification, the person answerable for it, and the evidence that both were applied. Without it, integrity controls exist but nobody can say who owns a broken one.
The regulated version of this is stricter and worth designing for even if you are not regulated yet. Supervisors including OJK in Indonesia, APRA in Australia, MAS in Singapore and the NAIC in United States insurance all expect a firm to show that a control operated, not merely that it existed. That means the passing test is stored with a timestamp and an owner, which is a design decision you make when you build the check rather than a report you assemble afterwards.
These three get used interchangeably and they answer three different questions. Integrity asks whether the data is still what it was recorded as. Quality asks whether it is fit for the use somebody has in mind. Security asks who is allowed to reach it.
| Discipline | The question it answers | A failure that belongs to it |
|---|---|---|
| Data integrity | Is this data still what was recorded, and can I show it | A join fans out and revenue doubles, with every value individually valid |
| Data quality | Is this data fit for the use somebody has in mind | A structurally perfect address that is not where the customer lives |
| Data security | Who is allowed to read or change this data | An analyst with write access to a production finance table |
They overlap in one place worth knowing: access control is one of the strongest integrity controls there is, which is why a security decision often turns out to be an integrity decision. Decube covers the two neighboring comparisons in full, so this page does not repeat them. For the quality boundary, read the difference between data quality and data integrity. For the security boundary, read data integrity vs data security.
The benefits are easiest to state as things that stop happening. Reconciliations stop disagreeing. Schema changes stop causing incidents nobody predicted. Reports stop being rebuilt by hand in a spreadsheet. What follows are the three that a leadership team actually notices.
A decision made on data with broken integrity is not a worse decision, it is an unknown one, because nobody can say how wrong the input was. The gain from fixing integrity is not that the numbers get better; it is that the confidence interval around them becomes knowable. A forecast built on a table with a tested unique key and a reconciled row count can be argued about on its merits. A forecast built on a table that might contain duplicates cannot be argued about at all.
This matters more as decisions get automated. A person looking at a doubled revenue figure usually notices. A pricing rule or an AI agent reading the same figure does not, and it acts on it within seconds.
Integrity is the part of quality you can automate. Completeness, uniqueness and validity are all machine checkable, and once they are checked continuously the quality conversation moves on to the parts that need human judgment: whether a definition is right, whether a field still means what it meant two years ago, whether the data describes the world correctly.
That is the real return. Fixing integrity does not just raise a quality score, it frees the data team from re litigating basic correctness so it can spend its time on the questions only people can answer.
Nearly every data regulation asks the same two things: show that the control existed, and show that it operated. Integrity controls are unusually easy to evidence because they produce a record by design. A constraint declaration is in the schema history, a passing test has a timestamp, an access grant has an owner, and a lineage graph shows where a field traveled.
The practical advice is to store the passing runs, not only the failures. Most teams record incidents and discard green results, and the green results are exactly what an auditor asks for, because a control with no evidence of operating is a control that did not operate.
Implementing data integrity measures has been a game changer for our organization. We experienced data inconsistencies and inaccuracies in the past, which made it challenging to make informed decisions. However, with a strong focus on data observability, discovery, and governance tools, we have been able to ensure the accuracy and reliability of our data. Our team now has the confidence to rely on the data for critical decision-making processes. Data integrity is truly the key to successful data management.
Data integrity is the guarantee that data is complete, internally consistent and unchanged since it was recorded, apart from changes that were authorized and recorded. It comes in five types, one physical and four logical, and each one has a control that belongs to it. The single most useful thing to check this week is whether your warehouse is enforcing the constraints you assume it is, because on standard Snowflake tables primary key, foreign key and unique are documented as not enforced.
The work itself is unglamorous and finite. Declare the constraints your database will honor, write tests for the ones it will not, monitor volume, freshness and schema drift so the four common failures raise an incident, keep human hands off production writes, and store the evidence that each check ran.
Decube brings the catalog, column level lineage, quality monitoring and governance policy into one platform so all five types of integrity are observable in the same place. If you want to see it against your own tables, book a Decube demo.
Data integrity is the guarantee that a piece of data is complete, internally consistent and unchanged since it was recorded, apart from changes that were authorized and recorded. It is a property of the data and of the system that holds it, not an opinion about whether the data is useful. There are five types: physical integrity, and the four logical types, which are entity integrity, referential integrity, domain integrity and user defined integrity.
It means you can state, and prove, that the data in front of you is the data that was written, that every record can be told apart from every other record, that every reference points at something that exists, that every value sits inside its allowed set, and that any business rule spanning several columns still holds. If any one of those five statements fails, integrity is broken even when the numbers still look reasonable on a dashboard.
A data integrity control is a rule the system applies on its own so a bad write cannot land, or a test that catches one that already has. The main ones are primary keys and unique indexes for entity integrity, foreign keys with an explicit ON DELETE action for referential integrity, data types and CHECK constraints for domain integrity, cross column CHECK constraints and custom SQL monitors for user defined integrity, and checksums, replication and tested restores for physical integrity. Access controls and an append only audit log sit underneath all five.
Put the rule as close to the write as you can get it, then test what the engine will not enforce. In an operational database that means declaring primary keys, foreign keys, NOT NULL and CHECK constraints. In a cloud warehouse it means writing tests, because Snowflake documents PRIMARY KEY, FOREIGN KEY and UNIQUE constraints on standard tables as optional and not enforced, and only NOT NULL and CHECK as enforced. Add volume, freshness and schema drift monitors so a partial load or a renamed column raises an incident instead of arriving quietly, and keep direct write access to production tables away from people.
Data integrity management is the ongoing work of deciding which rules apply to which datasets, putting each rule somewhere it will actually run, watching whether it fires, and holding a named owner accountable when it does. It covers constraint coverage on new tables, the monitors that stand in for constraints in the warehouse, the incident process when one trips, the access model that decides who may write, and the audit evidence a regulator can read.
A data integrity platform is tooling that keeps the five types of integrity observable in one place rather than in scattered scripts. In practice it combines a catalog so you know which tables exist and who owns them, column level lineage so you can see what a change reaches before you approve it, quality monitors that watch volume, freshness, schema drift and custom SQL rules, and governance policy so classification and access are recorded rather than remembered. Decube covers those four in a single platform.
Confirmation is the evidence step: the check that proves the data was not altered between two points. For a file or an object it is a checksum or hash comparison, matching the value computed after a transfer against the value computed before it. For a table load it is reconciliation, comparing the row count and a control total such as a summed amount column against the source. A confirmation that is never recorded is not confirmation, so the result belongs in a load audit table you can query later.
Data integrity is about whether the data is still what it was recorded as. Data security is about who is allowed to reach it. They overlap because access control is one of the strongest integrity controls there is, and they come apart because a perfectly secured database can still be full of orphaned foreign keys and duplicated rows. Decube covers the comparison in full in Data Integrity vs Data Security: Key Differences and Best Practices.
Fidelity asks how closely a copy matches its source, so it is the question you ask about a replica, an extract or a downsampled table. Integrity asks whether the data satisfies its own rules, so it is the question you ask about a single system on its own. A replica can have perfect fidelity to a source that already had broken integrity, which is why a migration that only reconciles row counts will happily carry every duplicate across.
First step to AI Readiness
Table of Contents
Sneak peek from the data world.
Bring lineage, quality, governance, and AI together in one platform—so your teams and AI agents can act with confidence.
_For%20light%20backgrounds.svg)
.svg){kind=small}
.webp)