Optimize BETWEEN Query in SQL Server Azure
To make this query run fast you will need the data ordered by timestamp.
You have a few options
If you use a clustered columnstore index you need to FIRST order the data in the table by adding a clustered index on Timestamp and then create the columnstore index using maxdop = 1 and with drop_existing.
full syntax https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/performance-tuning-ordered-cci
You could get rid of the columnstore index and just use a clustered index on Timestamp.
You could use a nonclustered index on Timestamp and include all the other indexes in the table (might be OK if you only have 5 columns depending on the datatypes).
You could partition the table by timestamp - this is another longer conversation