Exam DP-203 topic 1 question 69 discussion

• Show order counts by week. • Calculate sales totals by region. • Calculate sales totals by product. For these, Group By is required while querying, hence cannot be a parition. But fourth one, requires you to use WHERE clause, so month is ideal for a partition

When designing a fact table in a data warehouse, it is important to consider the types of queries that will be run against it. In this case, the queries that need to be optimized include: show order counts by week, calculate sales totals by region, calculate sales totals by product, and find all the orders from a given month. Partitioning the table by month would be the best option in this scenario as it would allow for efficient querying of data by month, which is necessary for the query operations described above. For example, it would be easy to find all the orders from a given month by only searching the partition for that specific month.

Correct Answer: B

I choose B, partitioning by month. This will make finding all orders for a specific month much faster. It also makes it easier to count orders by week. The other queries, like calculating sales totals by region and product, are simpler and don’t need special partitioning.

Partition by week. Partition by month sounds correct, but it is not. If partition by week, then get the four weeks data from 4 partitions. If it is a month, retrieve the month partition and get the data for a week. The first one is simpler than the second one.

Got this question on my exam on january 17, answer B is correct

Should be B month.

B is right

Can someone please link documentation to where this is stated? I feel like any of these categories would be an effective partition strategy as there is a use case for each. I am confused

Chat GPT: Based on the given usage patterns and requirements, the recommended folder structure would be option B: \DataSource\SubjectArea\YYYY-WW\FileData_YYYY_MM_DD.parquet This structure allows for easy filtering of data by year and week, which aligns with the identified usage pattern of most queries filtering by the current year or week. It also organizes the data by data source and subject area, which simplifies folder security. By using a flat structure, with the data files directly under the year-week folder, query times can be minimized as the data is organized for efficient partition pruning. Option A is similar but includes an additional level of hierarchy for the year, which is unnecessary given the requirement to filter by year-week. Options C, D, and E do not follow a consistent hierarchy, making it difficult to navigate and locate specific data files.

How can we partition by unique months unless we have the year too?

Correct

Why B) month and not C) week?

B should be the correct answer here

Correct

Answer is correct