dbt-labs/dbt-core · error · AdapterError

'{strategy}' strategy requires lightweight deletes, but the

Error message

'{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).

What it means

The delete_insert and microbatch incremental strategies on ClickHouse are implemented with lightweight deletes, which need the server setting identified by ND_MUTATION_SETTING (allow_deexperimental_lightweight_delete / related mutation setting) to be enabled. If the adapter could not enable it on the connected server, validation fails before the run. The message points to warnings logged at connection time, which contain the underlying reason.

Source

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

/// cross-config checks, with Python-exact messages.
pub(crate) fn validate_incremental_strategy(
    strategy: &str,
    has_predicates: bool,
    has_unique_key: bool,
    has_partition_by: bool,
    has_lw_deletes: bool,
) -> 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."

View on GitHub (pinned to 0267ce9170)

Solutions

1. Upgrade the ClickHouse server to a version supporting lightweight deletes
2. Check the connection-time warnings in the logs for why the setting could not be enabled and fix that cause
3. Ensure the connecting user has permission to change the required setting, or have an admin enable it
4. Switch incremental_strategy to 'append' or 'legacy' if lightweight deletes cannot be enabled

Example fix

# before
models:
  - name: my_model
    config:
      materialized: incremental
      incremental_strategy: delete_insert
# after (server cannot enable lightweight deletes)
models:
  - name: my_model
    config:
      materialized: incremental
      incremental_strategy: append

// before running delete_insert/microbatch, confirm the server supports the setting
// and that connection logs show no warnings about enabling it
assert!(connection_info.supports_lightweight_deletes);

match validate_incremental_strategy(...) {
    Err(AdapterError { kind: Configuration, msg, .. }) if msg.contains("lightweight deletes") => {
        fallback_to_append_strategy()
    }
    other => other,
}

When it happens

Trigger: Running an incremental model with incremental_strategy='delete_insert' or 'microbatch' against a ClickHouse server where the lightweight-delete mutation setting could not be applied — typically old ClickHouse versions, managed offerings that restrict settings, or a user without permission to change server settings.

Common situations: Connecting to an outdated self-hosted ClickHouse that predates lightweight deletes; ClickHouse Cloud or a managed service where SET is blocked; running as a restricted user without setting-grant permissions.

Related errors

• The incremental strategy '{strategy}' is not valid for Click
• '{strategy}' strategy requires a non-empty 'unique_key'.
• 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/94fb1e0ebc0e736a. Report an issue: GitHub.