DP-203 Develop data processing Practice Question
You are implementing a Mapping Data Flow in Azure Synapse Analytics that reads from a Parquet source and writes to a Delta sink. The data flow includes a Surrogate Key transformation to generate unique keys for each row. You notice that when the data flow runs multiple times, the surrogate keys are not consistent across runs and sometimes overlap. You need to ensure that surrogate keys are unique and stable across runs. What should you do?
⚠ Common exam trap
The trap here is assuming that the Surrogate Key transformation generates keys that are stable across runs, when it actually restarts numbering each time.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Use a Derived Column transformation to concatenate a business key from the source with a hash function to create a deterministic surrogate key.
To ensure surrogate keys are unique and stable across runs, you must derive them deterministically from the source data. Hashing a business key with a function like `sha2` or `md5` produces the same key for the same input, preventing overlaps and ensuring consistency. Sequential Surrogate Key transformations or random UUIDs do not provide stability across multiple executions.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use a Derived Column transformation with the `uuid()` function to generate a unique identifier for each row.
Why it's wrong here
The `uuid()` function generates a random UUID for each row, which is unique but not stable across runs. If the data flow is rerun, new UUIDs are generated for the same source rows, resulting in different keys. This does not meet the requirement for stable surrogate keys that remain consistent across multiple executions.
- ✗
Use a Surrogate Key transformation with the 'Start value' set to a value that increments based on the maximum existing key in the sink.
Why it's wrong here
The Surrogate Key transformation generates sequential numbers starting from a specified value, but it does not automatically query the sink for the maximum existing key. Even if you manually set a start value, concurrent runs or reprocessing could cause overlaps. It does not provide stable keys across runs because the generation is not coordinated with existing data.
- ✗
Use a Surrogate Key transformation with the 'Start value' set to 1 and enable the 'Unique' option.
Why it's wrong here
The Surrogate Key transformation does not have a 'Unique' option. It generates sequential numbers starting from the specified value, but if the data flow runs multiple times, the same start value will produce overlapping keys. This approach does not guarantee uniqueness or stability across runs unless combined with other logic.
- ✓
Use a Derived Column transformation to concatenate a business key from the source with a hash function to create a deterministic surrogate key.
Why this is correct
Creating a deterministic surrogate key by hashing a business key ensures that the same source row always produces the same key, regardless of how many times the data flow runs. This provides stability and uniqueness as long as the business key is unique. It avoids overlaps and is the correct approach for stable surrogate keys.
Go deeper
Related to this question
About these practice questions
One of 509 original DP-203 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Microsoft exam blueprint
This DP-203 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-203 exam.