Partial restatement on incremental by unique key

[dberardo-com]

the doc states:

Note: Models of the INCREMENTAL_BY_UNIQUE_KEY kind are inherently non-idempotent, which should be taken into consideration during data restatement. As a result, partial data restatement is not supported for this model kind, which means that the entire table will be recreated from scratch if restated.

why wouldnt you allow the user to do a partial restatement all-the-same ? say, like simply upserting rows.

I believe i am missing something probably.

in particular in my use case i have a table in which i want to perform UPSERTs, so appending new rows if none exist or simply overwrite existing ones, but without having to erase the table every time

[IlyaKhalafi]

Normal INCREMENTAL_BY_UNIQUE_KEY runs already do the upserts you want. Matching keys are updated, new keys are inserted, and target rows absent from the incoming batch remain. The full rebuild applies when you explicitly restate the model, not on every incremental run.

For example, the model declaration can be

MODEL (
  name analytics.customers,
  kind INCREMENTAL_BY_UNIQUE_KEY (
    unique_key customer_id
  )
);

Its query should return one row per key in each batch. You can filter the source using @start_date and @end_date when it has a suitable change timestamp.

The restatement restriction matters because a unique key is not tied to one time interval. Replaying an older batch can overwrite a newer value for the same key. SQLMesh therefore expands restatement to the full history for this model kind.

A simple overwrite upsert can be idempotent for identical input, so the wording is broader than that special case. It doesn't mean you need to erase the table to insert or update rows.