Exam DP-201 topic 1 question 4 discussion

Actual exam question from Microsoft's DP-201

Question #: 4
Topic #: 1

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are designing an Azure SQL Database that will use elastic pools. You plan to store data about customers in a table. Each record uses a value for
CustomerID.
You need to recommend a strategy to partition data based on values in CustomerID.
Proposed Solution: Separate data into customer regions by using vertical partitioning.
Does the solution meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: B 🗳️
Vertical partitioning is used for cross-database queries. Instead we should use Horizontal Partitioning, which also is called charding.
Reference:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-overview

by kate208 at June 15, 2020, 12:11 a.m.

Comments

Submit Cancel

kate208

Highly Voted 4 years, 11 months ago

Sharding, not charding haha

upvoted 32 times

...

Treadmill

Highly Voted 4 years, 10 months ago

Customer scenarios for elastic query are characterized by the following topologies: • Vertical partitioning - Cross-database queries (Topology 1): The data is partitioned vertically between a number of databases in a data tier. Typically, different sets of tables reside on different databases. That means that the schema is different on different databases. For instance, all tables for inventory are on one database while all accounting-related tables are on a second database. Common use cases with this topology require one to query across or to compile reports across tables in several databases. Horizontal Partitioning - Sharding (Topology 2): Data is partitioned horizontally to distribute rows across a scaled out data tier. With this approach, the schema is identical on all participating databases. This approach is also called “sharding”. Sharding can be performed and managed using (1) the elastic database tools libraries or (2) self-sharding. An elastic query is used to query or compile reports across many shards. Shards are typically databases within an elastic pool. You can think of elastic query as an efficient way for querying all databases of elastic pool at once, as long as databases share the common schema.

upvoted 27 times

...

ShauryaRana

Most Recent 3 years, 11 months ago

'Avoid creating "hot" partitions that can affect performance and availability. For example, using the first letter of a customer's name causes an unbalanced distribution, because some letters are more common. Instead, use a hash of a customer identifier to distribute data more evenly across partitions.' - From MS documentation for Horizontal Partitioning.

upvoted 1 times

...

Deepu1987

4 years, 3 months ago

Here we're storing Customers data in a table and now we want to partition cust region so we need to use sharding as per the right concept as they are performed as long as DBs share common schema as per defn.

upvoted 1 times

...

Shanmahi

4 years, 4 months ago

Answer : No Applicable solution : Horizontal partitioning Reference : https://docs.microsoft.com/en-us/azure/architecture/best-practices/data-partitioning

upvoted 1 times

...

redalarm2000

4 years, 4 months ago

Ok i am confused as to the difference between question 4 and question 5 on this site. Question 4 says to use horizontal partitioning but Question 5 says it recommends to use horizontal partition and the wording is the same but they say that answer should be No still why?

upvoted 1 times

...

mojedapr

4 years, 8 months ago

Still don't know why horizontal and not vertical !

upvoted 1 times

clownfishman

4 years, 8 months ago

it is because it is to partition customers IDs, so it means it is just 1 database.

upvoted 3 times

...

chaoxes

4 years, 5 months ago

Vertical partitioning is to reduce the I/O and performance costs associated with fetching items that are frequently accessed. Vertical partitioning splits table and in the result we have more partitions with different schema instead of 1 big table. This is not what is expected in this scenario. Horizontal partitioning using sharding is expected. Horizontal sharding will split table row-wise, so we have multiple partitions with the same schema, but based on region in that case. For instance split table containing all customers world-wise into multiple partitions based on the regions (customer from Europe, customers from USA etc)

upvoted 1 times

...