mishka-723908 (10/21/2016)
Thank you for your responseWe are not sure yet whether to create it as clustered or not. This is something we will need to test with.
The deletes will happen through a daily ETL data load. In some cases we may have more than one loads per day there are multiple fact tables we delete from.
You'll certainly have to do a lot of testing. Specifically which impacts performance more: dropping/rebuilding as part of your ETL or having a Columnstore index present during updates.
Then you'll need to consider the pros/cons of clustered vs nonclustered. E.g you can update a clustered cs index but you can't have any other indexes on that table. You can't update a nonclustered cs index but you don't have to include all the columns (maybe a huge benefit considering how many columns you're working with.
It will be interesting to see how this works out for you. Cheers.
-- Itzik Ben-Gan 2001