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:
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.
For an in-depth comparison between data warehouses and data lakes, visit our dedicated comparison page for datawahouse vs data lake.
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.
For an in-depth comparison between data mart and data warehouses visit our dedicated comparison page for data mart vs data warehouse.
How can AWS support your data warehouse efforts?
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.
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.
Get started with data warehouse on AWS by creating an account today.
