Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 38 ยท Patterns and Pipelines

Incremental Loads and Partitioned Output

Load only new data with watermarks, upsert with INSERT OR REPLACE and ON CONFLICT, MERGE-style updates, and write Hive-partitioned Parquet with COPY PARTITION_BY.

Rebuilding everything on every run works until the data grows. Incremental loading processes only what changed since last time. This lesson covers the three building blocks: tracking progress with a watermark, merging new rows into existing tables (upserts), and writing output partitioned into folders so later reads can skip most of it.

The real DuckDB cannot run on this site, so file and COPY ... PARTITION_BY statements are read-only. The upsert and watermark logic is portable SQL and runs here.

The watermark pattern

A watermark is the highest value you have already processed: the latest updated_at, an auto-increment id, or a file name. Store it in a small control table:

SQL
CREATE TABLE IF NOT EXISTS etl_state (job TEXT PRIMARY KEY, watermark TIMESTAMP);

Each run reads rows newer than the watermark, loads them, then advances it, all in one transaction so a failure never moves the watermark without the data:

SQL
BEGIN;
INSERT INTO staging.events
SELECT * FROM read_parquet('incoming/*.parquet')
WHERE updated_at > (SELECT coalesce(max(watermark), TIMESTAMP '1970-01-01')
                    FROM etl_state WHERE job = 'events');

INSERT OR REPLACE INTO etl_state
SELECT 'events', max(updated_at) FROM staging.events;
COMMIT;

Late-arriving data can slip behind a watermark. Re-read a safety margin (for example the last two days) and let the upsert absorb the duplicates.

SQL Watermark: load only new rows

Press Run (or Ctrl + Enter) to see the result here.

Upserts

Rows with an existing key must be updated, new ones inserted. DuckDB supports several forms:

SQL
-- Needs a PRIMARY KEY or UNIQUE constraint on id
INSERT INTO customers
SELECT * FROM staging.customers
ON CONFLICT (id) DO UPDATE SET
    email = EXCLUDED.email,
    city  = EXCLUDED.city;

-- Shorthand: replace the whole row on a key clash
INSERT OR REPLACE INTO customers SELECT * FROM staging.customers;

-- Skip rows that already exist
INSERT OR IGNORE INTO customers SELECT * FROM staging.customers;

EXCLUDED refers to the incoming row. Newer DuckDB versions also support a standard MERGE INTO ... USING ... WHEN MATCHED ... WHEN NOT MATCHED statement; check your version's documentation. An upsert needs an index on the key, so keep the constraint if you rely on it. Bulk upserts into big tables are slower than bulk appends; when the target is partitioned Parquet, replacing a whole partition is usually simpler.

SQL Upsert with ON CONFLICT

Press Run (or Ctrl + Enter) to see the result here.

Row 2 was updated, row 3 inserted, row 1 untouched. (SQLite needs WHERE true in an INSERT ... SELECT with ON CONFLICT to avoid a parsing ambiguity; DuckDB does not.)

Anti-join for "new rows only"

When there is no good watermark, insert rows whose key is not yet present:

SQL Insert only rows that do not exist yet

Press Run (or Ctrl + Enter) to see the result here.

DuckDB also offers ANTI JOIN syntax (FROM feed f ANTI JOIN dim d ON d.id = f.id), which reads more clearly and plans efficiently.

Partitioned output

For large tables written as files, split the data into folders by a column that queries usually filter on:

SQL
COPY (SELECT *, year(order_date) AS year, month(order_date) AS month FROM staging.orders)
TO 'lake/orders'
(FORMAT parquet, PARTITION_BY (year, month), OVERWRITE_OR_IGNORE, COMPRESSION zstd);

The result:

TEXT
lake/orders/year=2026/month=1/data_0.parquet
lake/orders/year=2026/month=2/data_0.parquet
...

Later queries read only the folders they need:

SQL
SELECT sum(amount)
FROM read_parquet('lake/orders/*/*/*.parquet', hive_partitioning = true)
WHERE year = 2026 AND month = 2;
flowchart LR
    A["New data for March"] --> B["COPY ... PARTITION_BY (year, month)"]
    B --> C["lake/orders/year=2026/month=3/"]
    D["Query WHERE year=2026 AND month=3"] --> C
    D -. skips .-> E["all other folders"]

Partition columns become folder names; filters on them skip whole folders.

Design rules:

  • Choose low-cardinality partition columns (year, month, country), not ids. Thousands of tiny files hurt.
  • Keep files at least tens of megabytes where possible.
  • Match partitioning to your most common filter.
  • For an incremental job, write each run into its own partition and replace it (OVERWRITE or delete the folder first) so reruns are idempotent. Appending twice duplicates data.
  • Use FILENAME_PATTERN 'run_{uuid}' to avoid collisions when several runs write into the same partition.
SQL Which partitions would a query touch? (portable)

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is a watermark in an incremental load?

    The watermark records progress, so each run loads only rows beyond it.
  2. 2. What does INSERT ... ON CONFLICT (id) DO UPDATE do?

    It is an upsert: rows with an existing key are updated, others are inserted.
  3. 3. Why choose low-cardinality partition columns?

    Too many partitions create tiny files, which slow reads and cost more requests.
  4. 4. How do you keep a partitioned load idempotent?

    Overwriting the partition produces the same result no matter how many times the job runs.

Summary

  • Use a watermark in a control table and advance it in the same transaction as the load.
  • Upsert with ON CONFLICT, INSERT OR REPLACE or MERGE; use anti-joins for insert-only feeds.
  • Write Parquet with PARTITION_BY on low-cardinality columns and read with hive_partitioning.
  • Replace whole partitions for safe reruns; avoid tiny files.

Next: Data Quality and Testing Pipelines