Partitioning Strategy

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