When should indexes be dropped and recreated?
If this is cyclical ETL, and you are in a development (i.e. NOT LIVE) data environment, then you definitely should manage your indexes as a part of your load cycle.

I do this for several data sets every month, the largest of which adds around 100 GB monthly to a 5 TB data set.

I have done extensive testing, and from my own experience the most efficient way to load with regard to indexes is:


DISABLE non-clustered indexes, leaving clustered index intact
Perform load of raw into your data table
REBUILD NC indexes


If you only add rows periodically as part of managed ETL, this is the way to go.  This also ensures all your statistics are up to date.

For stats, it's important to note that adding 20GB to a 1TB database won't reach the tipping point for a stats auto-update, so you can add a whole month of data without ever updating statistics.

Rebuilding your NC indexes is a good way around this.  You may want to also do a clustered index rebuild periodically if fragmentation gets high (depending on your table structure and your clustered key).