Introduction: The ETL Problem You’ve Been Solving Wrong

Have you ever encountered this scenario:

• Every day you receive a new CSV file of user data from an upstream system
• You need to use Python to first query existing data, then compare differences, then execute INSERT / UPDATE / DELETE separately
• The code starts at 50 lines, and you constantly miss edge cases (new users added, existing users deleted, partial field updates)
• When the data gets large, pandas runs out of memory

This is the most common “Upsert” need in ETL pipelines.

The traditional approach requires three-way table joins, complex logic, and is prone to errors. DuckDB handles all of this with a single MERGE INTO statement — insertions, updates, and deletions in one atomic operation.

In this article, I’ll walk you through everything from basics to advanced techniques for DuckDB’s MERGE INTO syntax, and at the end, I’ll show you how to turn this capability into a sellable data product.

Core Principles of DuckDB MERGE INTO

MERGE INTO is a SQL standard statement used to implement Upsert (Update + Insert) operations. DuckDB fully supports this syntax with targeted optimizations:

• Atomicity: The entire MERGE is a transaction — either all succeed or all roll back
• Vectorized execution: DuckDB’s columnar storage makes the matching logic orders of magnitude faster than traditional relational databases
• Zero dependencies: No additional extensions or plugins needed, works out of the box

Syntax Structure

MERGE INTO target_table AS target
USING source_table AS source
ON target.key_column = source.key_column
WHEN MATCHED THEN
    UPDATE SET col1 = source.col1, col2 = source.col2
WHEN NOT MATCHED THEN
    INSERT (col1, col2) VALUES (source.col1, source.col2);

Key components:

• MERGE INTO specifies the target table
• USING specifies the source data (can be a table, subquery, or CTE result)
• ON is the matching key condition
• WHEN MATCHED triggers UPDATE when rows match
• WHEN NOT MATCHED triggers INSERT when no match is found

Practical Example 1: Basic UPSERT — Insert New, Update Existing

Suppose you receive a daily user snapshot table daily_users and need to update the users table based on the primary key user_id:

import duckdb

con = duckdb.connect("ecommerce.db")

# Create the target table (initial state)
con.execute("""
CREATE TABLE IF NOT EXISTS users AS SELECT * FROM (VALUES
    (1, 'Alice',   '[email protected]',   '2024-01-01'),
    (2, 'Bob',     '[email protected]',     '2024-01-01'),
    (3, 'Carol',   '[email protected]',   '2024-01-01')
) t(user_id, name, email, updated_at)
""")

# Simulate today's new data
con.execute("""
CREATE TABLE IF NOT EXISTS daily_users AS SELECT * FROM (VALUES
    (2, 'Bob_Jr',    '[email protected]',  '2024-09-24'),
    (4, 'Dave',      '[email protected]',     '2024-09-24'),
    (1, 'Alice_v2',  '[email protected]', '2024-09-24')
) t(user_id, name, email, updated_at)
""")

# One MERGE INTO handles everything
con.execute("""
MERGE INTO users AS target
USING daily_users AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN
    UPDATE SET
        name       = source.name,
        email      = source.email,
        updated_at = source.updated_at
WHEN NOT MATCHED THEN
    INSERT (user_id, name, email, updated_at)
    VALUES (source.user_id, source.name, source.email, source.updated_at)
""")

# View the result
result = con.execute("SELECT * FROM users ORDER BY user_id").fetchdf()
print(result.to_string(index=False))

Result interpretation:

• user_id=2 (Bob → Bob_Jr) and user_id=1 (email changed) were updated
• user_id=4 (Dave) was inserted
• The original user_id=3 (Carol) remains unchanged

💡 Key Insight: MERGE INTO is an atomic operation. The entire process won’t lose data or create race conditions. In distributed systems, this is why it’s safer than manually writing INSERT + UPDATE + DELETE.

Practical Example 2: Conditional Updates with Filters

Not all fields should be overwritten unconditionally. For example, only update when the “name” has actually changed:

MERGE INTO users AS target
USING daily_users AS source
ON target.user_id = source.user_id
WHEN MATCHED AND target.name <> source.name THEN
    UPDATE SET
        name       = source.name,
        updated_at = source.updated_at
WHEN NOT MATCHED THEN
    INSERT (user_id, name, email, updated_at)
    VALUES (source.user_id, source.name, source.email, source.updated_at);

AND target.name <> source.name is a conditional filter — only rows that satisfy the condition trigger an UPDATE, avoiding unnecessary writes. This is critical when dealing with large datasets, as it reduces I/O and lock contention.

Practical Example 3: Full Sync with Soft Delete

In real business scenarios, upstream data may have deleted certain users. Use the DELETE clause to implement reverse deletion:

-- Add a soft delete flag to the users table
con.execute("ALTER TABLE users ADD COLUMN IF NOT EXISTS is_deleted BOOLEAN DEFAULT FALSE")

MERGE INTO users AS target
USING daily_users AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN
    UPDATE SET
        name       = source.name,
        email      = source.email,
        updated_at = source.updated_at
WHEN NOT MATCHED THEN
    INSERT (user_id, name, email, updated_at)
    VALUES (source.user_id, source.name, source.email, source.updated_at)
WHEN NOT MATCHED BY SOURCE AND target.is_deleted = FALSE THEN
    UPDATE SET is_deleted = TRUE, updated_at = DATE '2024-09-24';

Logic breakdown:

• MATCHED → exists, so update
• NOT MATCHED (in source but not in target) → insert
• NOT MATCHED BY SOURCE (in target but not in source) → mark as soft-deleted

💡 Soft Delete vs Physical Delete: Soft delete is safer than physical delete — the data is still there, just marked as invalid, and can be restored at any time. This is crucial in commercial scenarios where you may need to reconstruct historical data for clients.

Practical Example 4: ON CONFLICT Shortcut — Pure Upsert Scenarios

If you’re more familiar with PostgreSQL syntax, DuckDB also supports an alternative写法:

INSERT INTO users (user_id, name, email, updated_at)
SELECT user_id, name, email, updated_at
FROM daily_users
ON CONFLICT (user_id) DO UPDATE SET
    name       = excluded.name,
    email      = excluded.email,
    updated_at = excluded.updated_at;

excluded is a built-in pseudotable in DuckDB representing the rows that were rejected during insertion. This concise syntax is ideal for scenarios that only need insert and update, no deletion.

Smart Updates: COALESCE to Prevent Dirty Data Overwrites

When both old and new data have values, deciding which source to use is critical. Use COALESCE for smart updates — only overwrite when the source data has a value:

MERGE INTO users AS target
USING daily_users AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN
    UPDATE SET
        name       = COALESCE(source.name, target.name),
        email      = COALESCE(source.email, target.email),
        updated_at = source.updated_at
WHEN NOT MATCHED THEN
    INSERT (user_id, name, email, updated_at)
    VALUES (source.user_id, source.name, source.email, source.updated_at);

This way, even if upstream data has some empty fields, you won’t overwrite valid data in the target table. This is a very practical technique in production environments.

Performance Comparison: MERGE INTO vs Traditional Python

Core advantages summarized:

1. Atomicity — MERGE is a transaction, all or nothing
2. Conciseness — One statement replaces multiple operations
3. Performance — DuckDB optimizes matching logic internally, far faster than manual JOINs
4. Zero memory bloat — Columnar storage + streaming processing handles millions of rows easily

Common Pitfalls and How to Avoid Them

Pitfall 1: Forgetting Indexes on ON Conditions

If user_id has no index, MERGE will do a nested loop match against the entire table, which is extremely slow with large datasets. Create a primary key or index:

ALTER TABLE users ADD PRIMARY KEY (user_id);
-- Or
CREATE INDEX idx_users_user_id ON users(user_id);

Pitfall 2: The NOT MATCHED BY SOURCE Trap

NOT MATCHED BY SOURCE matches ALL rows in the target table that don’t exist in the source. If you only want to delete rows with a “soft delete flag = false”, remember to add a filter condition, or you’ll accidentally delete historical archived data:

-- ❌ Dangerous: deletes all rows not in the source
WHEN NOT MATCHED BY SOURCE THEN DELETE

-- ✅ Safe: only soft-deletes unmarked rows
WHEN NOT MATCHED BY SOURCE AND target.is_deleted = FALSE THEN
    UPDATE SET is_deleted = TRUE, updated_at = CURRENT_TIMESTAMP

Pitfall 3: Choosing the Right Source for Updates

When both old and new data have values, you need to decide based on your business scenario:

-- Strategy A: New data takes priority (for incremental sync)
UPDATE SET name = source.name

-- Strategy B: Use old value only when new data is null (for partial updates)
UPDATE SET name = COALESCE(source.name, target.name)

-- Strategy C: Always keep the earliest creation time
UPDATE SET updated_at = GREATEST(source.updated_at, target.updated_at)

Monetization Guide: From Skill to Revenue

The MERGE INTO skill itself has limited value, but if you package it as a data synchronization product, you can generate real income:

Plan A: Data Sync SaaS Service

Target SMEs in e-commerce, retail, and hospitality with automated daily/weekly data sync services:

• Basic: ¥500/month, sync 1-2 data sources, daily updates
• Professional: ¥1500/month, sync 5 data sources, real-time incremental sync + soft delete protection
• Enterprise: ¥3000/month, custom sync rules + API integration + data quality reports

One freelance analyst serving 10 clients simultaneously = ¥5,000-15,000/month revenue. After the system runs automatically, maintenance time is less than 3 hours per month.

Plan B: One-Time Project Delivery

Custom data sync pipelines for enterprises:

• Data Collection → DuckDB Cleaning → MERGE INTO Incremental Update → Output to BI Tools
• Single project: ¥3,000-8,000, with follow-up maintenance at ¥500-1,000/month
• Standardized template reuse, near-zero marginal cost

Plan C: Tutorial Products

Package this experience into paid courses:

• Beginner tutorial: ¥99, covering MERGE INTO basics and 3 practical cases
• Advanced course: ¥299, including complete ETL pipeline construction + monetization case studies
• 1-on-1 consulting: ¥500/hour, customized solutions for specific enterprise scenarios

Remember: You’re not selling SQL knowledge. You’re selling “freedom from data chaos.” The biggest pain point for SMEs isn’t a lack of data — it’s the anxiety that data changes every day and manual maintenance can never keep up. Your MERGE INTO solution sells “peace of mind.”

Tonight’s Action Plan

1. Install DuckDB: pip install duckdb
2. Create a test database and run through the examples above
3. Use your business data (CSV or database) as the daily_users table, run a MERGE INTO once
4. Add soft delete logic and experience the complete data sync flow
5. If you’re a data service provider, package this workflow as a standardized data sync product

One MERGE INTO replaces fifty lines of Python comparison code. In incremental sync scenarios, it’s the most efficient solution available.

📌 Bookmark this article for your next data sync project. 🔍 duckdblab.org for systematic DuckDB learning.