Backfilling data and partitions - looking for best practice/advice
We have a table partitioned by date and a group partitioned by calendar_hierarchy_day.
We often have a backfilling exercise, which obviously inserts records into old and large partitions which then causes a mergeout.
How can I avoid that ? The date used is not updated, so it's only new (but old) records.
Queries use this date as a filter so on one side it's a good candidate for partitioning.
Also increasing activepartitioncount isn't appropriate since it's common to backdate many years, often up to 30 years, including all years from that point.