What is a data warehouse?

A data warehouse is a collection of data gathered from different sources into a single, central location so that it can be compared and analyzed. Data could come from internal applications like those used by marketing, sales, and finance departments, from customer-facing websites and applications, and from external systems used by partners and vendors.

With data warehouses, analytics and decision support are the names of the game. By building a vast, historical repository of data, these systems can support data analysis and data mining as well as advancements like artificial intelligence (AI) and machine learning (ML). With massive volumes of data in one place, organizations can run in-depth analytics in ways that a standard, smaller database can’t support.

Data warehouses were first conceptualized in the 1980s to start helping organizations use data not just for powering operations but also for increasing business intelligence and helping teams make decisions. Traditionally, they were physically hosted on-premise using something like a mainframe computer. Today, many data warehouses are hosted in the cloud.

What a data warehouse is not.

To better understand what a data warehouse is, it can be helpful to look at what it is not. 

A data warehouse is not a database
While both data warehouses and databases are collections of data, they operate on different scales and for different purposes. A database is designed for fast queries and transaction processing, while a data warehouse is designed for analytics. Databases are for a focused set of data on a particular topic or for a specific application. Data warehouses can store data from any and all applications and systems across an organization. You could think of a data warehouse as a collection of databases. 

A data warehouse is not a data lake
When a data warehouse gathers raw data, it structures it using predefined schemas designed to optimize the data for analytics purposes. It organizes the data into files or folders so it is ready for reporting. A data lake does not. In essence, a data lake is a data warehouse without predefined schemas. A data lake stores all types of data — raw, structured, and unstructured — in whatever native format it originally appeared.

A data warehouse is not a data mart
The differences between data marts and data warehouses center on scope. A data mart is typically limited in its application, while data warehouses are bigger and have a wider variety of data. Data marts can be subsets of data warehouses.

Why is a data warehouse important?

Data warehouses lead to higher quality analytics and greater business intelligence. By pulling data into one, organized location, organizations can analyze larger amounts of varied data and gain valuable insights that improve business processes and experiences. Data warehouses also keep a continual historical record that organizations can access at any time. Without a central location for data, like a data warehouse, data integration would be extremely difficult. 

Benefits of data warehouses.

Higher quality data
Data warehouses provide a more complete picture of data. Centralizing data from many different sources, cleansing it, eliminating duplicates, and standardizing data sets creates a single source of truth and elevates the quality and usefulness of the data collected. 

More rapid insights
When data has to be manually fetched from many different locations, creating reports can be a time-consuming process. Data silos also mean data can be missed, leaving decision makers to operate without a complete data set. Data warehouses make assembling reports and data analysis a quicker, simpler process. 

Increased business intelligence
Using a data warehouse helps an organization support large-scale business intelligence operations. ML, AI, and data mining all rely on access to large volumes of data. With these tools, leaders can make smarter decisions based on a more complete view of their organization. Streamlining internal processes, managing finances, even scaling inventory require greater business intelligence, and that comes from centralized data. 

How does a data warehouse work?

The exact architecture of a data warehouse can vary depending on an organization’s unique needs, but each warehouse follows the same general structure. Think of a data warehouse as a three-layer cake. 

Layer 1
The first layer is a data warehouse server that gathers, cleans, and transforms data from many different sources. The server uses Extract, Transform, and Load (ETL) tools to bring data together into a standardized format. 

Layer 2
In the middle of this metaphorical cake lies an online analytical processing (OLAP) server. This server is responsible for powering fast queries. 

Layer 3
The final layer is a front-end user interface that makes it possible for individuals to create reports and perform data analysis. 

Data warehouse architecture. 

Even though data warehouses use the same general structure, there are different approaches organizations can take to the architecture. Some of those approaches include: 

Simple data warehouse
This is the most basic architecture. Data comes into the warehouse where it is stored in a central repository. End users then access that data for reporting and analysis.

Simple data warehouse with a staging area
Before data is stored in the warehouse, it has to be cleaned and transformed. This process can take place in the warehouse, but some organizations choose to add a staging area to the data flow. There, data is prepared before it enters the warehouse proper.

Hub and spoke data warehouse
This approach adds data marts between the data warehouse repository and end users. Doing so allows organizations to customize the data warehouse for specific lines of business.

Types of data warehouses.

Organizations commonly use three types of data warehouses:

On-premise/license data warehouse software
Organizations can buy a data warehouse license and then use their current on-premise infrastructure for deployment. This option can be more expensive and more difficult to scale than other data warehouse types because businesses are managing hardware and software internally, but it does offer a great degree of control over data security and privacy. 

Data warehouse appliance
An organization can purchase a pre-integrated bundle of both hardware and software that connects to its existing network. This bundle is referred to as a data warehouse appliance. They offer the convenience of “plug and play” data warehousing, and organizations can start using all the elements as-is. 

Cloud data warehouse
Cloud-based data warehouses are built as managed services that run in the cloud. Organizations don’t have to worry about physical data warehouse infrastructure, and all solution maintenance is managed by the service provider.

How do different industries use data warehouses?

Every industry generates and collects data. When organizations within those industries use a data warehouse, they gain greater insights and can use that knowledge to grow and become more successful. Here are a few of the ways improved analysis using data warehouses supports various industries. 

Retail and consumer goods and services

• Understanding the effects of marketing campaigns on customer behavior
• Forecasting trends or peak purchase times
• Identifying the need for new product features
• Improving supply chain and manufacturing processes

Finance

• Predicting market trends
• Analyzing the feasibility of potential mergers and acquisitions
• Viewing cardholder spending patterns
• Managing collections departments
• Identifying and assessing the potential risk of default

Education

• Gathering information for grant proposals and annual reports
• Tracking student demographics
• Course scheduling

 

Government

• Determining the effectiveness of social programs
• Tracking patterns in the justice system including criminal activity
• Auditing tax records
• Demographic patterns for government services

Healthcare

• Analyzing how well hospitals are providing patient care and services
• Comparing costs of services across departments and hospital properties
• Gathering patient statistics for research
• Tracking continuing education and certification for staff
• Discovering best practices that improve care and reduce operation costs

How will data warehouses evolve in the future?

Data warehouses alone can sometimes be rigid, and it can be challenging to deploy applications that drive business impact. But, when leveraged properly, data warehouses can function as an integral component to a company’s BI engine.

The future of data warehouses lies in the cloud. With many organizations making the move to the cloud for operations, data warehouses will follow suit. Cloud data warehouses (CDW) are accessible from anywhere, make collaboration simple, and offer a flexible, scalable solution to storing data. Additionally, deploying cloud-based systems is cost-effective and quick.

With the right multi-cloud framework, a data warehouse can be more flexible and provide IT with the freedom to assign workloads to specific tasks — like indexing, queries for visualizations, and data transformations — without needing to move data. 

Cross-cloud data warehouses are becoming more prevalent due to the complexity of cloud migration and enterprise digital transformation initiatives. Data lifecycles must be minimized in order for CDW investments to be maximized. This includes providing business users with access to specific warehouse environments with the right data governance processes in place.

Organizations that already have an on-premise data warehouse or use a data warehouse appliance and are hesitant to make the full jump to the cloud can still benefit from cloud-based services. Tools like Domo integrate with existing data warehouses or data lakes and make that data available for business analysis.