Referential Integrity

Referential integrity is the requirement that a reference from one record to another resolves to a valid target. In a typical relational design, a child table contains a foreign key that references a primary key or another eligible unique key in a parent table. For example, each non-null customer_id in an orders table must match a customer_id in the customers table. An order that references a nonexistent customer is an orphan record.

Define the relationship before implementing a quality rule. Identify the child dataset, parent dataset, referencing columns, target key, and whether a missing reference is permitted. For composite keys, validate the complete combination: checking tenant_id and customer_id independently does not prove that the customer exists within that tenant. Validate parent-key uniqueness separately, because duplicate parent keys can multiply rows during joins and inflate analytical results.

In a relational database that supports enforcement, create foreign-key constraints and choose appropriate update and delete behavior. A restrictive delete policy prevents removal of a customer who still has orders. Cascading deletion removes dependent records automatically, which may be correct for temporary detail records but dangerous for financial history. Making a foreign-key column nullable permits an absent relationship under the applicable database rules; add a NOT NULL constraint when the relationship is mandatory.

In cloud data lakes, files and catalog schemas generally do not enforce relationships between datasets. Implement validation in ingestion or transformation jobs instead. A practical check uses a left anti join or a NOT EXISTS query to find orders whose customer identifiers have no match in the customer dataset. Handle nullable foreign keys explicitly, and avoid assuming that NOT IN behaves like NOT EXISTS when nulls are present. Check both the number of unmatched records and the proportion of eligible child records that match.

Define executable data quality rules with a clear scope and failure action. For example, require every non-null order customer_id to exist in the approved customer snapshot, and separately require customer_id to be populated if every order must have a customer. AWS Glue Data Quality provides a ReferentialIntegrity rule for reference-dataset checks. In AWS Glue DataBrew, preparation joins can expose unmatched references, after which rules can validate the resulting match indicators or required fields. Profiling a single column for completeness or distinct values alone does not establish cross-dataset integrity.

Investigate failures before changing data. Compare failed identifiers with source records, ingestion timestamps, transformation outputs, and parent deletion events. Common causes include late-arriving parent records, failed ingestion batches, inconsistent identifier types, lost leading zeros, whitespace differences, and incorrect join keys. Validate corresponding snapshots or processing watermarks so that different extraction times do not create misleading failures. Preserve failed rows and batch metadata for diagnosis, then repair and revalidate them.

Enforcement and detection have different trade-offs. Database constraints can reject invalid writes immediately but add checking overhead and constrain load order. Batch checks scale across distributed datasets but consume compute and leave a period during which invalid relationships may exist. A tolerance threshold can support operational monitoring, but allowing unmatched records is not strict referential integrity. For mandatory relationships, block publication or quarantine failures; for expected late arrivals, use controlled retries or explicitly modeled placeholder records rather than silently discarding data.