Data Integrity

Entity integrity ensures each record in a table is uniquely identifiable and not null. It is enforced at the database level through primary key constraints.

• Primary Key: A column (or combination of columns) that uniquely identifies each row. The DBMS enforces uniqueness and non-nullability.
• Uniqueness Constraint: Prevents duplicate values in a specified column, even if it is not the primary key.
• Example: In a users table, a user_id column defined as PRIMARY KEY guarantees no two users share the same ID and no ID is NULL.

Referential integrity maintains consistent relationships between tables by ensuring foreign key values always reference existing primary keys. It is enforced via foreign key constraints.

• Foreign Key: A column in one table that references the primary key in another (parent) table.
• Actions: Constraints define behavior on UPDATE or DELETE of the parent record (e.g., CASCADE, SET NULL, RESTRICT).
• Example: An orders.customer_id foreign key referencing customers.id. The constraint prevents creating an order for a non-existent customer and can cascade deletions.

Domain integrity ensures that all values in a column adhere to a defined set of permissible values, formats, and ranges. It is enforced through data types and check constraints.

• Data Types: The fundamental constraint (e.g., INTEGER, VARCHAR(255), TIMESTAMP).
• Check Constraints: Custom rules (e.g., age >= 0, status IN ('active', 'inactive', 'pending')).
• Example: A product_price column with a DECIMAL(10,2) type and a CHECK (product_price > 0) constraint.

User-defined integrity encompasses custom business logic and complex validation rules that extend beyond basic database constraints. It is enforced at the application or pipeline layer.

• Business Rules: Logic like "discount cannot exceed 50% of the original price" or "ship date must be after order date."
• Enforcement Points: Application code, stored procedures, or data pipeline validation frameworks (e.g., Great Expectations, dbt tests).
• Example: A validation job that runs daily to ensure all invoice_total values match the sum of their associated line_item amounts.

Physical integrity protects data from physical corruption or loss at the storage level. It ensures data is accurately written to, stored in, and retrieved from disk or memory.

• Mechanisms: Error-correcting code (ECC) memory, checksums, RAID configurations, and atomic write operations.
• Transactional Guarantees: ACID properties (Atomicity, Consistency, Isolation, Durability) provided by database systems ensure physical and logical consistency during failures.
• Example: A database using write-ahead logging (WAL) to guarantee that committed transactions are durable even after a power failure.

Logical integrity ensures data remains consistent and accurate within the context of its business meaning, regardless of how it is accessed or transformed. It maintains semantic correctness.

• Consistency Across Views: Ensuring different reports or derived tables based on the same source data produce congruent results.
• Semantic Constraints: Rules like "an employee's department must exist in the company's current organizational chart."
• Example: A materialized view that aggregates daily sales must always match the sum of the underlying transaction records, enforced through reconciliation checks.

The core axiom of machine learning: a model's output quality is directly determined by its input data quality. Data integrity failures—like incorrect labels, missing values, or schema drift—propagate through the pipeline, causing:

• Model bias and unfair predictions
• Reduced accuracy and poor generalization
• Eroded user trust and potential business risk High-integrity data is the first and most critical line of defense against these outcomes.

Data integrity issues create exponential downstream costs. A single error in a training dataset can be replicated millions of times during model inference. Key impacts include:

• Wasted compute resources training on flawed data
• Engineering time lost debugging mysterious model failures
• Revenue loss from incorrect automated decisions (e.g., fraud detection, dynamic pricing)
• Regulatory fines for non-compliant or biased algorithmic outputs Proactive integrity checks are far cheaper than reactive fixes.

Scientific rigor in AI demands reproducible experiments. Data integrity is essential for this, as it guarantees that the same input data yields the same model behavior across training runs. This involves:

• Immutable data versioning (e.g., using DVC, LakeFS)
• Strict schema enforcement to prevent silent structural changes
• Comprehensive metadata tracking data provenance and transformations Without these controls, model performance claims cannot be reliably verified or replicated.

You cannot monitor what you do not measure. Data integrity establishes the baseline for detecting data drift and concept drift in production. Key practices include:

• Statistical profiling to establish expected value ranges and distributions
• Automated validation of incoming inference data against the training schema
• Lineage tracking to trace prediction errors back to specific data sources This observability layer is impossible if the foundational data's integrity is not assured.

Compromised data integrity is a primary attack vector. Adversarial actors can poison training data or manipulate inference inputs to cause targeted model failures. Robust integrity measures act as a defense:

• Data provenance to verify source authenticity
• Anomaly detection on data streams to identify injection attacks
• Immutable audit logs for all data transformations These controls are critical for AI systems in finance, healthcare, and security-sensitive domains.

Frameworks like the EU AI Act mandate high standards for data quality and governance. Data integrity is the technical implementation of these requirements, enabling:

• Auditability of training datasets for bias assessments
• Explainability (XAI) by ensuring model decisions are based on accurate, traceable facts
• Right to explanation compliance by maintaining clear lineage from input to output Formal data contracts and validation pipelines provide the evidence needed for compliance audits.

The foundational process of verifying that a data structure conforms to a predefined formal specification, or schema. This schema defines the expected format, data types, and structural constraints (e.g., required fields, nested object shapes). It is the first line of defense against malformed data entering a system. Common implementations include JSON Schema, Avro Schema, and XML Schema (XSD).

A formal, versioned agreement between data producers and data consumers that codifies the expectations for a data product. It extends beyond schema to include:

• Semantics and business definitions
• Data quality guarantees (e.g., freshness, completeness SLOs)
• Evolution rules (backward/forward compatibility)
• Service-level objectives for availability This creates a producer-consumer API model for data, shifting quality accountability upstream.

A critical property in relational databases that ensures consistency in relationships between tables. It guarantees that any foreign key value in a child table must reference an existing primary key value in a parent table. This prevents orphaned records and maintains logical links. It is typically enforced by the database management system through constraints, which also cascade updates or deletes.

A formal, executable assertion that defines a specific constraint data must satisfy to be considered fit for use. These are the operational building blocks of integrity checks. Examples include:

• Uniqueness: No duplicate values in a column.
• Range Check: A numeric value must fall between 0 and 100.
• Pattern Matching (Regex): A phone number must match (XXX) XXX-XXXX.
• Completeness Check: A critical field cannot be null. Rules are implemented in data pipelines, ETL frameworks, or dedicated data quality tools.

The practice of managing schema changes over time in data streaming architectures (e.g., Apache Kafka) without breaking downstream consumers. A Schema Registry is a central service that stores and manages schemas (Avro, Protobuf, JSON Schema), enabling:

• Compatibility Checks: Validating new schema versions against old ones (backward/forward compatibility).
• Client Coordination: Ensuring producers and consumers use compatible schemas.
• Change Auditing: Tracking the history of schema modifications.

A rule enforced at the database engine level to limit the data that can be inserted, updated, or deleted in a table. These are declarative guarantees of logical integrity. Core types include:

• PRIMARY KEY: Enforces uniqueness and non-null values for a row identifier.
• FOREIGN KEY: Enforces referential integrity between tables.
• UNIQUE: Ensures all values in a column are different.
• CHECK: Validates that a column value meets a Boolean expression (e.g., age > 0).
• NOT NULL: Prevents null values in a column.