DuckDB MERGE INTO in Production: Soft Deletes, Batch Processing & Performance Tuning
Difficulty: ⭐⭐⭐ | Estimated Time: 20 minutes to master, forever goodbye to dirty data
1. Why MERGE INTO Is Every Data Engineer’s Swiss Army Knife
In data engineering, incremental sync is the most common operation you’ll face daily. You have a target table with historical data and a source table with today’s changes. Your job: update existing records, insert new ones, and handle deletions gracefully.
The traditional approach — writing Python loops that check existence, then decide UPDATE or INSERT — is verbose, error-prone, and vulnerable to race conditions in concurrent environments.
DuckDB’s MERGE INTO statement handles all of this in a single SQL command, supporting UPDATE, INSERT, and DELETE operations. This article focuses on production-grade patterns: soft deletes, batch processing, and performance tuning.
2. Quick Refresher: The Three Pillars of MERGE INTO
MERGE INTO target_table AS target
USING source_data AS source
ON target.key = source.key -- Match condition
WHEN MATCHED THEN UPDATE SET ... -- Action when matched
WHEN NOT MATCHED THEN INSERT ... -- Action when not matched
2.1 Basic Example: User Data Incremental Sync
-- Create target table (simulating historical user data)
CREATE TABLE users AS
SELECT * FROM (VALUES
(1, 'Zhang San', 28, 'Beijing'),
(2, 'Li Si', 35, 'Shanghai'),
(3, 'Wang Wu', 22, 'Guangzhou'),
(4, 'Zhao Liu', 41, 'Shenzhen')
) AS t(user_id, name, age, city);
-- Create source table (today's updates)
CREATE TABLE new_users AS
SELECT * FROM (VALUES
(2, 'Li Si', 36, 'Shanghai'),
(3, 'Wang Wu', 23, 'Hangzhou'),
(5, 'Sun Qi', 30, 'Chengdu'),
(6, 'Zhou Ba', 27, 'Wuhan')
) AS t(user_id, name, age, city);
-- Execute MERGE INTO
MERGE INTO users AS target
USING new_users AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN UPDATE SET
name = source.name,
age = source.age,
city = source.city
WHEN NOT MATCHED THEN INSERT VALUES
(source.user_id, source.name, source.age, source.city);
-- View results
SELECT * FROM users ORDER BY user_id;
Output:
user_id | name | age | city
--------|-----------|-----|----------
1 | Zhang San | 28 | Beijing
2 | Li Si | 36 | Shanghai ← Age updated 35→36
3 | Wang Wu | 23 | Hangzhou ← City updated Guangzhou→Hangzhou
4 | Zhao Liu | 41 | Shenzhen ← Unchanged
5 | Sun Qi | 30 | Chengdu ← New
6 | Zhou Ba | 27 | Wuhan ← New
3. Advanced Technique #1: Soft Delete Patterns
In production, one frequent requirement is: what do you do when a record disappears from the source data?
Hard deletes risk permanent data loss. A better approach is soft deletion — marking records as deleted rather than removing them physically.
3.1 Approach One: MERGE INTO with a deleted_users Table
-- Create a deleted users tracking table
CREATE TABLE deleted_users AS
SELECT * FROM (VALUES
(4, 'Zhao Liu') -- Zhao Liu marked as resigned today
) AS t(user_id, reason);
-- MERGE INTO handles updates, inserts, and deletions simultaneously
MERGE INTO users AS target
USING new_users AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN UPDATE SET
name = source.name,
age = source.age,
city = source.city
WHEN NOT MATCHED AND source.user_id IS NOT NULL THEN
INSERT VALUES (source.user_id, source.name, source.age, source.city)
WHEN NOT MATCHED AND target.user_id IN (SELECT user_id FROM deleted_users) THEN
UPDATE SET is_deleted = true;
3.2 Approach Two: Add is_deleted Field (Recommended)
A more universal pattern is to reserve is_deleted and deleted_at fields in your schema:
-- Rebuild table with soft-delete fields
DROP TABLE IF EXISTS users;
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
name VARCHAR,
age INTEGER,
city VARCHAR,
is_deleted BOOLEAN DEFAULT false,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Insert initial data
INSERT INTO users VALUES
(1, 'Zhang San', 28, 'Beijing', false, '2026-07-13'),
(2, 'Li Si', 35, 'Shanghai', false, '2026-07-13'),
(3, 'Wang Wu', 22, 'Guangzhou', false, '2026-07-13'),
(4, 'Zhao Liu', 41, 'Shenzhen', false, '2026-07-13');
-- MERGE INTO auto-marks disappearing users
MERGE INTO users AS target
USING new_users AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN UPDATE SET
name = source.name,
age = source.age,
city = source.city,
updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT VALUES
(source.user_id, source.name, source.age, source.city, false, CURRENT_TIMESTAMP)
WHEN NOT MATCHED THEN UPDATE SET
is_deleted = true,
updated_at = CURRENT_TIMESTAMP;
After execution, users not appearing in new_users (Zhang San #1 and Zhao Liu #4) are marked as deleted.
3.3 Querying Active Records Only
-- Only query active users
SELECT * FROM users WHERE is_deleted = false ORDER BY user_id;
-- View deleted users
SELECT * FROM users WHERE is_deleted = true;
4. Advanced Technique #2: Batch Processing for Large Datasets
When source data reaches millions or tens of millions of rows, a single MERGE INTO can cause memory exhaustion or transaction timeouts. Here are three optimization strategies:
4.1 Filter by Time Window
Only sync recently changed data instead of full comparison:
MERGE INTO users AS target
USING (
SELECT * FROM new_users
WHERE updated_at > (SELECT MAX(updated_at) FROM users)
) AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN UPDATE SET
name = source.name, age = source.age, city = source.city,
updated_at = source.updated_at
WHEN NOT MATCHED THEN INSERT VALUES
(source.user_id, source.name, source.age, source.city, false, source.updated_at);
4.2 Batch MERGE
Split large tables into smaller batches processed sequentially:
# Control batch size in Python
batch_size = 50000
for offset in range(0, total_rows, batch_size):
con.execute(f"""
MERGE INTO users AS target
USING (
SELECT * FROM new_users
LIMIT {batch_size} OFFSET {offset}
) AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN UPDATE SET
name = source.name, age = source.age, city = source.city
WHEN NOT MATCHED THEN INSERT VALUES
(source.user_id, source.name, source.age, source.city, false, CURRENT_TIMESTAMP)
""")
print(f"Processed batch at offset {offset}")
4.3 Pre-filter Using Temporary Tables
For complex filtering logic, first narrow down the dataset using a temp table:
-- Step 1: Create filtered temp table
CREATE TEMP TABLE temp_merge_source AS
SELECT * FROM new_users
WHERE updated_at >= DATE('now', '-1 day')
AND status = 'active';
-- Step 2: MERGE against the temp table
MERGE INTO users AS target
USING temp_merge_source AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;
-- Step 3: Clean up
DROP TABLE temp_merge_source;
5. Advanced Technique #3: Performance Tuning
5.1 Disable WAL for Write Performance
DuckDB enables WAL (Write-Ahead Logging) by default. In in-memory database scenarios, disabling it improves write throughput:
import duckdb
con = duckdb.connect(":memory:")
con.execute("PRAGMA wal_off;")
con.execute("""
MERGE INTO users AS target
USING new_users AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN UPDATE SET name = source.name
WHEN NOT MATCHED THEN INSERT VALUES (source.user_id, source.name)
""")
5.2 Adjust Parallelism
-- Set maximum parallel threads
PRAGMA threads=8;
-- Check current configuration
PRAGMA threads;
PRAGMA memory_limit;
5.3 Indexes for Faster Lookups
While DuckDB’s columnar storage excels at scan-heavy workloads, indexes on primary keys significantly accelerate the ON clause lookup in MERGE INTO:
-- Create indexes for both tables
CREATE INDEX idx_users_user_id ON users(user_id);
CREATE INDEX idx_new_users_user_id ON new_users(user_id);
-- Execute MERGE INTO
MERGE INTO users AS target
USING new_users AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;
5.4 Diagnose with EXPLAIN ANALYZE
EXPLAIN ANALYZE
MERGE INTO users AS target
USING new_users AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN UPDATE SET
name = source.name, age = source.age, city = source.city
WHEN NOT MATCHED THEN INSERT VALUES
(source.user_id, source.name, source.age, source.city);
The output shows per-stage timing, helping you identify bottlenecks:
Merge Into
Output: target.user_id, target.name, target.age, target.city
-> Hash Join (Inner, Left Anti)
Hash Cond: target.user_id = source.user_id
-> Table Scan on users
-> Table Scan on new_users
6. Complete Python ETL Example
Here’s a full production-ready Python ETL script integrating all techniques above:
import duckdb
import pandas as pd
from datetime import datetime
class IncrementalETL:
def __init__(self, db_path="data.db"):
self.con = duckdb.connect(db_path)
# Disable WAL for write performance
self.con.execute("PRAGMA wal_off;")
# Set parallelism
self.con.execute("PRAGMA threads=4;")
def init_tables(self):
"""Initialize target and source tables"""
self.con.execute("""
CREATE TABLE IF NOT EXISTS users (
user_id INTEGER PRIMARY KEY,
name VARCHAR,
age INTEGER,
city VARCHAR,
is_deleted BOOLEAN DEFAULT false,
updated_at TIMESTAMP
)
""")
def merge_incremental(self, source_df, batch_size=50000):
"""
Execute incremental MERGE
Args:
source_df: DataFrame with update data
batch_size: Rows per batch
"""
# Register as temp table
self.con.register('new_users', source_df)
# Create indexes for faster lookups
self.con.execute("CREATE INDEX IF NOT EXISTS idx_users_uid ON users(user_id)")
self.con.execute("CREATE INDEX IF NOT EXISTS idx_new_users_uid ON new_users(user_id)")
# Execute MERGE INTO
self.con.execute("""
MERGE INTO users AS target
USING new_users AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN UPDATE SET
name = source.name,
age = source.age,
city = source.city,
is_deleted = false,
updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT VALUES
(source.user_id, source.name, source.age, source.city, false, CURRENT_TIMESTAMP)
WHEN NOT MATCHED THEN UPDATE SET
is_deleted = true,
updated_at = CURRENT_TIMESTAMP
""")
print(f"MERGE completed at {datetime.now()}")
def get_active_users(self):
"""Get list of active users"""
return self.con.execute("""
SELECT * FROM users
WHERE is_deleted = false
ORDER BY updated_at DESC
""").fetchdf()
def close(self):
self.con.close()
# Usage example
if __name__ == "__main__":
etl = IncrementalETL("production.db")
etl.init_tables()
# Simulate daily update data
new_data = pd.DataFrame({
'user_id': [2, 3, 5, 6],
'name': ['Li Si', 'Wang Wu', 'Sun Qi', 'Zhou Ba'],
'age': [36, 23, 30, 27],
'city': ['Shanghai', 'Hangzhou', 'Chengdu', 'Wuhan']
})
etl.merge_incremental(new_data)
# View results
active = etl.get_active_users()
print(active)
etl.close()
7. Comparison with Traditional Tools
| Feature | DuckDB MERGE INTO | Python Loop | PostgreSQL Upsert | Spark MERGE |
|---|---|---|---|---|
| Code Lines | 1 SQL statement | 10-20 lines | 1 SQL statement | Requires DataFrame API |
| Concurrency Safe | ✅ Built-in transactions | ❌ Needs manual locking | ✅ | ✅ |
| Soft Delete Support | ✅ | ⚠️ Manual implementation | ⚠️ Manual implementation | ⚠️ Manual implementation |
| Large Dataset Performance | ⭐⭐⭐⭐ | ⭐⭐ | ⭐⭐⭐ | ⭐⭐⭐⭐ |
| Learning Curve | Low | Medium | Medium | High |
| Deployment Complexity | Zero | Needs Python env | Needs PG service | Needs Spark cluster |
8. Monetization Tips
Mastering production-level MERGE INTO opens several revenue paths:
- Build a data product backend: Use DuckDB + FastAPI to build real-time updated dashboards for small businesses as a SaaS service ($50-300/month per client)
- Automated reporting services: Provide daily auto-synced reporting systems for e-commerce clients, priced by data volume
- Data cleaning outsourcing: Many traditional enterprises have massive manual Excel data needing cleanup and ingestion — MERGE INTO can make your delivery efficiency 10x faster
- Microservice data sync: In Go/Node.js microservice architectures, use DuckDB as a local cache layer for incremental sync, reducing database load
💡 More DuckDB production best practices and ETL case studies → duckdblab.org