# Uniqueness Constraints

Uniqueness constraints are rules that require a column value or combination of column values to occur no more than once within a defined scope.

Cloud concept. Updated 2026-10-29. Page: https://blowstack.com/tools/cloud-concepts-hub/uniqueness-constraints

> Uniqueness constraints prevent multiple records from representing the same identity when the data model requires a single record. They protect joins, aggregations, and downstream processing from duplicate-driven errors, but their effectiveness depends on choosing the correct business key, comparison rules, and validation scope.

Practice exams covering Uniqueness Constraints: [DEA-C01 AWS Certified Data Engineer - Associate](https://blowstack.com/practice-exams/aws-certified-data-engineer-associate).

## 6 key aspects of Uniqueness Constraints

Reliable uniqueness controls combine correct key selection, explicit scope and comparison semantics, appropriate enforcement, and auditable handling of violations.

1. **Business-aligned keys**: Choose the column or combination of columns that identifies one valid record at the intended granularity. Test an order line using its order identifier and line number rather than assuming the order identifier alone must be unique.
2. **Explicit validation scope**: Specify whether uniqueness applies within a batch, tenant, time window, or complete dataset. Validate new records against previously accepted records when the requirement extends beyond the current batch.
3. **Defined comparison and null semantics**: Document case sensitivity, normalization, and treatment of missing values. Add a completeness rule when unique identifiers must also be present.
4. **Detection versus enforcement**: A quality check reports duplicate keys, while an enforced database constraint prevents conflicting writes. Use atomic write conditions or enforced constraints when concurrent writers must not create duplicate identities.
5. **Measurable quality rules**: Use zero violations for a strict uniqueness requirement and label any permitted duplicate rate as a tolerance. Verify whether the selected metric measures distinct values or records with keys that occur exactly once.
6. **Controlled remediation**: Quarantine conflicting records or select survivors using a documented, deterministic rule. Retain enough evidence to distinguish ingestion retries from genuinely conflicting source records.

## Cloud services for Uniqueness Constraints

DataBrew can profile incoming data and evaluate uniqueness rules, while AWS Glue can incorporate data quality checks into transformation workflows. A pipeline should define whether violations block publication, trigger investigation, or route records to quarantine. Amazon RDS can enforce uniqueness for transactional records, and DynamoDB conditional writes can protect key-based creation against concurrent attempts. Before loading Amazon Redshift, validate keys against both the incoming batch and existing records because its declared unique constraints are not enforced. These controls address different stages: detection in pipelines, prevention at write time, and continued verification in analytical datasets.

- **[AWS Glue DataBrew](https://blowstack.com/tools/cloud-services-hub/aws-glue-databrew)**: DataBrew can evaluate uniqueness rules through data quality rulesets associated with profile jobs. Use the results to identify duplicate-key problems, while recognizing that profiling does not enforce uniqueness on subsequent writes.
- **[AWS Glue](https://blowstack.com/tools/cloud-services-hub/aws-glue)**: AWS Glue Data Quality provides rules such as IsUnique and Uniqueness for evaluating key quality. Integrate evaluations into ETL workflows and configure explicit failure or quarantine handling rather than assuming that a failed rule automatically prevents publication.
- **[Amazon RDS](https://blowstack.com/tools/cloud-services-hub/amazon-rds)**: Supported relational engines enforce UNIQUE constraints and primary keys during writes. Composite constraints support multi-column identities, while null semantics and implementation details depend on the database engine.
- **[Amazon Redshift](https://blowstack.com/tools/cloud-services-hub/amazon-redshift)**: Redshift does not enforce UNIQUE or primary key constraints; these declarations provide information to the query planner. Validate and maintain key integrity in ingestion and transformation workflows before declaring such constraints.
- **[Amazon DynamoDB](https://blowstack.com/tools/cloud-services-hub/amazon-dynamodb)**: A DynamoDB table identifies each item by its complete primary key, but an unconditional write to an existing key can replace the item. Use a conditional write to prevent replacement; uniqueness for non-key attributes requires an explicit design, often involving transactional reservation items.

## Uniqueness Constraints exam questions

Real practice exam questions on Uniqueness Constraints, with the correct answer and its explanation.

### Question 1 (DEA-C01)

**Question:**

A company uses an AWS Glue ETL job to consolidate customer records from multiple Amazon S3 files into a daily snapshot. Each record must contain a non-null tenant_id and customer_id. A customer_id can appear in multiple tenants, and each tenant can have multiple customers. However, the same combination of tenant_id and customer_id must not appear more than once anywhere in the completed snapshot, including across source files. The engineer must validate these requirements without deleting or modifying records. Which AWS Glue Data Quality rule configuration meets these requirements?

**Correct answer:** Require 100% completeness for both columns and 100% uniqueness of the combined tenant_id and customer_id key across the completed snapshot.

**Explanation:** Completeness rules ensure that neither identifier is null. A composite-key uniqueness rule detects repeated tenant-and-customer combinations while allowing each individual identifier to repeat. Evaluating the completed snapshot also detects duplicates that originate in different source files.

## Practice Uniqueness Constraints in a full exam

Questions on Uniqueness Constraints in each practice exam, with every answer option and an explanation for each:

- [DEA-C01](https://blowstack.com/practice-exams/aws-certified-data-engineer-associate): 2 questions

## What is 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.

## Learn Uniqueness Constraints in lessons

2 short lessons on Uniqueness Constraints in 1 certification course.

- DEA-C01: Referential Integrity vs Uniqueness Constraints (Premium), https://app.blowstack.com/lessons/referential-integrity-vs-uniqueness-constraints-dea-c01
- DEA-C01: Uniqueness Constraints: defining key-based quality rules (Premium), https://app.blowstack.com/lessons/uniqueness-constraints-defining-key-based-quality-rules-dea-c01
