Enterprise data warehousing: architecture, types, best tools & selection tips

The major difference lies in the data volume stored within the data warehouse and the complexity of logic behind it, inсluding the number of data models. Generally, an enterprise data warehouse houses cross-functional business data and serves the needs of all business departments, whereas a traditional data warehouse serves the needs of a particular department/departments (similar to data marts). However, it’s worth mentioning that these terms can also be used interchangeably.

Data marts (sometimes referred to as ‘traditional’ or ‘usual’ data warehouses) are actually subsets of an enterprise data warehouse. They have the same functionality as enterprise data warehouses – collecting data from different sources and making it available for analysis. However, data marts have a narrower scope as they are designed to meet the needs of particular departments or lines of business. This means that they collect only the data that its users (sales, marketing, HR, etc.) need. All in all, data marts are usually created for the sake of speed, since their requests are more specific and their queries run against a smaller amount of information.

Operational data stores contain up-to-date information in its original format consolidated from disparate operational sources and enable real-time reporting. ODS are not substitutes for a data warehouse but rather complement the whole enterprise data warehousing environment. They also have some significant differences from EDW:

• Data timeliness
  Enterprise data warehouses store both historical and current data, while ODS storage capabilities are limited to maintaining the most recent records, making it a go-to tactical tool.
• Data aggregation
  Enterprise data warehouses host information after it undergoes particular transformations (cleansing, enrichment, reformatting, etc.), while ODS keeps information in its raw state, which ensures superior speed of analysis and reporting.
• Query complexity
  Enterprise data warehouses are built for running complex analytics queries on huge volumes of information, while ODS is used for relatively simpler queries run on real-time data – for example, to quickly identify the reason behind a failed transaction.

A data lake is a centralized repository that maintains all types of data in its raw or pre-processed format, while enterprise data warehouses store only highly-structured data according to predefined data models. Because of it, data lakes can store a near-infinite volume of data at a relatively low price until this information is used. The use cases of these two repositories also differ - while the enterprise data warehouse serves as a central BI component, the data in the data lake is aimed at data scientists and engineers, who use it to train ML and run predictive and big data analytics.

A data lakehouse is a hybrid storage solution that houses all data types and has strong metadata management capabilities, which allows it to cover both BI use cases (as any DWH) and big data analytics and ML workloads (as a data lake). Data lakehouses are more cost-effective than DWHs, as the data is maintained in cheaper repositories, which makes them a preferable option in many scenarios and for various data volumes.

A virtual data warehouse is an alternative to a traditional enterprise data warehouse, which implies creating a virtual layer on top of multiple databases where the data is stored, thus no data is moved physically. A virtual data warehouse is a good option for companies that store their data in a standardized form, which doesn't require complex transformations.