Azure data warehouse supports both clustered and non-clustered indexes in addition to columnstore (which is the default for any new table).
I know that having a large clustering key is normally discouraged, as it forces sql server to internally generate a larger key to guarantee uniqueness (and unique constraints are not supported). Of course there are loads of benefits of using columnstores, such as batch mode and segment elimination, however columnstore will use hash match and hash join operations for analytical queries.
Experimentally I've seen situations where a clustered key performs better where all the grouping columns are included in the clustering key (4-5 columns, some varchars), because the guaranteed order allows a stream aggregate to be used instead.
My tables are distribution aligned and the estimated execution plan shows that the queries against the columnstore indexes are indeed using batch mode.
What are the potential pitfalls with this approach, is this scalable for billions of rows of data?