I'm working on a data storage model for a clickstream analytics system. User action data comes from a third-party system as a set of large JSON files. Currently, we will have an ETL process to read JSON files as a source and save data into our store for future analysis and reporting.
Depending on some business rules of the source system, each event can have an is_success field set to true or false. Non-successful user actions have a JSON field with an array of nested objects with diagnostic data about failures.
The draft data model for the storage system is the following:
I have concerns about the relation between fact_events and dim_failure_details on the diagram above. To me, dim_failure_details does not look like a dimension because it has a many-to-one relationship to the fact table.
I've read a design tip from the Kimball Group. That article recommends using a bridge table in a similar situation. But I don't understand how to apply that solution in my case because each event can have different and unpredictable values for attribute_key and attribute_value even when failure_type is the same for multiple events.
I also saw a few similar questions (Star schema [fact 1:n dimension]...how?, Star schema [fact 1:n dimension]...how?), but still don't know how the relationship should be organized correctly. Any help will be much appreciated.