Forum Discussion
Tackling design issue
Hi,
I'm working out a design for ticket data. We use different systems with tickets and there is a need to create a single semantic model with the data from the different systems. I work with the mediallion architecture, lakehouse and warehouse, star/snowflake schema.
We don't use have any use for SCD2, so the data shows the current state. The master data of each system doesn't change much, the ticket data does. So for the ticket data we are using incremental loading. Some of the data comes from databases on prem, others from api's not on prem. Getting the data is no issue.
But now I want to create the golden layer and then link everything together in the semantic model. The ticket data should be in 1 fact table (the source of the data is a column in the table). But then I need to link everything together with the dimension tables. The states, the teams, the persons, the services,... are all defined differently in each systems. But I'm struggling how to create the dimension tables and how to link them to the fact table.
I have done it for other data with no issue, but this is the first time merging simular data together in one and I don't find a lot of info about it online.
So, if anyone has some tips and tricks, please share. đ
Thanks
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.
4 Replies
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.
Hi Jessy_D ,
I would also take a moment to thank Vinodh247 , for actively participating in the community forum and for the solutions youâve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions
Hi Jessy_D ,
Is it possible to share the table schema to understand the issue much better and solve precisely.
