Can certainly see that ETL's are no joke from your post, I shall copy and paste it in an email to my manager the next time he questions my estimates for a new ETL process 🙂
I did a similar thing with the schemas to DuvetSalt, having Landing, Staging, Dim, Fact schemas for the data and Process and Admin schemas for sprocs, admin tables etc. I then created a Processor role which has access to all the schemas and a Reporter role which only has access to Dim & Fact schemas.
I also have 3 file groups, a default one where all the sys objects and my sprocs etc. go. A Load filegroup where Landing and Staging objects reside and a Data filegroup which is spread across multiple files where Dim and Fact objects sit. The Data filegroup is read-only when the ETL is not running. I read somewhere that if the tables reside on a read only filegroup then SQL doesn't have to worry about locking which can help with performance.
