Transactional Data Vs Non Transactional Data : Partition Depth

Question

Transactional Data Vs Non Transactional Data : Partition Depth

Anand Deshpande 40

Please can I have some perspective on storing Transactional Data in ADLS Gen2. I have read that we should store in Day wise partitions although the write operation could be slow but read operation could be faster. We have set of T and NT tables whose data should be archived from on-prem to cloud in ADLS Gen2. Should we categories the table on transnationality and store them in different partition depth. Transactional (T) in year/month/day partitions and Non Transactional(NT) in year/month.

Nandamuri Pranay Teja 4,445 Reputation points Microsoft External Staff Moderator

2025-07-18T16:54:00.6966667+00:00
Hello Anand Deshpande

Thank you for your question!

When storing transactional data in ADLS Gen2 partitions, aim for partition sizes between 100MB and 1GB to balance metadata overhead and processing efficiency. Additionally, partition your data based on common query filters to optimize performance and enable partition pruning during data retrieval.

Transactional (T) Tables: Store in a year/month/day partition structure. This allows for efficient querying and retrieval of data based on specific time frames. While write operations may be slower due to the increased number of partitions, read operations will benefit from faster access times.

Non-Transactional (NT) Tables: Use a year/month partitioning scheme. This structure is suitable for data that does not require daily granularity, thus reducing the number of partitions and improving write performance

You can refer https://learn.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-best-practices

However, to investigate further please clarify the below things.

How frequently do you access your T and NT data?

What specific queries do you usually run on this data?

Are there any operational constraints that limit your partitioning options?

How do you currently handle backup and archiving for your data?

Hope the above answer helps! Please let us know do you have any further queries.

Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.

Accepted answer

0 additional answers

Your answer

Nandamuri Pranay Teja 4,445 Reputation points Microsoft External Staff Moderator

2025-07-18T16:54:00.6966667+00:00

Hello Anand Deshpande

Thank you for your question!

When storing transactional data in ADLS Gen2 partitions, aim for partition sizes between 100MB and 1GB to balance metadata overhead and processing efficiency. Additionally, partition your data based on common query filters to optimize performance and enable partition pruning during data retrieval.

Transactional (T) Tables: Store in a year/month/day partition structure. This allows for efficient querying and retrieval of data based on specific time frames. While write operations may be slower due to the increased number of partitions, read operations will benefit from faster access times.

Non-Transactional (NT) Tables: Use a year/month partitioning scheme. This structure is suitable for data that does not require daily granularity, thus reducing the number of partitions and improving write performance

You can refer https://learn.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-best-practices

However, to investigate further please clarify the below things.

How frequently do you access your T and NT data?

What specific queries do you usually run on this data?

Are there any operational constraints that limit your partitioning options?

How do you currently handle backup and archiving for your data?

Hope the above answer helps! Please let us know do you have any further queries.

Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.

Answer 1

hossein jalilian 11,520 Volunteer Moderator

Thanks for posting your question in the Microsoft Q&A forum

It's best to separate transactional and non-transactional data by their update frequency and access patterns. Transactional data like orders or logs is updated frequently and benefits from deep partitioning for faster queries and easier retention. Non-transactional data such as master or reference tables changes rarely, so shallow partitioning keeps structure simple without hurting performance. This strategy improves query speed, storage organization, and overall manageability. Always align partition depth with how data is queried and updated.

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful

Anand Deshpande 40 Reputation points

2025-07-18T16:50:09.6833333+00:00

Thanks @hossein jalilian for prompt response. I believe we can always switch from year/month/day to year/month and vice a versa?

Share via

Transactional Data Vs Non Transactional Data : Partition Depth

0 additional answers

Your answer