SFMC - Data extension schema for storing an attribute with different values

Question

I'm trying to come with a data extension schema to store an attribute that could contain a number of different values (eg. hobby). We will get a daily extracts of files with a list of contacts and their related hobbies. We want create an automation that imports these files into a data extension. So for example:

File 1:

ContactID	Hobby
ContId111	Hobby1
ContId222	Hobby1
ContId333	Hobby1

File 2:

ContactID	Hobby
ContId111	Hobby2
ContId222	Hobby2
ContId444	Hobby2

File 3:

ContactID	Hobby
ContId111	Hobby3
ContId555	Hobby3

Please note if there is new hobby then a separate file will be created and we will need to import that in the same data extension. Options considered:

1. Have separate column for each of the hobbies.

Pros: Single contact record with hobbies.

Cons: What happens when a new hobby needs to be created?

Cons: Import automation will need to changed to cater for new hobby.

Cons: Could end up having hundreds of columns (scalability?).

ContactID	Hobby1	Hobby2	Hobby3
ContId111	Y	Y	Y
ContId222	Y	Y
ContId333	Y
ContId444		Y
ContId555			Y

2. Have single column to store hobbies.

Pros: No need to make changes to the automation when new hobby is imported.

Pros: Can be easily referenced in the journeys by looking at just one field.

Cons: Could end up having a large number of records as contacts are repeated for each hobby (scalability?).

ContactID	Hobbies
ContId111	Hobby1
ContId111	Hobby2
ContId111	Hobby3
ContId222	Hobby1
ContId222	Hobby2
ContId333	Hobby1
ContId444	Hobby2
ContId555	Hobby3

3. Store each file in a different data extension

Cons: Automation will need to be modified and create a new data extension each time a new hobby is created.

My question - What is the best way to create an automation to import these files into a data extension and how do we structure the data extension? Please note I'm using hobby as an example but in real life there could be hundreds of hobbies and millions of contacts.

Answer 1

You are definitely on to something.

Have you thought about:

Contact, Key, status
ContId111,Hobby1,true
ContId111,Hobby2,true
ContId222,Hobby1,true
ContId222,Hobby3,false

Contact & Key == Primary Keys; That allows you to:

a) grow the list by new hobbies (as you say)

b) remove associations without deleting (think preference center!)

...or by extension:

Contact, key, status, value
ContId111,favoriteHobby,true,tennis
ContId222,favoriteHobby,true,football
ContId333,favoriteHobby,true,chess
ContId4444,favoriteHobby,true,baseball

Contact & key again are the Primary keys. That way you can also store Key:Value pairs and again, "turn them off". Of course the second structure can be used like the first if you just have pseudo "booleans", and make the "value" column optional.

Here is a performance indication on DE size:

https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/optimizing_api_calls_and_data_structures_to_improve_performance_in_the_salesforce_marketing_cloud.html

• I think you will be fine with this unless you go into hundreds of millions. As long as you set primary keys, lookups / joins shouldn't be too bad.

As a rule of thumb, Marketing cloud is actually pretty forgiving with "long" tables (lots of rows) than it is with "wide" ones (lots of columns). Just consider that in Sales Cloud, the opposite is true - long tables are expensive there.

Still, having a key / value structure is immensely valuable and I have seen / used it in many implementations in and around Marketing Cloud whenever flexibility is needed and data structure is not expected to be static. As you say - in the first of your examples, any integration would have to change alongside your DE for each new "hobby". That will be very complex / will need integration re-tests very often.

The only downside is that you'll have to have very generic data type (text, some maxlength) on "value". When it comes to working with "values" that are e.g. dates or numbers instead of text, there is always a bit of conversion effort, but that can be done. In my book, worth it.