Exam DP-201 topic 1 question 34 discussion

I think the partition key should be Department rather than Surname. The reason for this is the read latency. As the question stated, "most of query would filter by Current Department or Employer Surname". Having Surname as partition key, you would have 40,000 partition (40,000 unique value), and when you filter your query by Department, you query will need to go through 40,000 partition which would be real bad on performance. Other another hand, having Department as partition key, you would have 25 partition, and to filter surname query, it would be much faster compare to query on 40,000 partition.

i think the answer should be partition by surname ( as it has more unique values than department) and employeeId as itemid since it's unique.

I think the answer is correct. if you see the data populated column, for the surname it's 99%. A partition key column should not have null values. for department it's 100%, hence department is the best choice here

Isn't employee ID a good candidate for partitioning ?

The anwser is wrong ! look at this because the Department could change. https://docs.microsoft.com/en-gb/azure/cosmos-db/partitioning-overview#choose-partitionkey The answer would be good for a large containers. But in this use case we have a small one. Partition strategy depends on the container size. Since we do not have an Read-Heavy container. We shoud use a property that does not change. Partion key = EmployeeID . (both Department and Surnames can change). " For large read-heavy containers, however, you might want to choose a partition key that appears frequently as a filter in your queries. Queries can be efficiently routed to only the relevant physical partitions by including the partition key in the filter predicate. If most of your workload's requests are queries and most of your queries have an equality filter on the same property, this property can be a good partition key choice."

The partition key should be employee_id. From the documentation (https://docs.microsoft.com/en-us/azure/cosmos-db/partitioning-overview): For all containers, your partition key should: Be a property that has a value which does not change. If a property is your partition key, you can't update that property's value. Have a high cardinality. In other words, the property should have a wide range of possible values. Spread request unit (RU) consumption and data storage evenly across all logical partitions. This ensures even RU consumption and storage distribution across your physical partitions. currentDepartment has a low cardinality and can change. Lastname can change (people get married) and 1% has null lastname, which creates one large partition and thus uneven distribution. The same documentation states: "For small read-heavy containers or write-heavy containers of any size, the item ID is naturally a great choice for the partition key.". This container certainly qualifies as small, it's just 75.000 employee records.

lot of confusion here. what is the correct answer?

I agree with the given solution partion key - current dept item id - emp id

The answer given is correct. Put aside all the theory and concepts about partitioning and just think about it: A company has different departments and each department has its own employees. Between name/surname and ID, ID is definitely the better identifier.

partition key should be current department(populated 100%) as surname is only 99% populated. we cannot have a partition key as NULL/ not populated.

I suspect it's surname rather than department. Firstly there are simply too few variants, its also "current department" so might is likely to change. Surname is similarly bad on the changeable nature (assume a woman getting married e.g.) but assuming all else it's better than department.

I agree with the suggested answer, I would also argue that the distribution of names will be highly uneven and would result in partitions of very different sizes, including 40,000 with one unique entry. So Current Department by elimination really.

'Current' department suggests that it changes. Surnames change all the time. As a result neither of them are good choice for partition keys. I believe that leaves Employee ID

From the docs: For all containers, your partition key should: Be a property that has a value which does not change. If a property is your partition key, you can't update that property's value. So current dept can’t be partition key. So obviously it’s surname. Employee ID should be item ID

I think the answer is correct as https://docs.microsoft.com/en-us/azure/cosmos-db/partitioning-overview#choose-partitionkey If your container could grow to more than a few physical partitions, then you should make sure you pick a partition key that minimizes cross-partition queries. Your container will require more than a few physical partitions when either of the following are true: • Your container will have over 30,000 RU's provisioned Your container will store over 100 GB of data surname has 40,000 values, which " more than a few physical partitions", we should pick a partition key that minimizes cross-partition queries and used in filter. which is "Current Department"

Surname cannot be a partition key because ~750 records have a null value.

I think the answer for Partition Key should be Employee Surname. It has a wider range and more unique values, see: https://docs.microsoft.com/nl-nl/azure/cosmos-db/partitioning-overview#choose-partitionkey