How Data Warehouse Automation Tools Do (and Don’t) Ease Cloud Moves
In most scenarios, a data warehouse automation (DWA) toolwill assist an organization as it moves an on-premises data warehouse system to a cloud service. Before that happens, however, an organization must standardize on the DWA tool as the primary means of designing, deploying and maintaining its data warehouse system. Because the DWA tool captures and manages all of the work that is performed on the data warehouse, it can bring this work forward to the new warehouse target platform. If this condition is met, the use of a DWA tool can simplify the migration process. This is an ideal on-premises-to-cloud migration scenario. However, if an organization has not already standardized on DWA, a DWA tool is less helpful–at least at first. In this less-than-ideal on-premises-to-cloud migration scenario, a DWA tool can still be helpful, albeit primarily in the context of re-engineering the existing data warehouse system for the new target environment.
Data Warehouse Automation and the Many Modes of Cloud
All IT people grok the distinction between the different types of cloud data warehouse services. To quickly recap: The IaaS data warehouse essentially replicates on-premises infrastructure in a virtualized cloud context; the PaaS data warehouse is a managed cloud service that exposes ease-of-getting-started and ease-of-use features. It likewise abstracts much of the complexity involved in configuring, managing, administering and, for a subset of tasks, maintaining the cloud data warehouse.
Almost all conventional on-premises data warehouse systems are also available as PaaS offerings. Several cloud providers also market PaaS-only data warehouses, the most visible of which are Amazon Redshift, Microsoft Azure Synapse Analytics and Snowflake. In most cases, even if a cloud data warehouse provider bills its service as a “software-as-a-service” offering, it is PaaS. An exception to this is a query-as-a-service (QaaS) platform such as Google BigQuery. Although BigQuery is not the only QaaS platform, it is the only one that is officially supported by commercial DWA tools.
The steps involved in using a DWA tool are mostly the same irrespective of “where” the target data warehouse lives. In other words, using a DWA tool to move an on-premises data warehouse system to the IaaS cloud is a lot like using a DWA tool to assist with an on-premises-to-on-premises migration.
PaaS data warehouse migrations can be more complicated, chiefly because DWA tools officially support only a few PaaS data warehouse environments–usually, Azure Synapse Analytics, Redshift and Snowflake. We will say more about this topic in the third article in this series.
Basically, the DWA tool regeneratesthe existing data warehouse–along with all its assets–for the new target platform. To understand what this means, think of this as analogous to using a compiler to recompile code so that it runs on a completely different instruction set architecture (ISA), to take advantage of the new instructions supported by a specific revision of an ISA.
In “regenerating” the warehouse, the DWA tool automates the following tasks:
From the user’s perspective, the DWA tool automatically regenerates existing views, indexes, models, stored procedures, UDFs, metadata, documentation, and so on for the cloud data warehouse target. The DWA tool also brings forward all the data integration–that is, data engineering–pipelines that feed the warehouse. This gets at an important point: In DWA, data engineering is usually performed in the target data warehouse itself. (This extract, transform, load [ETL] technique centralizes the work of managing and maintaining data engineering logic, as well as scheduling data engineering jobs. It also simplifies migration to other targets.) The DWA tool schedules, tests and validates ETL jobs in the destination IaaS or PaaS data warehouse, too.
This is just to scratch the surface, of course.
