dbt-labs/dbt-core · error · AdapterError

'{strategy}' strategy requires a non-empty 'unique_key'.

Error message

'{strategy}' strategy requires a non-empty 'unique_key'.

What it means

delete_insert and microbatch incremental strategies on ClickHouse identify which rows to replace during the incremental run, so a non-empty unique_key is mandatory. validate_incremental_strategy raises this Configuration error when either strategy is selected without one.

Source

Thrown at crates/dbt-adapter/src/metadata/clickhouse/mod.rs:912

) -> AdapterResult<()> {
    let err = |msg: String| Err(AdapterError::new(AdapterErrorKind::Configuration, msg));
    if !matches!(
        strategy,
        "legacy" | "append" | "delete_insert" | "insert_overwrite" | "microbatch"
    ) {
        return err(format!(
            "The incremental strategy '{strategy}' is not valid for ClickHouse."
        ));
    }
    if matches!(strategy, "delete_insert" | "microbatch") && !has_lw_deletes {
        return err(format!(
            "'{strategy}' strategy requires lightweight deletes, but the required setting \
             '{ND_MUTATION_SETTING}' could not be enabled on this ClickHouse server \
             (see the warnings logged at connection time)."
        ));
    }
    if matches!(strategy, "delete_insert" | "microbatch") && !has_unique_key {
        return err(format!(
            "'{strategy}' strategy requires a non-empty 'unique_key'."
        ));
    }
    if !matches!(strategy, "delete_insert" | "microbatch") && has_predicates {
        return err(format!(
            "Cannot apply incremental predicates with '{strategy}' strategy."
        ));
    }
    if strategy == "insert_overwrite" && !has_partition_by {
        return err(format!(
            "'{strategy}' strategy requires non-empty 'partition_by'. Current partition_by is None."
        ));
    }
    if strategy == "insert_overwrite" && has_unique_key {
        return err(format!(
            "'{strategy}' strategy does not support unique_key."
        ));
    }

View on GitHub (pinned to 0267ce9170)

Solutions

1. Add a non-empty unique_key to the model config (a column name or list of column names)
2. Verify the key columns actually uniquely identify rows, or the delete/insert will duplicate data
3. If no natural key exists, switch incremental_strategy to 'append'

Example fix

// before
{{ config(materialized='incremental', incremental_strategy='delete_insert') }}
// after
{{ config(materialized='incremental', incremental_strategy='delete_insert', unique_key='id') }}

if matches!(cfg.incremental_strategy.as_str(), "delete_insert" | "microbatch") {
    assert!(!cfg.unique_key.as_ref().map_or(true, |k| k.is_empty()), "unique_key required");
}

When it happens

Trigger: Configuring incremental_strategy='delete_insert' or 'microbatch' on a ClickHouse model without setting unique_key, or setting unique_key to an empty value/empty list.

Common situations: Copying a model config from an append-only model that never needed unique_key; removing unique_key during a refactor while keeping the strategy; misunderstanding that microbatch also requires a key.

Understand the failure class

Background: "is required", "must be set", "missing required field": configuration validation errors across open-source libraries — this error's family across 36 libraries.

Related errors

• The incremental strategy '{strategy}' is not valid for Click
• '{strategy}' strategy requires lightweight deletes, but the
• Cannot apply incremental predicates with '{strategy}' strate
• '{strategy}' strategy requires non-empty 'partition_by'. Cur
• '{strategy}' strategy does not support unique_key.

AI-assisted analysis of dbt-labs/dbt-core@0267ce9170 (2026-09-07). Data as JSON: /api/errors/5f6537aeeee5f99d. Report an issue: GitHub.