Is columnstore indexes not a good fit for this for performance
Thank you in advance.
Not necessarily, but it does mean you'll need to do more than just slap a load of nonclustered columnstore indexes on your current schema to see benefits.
For whatever reason (impossible to say when anonymized) the plan you showed is using row mode processing for almost all operations, which eliminates most of the potential benefit of using columnstore indexes (in this case). The large number of row mode exchanges (with high row counts) are a sure sign that this plan will not go well.
Ideally, the plan would feature batch mode (mostly hash) processing almost exclusively, and only use row mode where that can't be avoided, and even then only on a relatively small number of rows.
I suggest you take a step by step approach. On a test system, replace all the existing indexing and partitioning with clustered columnstore. Take a representative query, and see how it performs, adding nonclustered b-tree indexes where justified. You may also find it useful and instructive to start with smaller queries with just a few major operations and joins, and work methodically from there.
Take some time to review the documentation on columnstore best practices before you begin. You may need to adjust some syntax in the source query to work well with columnstore.
I see the operations are using Batch mode in the main query, where do you see the row mode?
Same query with more Data using columnstore 7570 Sec
Same query with more Data Not using columnstore 782Sec
Same Query More Data ColumnStore 7570 Seconds
Same Query More Data No ColumnStore 782 Seconds