Wouldn't you want to use the natural key from the source? Typically auto cdc use cases (be they Snowflake, Big Query, Fabric, Databricks, or other) are those which are cloning an external source using its source ID, and then when subsequent information is desired, a downstream transformation takes place. In situations where that's not sufficient, then typically it's some form of structured streaming with a message queue relay in between or some other path. It sounds like your use case is just mis-assigning transformation layers.