Data Consistency

Logical coherence is the fundamental requirement that data does not contain contradictory facts. This means values across different records, tables, or systems must align according to defined business rules and real-world logic.

• Example: A customer's status in a CRM system is 'Active', but their corresponding record in the Billing system is marked 'Closed'. This logical contradiction violates data consistency.
• Enforcement: This is typically governed by integrity constraints in databases (e.g., foreign keys, check constraints) and validated by cross-system business rules.

Referential integrity is a specific, technical form of consistency that enforces valid relationships between tables in a relational database. It ensures that a foreign key value in one table must match an existing primary key value in the related table, or be null.

• Violation Example: An orders table contains an OrderID of 789, but the corresponding customer_id C505 does not exist in the customers table. This is an orphaned record.
• Mechanism: Enforced by the database management system (DBMS) through foreign key constraints, which can cascade updates or deletes or prevent them entirely.

In modern, distributed architectures (microservices, data meshes), data is often replicated or derived across multiple systems. Consistency here refers to the eventual or immediate alignment of this data.

• Eventual Consistency: Systems guarantee that if no new updates are made, all reads will eventually return the last updated value (common in NoSQL databases like Apache Cassandra).
• Strong Consistency: Every read receives the most recent write (required for financial systems).
• Challenge: Maintaining this across systems involves complex change data capture (CDC) and reconciliation processes.

Temporal consistency ensures that time-series data or sequenced events are logically ordered and complete. It validates that timestamps and event sequences make sense across related data points.

• Example: An account_status_changed event logged at 10:05 AM should not exist without a prior account_created event.
• Example: A sensor reading timestamp should always be later than the device's registered deployment_date.
• Monitoring: This is checked by validating sequence gaps, out-of-order timestamps, and anachronistic values in historical data.

Semantic consistency ensures that data values share the same meaning, interpretation, and unit of measurement across different contexts, even if the underlying storage format differs.

• Unit of Measure: A revenue field in one dataset measured in 'Euros' must be consistently labeled and not mixed with 'US Dollars' in another dataset without explicit conversion.
• Code Values: A status code of 1 must mean 'Active' in all tables that reference it, not 'Pending' in some and 'Active' in others.
• Governance: Maintained through a centralized business glossary and data dictionary.

This characteristic validates that aggregated, summarized, or calculated data (e.g., reports, KPIs, machine learning features) is mathematically consistent with its underlying source data.

• Example: The sum of all daily_sales records for January in a granular table must equal the monthly_sales total for January in a summary table.
• Example: A customer_lifetime_value metric calculated in a data warehouse must reconcile with source transaction systems.
• Testing: Enforced through aggregate reconciliation tests and checksum validation as part of data pipeline orchestration.

A single entity is assigned contradictory states in different systems. This is a classic logical violation.

Examples:

• A customer marked as Active in the CRM but Inactive in the billing system.
• A shipment status showing Delivered in the logistics tracker while the inventory system shows it as In Transit.
• An employee listed as Terminated in HR records but still having Active directory access.

This forces downstream processes to guess which source is correct, leading to operational errors.

Summary or roll-up numbers do not match the sum of their constituent detailed records. This breaks the fundamental relationship between transactional and reporting data.

Examples:

• The total revenue on a quarterly report ($1.2M) does not equal the sum of all daily sales transactions for that quarter ($1.15M).
• The total_inventory_count in a warehouse summary table differs from a COUNT(*) query on the detailed stock-keeping unit (SKU) table.
• Aggregate website visitor metrics in a dashboard disagree with the raw event log counts.

Such mismatches invalidate financial reporting, inventory management, and key performance indicators.

A record in a child table references a non-existent parent record, breaking defined database relationships. This creates 'orphaned' data.

Examples:

• An orders table contains a customer_id that does not exist in the customers table.
• A line_items record references a product_sku deleted from the master products catalog.
• A log entry points to a session_id not found in the user sessions table.

This inconsistency prevents reliable joins, corrupts analytics, and can cause application errors when enforcing foreign key constraints.

Core business entities (like customers, products, employees) have different attribute values across systems that are supposed to be synchronized. There is no single source of truth.

Examples:

• A customer's address is 123 Main St in the shipping system but 456 Oak Ave in the marketing database.
• A product's unit_price is $29.99 in the e-commerce catalog but $31.50 in the legacy ERP.
• An employee's department_code differs between the HR platform and the project management tool.

This leads to mis-shipments, pricing errors, and incorrect organizational reporting.

Time-series or event data contains illogical sequences or overlaps that violate business rules about order and duration.

Examples:

• A support ticket has a resolved_at timestamp that is earlier than its created_at timestamp.
• A user's subscription shows an end_date of 2023-12-01 and a subsequent renewal_date of 2023-11-15.
• Sensor readings are logged out of chronological order, creating an impossible physical sequence.

These inconsistencies make it impossible to accurately calculate durations, sequences, or states over time.

Data values are syntactically valid but represent a logically impossible or highly improbable real-world scenario when combined.

Examples:

• A patient's record lists date_of_birth implying an age of 5 years, but their medical_history contains an entry for a hip replacement surgery.
• A financial transaction has currency = USD but an amount formatted with a Euro symbol (€100).
• A server location is recorded as AWS us-east-1 while its associated IP_address geolocates to Tokyo, Japan.

Detecting these requires domain-specific business rules that go beyond basic type or format validation.

Data accuracy measures the degree to which data values correctly represent the real-world entities or events they are intended to describe. It is a measure of correctness against a verified source of truth.

• Example: A customer record where the postal_code field matches the official postal database for the given street_address.
• Contrast with Consistency: A dataset can be internally consistent (e.g., all dates follow the same format) but still be inaccurate (e.g., all dates are wrong).

Data completeness measures the proportion of expected data values that are present and non-null within a dataset, table, or field. It assesses whether all required data is available.

• Key Metric: The null rate for a column.
• Example: An e-commerce orders table where 98% of records have a value in the customer_id field indicates a 98% completeness rate for that attribute.
• Relationship to Consistency: Inconsistent data formats (e.g., mixing NULL, N/A, -) can artificially lower measured completeness if checks are not normalized.

Referential integrity is a specific, rule-based form of data consistency that validates the correctness of relationships between tables in a relational database. It ensures that foreign key values in one table have corresponding primary key values in a related (parent) table.

• Core Database Constraint: Enforced via FOREIGN KEY constraints.
• Violation Example: An orders.customer_id that does not exist in the customers.id column.
• Impact: Breaks in referential integrity cause join operations to fail or return incomplete results, directly impacting downstream reports and models.

Data validity measures the degree to which data values conform to a defined set of syntactic rules, formats, or allowable value ranges (a domain). It checks if data is well-formed according to specification.

• Examples:
  • A phone_number field containing only digits and hyphens.
  • An age field containing only integers between 0 and 120.
  • An email field matching a standard regex pattern.
• Contrast with Consistency: Validity is rule-based within a single field; consistency is relationship-based across fields or datasets. Data can be valid (a correctly formatted date) but inconsistent (a ship_date that precedes the order_date).

Schema consistency ensures that the structure of a dataset—its column names, data types, and constraints—remains stable and predictable over time and across environments. Schema drift is the metric for its violation.

• Critical for Pipelines: Inconsistent schemas cause ETL/ELT jobs to fail.
• Common Checks:
  • Monitoring for new, missing, or renamed columns.
  • Detecting changes in data type (e.g., string to integer).
  • Verifying that primary key or NOT NULL constraints are unchanged.
• Operational Context: A subset of broader data consistency focused exclusively on structural metadata.

Data uniqueness measures the absence of duplicate records or entities within a defined dataset or across a specified set of key fields. The duplicate count is its primary metric.

• Business Impact: Duplicate customer records lead to inaccurate marketing spend, skewed analytics, and poor user experience.
• Detection Methods:
  • Exact Matching: On primary keys like user_id.
  • Fuzzy Matching: On attributes like name and address to find near-duplicates.
• Relationship to Consistency: A lack of uniqueness is a fundamental inconsistency—the same real-world entity is represented by multiple, potentially conflicting records.