TL;DR
Data integrity testing checks that keys, relationships and values stay correct and consistent as data is written, moved and transformed. Each test is a small SQL query that returns the rows breaking one rule, so zero rows means a pass. The core tests cover entity, referential, domain, constraint, row-count, checksum, cross-system and temporal integrity. Run them where data changes hands, at write time, after ingestion, after each transformation and before reports read it. Declared keys are not enough, because Fabric Warehouse, Databricks and Snowflake standard tables do not enforce primary or foreign keys. Automate the tests in CI/CD and keep every run as audit evidence, with a named owner for each failure.
Key Takeaways Data integrity testing proves that records, relationships and business rules still hold after data moves between systems. Seven test types cover most failures, from duplicate keys and orphan rows to impossible dates and cross-system drift. Every test should return only the violating rows, which makes pass and fail easy to read and easy to automate. Row counts alone can pass while records are missing, duplicated or changed, so pair them with key and hash checks. Many cloud platforms accept primary and foreign keys without enforcing them, so integrity has to be tested, not assumed. Tests run in CI and after every load, with severity rules and stored results, give auditors proof instead of promises. Watch on YouTube
Validate & Reconcile Data in Minutes with FLIP | Automated Data Reconciliation Solution
Kanerika shows how FLIP compares source and target data and flags every mismatch automatically, the same trace-it-back work behind the revenue story below.
One Wrong Revenue Number and the Trail Behind It A finance director opens the monthly revenue dashboard and stops at one figure. Revenue for the West region is higher than the sales team’s own count, and nobody can explain the gap. The dashboard itself works fine, so the error must come from somewhere upstream.
An analyst starts walking backward. The dashboard reads a reporting table, and that table joins orders to customers. Weeks earlier, a retried load had inserted one customer record twice. So every order for that customer now appears twice after the join, and the West region total is inflated.
The orders table still had the right row count. The duplicate lived one table over, in a key that was declared unique but never enforced. One short SQL query would have caught it on the day of the retry. Instead, the fix took one DELETE statement, and the search took days.
What Is Data Integrity Testing? Data integrity testing uses repeatable queries and constraints to check that data stays accurate and consistent as systems create, move and change it. NIST’s glossary describes data integrity as data that has not been altered in an unauthorized manner since it was created, sent or stored. Testing turns that property into checks a team can run, pass or fail.
Two layers sit under that idea. Physical integrity covers storage, such as disks, backups and corrupted files, and it belongs to infrastructure teams. Logical integrity covers meaning, such as unique keys, valid relationships and values that follow business rules. This guide is about logical integrity, because that is where SQL tests do their work.
Integrity testing also differs from broader quality work. Quality asks whether data is fit for a purpose, for example fresh enough for a forecast. Integrity asks whether structure and rules still hold, and the data integrity vs data quality guide covers that boundary in depth.
What a Data Integrity Test Contains A useful test is more than a query. It names the table under test, the rule in plain words and the SQL that finds violations. It also states a threshold, the point in the flow where it runs, the person who owns it and the record it leaves behind.
Writing the rule in plain words first matters more than it seems. “Every order belongs to a real customer” is a sentence a finance lead can confirm. The SQL is simply its translation, so when the business changes the rule, the test changes with it.
Ownership is the part teams skip most often. A test with no owner fails quietly in a log that nobody reads. Name one person or team per test, and route its failures to them directly.
How to Decide Whether a Test Passed Key and relationship rules are strict. A duplicate primary key or an orphan order is a failure at one row, so the expected count of violations is zero.
Numeric comparisons need a tolerance instead. Two systems can round currency differently. So a revenue total that differs by a cent may be fine, while a gap of a thousand dollars is not. Write that tolerance down before the first run, not after the first failure.
NULL values need an explicit decision too. A nullable foreign key is not an orphan, and any comparison with NULL returns unknown rather than true or false. So decide for each column whether a missing value passes, fails or goes to review.
Finally, separate reporting a failure from stopping one. Some tests should block a load, while others should open a ticket and let data flow. That choice belongs in the test’s design, and the severity section below shows how to set it.
Types of Data Integrity Testing Seven test types cover nearly every integrity failure a data team meets. Each one catches a different kind of break. Each also has a simple pass condition you can express in SQL, which is what makes them easy to automate later.
Schema and Metadata Integrity Tests All seven types assume the table still has the shape you expect, so a schema test checks that first. Schema drift happens when a source adds, drops, renames or retypes a column without warning. A retyped column can truncate values or break joins, and a dropped one breaks every query that selects it.
The test compares the live table with a written contract that lists each column’s name, data type and nullability. In dbt, an enforced model contract runs a preflight check that the model returns exactly those names and types. It also reports a removed column or a changed type as a breaking change. Delta tables on Databricks add schema enforcement on write , so an insert that names an unknown column fails. On other platforms, a query against INFORMATION_SCHEMA.COLUMNS does the same job.
Entity Integrity Tests Entity integrity means every row is uniquely identifiable. So the test looks for primary keys that repeat or are NULL. It should also check business identifiers, because a table can hold unique surrogate IDs and still store the same customer twice under two IDs.
Composite keys, however, need extra care. If a row is unique only by order_id and line_number together, then testing either column alone raises false failures. Always test the full key the business actually uses, and record it in the model as part of good data modeling practice .
Referential Integrity Tests Referential integrity means every reference points at something real. An order’s customer_id must exist in the customers table, and a line item’s order_id must exist in orders. Rows that break this rule are orphans.
Orphans distort reports in a sneaky way, and that is why they matter. A revenue figure built with an inner join drops them, while a plain count of orders keeps them. So two reports disagree, and both look correct to the people reading them.
Domain Integrity Tests Domain integrity means each value comes from its allowed set. Status codes must sit on the approved list, quantities must be positive and dates must be real dates. Formats such as country codes or currency codes must also match a standard.
These tests catch typos, unmapped codes from a newly connected source and placeholder defaults. A date of 1900-01-01, for example, often hides a missing value that someone filled to satisfy a NOT NULL rule. Data profiling is a good way to discover which values a column really holds before you write the allowed list.
Constraint and Business Rule Tests Some rules span several columns or rows. An order’s total should equal the sum of its line items plus tax, and a shipped order must have a ship date. A refund should never exceed the original payment, and the database rarely enforces any of this for you.
Constraint tests also include negative tests. In a test environment, try to insert a row that breaks a NOT NULL or CHECK rule, then confirm the database rejects it. If it accepts the row, the constraint is either missing or not enforced.
Row-Count and Checksum Tests Row counts compare how many records left a source with how many arrived. They are the cheapest check on this list, and they catch missing batches and duplicate loads quickly. Yet equal counts can hide one missing row and one duplicated row in the same batch.
Checksums, however, close that gap. A hash computed over each row’s important columns changes when any of those values change. So comparing hashes key by key finds edited records that counts alone would never notice.
Cross-System Consistency Tests Cross-system tests check that the same business record agrees everywhere it lives, such as a customer’s status in the CRM and in the warehouse. They match records on a business key and then compare the fields that matter. Timing is the hard part, because one system may update minutes or hours after the other.
Any data integration project that syncs CRM, ERP and warehouse records needs these tests from the first load. Without them, drift between systems surfaces only when two reports disagree.
Finance reconciliation is the classic case, where one mismatched receivable is enough to put a wrong outstanding balance into reporting. The data reconciliation guide covers that discipline on its own.
Case Study
90% Accuracy in AR Reconciliation with UiPath
A financial services firm matched bank statements to ERP receivables on account name, number, entry type and amount, reaching 90% matching accuracy, a 90% faster reporting cycle and 70% less manual work.
Read the Case Study → Temporal Integrity Tests Temporal tests check that time makes sense. A ship date cannot come before its order date, and an end date must follow its start date. Validity windows for the same customer or price should not overlap either.
Future-dated transactions deserve their own check. They often point to a time zone error or a day-month swap during parsing at ingestion, and they quietly push revenue into the wrong period.
The table below puts all seven types side by side. Use it as a quick review sheet when you plan tests for a new dataset.
Table 1: Data Integrity Test Types at a Glance
Test type What it catches How to check it Pass condition Entity Duplicate or NULL keys, the same customer under two IDs GROUP BY key with HAVING COUNT(*) > 1; IS NULL filter Zero rows returned Referential Orphan child rows with no parent NOT EXISTS or LEFT JOIN to the parent table Zero rows returned Domain Invalid codes, ranges and formats Allowed-list, range and pattern filters Zero rows returned Constraint and business rule Broken cross-column rules, bad writes accepted Rule query plus a negative insert test Zero rows; the bad write is rejected Row count Missing batches and duplicate loads COUNT(*) per batch on both sides Counts match for every batch Checksum Values changed in transit Row hashes compared by key Hashes match for every key Cross-system Records that disagree between systems Key match plus a field diff in both directions No unexplained differences Temporal Impossible dates and overlapping windows Date comparisons and overlap joins Zero rows returned
How to Perform Data Integrity Testing With SQL SQL is the most portable tool for data integrity testing, because every warehouse and lakehouse speaks it. The six tests below share one small sales schema. It has orders, customers, a staging copy of source orders and a reporting table built from them. Rename the tables and columns, and the same patterns work on SQL Server, Azure SQL, Fabric, Databricks or Snowflake.
Every query follows one rule. It returns only the rows that break the rule, so an empty result is a pass and any row is evidence of a failure.
Test 1: Find Duplicate and Null Keys Start with entity integrity, since every later test depends on keys being trustworthy. The first query finds repeated order IDs, and the second finds orders that have no ID at all.
-- Test 1a: duplicate primary keys (expect zero rows)
SELECT order_id, COUNT(*) AS copies
FROM sales.orders
GROUP BY order_id
HAVING COUNT(*) > 1;
-- Test 1b: missing primary keys (expect zero rows)
SELECT *
FROM sales.orders
WHERE order_id IS NULL;
-- Test 1c: one customer stored under two IDs (expect zero rows)
SELECT LOWER(TRIM(email)) AS email_key,
COUNT(DISTINCT customer_id) AS ids
FROM sales.customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(DISTINCT customer_id) > 1;Test 1c is the one teams forget. Surrogate keys can be perfectly unique while the same person sits in the table twice. The opening example was the plainer case, one customer_id loaded twice, which Test 1a catches as soon as it also runs against sales.customers. Normalize the business key first, since “[email protected] ” and “[email protected] ” are the same customer.
Test 2: Find Orphan Records Referential tests look for child rows whose parent is missing. NOT EXISTS is the safest form, because it behaves predictably when NULL values are involved.
-- Test 2: orders that point at a customer who does not exist
SELECT o.order_id, o.customer_id
FROM sales.orders AS o
WHERE o.customer_id IS NOT NULL -- nullable FKs are tested separately
AND NOT EXISTS (
SELECT 1
FROM sales.customers AS c
WHERE c.customer_id = o.customer_id
);Avoid writing this test with NOT IN. Microsoft’s documentation for IN warns that NULL values in the list return UNKNOWN. So one NULL customer_id in the subquery can make a NOT IN version return no rows at all. The test then reports a pass that never happened.
Test 3: Check Allowed Values, Ranges and Business Rules Domain tests then compare each value against what the business allows. Keep the allowed list in a reference table where you can, so the test updates when the business adds a status.
-- Test 3a: invalid status, quantity or date (expect zero rows)
SELECT order_id, order_status, quantity, order_date
FROM sales.orders
WHERE order_status IS NULL
OR order_status NOT IN ('PLACED', 'SHIPPED', 'DELIVERED', 'CANCELLED')
OR quantity <= 0
OR order_date < '2015-01-01'
OR order_date > CURRENT_DATE; -- use CAST(GETDATE() AS date) on SQL Server
-- Test 3b: order total must equal its lines plus tax, within one cent
SELECT o.order_id, o.total_amount,
SUM(l.line_amount) + o.tax_amount AS expected_total
FROM sales.orders AS o
JOIN sales.order_lines AS l ON l.order_id = o.order_id
GROUP BY o.order_id, o.total_amount, o.tax_amount
HAVING ABS(o.total_amount - (SUM(l.line_amount) + o.tax_amount)) > 0.01;Notice the explicit IS NULL check in Test 3a. Without it, a NULL status slips past the NOT IN filter for the same reason described in Test 2. Test 3b is a business rule, and the one-cent tolerance is a decision someone in finance should sign off.
Test 4: Compare Row Counts by Batch Total counts across a whole table hide too much. Instead, compare counts for the same scope on both sides, such as one batch, one load date or one partition.
-- Test 4: batches where source and reporting counts differ
WITH src AS (
SELECT batch_id, COUNT(*) AS src_rows
FROM staging.source_orders
GROUP BY batch_id
),
tgt AS (
SELECT batch_id, COUNT(*) AS tgt_rows
FROM reporting.orders
GROUP BY batch_id
)
SELECT COALESCE(s.batch_id, t.batch_id) AS batch_id,
s.src_rows, t.tgt_rows
FROM src AS s
FULL OUTER JOIN tgt AS t ON t.batch_id = s.batch_id
WHERE COALESCE(s.src_rows, -1) <> COALESCE(t.tgt_rows, -1);The FULL OUTER JOIN matters here. It catches a batch that exists on only one side, which a simple inner join would silently skip. Still, a matching count proves only that the totals agree, so Tests 5 and 6 check the records themselves.
Test 5: Diff Records Across Systems Once both datasets sit in one query engine, a set difference shows which records disagree. Run it in both directions, since each direction answers a different question.
-- Test 5a: rows in the source that are missing or different in reporting
SELECT order_id, customer_id, CAST(total_amount AS DECIMAL(18,2)) AS total_amount
FROM staging.source_orders
EXCEPT
SELECT order_id, customer_id, CAST(total_amount AS DECIMAL(18,2))
FROM reporting.orders;
-- Test 5b: rows in reporting that the source never sent, or that changed
SELECT order_id, customer_id, CAST(total_amount AS DECIMAL(18,2)) AS total_amount
FROM reporting.orders
EXCEPT
SELECT order_id, customer_id, CAST(total_amount AS DECIMAL(18,2))
FROM staging.source_orders;Cast both sides to the same type and precision first, or rounding alone will flood the result. Also know the operator’s limits. EXCEPT returns distinct rows , so an extra copy of an identical row never shows up in this diff. That gap is why Test 1 and Test 4 still run alongside it.
This pattern is the core of any system-to-system comparison, including checks after a platform move. Teams planning one will find the full test strategy in the data migration testing guide.
Test 6: Compare Row Hashes Wide tables, though, make column-by-column diffs slow and noisy. A row hash folds the columns you care about into one value per key, so comparing two tables becomes a single join.
-- Test 6: keys whose row hash differs (SQL Server, Azure SQL, Fabric Warehouse)
WITH src AS (
SELECT order_id,
HASHBYTES('SHA2_256', CONCAT_WS('|',
COALESCE(CAST(customer_id AS VARCHAR(20)), '~null~'),
COALESCE(UPPER(TRIM(order_status)), '~null~'),
COALESCE(CONVERT(VARCHAR(10), order_date, 23), '~null~'),
COALESCE(CAST(CAST(total_amount AS DECIMAL(18,2)) AS VARCHAR(30)), '~null~')
)) AS row_hash
FROM staging.source_orders
),
tgt AS (
SELECT order_id,
HASHBYTES('SHA2_256', CONCAT_WS('|',
COALESCE(CAST(customer_id AS VARCHAR(20)), '~null~'),
COALESCE(UPPER(TRIM(order_status)), '~null~'),
COALESCE(CONVERT(VARCHAR(10), order_date, 23), '~null~'),
COALESCE(CAST(CAST(total_amount AS DECIMAL(18,2)) AS VARCHAR(30)), '~null~')
)) AS row_hash
FROM reporting.orders
)
SELECT s.order_id
FROM src AS s
JOIN tgt AS t ON t.order_id = s.order_id
WHERE s.row_hash <> t.row_hash;Two details make or break this test. First, CONCAT_WS ignores NULL values , so without the COALESCE wrappers two different rows can produce the same string. Second, use a strong hash. Microsoft’s CHECKSUM documentation advises HASHBYTES when a missed change is not acceptable, since CHECKSUM collides far more often.
On Databricks and Snowflake, the sha2 function plays the same role. A hash match is strong evidence rather than absolute proof. So keep a full field diff for the few tables where money or regulatory figures live.
Turn Each Query Into a Reusable Assertion One-off queries help during an incident, but they do not protect the next load. The fix is to wrap each check so that every run writes a result row. That is the same contract dbt data tests use, where a test passes when it returns zero failing rows.
-- Record the outcome of Test 2 as evidence for this run
INSERT INTO dq.test_results
(test_id, run_id, run_at, dataset, failed_rows, status)
SELECT 'ORD-REF-001',
'load-2026-10-17-0600',
CURRENT_TIMESTAMP,
'sales.orders',
COUNT(*),
CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END
FROM sales.orders AS o
WHERE o.customer_id IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM sales.customers AS c
WHERE c.customer_id = o.customer_id);Give every test a stable ID, a short description and a version in source control. Then the results table becomes a history of what was checked, when, and with what outcome.
Where Data Integrity Tests Should Run A test is only useful at the moment data changes hands. Run each check where a failure is cheapest to catch and easiest to trace back. The data pipeline architecture guide explains how those stages are designed, and the data pipeline vs ETL guide shows where classic ETL fits. This section covers what to test at each one.
At the Point of Write The cheapest place to stop bad data is before it is stored. Enforced NOT NULL and CHECK constraints reject invalid values at insert time, and negative tests confirm they really fire.
Multi-step writes need a rollback test as well. Force the second step of a transaction to fail, then confirm that the first step’s rows rolled back. A partial write leaves orders without lines, which no later test can fully explain.
After Ingestion Fresh batches need fast checks before anything else touches them. Confirm required keys are present, compare the batch count with the source’s own manifest and look for a file that loaded twice. Also check new codes against the allowed lists, because a newly connected source often brings values nobody mapped.
After Transformation Joins are where most silent integrity damage happens. A join on a key that is not unique multiplies rows, while an inner join on a broken reference drops them. So compare row counts before and after each important join, and re-run key tests on the output.
Derived values also deserve a check. If a transformation calculates net revenue, test it against an independent calculation for a sample of orders. Keeping data lineage current makes it far faster to see which upstream step changed when a derived value drifts.
Before Reports and AI Models Read the Data The last checkpoint is a publish gate on the tables people actually use. Critical tests at this stage block the refresh, so a dashboard shows yesterday’s correct numbers rather than today’s wrong ones. For example, in a medallion architecture , this gate usually sits between the silver and gold layers.
Metric consistency belongs here as well. Revenue in the gold table should match the sum of the silver orders it came from, within the agreed tolerance. AI models need the same gate, because a model trained on duplicated customers learns the wrong patterns at scale.
Why Platform Constraints Do Not Replace Tests Declaring a primary key feels like protection, yet on several cloud platforms it is only a hint to the query optimizer. The documentation is explicit for each platform below, and the gap is the reason integrity tests remain necessary.
Table 2: Which Constraints Each Platform Enforces
Platform Enforced Declared but not enforced What to test with SQL Microsoft Fabric Warehouse No key or reference constraints PRIMARY KEY and UNIQUE (NONCLUSTERED, NOT ENFORCED), FOREIGN KEY (NOT ENFORCED) Duplicate keys, orphans Databricks (Delta Lake) NOT NULL and CHECK Primary key, foreign key and unique (informational) Duplicate keys, orphans Snowflake standard tables NOT NULL and CHECK PRIMARY KEY, FOREIGN KEY and UNIQUE Duplicate keys, orphans SQL Server and Azure SQL PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK and NOT NULL Foreign key and CHECK constraints switched off with NOCHECK CONSTRAINT Business rules, cross-system drift
The sources are direct. Microsoft’s Fabric table constraints page supports primary keys only as NONCLUSTERED and NOT ENFORCED. Databricks calls its primary, foreign and unique keys informational. Snowflake enforces them only on hybrid tables, not standard ones.
Watch on YouTube
Snowflake Cortex for Data Quality: What ETL Tools Can’t Do (Demo)
A Kanerika demo of a Snowflake data cleanup that finds every child table tied to a customer record and tracks PII copies through lineage, useful when the platform will not enforce relationships for you.
So on Microsoft Fabric , Databricks and Snowflake , the duplicate customer from the opening story would load without a single error. Tests 1 and 2 are the only line of defense, and they should run on every load. Snowflake teams can add native checks too, which the Snowflake data quality guide walks through.
How to Automate Data Integrity Testing in CI/CD Manual checks catch the problem you already suspect. Automated data integrity testing catches the one nobody expected, on the night a source system changes without warning. The goal is simple to state. Every code change and every new batch of data should meet the same tests.
Keep Tests as Code Next to the Models Store each SQL assertion in the same repository as the transformation it protects. When a developer changes a model, the tests for that model sit in the same pull request. Reviewers then see the logic and its guardrails together.
Tooling is a matter of fit. dbt ships four generic tests, unique, not_null, accepted_values and relationships. Together they cover entity, domain and referential checks with a few lines of configuration.
Teams on Databricks with dbt often start there, then add singular SQL tests for business rules. Open-source validation libraries and the quality features inside each platform follow the same pattern.
Run Them on Every Pull Request CI should build the changed models in an isolated schema and run their tests against a fixed set of sample data. Fixtures should include the awkward rows on purpose, such as NULL keys, duplicate emails and orders dated in the future. If a critical test fails, the merge stops.
This is where testing shifts left. A broken join caught in a pull request costs a code review comment. The same join caught in production costs a wrong report and a week of tracing.
Run Them Again After Every Load CI proves the code is right, but it cannot prove tonight’s data is right. So the orchestrator should run the same assertions on each new batch, straight after it lands and again after each transformation. Failing rows go to a quarantine table with the batch ID, while the clean rows carry on. Feed the results into your data pipeline monitoring tools so a failure raises an alert instead of sitting in a log.
Watch on YouTube
AI in Software Testing: Shift Left, QAOps & What Actually Works
An episode of The Digital Shift on building testing into planning, build and release instead of checking at the end, the same shift-left habit that catches a broken join in a pull request instead of a board report.
Retries also need care, since a retried load is exactly how the duplicate customer in the opening story appeared. Re-run Test 1 on the affected tables after every retry, even when the load is meant to be idempotent. Data pipeline automation makes those runs routine rather than something an engineer remembers.
Set Severity: What Blocks and What Warns Still, not every failure deserves to stop the business. Sort tests into three levels before they go live.
Critical failures block the load or the publish step, such as duplicate primary keys, orphans on required relationships or totals outside tolerance on finance tables.Warning failures let data flow but open a ticket with an owner, such as a new status code or a small timing gap between systems.Informational checks only record trends, such as a slow rise in NULL rates that may become a problem next quarter.Exceptions should also expire. When someone approves a known failure, record who approved it, why and until when. Otherwise a temporary waiver quietly becomes permanent, and the test stops meaning anything.
Keep Large Tables Affordable Full comparisons on billion-row tables every hour are rarely worth the compute. Instead, test the partitions or batches that changed, then schedule a full comparison weekly or monthly. Business-key checks on new rows stay cheap even at large scale.
Sampling has one firm limit. It works for spotting value drift, but it cannot prove uniqueness, since a duplicate pair can sit outside any sample. Run key tests on the complete changed set every time.
How to Record Integrity Test Results for Audit Evidence Auditors rarely ask whether you test data. They ask which rule ran, on which data, when, with what result and who fixed the failures. In regulated industries the bar is written down. The FDA’s data integrity guidance for drug manufacturing is one example of how seriously regulators treat it.
What Every Test Run Should Record The results table from the SQL section is the start. Extend it until any reviewer can reproduce a run without asking the engineer who wrote it.
Table 3: Evidence Fields for Each Integrity Test Run
Evidence field Example Why a reviewer needs it Test ID and version ORD-REF-001, v3 Shows exactly which rule ran Dataset and scope sales.orders, batch 4471 Shows which data was checked Run ID and timestamp load-2026-10-17-0600 Ties the result to one execution Expected and actual 0 orphans expected, 12 found Proves the threshold applied Status and severity FAIL, critical Explains why the load stopped Code revision Commit a41f9c2 Links the result to the test code Owner Sales data team Names who must act
Store results centrally, keep them for as long as your retention policy requires and restrict who can edit them. Evidence that anyone can change is not evidence at all.
How to Document a Failure Through to Closure A failed test opens a short trail. Record the failing keys, the rule broken, the owner, the fix and the passing re-run that closed it. Mask personal values in failure samples, because a test log full of customer emails becomes its own compliance risk.
Automated reports can serve as that trail. In Kanerika’s premium reconciliation work for Fortegra , automated Excel reports served as audit-ready logs. That is the standard to aim for, where the evidence is produced by the run itself.
Ownership and policy sit above the tests. Kanerika’s data governance services define who owns each critical dataset, and the data governance guide explains how those roles fit together.
Case Study
90% Compliance Adherence for Healthcare with Purview
A North American healthcare organization used Microsoft Purview for a central catalogue and a classification framework, cutting data discovery time by 57% and raising compliance adherence by 90%.
Read the Case Study → Common Data Integrity Testing Mistakes Integrity tests fail in predictable ways. Each mistake below produces a green dashboard over broken data, which is worse than no test at all.
Trusting row counts alone. Equal totals can hide a missing row and a duplicate in the same batch.Assuming declared keys are enforced. On Fabric Warehouse, Databricks and Snowflake standard tables, they are not.Comparing different scopes. A source snapshot at 6:00 and a target at 6:05 will never match, so pin both sides to the same batch or cut-off.Forgetting NULL and set-operator behavior. NOT IN with a NULL in the list and CONCAT_WS skipping NULLs both produce false passes, and EXCEPT hides an extra copy of a row because it returns distinct rows.Running tests nobody acts on. A failure without an owner and a closure date is just noise in a log.Each of these is cheap to fix once you name it. Pair every count with a key test, and pin every comparison to one scope. Then give every failure a person. Data observability tools can add trend alerts on top, but they do not replace explicit rules.
A Data Integrity Testing Checklist Use this list when you set up tests for a new dataset or review an existing suite. Each item maps to a section above.
Write each rule in plain words and get a business owner to confirm it. Check primary keys and business keys for duplicates and NULLs. Look for orphans on every required relationship with NOT EXISTS. Cover allowed values, ranges, formats and dates, including NULLs. Compare counts per batch, then hashes or field diffs per key. Run tests at write, after ingestion, after transformation and before publish. Assign a severity, a tolerance and an owner to every test. Store every run with its scope, result, code version and closure record. Teams that already use a data quality framework can slot these tests into it as the integrity layer. The rules stay the same, and only the reporting changes.
Checklist
Data Engineering Checklist for Enterprise Teams
Review how your pipelines are designed, tested, monitored and maintained, from idempotent loads and data contracts to observability and CI/CD, before quality gaps reach your reports.
Get the Checklist → How Kanerika Builds Data Integrity Testing Into Enterprise Platforms Kanerika’s data engineering services run quality checks on every pipeline and automated tests on every build, so integrity tests ship with the platform instead of arriving after the first incident. Its DataOps work also enforces data contracts across workflows, the same idea behind the schema tests earlier in this guide.
Delivery follows Kanerika’s IMPACT methodology, which has six steps. The list below is not a separate Kanerika procedure. It shows where the integrity practices from this guide fit inside each step.
Identify the gaps. List the tables that feed finance, compliance and customer reports, then note which keys and relationships nobody tests yet.Map the requirements. Write each rule in plain words with its business owner, including tolerances and NULL handling.Prove the value. Run the key and orphan queries on one critical dataset first, so real failures make the case for the rest.Analyze root causes. Trace each failure back to the load, retry or mapping table that caused it, as in the revenue story at the top.Create the solution. Turn the queries into assertions with a severity and an owner, and run them in CI and after every load.Transform at scale. Extend the same tests to every pipeline and keep each run’s results as evidence.What This Looks Like in Kanerika Client Work The results show up in reconciliation work. For a financial services firm, Kanerika built rule-based matching of bank statements against ERP receivables on account name, account number, entry type and amount. The firm reached 90% matching accuracy, a 90% faster analysis and reporting cycle and 70% less manual intervention, according to the AR reconciliation case study .
For Fortegra, Kanerika replaced a manual comparison of bank data with insurance system records. Python scripts extract policy numbers, amounts and account details, and UiPath runs the reconciliation with Excel and database activities. The work reached 99% accuracy in data extraction and cut reconciliation time by 85%, from five days a month to a few hours. The same integrity discipline applies when a platform moves, which the enterprise data migration guide covers.
One pitfall is easy to miss. Teams test the tables they built and skip the reference data those tables join to, such as currency codes, region maps and product hierarchies. A broken mapping table corrupts every report that uses it, so reference data belongs under the same tests as transaction data.
Talk to Kanerika
Plan Integrity Tests for Your Critical Data
Tell us your use case, such as a business-critical dataset whose keys, relationships and evidence trail need testing, and a Kanerika consultant will contact you to scope it.
Book a Consultation → Wrapping Up Data integrity testing comes down to a few habits. Write each rule in plain words, translate it into a SQL query that returns only violations and run it wherever data changes hands. Do not trust declared keys on platforms that never enforce them. Automate the tests in CI and after each load, set severity so the right failures stop the line and keep every run as evidence. The wrong revenue number from the opening story would have surfaced on the day of the retry. It would have shown up as one empty result that suddenly was not empty.
Frequently Asked Questions
What is data integrity testing? Data integrity testing checks that data stays accurate and consistent as systems write, move and transform it. Teams express each rule as a SQL query or constraint that returns the rows breaking it. Typical tests look for duplicate keys, orphan records, invalid values, mismatched counts and records that disagree between systems.
What are the main types of data integrity testing? The main types are entity integrity (unique, non-null keys), referential integrity (no orphan records), domain integrity (allowed values and ranges), constraint and business rule tests, row-count and checksum tests, cross-system consistency tests and temporal tests that catch impossible dates. Together they cover most structural and logical failures in a dataset.
How do you test data integrity in a database using SQL? Write one query per rule that returns only the violating rows. Use GROUP BY with HAVING COUNT(*) greater than one for duplicate keys, NOT EXISTS for orphan records and filters for invalid values. An empty result means the test passed. Store each query with an ID so it can run automatically and record its result.
How do you ensure data integrity across systems? Match records on a shared business key, then compare the fields that matter in both directions. Pin both sides to the same batch or cut-off time so timing gaps do not cause false failures. Combine per-batch row counts with row hashes or field diffs, and assign an owner to investigate every unexplained difference.
Can row-count testing detect all data integrity errors? No. Row counts confirm that totals match, but a batch can lose one record and duplicate another and still show the same count. Counts also miss values that changed in transit. Pair them with duplicate key tests, orphan checks and row hashes or field-level comparisons to see what actually differs between source and target.
What is checksum testing in data integrity? Checksum testing computes a hash over the important columns of each row and compares hashes between source and target by key. A different hash means at least one value changed. Use a strong function such as SHA-256, normalize types and NULLs first, and remember that a matching hash is strong evidence rather than absolute proof.
Do Microsoft Fabric, Databricks and Snowflake enforce primary and foreign keys? Not on their standard warehouse or Delta tables. Fabric Warehouse accepts primary, unique and foreign keys only as not enforced, Databricks treats them as informational, and Snowflake enforces them only on hybrid tables. NOT NULL and CHECK rules are enforced on Databricks and Snowflake, so duplicate key and orphan tests must run as SQL.
How often should data integrity tests run? Run them whenever data or code changes. That means in CI on every pull request, after every load and transformation, and at the publish gate before reports refresh. On very large tables, test the changed partitions on every run and schedule full comparisons weekly or monthly, but never sample when checking uniqueness.
How can data integrity testing be automated in CI/CD? Keep each SQL assertion in version control next to the model it protects. CI builds changed models in an isolated schema, runs their tests on fixture data and blocks the merge when a critical test fails. The orchestrator then runs the same tests on each new batch and writes every result to a central table.
What happens when a data integrity test fails? The response depends on severity. A critical failure stops the load or the report refresh and moves failing rows to quarantine. A warning lets data flow but opens a ticket for the test owner. In both cases, the team records the failing keys, the fix and a passing re-run before the failure is closed.
What evidence does an auditor need from data integrity testing? Auditors want to see which rule ran, on which data, when and with what result. Record the test ID and version, dataset and batch, run ID and timestamp, expected and actual values, status, severity, code revision and owner. For failures, keep the fix and the passing re-run, and mask personal data in samples.
What is the difference between data integrity testing and data integration testing? Data integration testing checks that an integration job moves and maps data correctly between systems, covering connections, mappings and load behavior. Data integrity testing checks that the resulting data still obeys its rules, such as unique keys, valid relationships and allowed values. Integration projects use integrity tests as part of their acceptance criteria.
