Introduction

Amazon Redshift lets you declare primary keys and foreign keys, but it does not enforce them the way a transactional database would. That means duplicate rows can still be loaded unless you prevent them in your ETL logic, staging design, or post-load cleanup process.

Why Redshift behaves this way

In Redshift, key definitions are mainly informational hints for the optimizer. They can help query planning, but Redshift does not reject a duplicate row just because you declared a primary key.

That tradeoff favors warehouse-style loading speed over transactional integrity. It works well for analytics, but it means the burden of uniqueness moves to your data pipeline.

Use a staging table and deduplicate before insert

One common pattern is to load incoming data into a staging table first, then deduplicate before inserting into the final table.

For example:

1CREATE TEMP TABLE stage_orders (
2    order_id BIGINT,
3    customer_id BIGINT,
4    updated_at TIMESTAMP
5);

Load raw data into stage_orders, then keep only the winning row per business key:

1INSERT INTO orders
2SELECT order_id, customer_id, updated_at
3FROM (
4    SELECT
5        order_id,
6        customer_id,
7        updated_at,
8        ROW_NUMBER() OVER (
9            PARTITION BY order_id
10            ORDER BY updated_at DESC
11        ) AS rn
12    FROM stage_orders
13) t
14WHERE rn = 1;

This pattern is simple and works well when you can define a clear business key and a "latest row wins" rule.

Upsert-style loading

If the target table may already contain old rows for the same key, use a delete-and-insert or merge-style process instead of blind append loading.

A classic delete-and-insert pattern looks like this:

1DELETE FROM orders
2USING stage_orders
3WHERE orders.order_id = stage_orders.order_id;
4
5INSERT INTO orders
6SELECT order_id, customer_id, updated_at
7FROM stage_orders;

This is not the same as enforcement, but it is a practical way to keep duplicates out of the final table when loads are batch-oriented. It also makes the business rule visible in SQL instead of leaving it implicit in application code.

Validate the data explicitly

Because Redshift will not stop bad data for you, add explicit duplicate checks to the pipeline:

1SELECT order_id, COUNT(*)
2FROM orders
3GROUP BY order_id
4HAVING COUNT(*) > 1;

This kind of query belongs in monitoring, QA, or load validation. In Redshift, data integrity is a process responsibility, not a built-in guarantee.

Push uniqueness upstream when possible

The best duplicate-prevention strategy is often upstream of Redshift:

• deduplicate in the ETL job
• enforce uniqueness in the source system if possible
• generate deterministic load keys
• reject bad batches before warehouse insert

If duplicates enter every day and Redshift is the first place you notice them, the warehouse is already too late in the process. The cleanest warehouse loads are the ones that arrive with deduplication rules already decided.

Common Pitfalls

• Defining primary keys in Redshift and assuming they will block duplicates automatically.
• Appending directly into final tables without a staging or deduplication step.
• Forgetting to define a deterministic rule for which duplicate row should survive.
• Treating Redshift like an OLTP database when it is really an analytical warehouse.

Summary

• Redshift keys are informational and are not enforced for uniqueness.
• Prevent duplicates with staging tables, deduplication queries, and controlled upsert-style loads.
• Add explicit validation queries because Redshift will not reject bad rows for you.
• The strongest duplicate prevention usually happens upstream in ETL or the source system.