Custom Delta Retention period for Lakehouse table

Hello Mayumi_2021 

Given your daily data volume of maximum a thousand records per day:
1. The impact on performance should be minimal with proper maintenance, considering the relatively small daily data volume.
2. Regular OPTIMIZE operations will help maintain good read performance by compacting small files.
3. Ensure you run VACUUM operations periodically to clean up old files, especially important with such a long retention period.

some basic guidelines around Retention Period and Performance
1. The default file retention threshold for Delta tables in Fabric is seven days.
2. Setting a longer retention period impacts Delta’s time travel capabilities but doesn’t necessarily degrade performance if managed properly.
3. It’s generally recommended to set a retention interval of at least seven days because old snapshots and uncommitted files may still be in use by concurrent table readers and writers

Optimization and Maintenance
To maintain performance with a long retention period:
1. Regular maintenance is crucial. Running OPTIMIZE operations, as you plan to do daily or weekly, is a good practice.
2. The VACUUM operation is important for removing old files no longer referenced by the Delta table log, which helps optimize storage costs.
3. Fabric provides a GUI option in the context menu of each table to run ad-hoc operations like OPTIMIZE and VACUUM

If this is helpful please accept this solution and give kudos