Which ADF Data Flow Transformation Handles Upsert Logic?
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 transformation must specify logic indicating when a row from the input data must be upserted into the sink. Which type of transformation should you add to the data flow?
Community Votes
100% of anonymous learners picked answer B. Votes are pick records left by other test-takers — they are not the verified answer.
Community Insight
Tests knowledge of specific ADF transformation capabilities, with the common trap being confusion between Join, Select, and Alter Row functions.
The Alter Row transformation in Azure Data Factory enables conditional row operations like inserts, updates, and deletes during data flows. Community consensus confirms it is the correct choice for implementing upsert logic against Synapse Analytics.
Select is often chosen incorrectly because users assume basic column mapping handles row-level actions, but Select only transforms columns without controlling insert/update/delete behavior.
Community Discussion (3 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
The Alter Row transformation explicitly defines conditional logic for row-level actions such as InsertIf, UpdateIf, and DeleteIf. This capability is specifically designed to handle complex merge or upsert scenarios where source rows must be matched against existing sink records. By evaluating expressions before the sink operation, it ensures that only qualifying rows trigger the appropriate database command. This makes it the standard approach for incremental data loading in Azure Data Factory.Why the Other Options Are Wrong
The Join transformation combines two datasets based on matching keys but does not dictate how resulting rows are written to the destination. The Surrogate Key transformation generates unique identifiers for slowly changing dimensions and lacks row-action control. The Select transformation filters or renames columns but operates purely at the schema level without influencing insert, update, or delete behaviors. None of these transformations provide the necessary conditional execution required for upsert operations.Community Comment Notes
Candidates consistently validate this answer, with multiple contributors noting that Alter Row directly maps to upsert policy definitions. As highlighted in comment [2], the official Microsoft documentation explicitly outlines how to configure insert, update, and delete policies within this transformation. Another contributor [1] corroborates this by explaining that record existence checks inherently require Alter Row’s conditional syntax. The unanimous voting pattern further reinforces that this is a straightforward, documentation-backed fact.Official Reference
Exam Strategy
When facing ADF transformation questions, focus on the specific action required rather than just data shape changes. Memorize the distinct purpose of each transformation to quickly eliminate distractors during the exam.