I am building a DW. The sources are comming from rest API that returns Json. I need to design a staging area. I think I have 2 approaches: 1. Transform Json into a relational model. 2. Store the Json into a relational table using a key value. The key is going to be a field that I will use to performs join. The value is going to be the Json.
The first one is a by the book approach but I think it's harder to maintain. The second one is easier to maintain, but complicated to do complex queries.
Which are the drawbacks from each solution? Opinions are accepted.
Approach 1 is good for Data warehouse and second approach fits on Data lake scenario .
JSON - Jtore whole details intact to one document, We will be storing key unnecessary (increase size of data base performance hit) for each document , it will complicated /performance hit query where result recurred cross doc which is a common in DW scenarion ..