Forum Discussion

coolie's avatar
coolie
Helper I
1 year ago
Solved

Partitioning Strategy

I've partitioned my Datalake table (<10 million records) into product(10),year,month,day partitions. Data gets updated every day (about 1000-40000 records per product per day) so this seemed like a g...
Data Engineering
Lakehouse
  • v-achippa's avatar
    v-achippa
    1 year ago

    Hi coolie,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    The issue here is due to exceeding the number of files per table guardrail, which can lead to query performance. This happens because excessive small Parquet files generated by daily updates.

    • Use an OPTIMIZE or MERGE operation regularly to merge small files into larger ones. Enable Auto-Compaction and update existing records instead of creating new files daily.
    • The current partitioning strategy creates too many partitions and small files, if possible remove the day partition to reduce file count, so the structure becomes: Product → Year → Month
    • Implement the data retention policies to delete or archive older data

    These steps will reduce the  file count and improves the performance.

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

     

    Thanks and regards,

    Anjan Kumar Chippa

v-achippa's avatar
v-achippa
Community Support
1 year ago

Hi coolie,

 

Thank you for reaching out to Microsoft Fabric Community.

 

The issue here is due to exceeding the number of files per table guardrail, which can lead to query performance. This happens because excessive small Parquet files generated by daily updates.

  • Use an OPTIMIZE or MERGE operation regularly to merge small files into larger ones. Enable Auto-Compaction and update existing records instead of creating new files daily.
  • The current partitioning strategy creates too many partitions and small files, if possible remove the day partition to reduce file count, so the structure becomes: Product → Year → Month
  • Implement the data retention policies to delete or archive older data

These steps will reduce the  file count and improves the performance.

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

 

Thanks and regards,

Anjan Kumar Chippa

v-achippa's avatar
v-achippa
Community Support
1 year ago

Hi coolie,

 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

 

Thanks and regards,

Anjan Kumar Chippa

  • v-achippa's avatar
    v-achippa
    Community Support
    1 year ago

    Hi @coolie,

     

    We wanted to kindly follow up to check if the solution I have provided for the issue worked.
    If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

     

    Thanks and regards,

    Anjan Kumar Chippa

    • v-achippa's avatar
      v-achippa
      Community Support
      1 year ago

      Hi @coolie,

       

      As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked.
      If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

       

      Thanks and regards,

      Anjan Kumar Chippa