FWIW, we chose to use Hibernate's UUID generator, as it was still practical to change the backend data sources.
I think the ideal approach would extend Hibernate's ID generator 'select' strategy to support composite keys. Surrogate keys would be generated by the database system. The 'select' strategy could lookup the newly-generated SK by way of the composite key. The composite key would contain a source-system identifier Teiid could use to properly route the request, along with a unique identifier the backend database could use to lookup the surrogate key. This results in two outcalls, but would require zero backend configuration change, and have no impact to legacy systems. Unfortunately, Hibernate doesn't support it, so it would need to be implemented.