Tackling design issue

This is a conformed dimensions problem, not a fact table problem. Create one canonical business dimension per concept (state, team, person, service) that represents your business meaning, not source systems. Then add mapping (crosswalk) tables per source system that translate source specific codes or values to the canonical dimension surrogate key. In the Gold layer, resolve all ticket records to these business keys using the mappings and store only those keys in the single ticket fact table, along with a source system identifier for traceability. Do not join on text, do not mix source dimensions, and always include “unknown/unmapped” rows to keep pipelines stable. The hard part is governance and semantic ownership; once that is clear, the model becomes straightforward.