What is Data Warehouse Automation and What Does It Mean for Your Analytics Team?

In simple terms, Data Warehouse Automation is the process of automating data warehouse development, deployment, and maintenance. This process replaces traditional manual methods with automated ones, speeding up the entire data warehousing process and eliminating manual coding. This is accomplished by utilizing templates, capturing metadata, and automating repetitive tasks,  such as hand-coding functionality in dimensional modeling or in data vault models.

Data Warehouse Automation allows organizations to build data warehouses in a fraction of the time it would take with manual processes. It also minimizes the risk of human errors, as the automation process eliminates the chances of mistakes in coding. Additionally, Data Warehouse Automation facilitates increased agility and scalability of data warehouses, allowing companies to quickly respond to evolving business requirements. Modifications and enhancements can be carried out faster and easier due to the existence of common standards and development processes.

Data warehouse automation adds an effective governance layer on the development process: the templates can be centrally managed to avoid chaos. This chaos is mainly the result of team members using their experience as the best practice available. 

The Traditional Approach

The image below shows the traditional data warehouse development process, from requirements through to logical and physical modelling, architecture, storage and index management, ETL mapping, deployment and eventually maintenance and documentation. Each step has its own tool and its own team, and usually takes from between six weeks and six months or more to complete. Once you get to the end it is very complicated to make retrospective changes. At each step you are changing people and tools. After one team has finished with the code it passes it onto the next, which can often lead to human error and confusion if the completed work and instructions are not explained properly or sufficiently documented.

The Automated Approach

In the image below, the whole process and all its steps are managed in one toolset/framework and performed by the same team (often the same person). Using a common software means every act is taken according to the same standards and conventions. Once connected to your data ecosystem, the Data Automation software quickly scopes all data sources to give a profile of the data available, its quality, location and so on. Prototype architectures can then be spun up using this data in under an hour, and show it to the colleague(s) who requested it and check all requirements are understood before the build phase starts with no misunderstandings or misinterpretations.