Insert
This method inserts each change as a new record in the destination. For example, if you have an e-commerce record where the order status has changed, you will have two change events: one showing the status before and one after. This is useful for tracking data changes and optimizing data loading for some destinations. When choosing this method, consider when to clean up older records, as it affects query performance. Users may prefer not to include additional clauses in their queries to filter out older records, so creating views on top of the change data to retrieve only the latest state (i.e., the after change event) can simplify usage.Upsert
Upserts replace a matching record (based on a primary key) to avoid retaining older versions in the destination—a common approach in batch data processing. For example, if you have an e-commerce record where the order status has changed, instead of having two records, you retain only the latest state (i.e., the after change event). This is summarised in the image below: