2020-04-27T05:41:00Z

What are the benefits of having separate layers or a dedicated schema for each layer in ETL?


I am a solution architect for a global tech company with over ten years of experience.

What are the benefits of having separate layers or a dedicated schema for each layer in ETL? One dedicated layer for staging, one for type 1 persistent tables, and a dedicated schema/layer for tables at a dimensional model? Why it is not recommended to have all tables in one schema?

Thanks! I appreciate your help. 

Guest
22 Answers

author avatar
Top 5Real User

Here are some of the advantages of managing data in different layers:
1. Provides logical separation of data between different layers
2. For any maintenance of each layer such as backup or recovery or data model change apply, etc. are layer dependent
3. From data security perspective, only authorized resource can work in their respective layer
4. Space allocation for each layer can be done independently

On a practical point, it gives freedom to work each layer independently and putting them together will be a project nightmare.

2020-05-13T06:34:55Z
author avatar
Top 5Real User

Thank you Gouri !!!

2020-05-20T15:37:34Z
Learn what your peers think about Microsoft Azure Synapse Analytics. Get advice and tips from experienced pros sharing their opinions. Updated: April 2020.
448,076 professionals have used our research since 2012.