Polars Series

Advanced Deduplication Strategies in DuckDB & Polars Interoperability Patterns

December 5, 2025 8 min read

Master high-performance deduplication in DuckDB using QUALIFY, ARG_MAX, and sliding windows. Learn zero-copy interoperability patterns between DuckDB SQL and Polars DataFrames.

Advanced Deduplication Strategies in DuckDB & Polars Interoperability Patterns

Open In Colab   View on GitHub   Download Notebook

In modern Python data engineering, Polars and DuckDB form the ultimate in-process data stack. While Polars excels at expressive, multi-threaded DataFrame transformations, DuckDB is an in-process vectorized SQL OLAP engine optimized for complex analytical SQL queries, direct Parquet scanning, and automatic memory spillover.

When it comes to deduplication, DuckDB introduces elegant SQL idioms—such as the iconic QUALIFY clause and ARG_MAX aggregations—that eliminate nested CTE boilerplate and out-of-memory errors.

In this post, we’ll master advanced deduplication strategies in DuckDB SQL and explore zero-copy interoperability patterns between DuckDB and Polars to build blazingly fast data pipelines.

1. The Power of DuckDB & Polars Synergy

Why combine Polars and DuckDB for deduplication?

  1. Zero-Copy Arrow Integration: DuckDB and Polars share memory seamlessly via Apache Arrow PyCapsule interface. You can execute DuckDB SQL queries directly on Polars DataFrames without copying data in RAM.
  2. Boilerplate-Free SQL (QUALIFY): Standard SQL requires verbose subqueries or CTEs with ROW_NUMBER(). DuckDB’s QUALIFY clause filters window functions directly in a single SELECT statement.
  3. Out-of-Core Spill to Disk: If your dataset exceeds system RAM, DuckDB automatically spills intermediate sort and join buffers to disk, preventing out-of-memory crashes during massive deduplication runs.

2. Core DuckDB SQL Deduplication Idioms

Let’s set up a sample dataset in Polars and query it directly using DuckDB SQL:

import duckdb
import polars as pl

# Sample event dataset in Polars
df_pl = pl.DataFrame({
    "user_id": [101, 102, 101, 103, 102, 101, 104],
    "timestamp": [100, 105, 102, 101, 108, 99, 103],
    "status": ["click", "view", "purchase", "view", "click", "view", "purchase"],
    "amount": [10.0, None, 50.0, 5.0, 15.0, 10.0, 100.0]
})

print(df_pl)

shape: (7, 4)
┌─────────┬───────────┬──────────┬────────┐
│ user_id ┆ timestamp ┆ status   ┆ amount │
│ ---     ┆ ---       ┆ ---      ┆ ---    │
│ i64     ┆ i64       ┆ str      ┆ f64    │
╞═════════╪═══════════╪══════════╪════════╡
│ 101     ┆ 100       ┆ click    ┆ 10.0   │
│ 102     ┆ 105       ┆ view     ┆ null   │
│ 101     ┆ 102       ┆ purchase ┆ 50.0   │
│ 103     ┆ 101       ┆ view     ┆ 5.0    │
│ 102     ┆ 108       ┆ click    ┆ 15.0   │
│ 101     ┆ 99        ┆ view     ┆ 10.0   │
│ 104     ┆ 103       ┆ purchase ┆ 100.0  │
└─────────┴───────────┴──────────┴────────┘

Idiom 1: QUALIFY ROW_NUMBER() (The Cleanest Window Deduplication)

In traditional SQL (PostgreSQL, MySQL, Snowflake), extracting the latest record per group requires a nested Subquery or Common Table Expression (CTE):

-- Traditional Verbose SQL (Avoid in DuckDB!)
WITH RankedEvents AS (
    SELECT user_id, timestamp, status, amount,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp DESC) as rn
    FROM df_pl
)
SELECT user_id, timestamp, status, amount
FROM RankedEvents
WHERE rn = 1;

DuckDB simplifies this dramatically using the QUALIFY clause:

# DuckDB SQL: QUALIFY filters window results directly!
deduped_rel = duckdb.sql("""
    SELECT user_id, timestamp, status, amount
    FROM df_pl
    QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp DESC) = 1
    ORDER BY user_id
""")

# Convert DuckDB Relation back to Polars DataFrame instantly (zero-copy)
df_latest = deduped_rel.pl()
print(df_latest)

Output:

shape: (4, 4)
┌─────────┬───────────┬──────────┬────────┐
│ user_id ┆ timestamp ┆ status   ┆ amount │
│ ---     ┆ ---       ┆ ---      ┆ ---    │
│ i64     ┆ i64       ┆ str      ┆ f64    │
╞═════════╪═══════════╪══════════╪════════╡
│ 101     ┆ 102       ┆ purchase ┆ 50.0   │
│ 102     ┆ 108       ┆ click    ┆ 15.0   │
│ 103     ┆ 101       ┆ view     ┆ 5.0    │
│ 104     ┆ 103       ┆ purchase ┆ 100.0  │
└─────────┴───────────┴──────────┴────────┘

Idiom 2: ARG_MAX() Aggregation (Zero Window-Sort Overhead)

While QUALIFY ROW_NUMBER() is expressive, window functions still compute row ranks. If you simply want to extract attributes corresponding to the maximum timestamp per partition, DuckDB provides ARG_MAX(val, arg):

ARG_MAX(status, timestamp) returns the status value at the maximum timestamp.

# Ultra-fast aggregation deduplication without window sorting overhead
argmax_res = duckdb.sql("""
    SELECT 
        user_id,
        MAX(timestamp) AS latest_timestamp,
        ARG_MAX(status, timestamp) AS latest_status,
        ARG_MAX(amount, timestamp) AS latest_amount
    FROM df_pl
    GROUP BY user_id
    ORDER BY user_id
""").pl()

print(argmax_res)

Performance Advantage: ARG_MAX operates in a single parallel hash aggregation pass ($O(N)$), making it significantly faster than window sorting ($O(N \log N)$) when deduplicating wide tables with millions of groups!

3. Advanced Strategy 1: Conflict Resolution & Custom Coalesce

When duplicate records contain contradictory non-key values (e.g., missing phone numbers or updated emails), standard ROW_NUMBER() arbitrarily picks one row and discards non-null data from others.

In DuckDB, you can resolve non-key conflicts cleanly using FIRST(... ORDER BY ...) or LIST(DISTINCT ...):

user_updates_pl = pl.DataFrame({
    "user_id": [1, 1, 1, 2, 2],
    "email": ["[email protected]", None, "[email protected]", "[email protected]", None],
    "phone": [None, "+1-555-0199", None, None, "+1-555-0200"]
})

# Prioritize non-null email and phone numbers across duplicates
conflict_resolved = duckdb.sql("""
    SELECT 
        user_id,
        FIRST(email ORDER BY email IS NULL, email) AS email,
        FIRST(phone ORDER BY phone IS NULL, phone) AS phone
    FROM user_updates_pl
    GROUP BY user_id
    ORDER BY user_id
""").pl()

print(conflict_resolved)

Output:

shape: (2, 3)
┌─────────┬───────────────┬─────────────┐
│ user_id ┆ email         ┆ phone       │
│ ---     ┆ ---           ┆ ---         │
│ i64     ┆ str           ┆ str         │
╞═════════╪═══════════════╪═════════════╡
│ 1       ┆ [email protected] ┆ +1-555-0199 │
│ 2       ┆ [email protected]  ┆ +1-555-0200 │
└─────────┴───────────────┴─────────────┘

4. Advanced Strategy 2: Sliding Window Debouncing

Duplicate events in streaming or clickstream datasets often occur within a short time threshold (e.g. user double-clicking a submit button within 5 seconds).

Using DuckDB’s LAG() window function, we can compute inter-event intervals and filter out rapid duplicate events:

events_pl = pl.DataFrame({
    "user_id": [1, 1, 1, 2, 2],
    "timestamp": [100, 102, 120, 200, 204], # 100 & 102 are within 2s of each other
    "event": ["click", "click", "click", "buy", "buy"]
})

debounced_events = duckdb.sql("""
    WITH ranked AS (
        SELECT *,
               LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) AS prev_ts
        FROM events_pl
    )
    SELECT user_id, timestamp, event
    FROM ranked
    WHERE prev_ts IS NULL OR (timestamp - prev_ts) > 5
    ORDER BY user_id, timestamp
""").pl()

print(debounced_events)

Output:

shape: (3, 3)
┌─────────┬───────────┬───────┐
│ user_id ┆ timestamp ┆ event │
│ ---     ┆ ---       ┆ ---   │
│ i64     ┆ i64       ┆ str   │
╞═════════╪═══════════╪═══════╡
│ 1       ┆ 100       ┆ click │
│ 1       ┆ 120       ┆ click │
│ 2       ┆ 200       ┆ buy   │
└─────────┴───────────┴───────┘

5. Polars + DuckDB Interoperability Patterns

The true power of this stack lies in combining Polars and DuckDB seamlessly in a single pipeline without memory copying.

Pattern 1: Direct SQL Execution on Polars DataFrames & LazyFrames

DuckDB’s Python API automatically inspects local Python variables. You can reference any Polars DataFrame or LazyFrame directly inside DuckDB SQL queries by variable name!

import polars as pl
import duckdb

df_raw = pl.DataFrame({"id": [1, 1, 2], "val": [10, 20, 30]})

# DuckDB queries df_raw directly via Arrow zero-copy registration
result_df = duckdb.sql("SELECT id, MAX(val) FROM df_raw GROUP BY id").pl()

Pattern 2: Hybrid Pipeline (DuckDB Parquet Ingestion -> Polars Feature Engineering)

DuckDB excels at scanning raw multi-gigabyte Parquet or CSV files directly from disk (or S3) and executing deduplication before handing off clean Arrow streams to Polars:

import duckdb
import polars as pl

# 1. Use DuckDB to scan & deduplicate multi-GB raw Parquet files with QUALIFY
query = """
    SELECT 
        user_id,
        timestamp,
        event_name,
        payload
    FROM read_parquet('s3://my-bucket/events/*/*.parquet')
    QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id, event_id ORDER BY timestamp DESC) = 1
"""

# 2. Export zero-copy directly to a Polars DataFrame
df_clean = duckdb.sql(query).pl()

# 3. Perform fast expression-based feature engineering in Polars
df_features = df_clean.with_columns(
    is_recent = pl.col("timestamp") > 1700000000,
    event_category = pl.col("event_name").str.to_uppercase()
)

6. Out-of-Core & Memory-Spill Deduplication

When deduplicating multi-gigabyte datasets that exceed available RAM, in-memory DataFrames will throw Out-Of-Memory (OOM) exceptions.

DuckDB automatically spill intermediate sort and hash buffers to a temporary disk directory when memory limits are reached:

import duckdb

# Configure DuckDB memory threshold and temporary spill directory
con = duckdb.connect(database=":memory:")
con.execute("SET max_memory = '4GB';")
con.execute("SET temp_directory = './duckdb_temp_spill';")

# Execute massive deduplication query: DuckDB streams from disk to disk
con.sql("""
    COPY (
        SELECT *
        FROM read_parquet('huge_dataset.parquet')
        QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1
    ) TO 'deduplicated_dataset.parquet' (FORMAT PARQUET, COMPRESSION 'SNAPPY');
""")

7. Deduplication Cheat Sheet: Pandas vs. Polars vs. DuckDB

Deduplication StrategyPandasPolarsDuckDB SQL
Exact Duplicate Rowsdf.drop_duplicates()df.unique()SELECT DISTINCT * FROM df
Subset Deduplicationdf.drop_duplicates(subset=['id'])df.unique(subset=['id'])SELECT * FROM df QUALIFY ROW_NUMBER() OVER (PARTITION BY id) = 1
Keep Latest by Timestampdf.sort_values('ts').drop_duplicates('id', keep='last')df.filter(pl.col('ts') == pl.col('ts').max().over('id'))SELECT * FROM df QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY ts DESC) = 1
Value at Max Timestampdf.groupby('id').apply(...)df.group_by('id').agg(pl.col('val').sort_by('ts').last())SELECT id, ARG_MAX(val, ts) FROM df GROUP BY id
Filter Pristine Unique Onlydf.drop_duplicates(subset=['id'], keep=False)df.filter(pl.col('id').is_unique())SELECT * FROM df QUALIFY COUNT(*) OVER (PARTITION BY id) = 1
Time Window DebouncingCustom loop / shiftdf.filter(pl.col('ts').diff().over('id') > 5)WITH t AS (SELECT *, LAG(ts) OVER (PARTITION BY id ORDER BY ts) AS p FROM df) SELECT * FROM t WHERE ts - p > 5
Out-of-Core Execution❌ (OOM risk)✅ (LazyFrame.sink_parquet())✅ (SET max_memory='4GB')

Summary

By combining DuckDB and Polars, you get the best of both worlds:

  1. DuckDB QUALIFY and ARG_MAX eliminate verbose CTE subqueries and window-sort overhead.
  2. Zero-Copy Arrow pycapsule interface allows you to pass DataFrames seamlessly between DuckDB SQL and Polars expressions without memory overhead.
  3. DuckDB memory spilling guarantees robust out-of-core deduplication on datasets larger than RAM.

Happy Data Engineering! Explore more in our Polars Track Series.

Table of Contents
Polars Series
01 From Pandas to Polars: A Paradigm Shift in DataFrame Processing 02 Mastering Polars Data Types and Missing Values 03 Advanced Polars Expressions: String Operations & Complex Data Types 04 Advanced Deduplication Strategies in Polars: Beyond Sort and Drop 05 Advanced Deduplication Strategies in DuckDB & Polars Interoperability Patterns