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:
Image (above): 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 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 |
|---|---|---|
| Data | Relational data from transactional systems, operational databases, and line of business applications |
All data, including structured, semi-structured, and unstructured |
| Schema | 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) |
Price/Performance |
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) |
| Users | Business analysts, data scientists, and data developers |
Business analysts (using curated data), data scientists, data developers, data engineers, and data architects |
| Analytics | 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 |
|---|---|---|
| Scope | Centralized, multiple subject areas integrated together |
Decentralized, specific subject area |
| Users | Organization-wide |
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 |
Size |
Large, can be 100's of gigabytes to petabytes |
Small, generally up to 10's of gigabytes |
| Design | Top-down |
Bottom-up |
| 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.
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
- Follow this step-by-step guide and deploy an Amazon Redshift data warehouse »
- Get started with our 2-month free trial »