Data Engineering on Microsoft Azure — Question 137

You have an Azure Synapse Analytics dedicated SQL pool that contains a table named Table1. Table1 contains the following:
✑ One billion rows
✑ A clustered columnstore index
✑ A hash-distributed column named Product Key
✑ A column named Sales Date that is of the date data type and cannot be null
Thirty million rows will be added to Table1 each month.
You need to partition Table1 based on the Sales Date column. The solution must optimize query performance and data loading.
How often should you create a partition?

Answer options

• A. once per month
• B. once per year
• C. once per day
• D. once per week

Correct answer: A

Explanation

Creating a partition once per month is ideal given that thirty million rows will be added each month, allowing for efficient management and query optimization. If partitions were created less frequently, such as once per year, it would lead to larger partitions that could decrease performance. Daily or weekly partitions would be excessive and not necessary given the monthly data growth.