Data integrity best practices are the controls, tests, and records that keep data complete, consistent, and accurate as it is stored, moved, and changed. The goal is simple: when a number appears in a report, you can show that the data behind it arrived intact, and that someone would have noticed if it had not.
This guide is the operating checklist. For why integrity fails, read data integrity challenges. For migration-specific tests, read data integrity testing during data migration.
Key Takeaways
- Data integrity means data stays complete, consistent, and accurate as it is stored, moved, and changed.
- Good practice combines controls that prevent errors, tests that find them, and records that prove what happened.
- Use the ten-practice checklist in this post and automate each item, so it runs on every load and every change.
- Match how often checks run to how fast data changes: every release, every load, and on a schedule for tables at rest.
How to Ensure Data Integrity: What Do Controls, Tests, and Evidence Each Do?
Three things work together. Controls prevent or catch errors, such as required fields, unique keys, and limits on who can change data. Tests check the data itself, by comparing source with target and applying rules. Evidence is the record that shows the checks ran and what they found. Controls come in three kinds: preventive controls stop bad data getting in, such as required fields; detective controls find it, such as comparisons and rules; and corrective controls fix it and prove the fix, such as issue tracking and reruns. A complete practice has all three.
Why bother? An integrity error rarely announces itself. A job can finish successfully while the data is wrong, and the first person to notice is often the one reading the report.
Established frameworks follow the same pattern. The FDAโs data integrity guidance for drug manufacturers asks that data be attributable (you can tell who or what created or changed it), legible, recorded at the time, original, and accurate, a set known as ALCOA. The NIST SP 800-53 control catalog includes integrity checks, automated notification of violations, and automated response.
What Does a Data Integrity Checklist Look Like?
Table 1 is the checklist: ten practices, why each one matters, and how to automate or check it in Datagaps DataOps Suite. Work from top to bottom, and count a practice as done only when it runs without anyone having to remember it.
| # | Practice | Why it matters | How to automate or check it |
|---|---|---|---|
| 1 | Define what correct data means | Tests need something to compare against | Keep mappings and rules in Mapping Manager, with plain-language Business Transformation and Technical Transformation fields |
| 2 | Use keys and constraints | The database can reject bad data before it spreads | Check them with Metadata Compare, which reports foreign key and NOT NULL mismatches |
| 3 | Make loads repeatable | A retry should never create duplicate rows | Check Uniqueness in Data Compare, or a Duplicate Check rule |
| 4 | Control schema and mapping changes | Silent changes break loads without an error | Schema Drift and Impact Analysis in the Data Catalog, plus Metadata Compare |
| 5 | Compare source and target | Matching counts can hide wrong values | Data Compare for records, Metrics Compare for totals, Cross Source Compare for groups |
| 6 | Automate tests in the release pipeline | Manual checks get skipped under pressure | Scheduler, command line, REST API, and Azure DevOps Test Plans |
| 7 | Test rules on good and bad data | A rule that never fails proves nothing | The Test button, and sample data with known passing and failing records |
| 8 | Monitor tables continuously | Problems appear between releases | DataOps Data Quality rules, DQ score, and alerts; observability for unknown issues |
| 9 | Track every failure to resolution | Failures nobody owns come back | Data Quality Issues with Open, Resolved, or Ignored status; a Jira ticket from a failed batch run |
| 10 | Limit access and keep records | You must show who changed what, and when | Roles, password vault, single sign-on, audit logs, and comparison reports |
If you have little time, start with three: compare source and target (5), run the comparison on every release (6), and track failures to resolution (9).
Which Data Integrity Controls Are Governance and Which Are Testing?
Some practices are decisions and responsibilities. Others are checks that a tool runs. You need both, and Figure 1 shows which is which.
- Governance practices (1, 2, 4, 9, and 10): people decide what correct means, approve changes, limit access, and keep the record.
- Testing practices (3, 5, 6, 7, and 8): tools check the data on every load, release, or schedule.
Governance without testing is a policy nobody can prove. Testing without governance finds problems nobody owns.
How Do You Apply the Data Integrity Checklist Stage by Stage?
Design and Build (Practices 1 to 3)
1. Define what correct data means: Write each rule as one sentence with an example, such as โTotal children is never negative, so a value of -1 is a failure.โ
2. Use keys and constraints: Use keys and required fields wherever the database allows them. If the source cannot enforce them, enforce and test them in the target.
3. Make loads repeatable: Load on a business key, then prove it by running the same load twice and checking that no duplicates appear.
Load and Change (Practices 4 and 5)
4. Control schema and mapping changes: Agree how source teams announce changes, and keep mappings under version control. When drift appears, Impact Analysis shows which dataflows, business rules, and data models it affects.
5. Compare source and target: Choose how closely to compare: counts for every table, totals for business meaning, and full record comparison for the tables where one wrong row matters most.
Verify (Practices 6 and 7)
6. Automate tests in the release pipeline: Treat checks like code: they run automatically, and a failure stops the release.
7. Test rules on good and bad data: Before a rule goes live, run it on sample data with one record that should pass, one that should fail, and one on the boundary. Give each rule a response for when it fails: block the load, warn the team, or log the failure.
Operate and Prove (Practices 8 to 10)
8. Monitor tables continuously: Run proven rules on a schedule against tables that are already loaded, and watch the quality score over time.
9. Track every failure to resolution: Give every failure an owner and a status: Open, Resolved, or Ignored.
10.Limit access and keep records: Limit who can change data and pipelines, keep credentials in a vault, and keep the audit log and comparison reports as evidence. Evidence supports an audit but does not guarantee compliance.
How Do You Build Data Integrity Checks Into CI/CD and Scheduled Runs?
CI/CD is the automated process that builds and releases your data pipelines. Add integrity checks to it so that a failing check stops the release:
- Save your comparisons and rules as a dataflow.
- Call the dataflow from the release pipeline with the command line or REST API. The command line can report a pipeline runโs job status, so the release can read pass or fail.
- Schedule the same checks to run on every load, and on a timer for important tables at rest.
- Send alerts to the people who can fix a failure, and open a ticket, for example in Jira, when a batch run fails.
- Keep the comparison report and run history as evidence.
Match the schedule to the speed of change. Run tests on every release, pipeline checks on every load, and monitoring rules daily or more often where an error costs more. For tests across several applications, see application integration testing.
Our Recommendation: Automate the Checklist One Practice at a Time
Do not try to adopt all ten practices at once. Pick the tables that feed your reports, models, and regulatory submissions, and work through the checklist in three rounds:
- Round one, compare, gate, and track (practices 5, 6, and 9): compare source and target on your critical tables, run the comparison on every release, and give every failure an owner and a status.
- Round two, define, prevent, and test (practices 1, 2, 3, and 7): write down what correct means, add keys and required fields, make loads repeatable, and test each rule on passing and failing records before it goes live.
- Round three, watch and prove (practices 4, 8, and 10): control schema and mapping changes, schedule monitoring, and keep the audit log and comparison reports as evidence.
Measure progress simply. After each round, count how many critical tables have the practice running automatically, and review the list whenever a pipeline or mapping changes. A practice only counts when it runs without anyone having to remember it.
Datagaps DataOps Suite covers the checks, the schedule, and the records in one place. To see why checks fail in the first place, read data integrity challenges.
Frequently Asked Questions
How Do You Maintain Data Integrity?
Combine controls (keys, required fields, access limits), automated tests that compare source and target and apply rules, and records of every run. Review the checklist whenever pipelines or mappings change.
How Often Should Data Integrity Checks Run?
Match the schedule to the speed of change: on every release, on every load, and on a timer, such as daily, for important tables at rest. Run checks more often where an error costs more.
What Are Data Integrity Controls?
Controls are anything that prevents or catches an integrity error: required fields and keys, limits on who can change data, automated comparisons and rules, alerts, and audit records.
What Should a Data Integrity Checklist Include?
Ten practices: define correct data, use keys and constraints, make loads repeatable, control changes, compare source and target, automate tests, test rules on good and bad data, monitor continuously, track failures to resolution, and limit access and keep records. Table 1 lists them.
Book a personalized 30-minute demo and bring one pipeline,
or start a 14-day free trial.
RajMohan Achanta
Senior Associate Product Manager, Datagaps
Senior Associate Product Manager at Datagaps. Shapes the product experience across ETL Validator, BI Validator, and DataOps Data Quality.
Avinash Keshri
Head, Product Marketing
Head of Product Marketing at Datagaps and IIM Bangalore alumnus. 13+ years of experience in commercializing AI and data platforms across global markets.




