Here is the situation: There is a dimension with a natural key that is a good candidate for a primary key. However, there are a few columns that can have (and does have - 10% of the time) multiple values. The majority of the data have only one value per key.
- For the 90%, put it into the table and have "multiple" as the value for the rest; and snowflake the list.
- Same as #1, but LISTAGG the values. This will make numeric columns - non-numeric.
- Create a surrogate key, and replicate the rest of the columns with a flag to pick one of the rows - to avoid coding DISTINCT is the attribute is not being used.
- Just snowflake the column to another table.
- Any other ideas?