DuckDB MERGE INTO & Dynamic Filters: Double Your ETL Performance

In the world of data engineering, ETL (Extract-Transform-Load) is the backbone of every data pipeline. And the most common operation within ETL is incremental synchronization — merging new data into your target tables every day, updating existing records and inserting new ones.

The traditional approach involves running a SELECT query to distinguish new from existing data, then executing separate UPDATE and INSERT statements. This is not only cumbersome but also prone to data inconsistencies in concurrent scenarios.

DuckDB provides two powerful tools to solve these problems:

1. MERGE INTO — One SQL statement for upsert operations
2. Dynamic Filters — Automatic performance optimization for IN (subquery) patterns

This article dives deep into both features and demonstrates how to multiply your ETL efficiency with practical examples.

Part 1: MERGE INTO — One SQL for Incremental Sync

1.1 Basic Usage: Upsert in One Statement

The core idea of MERGE INTO is straightforward: match rows between a source table and a target table using a key, update matched rows, and insert unmatched ones.

MERGE INTO dim_products d
USING staging_products s
ON d.product_id = s.product_id
WHEN MATCHED THEN UPDATE SET
    product_name = s.product_name,
    price = s.price
WHEN NOT MATCHED THEN INSERT VALUES
    (s.product_id, s.product_name, s.price);

Breaking it down:

• dim_products is the target table (your dimension table)
• staging_products is the source table (temporary or external data)
• The ON clause defines the matching condition
• WHEN MATCHED specifies the update logic
• WHEN NOT MATCHED specifies the insert logic

1.2 Advanced: Update Only Changed Rows

In practice, your staging table might contain 1 million rows, but only hundreds have actually changed. Performing unconditional updates wastes resources and generates unnecessary WAL (Write-Ahead Log) overhead.

DuckDB allows you to add additional AND conditions to WHEN MATCHED:

MERGE INTO dim_products d
USING staging_products s
ON d.product_id = s.product_id
WHEN MATCHED AND d.price != s.price THEN UPDATE SET
    product_name = s.product_name,
    price = s.price,
    updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT VALUES
    (s.product_id, s.product_name, s.price, CURRENT_TIMESTAMP);

The key here is AND d.price != s.price — only rows where the price has actually changed get updated. Unchanged rows are skipped entirely, significantly reducing unnecessary write operations.

1.3 Configurable Incremental Sync

Combine MERGE INTO with a configuration table for precise incremental control:

-- Configuration table structure
CREATE TABLE sync_config (
    table_name VARCHAR PRIMARY KEY,
    max_id BIGINT
);

-- Initialize config
INSERT INTO sync_config VALUES ('dim_products', 0);

-- Incremental merge
MERGE INTO dim_products d
USING staging_products s
ON d.product_id = s.product_id
WHEN NOT MATCHED AND s.product_id > (
    SELECT max_id FROM sync_config WHERE table_name = 'dim_products'
) THEN INSERT VALUES
    (s.product_id, s.product_name, s.price, CURRENT_TIMESTAMP);

-- Update config after merge
UPDATE sync_config 
SET max_id = (SELECT MAX(product_id) FROM dim_products)
WHERE table_name = 'dim_products';

1.4 SCD Type 2: Slowly Changing Dimensions

In data warehousing, tracking historical changes is essential. SCD Type 2 is a classic approach: whenever a record changes, keep the old version and create a new one, managing the lifecycle through valid_from and valid_to fields.

-- Target table structure
CREATE TABLE dim_customers (
    customer_id INTEGER,
    name VARCHAR,
    email VARCHAR,
    is_active BOOLEAN,
    valid_from DATE,
    valid_to DATE
);

-- MERGE INTO implements SCD Type 2
MERGE INTO dim_customers dc
USING staging_customers sc
ON dc.customer_id = sc.customer_id
WHEN MATCHED AND dc.email != sc.email THEN UPDATE SET
    is_active = false,
    valid_to = CURRENT_DATE
WHEN NOT MATCHED THEN INSERT 
    (customer_id, name, email, is_active, valid_from, valid_to)
VALUES 
    (sc.customer_id, sc.name, sc.email, true, CURRENT_DATE, NULL);

After execution:

• Old records get valid_to set to today, marking them as expired
• New records carry valid_to = NULL, indicating they’re currently valid
• Query active data with: WHERE valid_to IS NULL

Part 2: Dynamic Filters — DuckDB 1.5.x’s Hidden Powerhouse

MERGE INTO solves “how to merge,” but when dealing with large-scale data, you also need to consider “how to query faster.” This is where DuckDB Dynamic Filters come in.

2.1 What Are Dynamic Filters?

Dynamic Filters is a query optimizer feature in DuckDB that automatically generates filters during execution, pushing them down to the data scanning stage. This dramatically reduces the amount of data that needs to be processed.

The most common application scenario is the IN (subquery) pattern:

SELECT source, COUNT(*), SUM(value)
FROM events
WHERE user_id IN (
    SELECT DISTINCT user_id 
    FROM events 
    WHERE event_date >= date '2024-06-01'
      AND event_date < date '2024-07-01'
)
GROUP BY source;

In traditional database engines, this query might first execute the subquery to get all user_ids, then scan the events table row by row for comparison. If the subquery returns 40,000 records and the events table has 500,000 rows, this full-table-scan-plus-row-by-row-comparison approach is extremely inefficient.

2.2 Automatic Bloom Filter Optimization

DuckDB 1.5.x’s Dynamic Filters automatically compress the subquery results into a Bloom Filter, then use it as a pre-filter during the outer table scan.

A Bloom Filter is an extremely space-efficient probabilistic data structure. For a collection of 40,000 elements, a Bloom Filter might only need a few hundred KB of storage while achieving very high accuracy in determining whether an element exists in the set. The only trade-off is a tiny false-positive rate — it might incorrectly flag non-matching elements as potential matches, but it will never miss an element that actually exists in the set.

By examining the EXPLAIN ANALYZE output, you can see Dynamic Filters in action:

TABLE_SCAN events
  Dynamic Filters:
    optional: user_id >= 'user_0'
    AND optional: user_id <= 'user_999'
    AND optional: user_id IN BF(#0)

The BF(#0) here is the auto-generated Bloom Filter. It compresses 40,000+ user_ids into a bitmap, and the outer scan passes rows through this filter first, eliminating a large number of non-matching rows before further processing.

2.3 Manual Control of Dynamic Filters

Although DuckDB enables Dynamic Filters automatically, you can manually control them via SET commands:

-- Enable caching operators (recommended for DuckDB 1.5.3+)
SET enable_caching_operators = true;

-- Disable dynamic filters (for debugging)
SET dynamic_filters = false;

-- View current settings
SHOW ALL LIKE '%filter%';

enable_caching_operators is an important performance switch that allows DuckDB to cache intermediate computation results (like subquery results), avoiding redundant calculations. In most ETL scenarios, it’s recommended to keep this enabled.

Part 3: Complete ETL Pipeline in Practice

Let’s combine MERGE INTO and Dynamic Filters to build a complete ETL pipeline.

3.1 Scenario Description

Imagine you operate an e-commerce data platform that collects order data from multiple sources daily and aggregates it into a summary table.

3.2 Data Setup

import duckdb

con = duckdb.connect("etl_pipeline.duckdb")

# Create the target aggregation table
con.execute("""
CREATE TABLE IF NOT EXISTS daily_orders (
    order_id VARCHAR PRIMARY KEY,
    customer_id VARCHAR,
    product_name VARCHAR,
    amount DECIMAL(10, 2),
    order_date DATE,
    status VARCHAR,
    last_updated TIMESTAMP
)
""")

# Create daily incremental data (simulating external API fetch)
con.execute("""
CREATE TABLE IF NOT EXISTS daily_staging AS
SELECT 
    'ORD-' || i AS order_id,
    'CUST-' || (i % 1000) AS customer_id,
    'Product-' || (i % 200) AS product_name,
    ROUND(UNIFORM(10, 500)::DECIMAL(10, 2), 2) AS amount,
    CURRENT_DATE - INTERVAL (i % 30) DAY AS order_date,
    CASE 
        WHEN i % 5 = 0 THEN 'cancelled'
        WHEN i % 3 = 0 THEN 'refunded'
        ELSE 'completed'
    END AS status,
    CURRENT_TIMESTAMP AS last_updated
FROM GENERATE_SERIES(1, 10000) AS t(i)
""")

3.3 Execute MERGE INTO

-- Incremental merge: update existing orders, insert new ones
MERGE INTO daily_orders d
USING daily_staging s
ON d.order_id = s.order_id
WHEN MATCHED AND d.amount != s.amount OR d.status != s.status THEN UPDATE SET
    customer_id = s.customer_id,
    product_name = s.product_name,
    amount = s.amount,
    status = s.status,
    last_updated = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT VALUES
    (s.order_id, s.customer_id, s.product_name, s.amount, 
     s.order_date, s.status, s.last_updated);

3.4 Leverage Dynamic Filters for Faster Queries

-- Query today's completed order totals (Dynamic Filter auto-optimizes)
SELECT product_name, SUM(amount) as total_amount
FROM daily_orders
WHERE order_date = CURRENT_DATE
  AND status = 'completed'
  AND customer_id IN (
      SELECT DISTINCT customer_id 
      FROM daily_orders 
      WHERE order_date = CURRENT_DATE
  )
GROUP BY product_name
ORDER BY total_amount DESC;

In this query, DuckDB automatically generates a Bloom Filter for customer_id IN (subquery), significantly boosting query performance.

3.5 Clean Up Temporary Data

-- Clear staging table for next batch
TRUNCATE TABLE daily_staging;

-- Or export data as Parquet
COPY daily_orders TO 'output/daily_orders.parquet' (COMPRESSION ZSTD);

Part 4: Performance Comparison with Traditional Approaches

DuckDB’s advantages:

• Simplicity: MERGE INTO replaces multi-step operations with one statement
• Automation: Dynamic Filters require zero manual configuration
• Performance: Columnar storage + vectorized execution, million-row data in seconds
• Zero Operations: No cluster deployment needed, called directly from Python

Part 5: Monetization Strategies

With mastery of DuckDB’s MERGE INTO and Dynamic Filters, you can create business value in several directions:

5.1 ETL Pipeline as a Service

Build customized ETL pipelines for small and medium businesses. Many companies still manually organize Excel reports daily — time-consuming and error-prone. With DuckDB, you can automate the entire process in minutes, charging $500-2000 per project.

5.2 Real-Time Data Monitoring

Leverage MERGE INTO’s incremental update capability to build real-time data monitoring dashboards. Trigger alerts automatically when customer data changes — ideal for e-commerce and finance industries.

5.3 Data Warehouse Migration Consulting

Help traditional companies migrate from MySQL/PostgreSQL to DuckDB as their analytics engine. MERGE INTO and Dynamic Filters significantly simplify their ETL logic, reducing maintenance costs.

5.4 Automated Report SaaS

Embed DuckDB into reporting tools, supporting incremental updates and automatic filtering. Price at $9.99-49.99/month, targeting SME automation needs.

Summary

DuckDB’s MERGE INTO and Dynamic Filters are two often-underestimated features:

1. MERGE INTO makes incremental sync simple and elegant — one SQL statement replaces multi-step operations, supports conditional updates and SCD Type 2
2. Dynamic Filters automatically optimize IN (subquery) performance through Bloom Filters — no manual configuration required, works out of the box
3. Combined together they form a highly efficient ETL pipeline capable of handling million-row incremental updates and complex queries

Mastering these skills not only boosts your data processing efficiency but also translates into tangible commercial value.

💡 More DuckDB tips and tutorials → duckdblab.org