I have a database (OLTP) in Azure SQL and my task is to create a DWH (with staging) and data marts. What is considered best practice: having everything in the same SQL database, under different schemas or creating different databases for the different purposes? And what are the differences in performance?
From the information I could find until now, it seems like the way to go would be to have the staging in the same database as the OLTP database, then one database for the DWH and another one for the data marts. Would this be right? Would it be simpler and cleaner to have it all in the same database?