Data integrity checks verify that data is accurate, consistent, complete, and valid throughout its lifecycle. Without them, errors introduced during collection, transformation, storage, or transmission propagate silently through systems — corrupting reports, breaking integrations, and producing unreliable analysis results.
Types of Data Integrity
Entity integrity ensures each row in a database table is uniquely identifiable — typically enforced through primary keys that cannot be null or duplicated. Referential integrity ensures foreign key relationships between tables remain valid — a record in an orders table cannot reference a customer ID that doesn’t exist in the customers table. Domain integrity ensures values in a field stay within defined acceptable ranges — a date field contains only valid dates, a percentage field contains only values between 0 and 100. User-defined integrity enforces business rules that don’t fit standard constraint categories — an order cannot have a ship date earlier than its order date.
Structural Validation: JSON and XML
For structured data formats like JSON and XML, structural validation is the first integrity check to run. Format Pilot’s JSON formatter validates JSON against RFC 7159 — catching missing brackets, trailing commas, unquoted keys, and type mismatches before they reach an API or database. Valid JSON structure is a prerequisite for data integrity in any system that processes JSON payloads.
CSV Integrity Checks
CSV files fail integrity checks in several common ways: inconsistent column counts across rows, values containing unescaped commas that break the delimiter parsing, mixed encoding that produces garbled characters, and blank rows that disrupt record counts. Before loading any CSV into a database, data warehouse, or analytics tool, validating column count consistency, delimiter uniformity, and encoding correctness prevents silent data corruption.
Duplicate Detection
Duplicate records are one of the most common data integrity failures. They inflate row counts, skew aggregations, and create conflicting states when the same entity has two different values for the same field. Deduplication should happen at every pipeline stage where data from multiple sources is merged. Format Pilot’s text utilities include a remove duplicate lines function for text-based deduplication during data preparation.
Frequently Asked Questions
What is the most important data integrity check?
Structural validity is the foundation — if a JSON file is malformed or a CSV has inconsistent column counts, no subsequent check can run reliably. After structural validation, uniqueness checks (no duplicate primary keys) and referential integrity checks (all foreign keys point to existing records) are the highest-value integrity constraints to enforce.
How do you check data integrity in a pipeline?
Run validation at each stage: validate schema and structure at ingestion, check row counts and field completeness after transformation, verify referential relationships before loading into a database, and run aggregation checks (sum, count, min, max) against expected values after loading. Automated tests at each stage catch integrity failures before they reach production systems.