On-Premises vs. Cloud Data Warehouses: Pros and Cons
Data warehouses are widely used by organizations of all sizes to ingest, store and process large amounts of data for BI and analytics applications. They emerged in the 1990s and are a mature, mainstream technology. Nowadays, though, one of the big decisions for an organization that’s looking to deploy a data warehouse is whether to put it on premises or in the cloud.
As with other types of IT systems, a cloud data warehouse offers various benefits over an on-premises installation — for example, easy scalability, more flexibility and less routine management work for database administrators (DBAs). But each organization has its own set of needs and priorities, which warrants a comparison of the cloud vs. on-premises options before planning a data warehouse deployment. To help with that, let’s look more closely at the two approaches and their advantages and disadvantages.
A high-quality computing environment — server, OS, storage and database all included — is critical to the success of any application that uses lots of data. That definitely applies to data warehousing: In order to select the best data warehouse platform for their organization, IT and data management teams need to evaluate full system environments, not just the database software at the heart of them. A traditional data warehouse architecture consists of the following three tiers: a bottom tier with a database server that houses the data warehouse itself; a middle one where data is processed for analysis, commonly by an online analytical processing, or OLAP, engine; and a top tier that serves as a presentation layer and front-end interface for BI and analytics tools. An enterprise data warehouse stores data from all of an organization’s business operations in a single, centralized platform; on the other hand, data marts are smaller warehousing systems that contain subsets of data for particular departments, business units or groups of users. Both are often included in a data warehouse architecture, and the following are the two primary methods of designing one — a choice that’s often referred to in shorthand as Inmon vs. Kimball. Top-down approach. Created by computer scientist, author and vendor executive Bill Inmon, this method starts with the enterprise data warehouse and then uses the data sets stored in it to set up various data marts. Bottom-up approach. Consultant Ralph Kimball flipped things around by developing this alternative method, in which separate data marts are built and then integrated to produce an enterprise data warehouse. Using those traditional concepts, the cloud enables data warehouse vendors to customize their underlying hardware and software architectures to meet different processing needs. Here are some prominent examples of cloud data warehouse offerings, listed in alphabetical order. Autonomous Database for analytics and data warehousing. Oracle’s flagship system for analytics data in the cloud is built on top of Oracle Database and the Oracle Exadata computing platform. The system is available in shared or dedicated infrastructure deployments and can also be installed on premises through Oracle’s [email protected] service. Oracle’s shared infrastructure option is a more traditional cloud service, while the dedicated one offers customers a totally private environment in the public cloud with their own compute, storage, network and database resources. Azure Synapse Analytics. Microsoft’s cloud analytics service offers serverless and dedicated resource models and uses a distributed SQL processing engine called Synapse SQL to run data warehouse queries. It also includes Apache Spark as a big data analytics engine and Azure Data Lake Storage Gen2 as its data store. The platform is based on a scale-out massively parallel processing (MPP) architecture that distributes workloads across multiple nodes and separates computing resources from storage, enabling customers to scale each independently. BigQuery.
