Automating many of the tasks associated with creating and managing a data warehouse has multiple benefits. The most obvious is your data warehouse (or mart or vault, or lake if you will) is created faster.
It is maintained more easily. It frees up developers to do more interesting things (innovation, which people are good at, rather than rote tasks, which software is better at). Most importantly of all, it gets to the whole point of the exercise faster: actionable insights that the business can use. I'm going to share with you some of the advantages of good tools and then provide some pointers on how to choose them.
One of the big advantages of data warehouse automation software is the implementation and enforcement of standards and best practices. Along the way, documentation is taken care of, addressing one of the biggest bugbears faced by teams - when Fred leaves and hasn't documented the work he's done on the data warehouse, what then? No need to worry about that when a good automation tool has taken care of it for you.
You're only human (and so are your developers)
Where software works particularly well is when it is used in tandem with the innate strengths people have. We're very good at coming up with new ideas. People shine when their intellect is allowed room. Developers are clever folk who like to do clever stuff.
But part of being human beings is that we make mistakes on the one hand. On the other, the very cleverness of smart people can, paradoxically, have its drawbacks: ask a dozen developers to build something, guaranteed a dozen variations will come back. That's because, unlike software, we all have preferences. There are many paths to the same or similar outcome and that's perfectly OK.
But when it comes to hand over the outcome, how you got there becomes important. More so if a map, or at least a trail of breadcrumbs, wasn't left behind at the time. In other words, problems can quickly crop up owing to those preferences. Unless, and even if, meticulous documentation is provided, the next developer might find it difficult to pick up where the last one left off.
This goes to the heart of why standards and best practices are desirable. It isn't necessarily for the ˜now"; it is often advantageous for the "later". Even in greenfield projects, this is essential; it is only greenfield once, after all.
Devs love documentation
And while on the subject of documentation, ever noticed how developers love to do it? You haven't, because administrative tasks don't have the same appeal that creative ones do. Documentation not being done is a recurrent problem because nobody likes doing it. Try being the manager insisting that it should happen; it is a job akin to herding cats. Not easy. Also, not pleasant.
Here's what to look for in automation tools
So, when you're looking for warehouse automation tools, start by looking for a solution which:
Generates code which looks and feels the same.
Automatically creates documentation.
Implements standards and best practices, so the baton passes smoothly from one developer to the next.
Manages meta-data, workflow processing and data lineage so you've got the full picture in one place.
Look for a solution, too, which will be welcomed - liked even - by those who are to use it.
Choose a tool that does it all and makes the dev's life easy. If you don't, you'll be bedevilled by multiple tools and hand-offs which will make things slower and more painful.
Most of all, look for a vendor which can demonstrate these things. Get that right, and your ticket to faster value from data warehousing and analytics projects is booked.