(For MS SQL server)

If UUIDs are not sequential (i.e. random), there could be performance problems to used those as PK with an underlying clustered index (CI).

The CIs work best with sequential values.

Inserting a random value into a CI will certainly cause: (1) touching of many pages (write amplification), (2) index fragmentation, (3) page splits, (4) pages being half-full.

Furthermore, if you have other indexes defined on the table, these problems apply to these indexes also.

This of cause depends on data quality and data volume.

For the "mostly-append" fact tables, I would advise against using UUIDs as PKs with CI.