Real-time Analytics

Traditional Data Warehouse vs. Cloud Data Warehouse

Differences between traditional and cloud data warehouse architectures.

Feb 16, 2022

A modernised cloud-based data warehouse architecture is designed to address the limitations and challenges of traditional data warehouse architecture. Migrating to a cloud data warehouse will give an enterprise the opportunity to leverage on the cloud’s benefits for data management.

The following section delves deeper into the differences between traditional and cloud data warehouse architectures.

Traditional on-premise Data Warehouse

A traditional Data Warehouse Architecture has unique characteristics like a three-tier architectural approach.

The following three-tier architecture approach is one of the most commonly found approaches to on-premises data warehousing.

Top Tier

The Top Tier consists of the Client-side semantic layer of the architecture. The Transformed and Logic applied information stored in the Data Warehouse will be used and acquired for Business purposes in this Tier. This tier helps business and business support community to perform standard and ad hoc reporting, data exploration, data analytics and mining.

data warehouse architecture

Middle Tier

The Middle Tier consists of the OLAP (Online Analytical Processing) engine. As this tier is located in the middle, it rightfully interacts with the information present in the Bottom Tier and passes on the insights to the Top Tier tools, which process the available information.

Bottom Tier

The Bottom Tier mainly contains Data Sources, ETL Tools, and Data Warehouse.

1. Data Sources

The Data Sources layer consists of the Source Data that is pulled or queried and provided to the Staging and ETL tools for further processing.

2. Extract, Transform & Load(ETL) Tools

  • Extract, Transform & Load (ETL) tools are very vital because they help in combining Logic, source Data, and Schema into one and loads the information to the Data Warehouse Or Data Marts.
  • In Bottom-Up or Kimball's approach, data gets loaded into the Data Marts first in ETL process, and then it is pushed into Enterprise Data Warehouse.
  • In Top-Down or Inman's approach, data gets loaded into the Data Warehouse first in ETL process, and then it is pushed into subjective data marts.

Cloud based Data Warehouse

Cloud-based data warehouse architecture is relatively new when compared to traditional data warehouse approaches. Cloud-based architecture means that the actual data warehouses are accessed through the cloud.

There are wide variety of options, each of which has different architectures for the same benefits of integrating, analyzing, and acting on data from different sources. The difference between a cloud-based data warehouse approach compared to that of a traditional approach include:

  • Up-front Capital Expenses: The different components required for traditional, on-premises data warehouses mandate pricey up-front capital expenses. Since the components of cloud architecture are accessed through the cloud and there is no need to purchase physical hardware, these expenses don’t apply.
  • Operating Expenses: While businesses with on-premise data warehouses must deal with upgrade and maintenance costs, the cloud offers a low, pay-per-use model.
  • Time to market: Cloud-based data warehouse architecture is substantially faster than on-premises data warehouse.
  • Indirect Cost: Potential downtimes and time to market delays.
  • Scalability: The elastic nature of cloud resources makes it ideal for scaling of big datasets. Additionally, cloud-based data warehousing options allows you to scale up, down, out and in.

Some of the more notable cloud data warehouses in the market include ByteHouse Cloud Data Platform, Amazon Redshift, Google BigQuery, Snowflake, and Microsoft Azure SQL Data Warehouse.

You can test drive ByteHouse cloud platform here. Or, find out about the architecture design consideration behind ByteHouse.

Related articles

Real-time Analytics

Difference between ROLAP, MOLAP and HOLAP in data warehouse

Real-time Analytics

Difference between ROLAP, MOLAP and HOLAP


Schedule Queries/Data Loading with ByteHouse & Airflow