DP-201 Exam QuestionsBrowse all questions from this exam
Go to Exam

DP-201 Exam - Question 4

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?

Show Answer
Correct Answer: B

The proposed solution suggests using vertical partitioning to separate data into customer regions. Vertical partitioning involves splitting a table vertically into multiple tables with fewer columns, and it is typically used to distribute tables with different schemas across databases. However, the requirement is to partition data based on CustomerID values, which indicates a need to distribute rows across multiple partitions. This is best achieved using horizontal partitioning, or sharding, which evenly distributes rows of a table across multiple databases with identical schemas based on a partition key like CustomerID. Therefore, vertical partitioning does not meet the goal, and the correct answer is 'No'.

Discussion

7 comments
Sign in to comment
kate208
Jun 15, 2020

Sharding, not charding haha

Treadmill
Aug 9, 2020

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.

mojedapr
Sep 23, 2020

Still don't know why horizontal and not vertical !

clownfishman
Sep 28, 2020

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

chaoxes
Dec 19, 2020

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)

redalarm2000
Jan 15, 2021

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?

Shanmahi
Jan 25, 2021

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

Deepu1987
Feb 17, 2021

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.

ShauryaRana
Jul 1, 2021

'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.