Uniqueness Constraints

A uniqueness constraint states that no two records may share the same value for a specified key within a defined dataset or scope. The key can be one column, such as an event identifier, or a combination of columns, such as tenant_id and customer_id. With a composite key, the combination must be unique; each individual column can legitimately contain repeated values. The rule must reflect the business model rather than merely target columns that happen to contain few duplicates.

For example, an order-line table can contain many rows with the same order_id because an order contains multiple items. Requiring order_id alone to be unique would reject valid records. A constraint on order_id and line_number is more appropriate. Similarly, a customer identifier might be unique within one tenant but not across all tenants. Historical tables may intentionally contain multiple versions of an entity, requiring a version identifier or effective timestamp in the uniqueness definition.

In cloud data pipelines, engineers commonly validate uniqueness after ingestion and before publishing curated data. A data quality ruleset can test whether an identifier is unique and report violations. AWS Glue DataBrew supports uniqueness validation through data quality rulesets evaluated by profile jobs. AWS Glue Data Quality also supports uniqueness checks. These checks detect problems; they do not inherently provide the same protection as a database constraint that rejects a conflicting write.

A practical duplicate check groups records by the intended key and identifies groups whose count exceeds one. For a composite key, group by every component rather than checking each column independently. Define null handling separately: a unique constraint is not automatically a requirement that values be present, and database engines differ in how they handle nulls. If an identifier is mandatory, apply both completeness and uniqueness rules. Also specify whether comparisons are case-sensitive and whether whitespace or other formatting differences should be normalized.

Validation scope is critical. A daily batch can contain no duplicates internally yet repeat identifiers already loaded yesterday. Global uniqueness requires comparison against existing data or an authoritative key registry. Streaming deduplication has a similar limitation: a bounded state-retention period can miss repeats that arrive after that period. Sampling can reveal duplicates but cannot prove that the entire dataset is unique.

Strict uniqueness normally requires zero violations. A quality threshold that permits a small duplicate rate may be useful for monitoring imperfect source data, but it is a tolerance policy rather than strict enforcement. Check the metric definition: counting distinct values is different from counting rows whose key occurs exactly once, and null handling can affect both measurements.

Enforcement introduces trade-offs. Distributed duplicate detection can require expensive shuffles and comparisons across partitions. Database uniqueness enforcement adds index maintenance and coordination costs, but protects concurrent writes that could defeat an application-level check followed by insertion. A declared key is not always enforced: Amazon Redshift treats unique and primary key constraints as informational. Finally, removing duplicates is remediation, not validation. Define a deterministic survivor policy, preserve rejected records for investigation, and avoid silently discarding conflicting business facts.