In an ADF Mapping Data Flow, what must you configure for an Alter Row upsert to actually update rows?

## What Alter Row is for Most transformations in an ADF Mapping Data Flow change the shape of a row. Alter Row changes its *intent*. It is the transformation that turns a stream of rows into a change set - these rows are new, these are changed, these are gone - so that a database sink can perform the right operation for each one instead of blindly appending. You configure it as an ordered list of policies. Each policy is one of **Insert if**, **Update if**, **Upsert if** or **Delete if**, paired with a boolean expression written in the data flow expression language. Typical expressions test a change flag or a comparison against a lookup of the current target state, for example `isNull(byName('deleted_at'))` for an upsert policy and `!isNull(byName('deleted_at'))` for a delete policy. ## Order matters The policies are evaluated top to bottom and the first matching expression wins - a row carries exactly one marker, not several. If a broad condition sits above a narrow one, the narrow one never fires. Put the most specific condition first and read the list as an if/else-if chain rather than a set of independent rules. This is a favourite interview trap: given a policy list where `Update if true` sits above `Delete if isDeleted`, every row is marked as an update and nothing is ever deleted. ## The sink is what makes the markers real This is the part candidates miss. Alter Row does not write anything. Downstream, the Sink transformation has an update-method section with independent checkboxes: allow insert, allow update, allow upsert, allow delete. A marker is honoured only if the corresponding box is ticked. A row marked for update arriving at a sink that allows only insert is not an error - it is simply not applied, and depending on configuration you get duplicate inserts or silently missing changes. For update, upsert and delete, the sink also needs **key columns**: the column or columns the service uses to locate the existing row in the target. Without keys there is no way to say which target row a source row corresponds to. A sink with allow upsert ticked but no key columns is the single most common cause of the symptom "my incremental load doubles the table every night". Two related sink settings are worth knowing. **Table action** (none, recreate table, truncate table) runs before the write and will happily undo the point of an incremental design if someone leaves it on recreate. And when the target has a database-generated identity or key column you do not want to write, sinks offer an option to skip writing the key columns while still using them for matching. ## Which sinks understand the markers Row-level intent needs a sink capable of row-level operations - a relational database table, Cosmos DB, or a REST endpoint that maps operations to calls. A file sink (Parquet, delimited text, JSON in a data lake) has no notion of updating row 4,712; it writes the rows it receives. If you need change semantics over files, you either write a full replacement, write change records and merge them later in the warehouse, or use a table format that supports merges outside the data flow. ## Diagnosing it When an upsert silently behaves like an append, walk the chain in this order: 1. Turn on data flow debug and preview the output of the Alter Row transformation. Previews show the marker assigned to each row, so you can see immediately whether the policy expression matched at all. 2. If the markers are right, open the sink and check the update method boxes. Only the ticked ones are applied. 3. Check key columns on the sink, and check that those columns are actually unique in the target. Matching on a non-unique key produces unpredictable updates. 4. Check table action, in case the target is being recreated or truncated first. ## A worked shape A typical Type-2-free incremental customer load looks like: Source over the landed change file, a Derived Column to normalise types, an Alter Row with `Delete if !isNull(byName('deleted_at'))` above `Upsert if true`, and a Sink on the target table with allow upsert and allow delete ticked and `customer_id` as the key column. Every element of that has to line up; the transformation alone is half the answer. ## Error handling Database sinks also offer error-row handling so a single bad row does not abort the whole write - you can continue on error and optionally log rejected rows to storage. That is worth mentioning in an interview because it shows you have run these things against real targets, where one oversized string in a million rows will otherwise fail the entire activity.