Choose and configure dbt incremental strategies: merge, insert_overwrite, and microbatch for different workloads

Verified steps

1. Set materialized='incremental' in the model config; then choose strategy: merge (default on most warehouses) performs an upsert on a unique_key, insert_overwrite replaces full partitions in one atomic swap, and microbatch (dbt 1.9+) processes data in discrete time-bounded batches.
2. For merge, define unique_key as the natural key of the entity; dbt generates a MERGE or DELETE+INSERT statement depending on warehouse capability; ensure the unique key is indexed or clustered for performance.
3. For insert_overwrite on BigQuery or Spark, specify partition_by in the config; dbt derives which partitions to overwrite from the rows in the incremental result set, making it safe for high-volume append-only fact tables.
4. For microbatch, set event_time to the timestamp column and batch_size to a duration (day, hour); dbt reprocesses each batch independently, enabling parallelism and safe retries of individual time windows.
5. Always include a filter clause referencing dbt's is_incremental() macro to restrict the source scan to new rows: WHERE event_time >= (SELECT MAX(event_time) FROM {{ this }}).
6. Run dbt build --full-refresh periodically or when upstream logic changes to rebuild the full history and avoid silent drift between incremental results and a fresh run.