Data Warehouse Concepts

What is a data warehouse?

A data warehouse is a central repository of information that can be analyzed to make more informed decisions. Data flows into a data warehouse from transactional systems, relational databases, and other sources, typically on a regular cadence. Business analysts, data engineers, data scientists, and decision makers access the data through business intelligence (BI) tools, SQL clients, and other analytics applications.

Data and analytics have become indispensable to businesses to stay competitive. Business users rely on reports, dashboards, and analytics tools to extract insights from their data, monitor business performance, and support decision making. Data warehouses power these reports, dashboards, and analytics tools by storing data efficiently to minimize the input and output (I/O) of data and deliver query results quickly to hundreds and thousands of users concurrently.

How is a data warehouse architected?

A data warehouse architecture is made up of tiers. The top tier is the front-end client that presents results through reporting, analysis, and data mining tools. The middle tier consists of the analytics engine that is used to access and analyze the data. The bottom tier of the architecture is the database server, where data is loaded and stored. Data is stored in two different types of ways: 1) data that is accessed frequently is stored in very fast storage (like SSD drives) and 2) data that is infrequently accessed is stored in a cheap object store, like Amazon S3. The data warehouse will automatically make sure that frequently accessed data is moved into the “fast” storage so query speed is optimized.

How does a data warehouse work?

A data warehouse may contain multiple databases. Within each database, data is organized into tables and columns. Within each column, you can define a description of the data, such as integer, data field, or string. Tables can be organized inside of schemas, which you can think of as folders. When data is ingested, it is stored in various tables described by the schema. Query tools use the schema to determine which data tables to access and analyze.

What are the benefits of using a data warehouse?

Benefits of a data warehouse include the following:

  • Informed decision making
  • Consolidated data from many sources
  • Historical data analysis
  • Data quality, consistency, and accuracy
  • Separation of analytics processing from transactional databases, which improves performance of both systems

How do data warehouses, databases, and data lakes work together?

Typically, businesses use a combination of a database, a data lake, and a data warehouse to store and analyze data. Amazon Redshift’s lake house architecture makes such an integration easy.

As the volume and variety of data increases, it’s advantageous to follow one or more common patterns for working with data across your database, data lake, and data warehouse:

Land data in a database or datalake, prepare the data, move selected data into a data warehouse, then perform reporting

Image (above): Land data in a database or datalake, prepare the data, move selected data into a data warehouse, then perform reporting.

Land data in a data warehouse, analyze the data, then share data to use with other AWS Analytics products

Image (above): Land data in a data warehouse, analyze the data, then share data to use with other analytics and machine learning services.

A data warehouse is specially designed for data analytics, which involves reading large amounts of data to understand relationships and trends across the data. A database is used to capture and store data, such as recording details of a transaction.

Unlike a data warehouse, a data lake is a centralized repository for all data, including structured, semi-structured, and unstructured. A data warehouse requires that the data be organized in a tabular format, which is where the schema comes into play. The tabular format is needed so that SQL can be used to query the data. But not all applications require data to be in tabular format. Some applications, like big data analytics, full text search, and machine learning, can access data even if it is ‘semi-structured’ or completely unstructured.

Data warehouse vs data lake

Characteristics Data Warehouse Data Lake

Relational data from transactional systems, operational databases, and line of business applications

All data, including structured, semi-structured, and unstructured


Often designed prior to the data warehouse implementation but also can be written at the time of analysis

(schema-on-write or schema-on-read)

Written at the time of analysis (schema-on-read)


Fastest query results using local storage

Query results getting faster using low-cost storage and decoupling of compute and storage

Data quality

Highly curated data that serves as the central version of the truth

Any data that may or may not be curated (i.e. raw data)


Business analysts, data scientists, and data developers

Business analysts (using curated data), data scientists, data developers, data engineers, and data architects


Batch reporting, BI, and visualizations

Machine learning, exploratory analytics, data discovery, streaming, operational analytics, big data, and profiling

Data warehouse vs database

Characteristics Data Warehouse Transactional Database

Suitable workloads

Analytics, reporting, big data

Transaction processing
Data source Data collected and normalized from many sources

Data captured as-is from a single source, such as a transactional system

Data capture

Bulk write operations typically on a predetermined batch schedule

Optimized for continuous write operations as new data is available to maximize transaction throughput

Data normalization

Denormalized schemas, such as the Star schema or Snowflake schema

Highly normalized, static schemas

Data storage

Optimized for simplicity of access and high-speed query performance using columnar storage

Optimized for high throughout write operations to a single row-oriented physical block

Data access

Optimized to minimize I/O and maximize data throughput

High volumes of small read operations

How does a data mart compare to a data warehouse?

A data mart is a data warehouse that serves the needs of a specific team or business unit, like finance, marketing, or sales. It is smaller, more focused, and may contain summaries of data that best serve its community of users. A data mart might be a portion of a data warehouse, too.

Data warehouse vs data mart

Characteristics Data Warehouse Data Mart

Centralized, multiple subject areas integrated together

Decentralized, specific subject area



A single community or department

Data source

Many sources

A single or a few sources, or a portion of data already collected in a data warehouse


Large, can be 100's of gigabytes to petabytes

Small, generally up to 10's of gigabytes




Data detail

Complete, detailed data

May hold summarized data

How can a data warehouse be deployed on AWS?

AWS allows you to take advantage of all of the core benefits associated with on-demand computing: accessing seemingly limitless storage and compute capacity, scaling your system in parallel with your growing amount of data collected, stored, and queried, and paying only for the resources you provision. AWS offers a broad set of managed services that integrate seamlessly with each other so that you can quickly deploy an end-to-end analytics and data warehousing solution.

The following illustration shows the key steps of an end-to-end analytics process, also called a stack. AWS offers a variety of managed services at each step.

AWS offers a variety of products and services at each step of the analytics process

Image (above): AWS offers a variety of products and services at each step of the analytics process.

Amazon Redshift is our fast, fully-managed, and cost-effective data warehouse service. It gives you petabyte-scale data warehousing and exabyte-scale data lake analytics together in one service, for which you only pay for what you use.

Next steps