Star Schema Model on Synapse Serverless

Question

Can we create a Star Schema denormalised data model on top of Synapse serverless pool having data files (raw) stored in Azure Data Lake ?

Answer 1

Absolutely- there is nothing in a serverless pool that enforces a certain type of database design.

I'm guessing you are asking about a scenario where the data is normalized but you want to build a view on top of that in a serverless pool that has a star schema. The answer here is still yes, but you will need to be mindful of performance, especially if your dataset is large. Making sure the underlying files are in parquet format and strategic use of CETAS can go a long way to keeping this viable as you add more data. I do need to make a note that using CETAS does create an additional set of parquet files in storage- you are no longer going purely from the raw data. However, you wouldn't necessarily need to convert all the data that way- you could use them just for removing performance bottlenecks and combine them with raw data in your views.

Answer 2

Go Data Vault - avoid dims and facts