Data integrity is achieved when data stays accurate and consistent across its lifecycle, from creation in a source system to its use in a dashboard. When integrity breaks, errors in source records flow into downstream reports and AI models, leading to inaccurate forecasts and flawed predictions.

Each data integrity issue has a distinct cause, and understanding that root cause helps data teams prevent errors before they appear in reporting. This guide explains the meaning of data integrity, how it differs from data quality, and the most common integrity issues teams encounter, along with tips to prevent them.

What is data integrity?

Data integrity is a property of data that stays accurate, complete, consistent, and secure from creation to deletion. Hardware failures, interrupted syncs, missing database rules, and unauthorized edits can all compromise data integrity.

Different teams have their own ways of maintaining data integrity. Infrastructure teams use redundant storage to guard against hardware failures, while database administrators set constraints that reject invalid values. Data engineers run automated pipelines to copy every record intact, and security teams restrict edits through role-based access controls.

Data integrity comes in two types: physical and logical.

Physical data integrity

Physical data integrity is the ability of storage hardware to write data and later retrieve it without corruption. When hardware fails because of disk crashes, power outages, overheating servers, or flood damage, stored files can become corrupted or disappear entirely.

Infrastructure teams protect physical integrity against these risks with a redundant array of independent disks, which copies data to several drives. They also maintain off-site backups so another copy remains available if the main data center is damaged.

Logical data integrity

Even when the hardware works perfectly, data integrity can break if invalid or contradictory values slip into a table. Logical data integrity ensures that every value in a database is valid and follows the rules of the schema. Relational databases support logical integrity through several constraint types:

• Entity integrity: Each row has a unique, non-null primary key, so one customer can’t appear twice in a customer table.
• Referential integrity: The database rejects a record linked to a missing row in another table, preventing an employee from belonging to a non-existent department.
• Domain integrity: Columns accept only values of a defined type and range, such as a date column that rejects free text.
• User-defined integrity: Business teams add custom rules, for example, a check that blocks discounts over 50%.

These constraints catch logical errors when an application writes a record. But whether a table suits a specific report depends on data quality and data curation.

Data integrity vs. data quality

Data integrity protects records from errors as they move through systems, while data quality rates how useful records are for a given analytical use. Here’s a side-by-side comparison:

Why data integrity matters

An integrity error in one source table spreads to every dashboard that uses the table. Strong data integrity carries these advantages:

• Trusted reporting: Finance teams publish revenue figures that match source systems, so leaders can act without having to reconcile first.
• Audit readiness: Compliance teams can show auditors who changed each record and when, fulfilling the record-keeping requirements of privacy and healthcare regulations.
• Reliable AI output: Product teams ship AI features with accurate answers because AI data preparation starts from complete, duplicate-free records.
• Lower cleanup costs: Data engineers can fix an integrity error at the source instead of tracing every affected report.

7 common data integrity issues and how to prevent them

Data integrity issues typically start during syncs from source systems to the warehouse, or inside older systems without modern APIs. Each issue has a clear cause and a prevention plan that data teams can control.

1. Fragmented data

Fragmentation happens when teams rely on different systems: Sales tracks deals in a CRM and finance keeps revenue in an ERP. Each system uses its own customer ID, so dashboards show conflicting totals for the same account.

The fix is to centralize CRM and ERP records in one warehouse through data integration and map each system’s customer ID to one master ID. A data integration platform like Fivetran syncs CRM and ERP data automatically so analysts don’t have to merge spreadsheet exports.

2. Duplicate data

Duplicates appear when pipelines reload rows already synced or an application creates a new account after an email change instead of updating the existing one. These duplicate rows inflate customer counts and scatter one buyer’s purchase history across multiple records.

Prevent duplication by giving every table a primary key and loading data with upserts, which update existing rows and insert only truly new ones. Add uniqueness tests on key columns to catch repeated IDs before they reach a dashboard.

3. Outdated data

Outdated data builds up when a pipeline copies inserts and updates but misses deletes, or when syncs run less often than records change. A customer record deleted from the CRM might stay active in the warehouse.

Use change data capture to read the source database’s change log and copy every change, including deletes. Set alerts on delayed syncs so engineers can spot a stuck pipeline before analysts see wrong numbers.

4. Lost data

Records go missing when a network connection drops mid-transfer or a large migration times out. Without a completeness check, analysts might unknowingly work from partial tables.

Build each ETL data pipeline to retry failed syncs and resume from the last confirmed record. Compare source and destination row counts after each load, and re-sync the affected time range to backfill absent rows.

5. Security and compliance gaps

Pipelines that copy every column often move sensitive fields, such as card numbers and patient IDs, into tables any analyst can query. Broad write access also allows users without a business reason to change records.

Block or hash sensitive columns before loading them into the destination tables, and encrypt data both in transit and at rest. Grant permissions by role so users only see and edit the data required to perform their jobs.

6. Missing audit trails

Pipelines without logging keep no record of which sync changed a table or who edited a connection’s settings. When a dashboard figure shifts overnight, engineers have no way of tracing the cause or proving to auditors that the underlying record remained intact.

Log every sync event and user action, including start time, end time, tables affected, and rows changed. Store the logs in the warehouse for the retention period regulators require.

7. Legacy systems

Older on-premises systems, such as mainframes and early ERP versions, typically lack modern APIs, so data teams extract data with custom scripts. But even a small schema change in the old system can break custom scripts without warning.

Replace one-off scripts with prebuilt connectors that read the legacy systems’ change logs. For any source without a prebuilt connector, build a custom connector with logging and retries.

Protect data integrity with Fivetran

Most integrity issues — duplicate rows, unhandled schema changes, failed syncs, and exposed sensitive columns — enter the data warehouse through the pipeline. Data engineering teams often patch pipeline failures with custom scripts and manual checks, but these fixes are fragile and errors still slip through.

Fivetran replaces custom scripts with fully managed pipelines, and the destination platform’s own constraints protect data after each sync. Here’s how Fivetran’s governance and security features protect data as it moves from source to destination:

• Managed syncs: Data engineering teams get built-in deduplication and automatic schema change handling, removing the need to write merge logic.
• Column protection: Administrators can block sensitive columns or hash their values, so analysts can join on hashed keys without seeing personal data.
• Access control: Security teams can assign role-based permissions on the Standard plan and above, with Okta or Microsoft Entra ID provisioning available on Enterprise and Business Critical plans.
• Audit logs: Compliance teams can review sync logs and user actions provided by the Fivetran Platform Connector in the warehouse or in catalogs such as Atlan or Collibra.
• Data tests: Analytics engineers can schedule dbt Core tests through Fivetran Transformations to check unique keys, null values, accepted values, and relationships after each sync.

Coupa, a spend management company, used Fivetran to consolidate Salesforce and NetSuite data in Snowflake and improve its audit readiness. The team reduced its data pipeline workload by 4 times and shortened time to value from 3–6 months to just a few weeks.

Start a free trial with Fivetran to keep duplicate rows and missed deletes out of your data warehouse.

FAQ

What are the ALCOA principles of data integrity?

The ALCOA principles define five qualities of trustworthy records: attributable, legible, contemporaneous, original, and accurate. These principles ensure that each entry names its creator and is captured as the event happens. Regulated companies apply ALCOA to electronic records as well, so every database change needs a named user and a timestamp.

What is an example of data integrity?

An example of data integrity is a database that rejects a new order because the order’s customer ID doesn’t exist in the customer table. The referential integrity rule prevents orders without a valid customer account from entering revenue reports.

‍

[CTA_MODULE]

‍