Drop Duplicate Rows

Drop Duplicate Rows removes rows that repeat across one or more selected columns. Select a single column, or select several to form a composite key: a row is only treated as a duplicate if it matches another row on every selected column together, not on any one of them alone. You choose one handling rule for the whole operation: keep the first occurrence, the last, or none at all.

All column types are supported as deduplication keys.

When to Use Drop Duplicate Rows

• Enforcing a single-column unique key. Customer IDs, order numbers, or transaction codes should appear only once. Drop duplicates on that column before any aggregation or join.
• Enforcing a composite unique key. A dataset of order line items is often unique on (order_id, product_id) together, not on either column alone. Select both columns as the deduplication key to remove only true duplicate line items, without collapsing an order down to a single row.
• Cleaning merged or appended datasets. When two datasets are combined, the same record can appear twice. Deduplicate on a unique identifier, single or composite, to collapse them to one.
• Keeping the latest record per entity. Sort by a timestamp column first, then deduplicate with Keep Last to retain the most recent entry per customer or product.
• Keeping the earliest record per entity. Deduplicate with Keep First to retain the original registration or first order per customer.
• Removing all ambiguous rows. Use Keep None when you want to discard any row where a duplicate exists, not just remove the extras.

Sample Dataset

The examples in this doc use the Edilitics sample orders dataset. Download it to follow along in your own workspace.

Relevant columns for Drop Duplicates examples:

Single-Column and Composite Keys

Select one column and Edilitics checks that column alone for duplicates. Select more than one column and they form a composite key together: a row is only a duplicate if every selected column matches another row's value for that column.

Example dataset of order line items:

Deduplicating on order_id alone would treat every row with the same order_id as a duplicate and collapse the order down to a single line item, losing real data: P-200 would be dropped from ORD-1.

Deduplicating on the composite key order_id + product_id correctly keeps all three rows: no combination repeats. Only a genuinely repeated (order_id, product_id) pair, such as the same product accidentally added twice to the same order, would be removed.

The Three Handling Rules

The handling rule applies once, to the whole deduplication key (single column or composite), not separately per column.

How to Apply Drop Duplicate Rows

Before and After

Dataset with duplicate customer_id values:

After deduplicating on customer_id with Keep Last (dataset pre-sorted ascending by order_date):

5 rows reduced to 3. Most recent order per customer retained.

Code Equivalent

-- Keep Last: use ROW_NUMBER to rank by date, keep rank = 1 after sorting desc
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
)
SELECT * EXCLUDE rn
FROM ranked
WHERE rn = 1;

-- Composite key (order_id, product_id): PARTITION BY both columns
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY order_date DESC) AS rn
  FROM order_line_items
)
SELECT * EXCLUDE rn
FROM ranked
WHERE rn = 1;

import polars as pl

# Single-column key, Keep Last occurrence per customer_id
df = df.unique(subset=["customer_id"], keep="last")

# Keep First
df = df.unique(subset=["customer_id"], keep="first")

# Keep None (drop all rows where customer_id appears more than once)
df = df.unique(subset=["customer_id"], keep="none")

# Composite key: a row is a duplicate only if BOTH columns match
df = df.unique(subset=["order_id", "product_id"], keep="first")

Operation Reference

Frequently Asked Questions

Next Steps

Need help? Email support@edilitics.com with your workspace, job ID, and context. We reply within one business day.