clustered index - data warehouse My source data comes from multiple OLTP databases where the primary key is an identitiy column. We anticipate adding data centers and/or splitting the databases by product. Currently i have one system incrementing from -9223372036848909236, 1 and the other is incrementing from 1134873337, 1 

I pull the data into my data warehouse from each of my sources and the data is partitioned on the UTCDate which is also a source value.

Filter criteria will always include the UTCDate. May or maynot have ID as criteria 

Question:
will i have less fragmentation on my clustered index UTCDate, ID or  ID, UTCDate
The Short Answer:
I misread your question a little bit. The below discussion still applies and explains my answer, but I would suggest UTCDate, ID for your index. Especially because of your partitions and the fact that UTCDate is included in every query. The question of fragmentation is a good one, and I imagine you'll see some in either of your two choices, but the performance benefit would be best with this way, I feel.

The Previous Discussion:

This is one you'll likely get a few answers on and I've asked a couple questions in the comments. That said a few quick thoughts to get started (ignoring the multiple system question and the way the IDs are being used):

1.) Presuming you are talking about SQL Server partitioning, a partition aligned clustered index is generally best. So in my mind that is a good vote for UTCDate.

2.) You indicate that filters will always (is that really always? or figuratively always?) have UTCDate. In my mind that is another good vote for UTCDate.

3.) Because you'll be having IDs coming in at both a high range and a low range, you will experience some level of index fragmentation on ID. Assuming UTCDate is always increasing and you aren't entering data from dates all over the place, you may actually experience less fragmentation going with UTCDate.

You also didn't indicate if this is a fact table or dimension. Assuming it is a fact table, that is another good argument for date in my book. If you are always querying filtering on a date or date range, then the clustered index being on that range scan value should help your queries. The fact that it would be partition aligned would also likely help.

If this is a dimension and you will sometimes join on ID to pull data out of it, then there could be a stronger argument for going with ID, though I'd argue that the unaligned nature of that index to your partition could cause you more grief in the future performance wise.

I would not bother adding ID to the index. Unless you include it enough as a filtering predicate and could actually see an improvement in performance of most queries. Yes, the date column alone wouldn't be unique, but if this table is a BIGINT, the 4 byte uniqueifier SQL Server adds to the clustered index to make it unique would actually cost less in terms of storage space than that ID column.

These are just my thoughts and they are quick thoughts. This is one of those things you should really test, try a couple ways and see how it works out. Though partitioning sometimes forces your hand a bit. So I would probably end up with the date column only as my first choice and date plus ID as my second choice. Others will likely have alternative ideas on that one, too. And more information edited back into your question will help.