Kindly fill up the following to try out our sandbox experience. We will get back to you at the earliest.
How to ensure data accuracy step by step: the six checks, where each one runs in the pipeline, the threshold to set, and what to do when one fails.
Updated on
Most guidance on how to ensure data accuracy gives you a list of good intentions: define standards, use reliable sources, monitor regularly, train your people. None of it survives contact with a pipeline, because it never says which check runs where, what number goes in the configuration, or what you do at nine in the morning when the check fails and a report is due at ten.
This article is the procedure instead. Six checks, ordered by the stage of the pipeline they belong to, each with the test to write, a starting threshold, and the response when it trips. Data accuracy means the degree to which a stored value matches the real world thing it describes, and if you want the business case for caring about that, our article on why data accuracy matters for business success covers the what and the why. This page covers the how.
The reason accuracy programs stall is that teams test the wrong property and then report a green dashboard. A customer record with the postcode 90210 passes every format check ever written. It is still wrong if that customer lives in Singapore. No rule you can write about the column alone will ever catch it, because the error is not in the shape of the value, it is in the relationship between the value and the world.
That distinction decides which test you write, so it is worth five lines before the procedure starts. Each quality dimension answers a different question and each one is proved by a different kind of check.
| Dimension | The question it answers | The test that proves it | Can it be tested in isolation |
|---|---|---|---|
| Accuracy | Does this value match the real thing it describes | Compare against an authoritative counterpart: the source system, a regulator file, a physical count, a human review of a sample | No |
| Validity | Is this value a permitted value for this column | Format, range, enumeration and regular expression checks against the schema | Yes |
| Completeness | Is anything missing that should be here | Null rate per column, row count against expectation, referential checks for orphaned keys | Yes |
| Consistency | Does this value agree with the same fact stored elsewhere | Cross system comparison of the same entity in two places | No |
| Timeliness | Is this value current enough to act on | Freshness monitor on the maximum event timestamp against the agreed lag | Yes |
| Uniqueness | Is this real thing represented once | Duplicate key check and fuzzy match on natural keys | Yes |
Read down the last column and the working method falls out of it. Four of the six can be tested inside your warehouse with nothing but the table in front of you, which is why they get automated first and why most teams stop there. Accuracy and consistency need a second opinion from outside the table, which is slower, so they get skipped, which is why the accuracy problem survives a dashboard full of green. If you want the mechanics of the four that can be tested in isolation, our guide to data validation practices covers the rules themselves. The related but separate question of whether data has been altered or corrupted in transit belongs to data integrity, which is a different property again.
Here is the whole procedure to ensure data accuracy on one screen. The rest of the article is one section per row, in order. The stage column is the part that matters: running the right test at the wrong stage is how a team ends up debugging a transformation for two days over an error that arrived in the source file.
| Step | Pipeline stage | What you check | Starting threshold | When it fails |
|---|---|---|---|---|
| 1 | Before any code | The written accuracy rule for the dataset: the field, the authority it is compared against, the measure, the target and the owner | One rule per critical dataset, none without a named owner | You do not have a test yet, you have an opinion |
| 2 | Before ingestion | The source: who owns it, how it is captured, what its own error rate is, and whether the schema is contracted | No new critical source onboarded without a named producer and an agreed schema | Profile a sample and quantify the damage before you build on it |
| 3 | At landing, before transformation | Schema drift, job failure, freshness against the agreed lag, and row volume against learned behavior | Freshness breach at twice the agreed lag; volume outside three standard deviations of the learned pattern | Halt the downstream run rather than publishing a partial load |
| 4 | After transformation | Field health at column level: null rate, uniqueness, allowed values, format, plus referential checks across tables | Null rate above the profiled baseline plus two percentage points; zero orphaned foreign keys | Quarantine the failing rows, publish the rest, raise an incident against the owner |
| 5 | Before publication | Reconciliation against the system of record: row counts, summed monetary values, and a sampled row by row comparison | Row count within 0.1 percent; monetary totals to the cent; sample accuracy at or above the written target | Hold publication. A reconciliation break is the one failure that should stop a release |
| 6 | Continuously | The thresholds themselves, the alert volume they produce, and the response loop behind them | Under 10 alerts a day per team, and every alert closed with a named cause | Tune by suppression first, threshold second. Never mute a monitor without a review date |
An accuracy rule is one sentence with five parts: the field, the authority it is compared against, the measure, the target, and the person accountable for it. Without the second part it is not an accuracy rule at all, because there is nothing to compare against. Without the last part nobody will act on it.
A usable rule reads like this. Customer postal address in the CRM is compared monthly against the national postal address file, measured as the percentage of addresses that match to street level, targeted at 98 percent, owned by the head of customer operations. That sentence tells an engineer exactly what to build. Compare it with "we will improve address quality", which tells them nothing.
The measure itself is deliberately simple. Accuracy rate is the number of records that match the authority divided by the number of records checked, expressed as a percentage. The interesting decision is not the formula, it is the denominator: whether you check every record or a sample.
If you cannot compare every record against the authority, you sample. The size of that sample decides whether a clean result means anything, and the arithmetic is unforgiving. Assume the true error rate in a table is 1 percent and that you draw rows at random. The chance that a sample of a given size contains no bad rows at all is 0.99 raised to the power of the sample size.
| Rows sampled | Chance the sample comes back completely clean | What a clean result means |
|---|---|---|
| 50 | 60.5 percent | Nothing. You are more likely than not to miss a 1 percent error rate |
| 100 | 36.6 percent | Very little. Roughly one clean result in three is a false all clear |
| 300 | 4.9 percent | A clean sample is now meaningful evidence, though not proof |
| 500 | 0.7 percent | A clean sample is strong evidence at this error rate |
| 1,000 | 0.004 percent | A clean sample effectively rules out a 1 percent error rate |
Three hundred rows is the practical starting point for a manual accuracy audit, and it explains a common frustration: a team spot checks twenty rows, finds nothing, and reports the data as clean while the business keeps finding errors. At a 1 percent error rate a twenty row check comes back clean about 82 times in 100. The check was never capable of detecting the problem.
Set the target as a number, not as a direction. "Improve accuracy" has no completion state. "Raise street level address match from 91 percent to 98 percent by the end of the second quarter, measured monthly on a 300 row random sample" can be passed or failed, and a target that can be failed is the only kind anyone acts on.
Almost every accuracy problem is inherited. The pipeline did not corrupt the address, it faithfully carried an address that was typed wrong into a form eighteen months ago. Downstream testing finds those errors late and expensively, which is why the cheapest check in the whole procedure is the one you run before you write any ingestion code at all.
Put five questions to every new critical source, and write the answers into the catalog entry rather than into a document nobody opens again.
| Question to put to the source | What a good answer looks like | What you do with a bad answer |
|---|---|---|
| Who produces this data and who is accountable for it | A named person or team who knows the data exists and accepts they own it | Do not onboard it as a critical source. An unowned source cannot be fixed when it breaks |
| How is the value captured | System generated from a transaction, or validated at entry against a reference list | Free text entry by a human under time pressure predicts a high error rate. Budget for cleansing and reconciliation |
| Is the schema contracted or can it change without warning | A published schema with a change notice period and a version | Assume it will change silently. Schema drift monitoring becomes mandatory, not optional |
| What does a profile of a real sample show | Null rates, distinct counts and value distributions that match what the producer describes | The gap between the description and the profile is your error budget. Quantify it before you build on it |
| Can this value be compared against anything authoritative | A second system, a regulator file, a reference dataset, or a physical count | If nothing authoritative exists, accuracy cannot be measured for this field. Say so publicly rather than reporting a number you cannot defend |
The profiling question is the one teams skip and it is the one that pays. Profiling a sample takes an afternoon and it tells you the null rate per column, the distinct value count, the distribution, and the values that appear once. A column the producer describes as a controlled list with eight values, which profiles at 340 distinct values, is not a controlled list. You have found the accuracy problem before writing a line of ingestion code.
These three checks run on raw data as it arrives, before any transformation. They exist to answer one question: did the thing that was supposed to arrive actually arrive, whole and on time. None of them measures accuracy directly. All of them catch the failures that make an accuracy conversation pointless, because a table that is two days stale or missing a third of its rows is not slightly inaccurate, it is not usable.
Freshness deserves the extra care because it is the check most often set wrong. The lag you monitor is the lag the business agreed to, not the lag the pipeline currently achieves, and the difference between those two numbers is the conversation worth having. Our note on data freshness covers how to pick the number.
Schema drift and job failure are the two that should switch on automatically for every source you connect, because they need no configuration and no baseline. Freshness and volume need a learned baseline, which means they are only as good as the period they learned from. A monitor trained during a quiet fortnight will treat a normal month end as an anomaly.
The short walkthrough below is the clearest summary of the choice between monitor types. It covers schema drift and job failure switching on at connection, how the freshness monitor learns each table's own update pattern, how the volume monitor watches row count behavior for a missing load or a spike, field health at column level with null, uniqueness and regular expression checks, custom SQL for rules that span tables, and group by monitoring that segments the same check by a dimension such as region so you can see where quality breaks rather than just that it broke.
Once the data has been joined, cast and modelled, the tests move from the shape of the load to the content of the columns. This is where most written data quality rules live, and it is also where teams write far too many of them and then mute the lot. Start with the columns a business decision actually depends on, which in most warehouses is a small fraction of the total.
| Column type | The test to write | Starting threshold | On failure |
|---|---|---|---|
| Business key | Uniqueness, and not null | Zero duplicates, zero nulls | Halt. A duplicated key silently double counts every measure built on it |
| Foreign key | Referential check against the parent table | Zero orphaned keys | Quarantine the orphans, publish the rest, raise it with the source owner |
| Categorical | Value in the allowed list | Zero values outside the list; alert on any new value on first appearance | Publish, and route the unrecognized value to the owner. New categories are usually real |
| Monetary or numeric measure | Range check plus distribution comparison against the profiled baseline | Values inside the agreed range; distribution mean within three standard deviations of the learned pattern | Halt if the range check fails, alert if only the distribution moved |
| Free text identifier such as an address or a name | Format check, then a match rate against a reference list | Match rate at or above the target written in step 1 | Alert and queue for cleansing. This is a target to raise over time, not a run to halt |
| Timestamp | Not null, inside a sane window, and ordered against related timestamps | Zero future dated rows, zero events completing before they started | Quarantine. Impossible timestamps corrupt every cohort and retention measure downstream |
Two rules keep this list from becoming noise. First, set the null rate threshold from the profiled baseline rather than from zero, because a column that is legitimately 12 percent null will fire every single day against a zero threshold and be muted within a week. Alert on the baseline plus a couple of percentage points, so you are detecting a change rather than a fact you already knew.
Second, distinguish between a test that halts the pipeline and a test that raises an alert. Halting is right for a duplicated business key or an impossible timestamp, because publishing that data does more damage than publishing nothing. Halting is wrong for a match rate target, which is a number you are working to raise over months. Teams that halt on everything end up with a pipeline nobody trusts to run unattended.
This is the step almost nobody writes about and it is the only one that measures accuracy rather than plausibility. Everything in steps 3 and 4 asks whether the data is internally sensible. Reconciliation asks whether it agrees with the system that is authoritative for the fact, which is the only question a finance or risk stakeholder is actually asking when they ask whether the numbers are right.
Pick one authoritative counterpart for each critical dataset. For revenue it is the billing or ledger system. For customers it is the CRM or the core banking record. For inventory it is the physical count. For addresses it is the national postal file. If nothing authoritative exists, say so publicly, because a number you cannot reconcile is a number you cannot defend in a meeting.
| What you compare | Tolerance to start from | How often | On a break |
|---|---|---|---|
| Row count for the period | Within 0.1 percent | Every load | Investigate before publishing. Almost always a late arriving batch or a filter difference |
| Summed monetary total for the period | Exact, to the cent | Daily | Hold the release. A monetary break is never rounding, it is a missing or duplicated set of transactions |
| Distinct entity count, such as active customers | Within 0.5 percent | Weekly | Usually a definition difference rather than a data error. Resolve the definition and write it into the catalog |
| Sampled row by row comparison of key fields | Match rate at or above the written target | Monthly, on a random sample of at least 300 rows | Feeds the accuracy rate reported against the step 1 target |
The most useful outcome of reconciliation is rarely the break itself. It is the discovery that the two systems define the thing differently: the warehouse counts a customer from first order, the CRM counts from account creation, and the gap has been reported as a data quality problem for two years. Resolve that in the definition and both numbers become correct.
When a reconciliation break is real, the question is what it touched, and that is a lineage question rather than a testing one. Column level data lineage turns the two hour hunt for affected dashboards into a lookup, which matters most when you are deciding whether to hold a release or issue a correction.
A related case is worth stating precisely, because it is the price of a monitoring program a regulator judged inadequate. On 14 March 2024 the Federal Reserve Board fined JPMorgan Chase approximately $98.2 million for an inadequate program to monitor firm and client trading activities for market misconduct occurring between 2014 and 2023, and stated that the penalties announced by the Board and the Office of the Comptroller of the Currency together totalled approximately $348.2 million. The Board's release does not describe the technical cause, and nothing here should be read as a claim about it. What it does establish is that a supervisor treats incomplete monitoring as a finding in its own right, whatever produced it.
A check with no agreed threshold is not a check, and a threshold chosen without arithmetic is a guess that somebody else will have to live with. The number you pick determines your alert volume before a single real problem occurs, and you can work that out in advance rather than discovering it in production.
Take a metric that is roughly normally distributed, such as daily row count for a stable table. A three standard deviation rule trips on about one observation in 370 purely by chance. Suppose you check 200 tables every hour, which is 4,800 checks a day.
| Threshold | Trips by chance on about one observation in | Alerts a day across 4,800 checks | What that means for a team of three |
|---|---|---|---|
| 2 standard deviations | 22 | 218 | Unworkable. Muted within a fortnight |
| 3 standard deviations | 370 | 13 | Workable if, and only if, every alert is triaged |
| 4 standard deviations | 15,787 | 0.3 | About one alert every three days. A good default for a new program |
| 5 standard deviations | 1,744,278 | 0.003 | About one a year. You will miss real problems |
Start new monitors at four standard deviations and tighten to three once the team is closing alerts reliably. Starting tight is the classic mistake: the monitors fire, nobody has time to triage 218 alerts a day, the channel gets muted, and six months later a real incident sits unread in it. Every number in the table above is chance alone, before any genuine problem is added.
The response matters more than the detection, and it should be written down before the first failure rather than improvised during it. Five moves, in order.
Review the whole set weekly and tune by suppression before you tune by threshold. Read twenty alerts from the noisiest monitor. If eighteen were benign for the same reason, write a narrow suppression for that reason. Raising the threshold for everything to silence one known pattern is how monitors go quiet on the failures they were built for.
The six steps above describe the finished state. Nobody gets to build it from nothing, and the practical question is usually how to improve data accuracy on something that is already running. If you have inherited a warehouse with hundreds of tables and no tests, the order of work is what decides whether the effort shows up in a business meeting or disappears into a backlog.
Two failure patterns are worth naming because they are almost universal. The first is writing hundreds of tests in the first month and muting all of them by the third: fewer tests with owners beat more tests without. The second is treating accuracy as a project with an end date rather than as a measure that is reported forever, which is the difference between a cleansing exercise and data quality management.
A research agent, a retrieval system or an internal assistant inherits the accuracy of whatever it retrieves, and it inherits it silently, because a fluent answer built on a stale table reads exactly like a fluent answer built on a current one. The checks do not change, but two of them become load bearing in a way they are not for a human reading a dashboard.
Freshness matters more, because a person looking at a report notices that the numbers stopped moving and an agent does not. Provenance matters more, because the useful answer is not just the value but which table it came from, when that table was last updated, and whether it currently has an open incident against it. An agent that can read the quality state of an asset alongside the asset itself can decline to answer, which is the behavior you want. One that cannot will answer confidently from a table that broke on Friday.
Decube is a data governance and context platform, and its part in this procedure is steps 3, 4 and 6. Connect a source and the schema drift and job failure monitors switch on without configuration. Freshness and volume monitors learn each table's own update pattern rather than holding you to a fixed number, which is what keeps the alert count in the range the arithmetic above says is workable. Field health covers the column level tests in step 4, and custom SQL covers the business rules that span tables. Those sit in the data observability and data quality platform.
For step 5, lineage is the part that matters. When a reconciliation break is real, column level lineage answers what it touched before you decide whether to hold the release, and the catalog carries the owner so the incident can be assigned to a person rather than a queue. Steps 1 and 2 stay human work: writing the accuracy rule and interrogating the source are decisions, not features.
If you want to see the monitors and the lineage against your own tables rather than in a demo dataset, request a demo.
Ensuring data accuracy comes down to running the right check at the right stage and agreeing in advance what happens when it fails. Write the accuracy rule with a named authority and a named owner. Interrogate the source before you ingest it. Watch schema, freshness and volume at landing, field health and referential integrity after transformation, and reconcile against the system of record before you publish. Then set the threshold with arithmetic rather than instinct and run the failure loop every time it trips.
The part most programs skip is step 5, and it is the only step that measures accuracy rather than plausibility. A dashboard full of green tests tells you the data is internally consistent. Only a comparison against something authoritative tells you it is right.
You run six checks at four stages of the pipeline. Before any code, write an accuracy rule for each critical dataset naming the field, the authority it is compared against, the measure, the target and the owner. Before ingestion, interrogate the source: who owns it, how the value is captured, whether the schema is contracted, and what a profile of a real sample shows. At landing, monitor schema drift, job failure, freshness against the agreed lag, and row volume against the learned pattern. After transformation, test field health at column level and check referential integrity across tables. Before publication, reconcile against the system of record. Then set the alert threshold with arithmetic and run a written failure loop every time a check trips.
The check that does this is reconciliation, and it belongs immediately before publication. Compare the dataset against the system that is authoritative for the fact: the ledger for revenue, the core record for customers, the physical count for inventory. Start with a tolerance of 0.1 percent on row counts, exact agreement to the cent on monetary totals, and a monthly row by row comparison on a random sample of at least 300 rows. A reconciliation break is the one failure that should stop a release, because every other check only proves the data is internally sensible, not that it is true.
Pick three datasets that someone has already complained about rather than the largest ones, because the complaint proves the data is used and the error matters. Reconcile them against the system of record and publish the accuracy rate as it stands, however unflattering, so later work can be proved. Switch schema drift and job failure monitors on across every source, since they need no baseline and no thresholds. Then fix upstream rather than downstream: a validation rule at the point of entry stops the error being created, while a cleansing rule in the warehouse has to be maintained forever and hides the problem from the person who could stop it.
Accuracy rate is the number of records that match an authoritative counterpart divided by the number of records checked, expressed as a percentage. The formula is not the difficult part. The difficult parts are naming the authority you compare against, which is what separates accuracy from validity, and choosing the sample size. At a true error rate of 1 percent, a 50 row sample comes back completely clean 60.5 percent of the time and a 100 row sample 36.6 percent of the time, so both are capable of reporting clean data that is not clean. A 300 row random sample comes back clean 4.9 percent of the time and is the practical starting point.
There is no universal number, and any page that gives you one is guessing. The target comes from what the field is used for. A monetary total feeding a regulatory return reconciles exactly, to the cent, because anything else is a missing or duplicated set of transactions. A street level address match rate of 98 percent is a demanding but reachable target for customer data. A free text field nobody makes decisions on may not need a target at all. Set the number so that it can be failed, measure it on a schedule, and report it to the person named as owner.
Validation proves that a value is permitted. Accuracy proves that it is true. A postcode of 90210 passes every format, range and enumeration check ever written, and it is still wrong if the customer lives in Singapore. Validation can be tested inside your warehouse with nothing but the table in front of you, which is why it gets automated first. Accuracy needs a second opinion from outside the table, which is slower, which is why it gets skipped and why accuracy problems survive a dashboard full of passing tests.
They inherit the accuracy of whatever they retrieve, silently, because a fluent answer built on a stale table reads exactly like a fluent answer built on a current one. Two of the six checks carry more weight for an agent than for a person. Freshness matters more, because a human reading a report notices that the numbers stopped moving and an agent does not. Provenance matters more, because the answer needs to carry which table the value came from, when that table last updated, and whether it has an open incident against it. An agent that can read the quality state of an asset alongside the asset can decline to answer, which is the behavior you want. One that cannot will answer confidently from a table that broke on Friday.
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)