I have tried incremental loads but the overall processing times did not change much and have concern on hitting the spice ingestion size since the incremental load is constantly appending to the dataset
What do you mean with overall processing did not change much ? Ingesting 100 M records with incremental refresh took the same as a full refresh of 500 M ?
Is the custom SQL that you are executing only looking at data for certain periods of time ? ( For eg : last 12 months )
If the incremental load append works for your data , you can use a multi-schedule approach ( Setting data retention period on incremental refresh dataset? - #6 by Giang_Nguyen )
Kind regards,
Koushik