You are building a data flow in Azure Data Factory that upserts data into a table in an Azure Synapse Analytics dedicated SQL pool. You need to add a transformation to the data flow. The…

The Join transformation merges two data streams based on a matching key condition but has no capability to define row-level write policies such as upsert for the sink.

The Alter Row transformation is specifically designed to apply conditional row policies on incoming data. It lets you define expressions that mark each row as insert, update, upsert, or delete before it reaches the sink, which is precisely the mechanism needed to control upsert behavior when writing to an Azure Synapse Analytics dedicated SQL pool.

The Surrogate Key transformation generates and appends an auto-incrementing integer column to the stream; it does not influence how rows are inserted or updated in the destination.

The Select transformation is used to project, rename, or drop columns from the data stream and plays no role in specifying upsert or other row modification logic.