How Data Warehouses Work
The logistics of collecting data from different parts of your business to extract useful information can scale in complexity as your business grows. Data warehouses can give your business a reliable way to consolidate that information into a single database and data model to allow analysts to run their queries.
Here’s how it works:
- Extract: Collect raw data from the disparate sources across your organisation (e.g., ERP, CRM, sales, marketing) into staging databases.
- Transform: Data from the staging layer is transferred into an integration layer, where data is combined and transformed into an Operational Data Store (ODS).
- Load: Data is moved from the integration layer into the data warehouse by defining the schema your analysts wish to use for their SQL queries before writing them into a relational database (schema on write).
The database you interact with in a data warehouse is relational, meaning data is structured—stored in tables consisting of columns and rows. These tables are organized by schema that were defined during the write.
When the transformation step is handled by an ODS that is external to the data warehouse, it’s called ETL (Extract, Transform, Load). When the data warehouse handles the transformations internally, it’s called ELT (Extract, Load, Transform). Whether you use ETL or ELT, data warehouses require structured data, and schema on write, to work with relational databases.
What are Data Warehouses Used for?
Common applications of data warehouses include:
- Online Transaction Processing (OLTP): A data warehouse can be optimised for data integrity and fast queries to handle a large volume of short data transactions. An example are transactions that occur on a high-frequency trading platform.
- Online analytical processing (OLAP): You can optimise a data warehouse for faster complex queries for a relatively lower volume of transactions. This is basically what an analyst uses to generate BI reports.
- Predictive analytics: An OLAP system can be optimised to forecast future events and generate “what if” scenarios for your business, often with the help of machine learning algorithms.
Because data warehouses are schema on write, it’s important to know what type of queries you wish to perform before adding schema to a data warehouse. To manage the complexity of disparate data sources, a data warehouse may be segmented into data marts to dedicate hardware and software resources to specific business functions like CRM.
Data Warehouse vs. Data Lake vs. Data Hub
While these three concepts may sound interchangeable, it’s important to understand their differences:
- Data warehouse: A single repository for integrating and storing structured data pulled from multiple unstructured data sources across your organisation.
- Data lake: A single unrefined repository of all the structured and unstructured raw data sources within an organisation (including data warehouses). Data must still be processed to extract BI insights.
- Data hub: A single interface that consolidates all data—both structured and unstructured—into a central accessible data layer. It differs from a data warehouse in that it can also handle operational data and it differs from a data lake by possessing the ability to serve data in multiple formats.
Data hubs provide the data governance needed to streamline data sharing between a diverse collection of endpoints. In this way, data hubs consolidate data lakes and data warehouses into a single access layer. Data processing is abstracted away behind the data hub, giving your organisation a centralized place to extract BI insights.
Why Choose Pure Storage for Your Data Warehouse Needs?
If you need to add a new OLAP or OLTP pipeline to your existing data warehouse infrastructure, it may be time to consider investing in Pure Storage's all-flash storage solutions.
As the industry’s first data hub, Pure Storage® FlashBlade® can not only handle the analytics and reporting workloads of a data warehouse but also deliver on the essential qualities of a data hub:
- Seamless data sharing across all your data endpoints
- Unified file and object storage
- The ability to handle operational data in real time
- Natively architected to scale out
- Engineered to deliver multidimensional performance for any type of data
- Massively parallel from software to hardware
