Having them in different schema clearly identifies their purpose.
In most cases separating the warehouse tables from the relational database is considered a best practice. Some of the reasons for doing this are:
Conceptual separation. In short relational databases and data warehouses are different types of SQL server databases and should be treated that way.
Security Separation: While implementing security is possible at a schema level, it is much easier to set up security at the database level.
Backups: This might be the biggest reason for wanting to separate the data warehouse from another database. Typically a data warehouse is backed up using a Simple Recovery Model where the transaction logs are not backed up. This is done because data warehouses are very large, but aren’t written to very often. A relational database is typically backed up using the Full Recovery Model, where the transaction logs are maintained and backed up regularly. The database can be recreated using the last back up and the transaction logs.
In most cases separating the warehouse tables from the relational database is considered a best practice. Some of the reasons for doing this are:
Conceptual separation. In short relational databases and data warehouses are different types of SQL server databases and should be treated that way.
Security Separation: While implementing security is possible at a schema level, it is much easier to set up security at the database level.
Backups: This might be the biggest reason for wanting to separate the data warehouse from another database. Typically a data warehouse is backed up using a Simple Recovery Model where the transaction logs are not backed up. This is done because data warehouses are very large, but aren’t written to very often. A relational database is typically backed up using the Full Recovery Model, where the transaction logs are maintained and backed up regularly. The database can be recreated using the last back up and the transaction logs.
