I'm only going on 4 years now in my data role and have built a data warehouse (more than one database) and a number of data marts from scratch. I have yet to work with OLTP databases though, only OLAP.

The process of building a data warehouse from scratch was a tough one for me being I had not done it before and I was not a seasoned DBA. I had to do a lot of research, speaking with vets and so on to get those projects to where they are now. Even after the many years of starting the project, there is still lots to improve on with every week or month that I either learn a better way of solving a problem or expand on with new features.

I think by far the biggest hurdle for me in designing a new data warehouse or even a new database is clearly outlining the ERD and all the ETL systems together in a holistic view. It's one thing to model one element logically. It's another thing to model multiple elements logically and tie them into other systems as one larger ecosystem. That was tough for me. Felt like someone dropped a bunch of metal, wood, rope on the ground, then we made a bunch of parts and tossed those back on the ground, then tried to figure out how they all pieced together to make that red Ferrari the boss wanted.