Feature Request: Use table-swap strategy instead of `DELETE ... WHERE TRUE` for FULL models

[petrikoro]

Description

FULL model refreshes currently use DELETE ... WHERE TRUE (sqlmesh 0.226.0, Postgres engine). For large, indexed tables this is significantly less efficient than creating a new table and swapping it into place. I’d like to propose adding configurable strategies for FULL model writes—similar in spirit to InsertOverwriteStrategy for incremental models - allowing engines to use swap operations when supported.

Problem

DELETE ... WHERE TRUE followed by INSERT forces the database to update indexes row-by-row, which is slow on large datasets. Engines like Postgres, ClickHouse, and StarRocks can perform more efficient table replacements (atomic swaps or controlled rename sequences).

Example (Postgres):

2025-11-08 19:19:38,384 - MainThread - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: DELETE FROM "sqlmesh__core"."sqlmesh_md5__61065466817997205b15df9c116b57bf" WHERE TRUE (base.py:2619) 2025-11-08 19:19:43,706 - MainThread - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: INSERT INTO "sqlmesh__core"."sqlmesh_md5__61065466817997205b15df9c116b57bf" ( ... ) (base.py:2619)

Proposal

Introduce a configurable SwapStrategy for FULL models. This separates behavior from incremental strategies while enabling engine-specific optimizations.

from enum import Enum

class SwapStrategy(Enum):
    # Existing behaviour: DELETE then INSERT, default
    DELETE_INSERT = 1

    # Use engine-native atomic swap:
    # e.g., ClickHouse EXCHANGE TABLES, StarRocks SWAP
    NATIVE_SWAP = 2

    # Recreate a new table, populate it, then rename it into place
    # (for engines without atomic swap or requiring manual grant/constraint handling)
    RECREATE_AND_RENAME = 3