Data Warehouse

A data warehouse provides a single source of truth for turning enterprise data into insightful analysis. Consolidating data into a central repository was supposed to address the challenges of growing data volumes. Yet, data warehousing cannot match the complexity of modern enterprise data.

This guide will explain data warehouses, why they were attractive data analytics solutions, and where they fall short compared to modern data lake analytics architectures.

Data warehouse architecture: How does a data warehouse work?

Data warehouse architectures are meant to centralize data to support business reporting, dashboards, and other analytics projects. This architecture comprises the enterprise’s disparate data sources, pipelines that ingest and process data, and the warehouse solution.

Disparate data sources

Enterprises rely on many different data sources, from the transactional systems that generate sales data to the HR databases that store employee information. However, each source has a unique schema that defines data structure, format, and other factors. Data quality can also vary from source to source. When adding a new source, engineers must develop a data model that defines how to convert data from the source’s schema to the warehouse’s schema.

ETL pipelines

These models inform the design of pipelines that extract, transform, and load data into the warehouse. Every pipeline uniquely addresses the requirements of its data model to ensure new data complies with the warehouse’s schema.

Extract: First, the pipeline copies raw data into a staging area, a temporary place to store data. Staging areas keep poor-quality data out of the warehouse while relieving data sources from the pipeline’s processing workloads.

Transform: Next, the pipeline aligns this new data with the warehouse’s schema by making formats consistent, addressing data quality issues, or generating derived or aggregated values.

Load: Finally, the pipeline moves the transformed data from the staging area into the appropriate warehouse tables.

Data teams may structure pipelines differently, transforming the data after loading it into the warehouse. These ELT pipelines use the data warehouse’s compute power to process data more efficiently.

Data warehouse solution

Data warehouses from providers like IBM, Oracle, or Snowflake store new and historical data in relational databases optimized for data analytics. On-premises solutions offer low latency and stricter enforcement of governance and security rules. Cloud data warehouses provide scalability and better integration with cloud infrastructure.

Benefits of a data warehouse

Data warehouses are centralized data repositories for use cases like forecasting, data mining, and decision support. Giving analysts easy access to enterprise data leads to faster, more robust business decisions. Two benefits in particular drove data warehouse adoption: performance and ease of use.

Performance

The fixed schema of a data warehouse provides consistency that speeds analysis. Table designs let queries run faster while consistent metadata and formatting speed discovery and reduce analysts’ post-query processing.

Ease of use

SQL-like operations let many kinds of data consumers use a data warehouse. Skilled data scientists and engineers can access data directly, leveraging these operations and warehouse APIs to automate business processes or develop predictive machine learning algorithms. Less sophisticated business users access the warehouse through visualization apps like Tableau to create regular or ad-hoc reports for decision-makers.

6 Challenges of a data warehouse

Despite their initial success, warehouses cannot keep pace with today’s rapidly growing data volume and complexity. Proprietary by design, warehouses are challenging to manage and expensive to scale. Worse yet, warehouses are not the “single source of truth” first promised. Here are six challenges organizations face when working with data warehouses:

1. Monolithic architecture

A data warehouse’s fixed schema requires rigorous planning because, once in place, it cannot change. This monolithic architecture limits the kinds of data sources available for analysis. When new questions arise, the warehouse may not yield the answers.

2. Closed ecosystem

Warehouse vendors lock customers into their solutions using proprietary formats, SQL implementations, and APIs. Migrating data to a competing vendor becomes time-consuming and expensive.

3. Expensive to scale

Scaling a warehouse is also expensive since the vendor packages compute and storage within its own single platform. Vendor pricing may bundle compute and storage, so customers must upgrade both when all they need is room for more data. As a result, warehouse expenses tend to grow unpredictably.

4. Patchwork of disparate systems

Data warehouses cannot be the promised single source of truth. Decision-makers need insights drawn from the full range of unstructured, semi-structured, and structured data. Warehouses store structured data, forcing data teams to patch together other systems to support the business.

5. Difficult to maintain

Data pipelines require constant maintenance to ensure changes at the source won’t break something somewhere in the ETL workflow. Since every new source needs a new pipeline, this maintenance challenge only worsens.

6. Slow time to insight

Data teams burdened with maintenance demands need time to patch together warehouses and other sources, delaying analysis and slowing the time to insight data-driven organizations depend upon for effective decision-making.

Related reading:

• Everything I thought I knew about enterprise data warehouses…
• More data warehouse challenges 

The evolution of data warehouses

Data warehouses emerged to address a key question: how to accelerate business analysis? However, the warehouse’s origins ultimately led to the architecture’s many challenges.

OLAP vs. OLTP

Business analysis initially focused on aggregating results from a company’s transactional systems, collected in Online Transactional Processing (OLTP) databases. Optimized to process transactions, OLTP databases make queries extremely inefficient, especially with expanding data volumes.

Data warehouses address this performance issue through an Online Analytical Processing (OLAP) database structure. Although OLAP’s design optimizes query performance, the warehouse architecture’s use of fixed schema for structured data ultimately slows insights.

Data warehouse vs. data mart

Data marts are mini warehouses focusing on data subsets, typically a subject like sales or a department like HR. Copying warehouse data into a data mart simplifies the user experience by eliminating irrelevant data sets. However, data marts may not contain the most current data. Moreover, this extra layer adds management complexity since it requires another set of pipelines.

Data lake vs. enterprise data warehouse

Enterprises adopted data lakes to address enterprise data warehouse limitations. Data lakes are OLAP databases but support formats like ORC and Parquet that store all types of data from any source. Ingestion pipelines are easier to develop and maintain since the lake stores raw data. In addition, data lake platforms run on general cloud storage infrastructure, creating enormous advantages in scalability and affordability.

However, traditional data lakes are storage solutions lacking the accessible analytics of a warehouse. Enterprises often create complex architectures that combine data lakes for storage and warehouses for analysis without eliminating warehouse challenges.

Do you need a data lakehouse? Why open lakehouse?

An open data lakehouse combines the best features of lakes and warehouses into a performant, scalable, efficient, and accessible analytics solution. Decoupling compute and storage on general cloud computing platforms lets data teams optimize investments in each. Self-service features allow end users to perform complex analyses without constant help from data teams.

Starburst’s modern data lake analytics solution goes further by federating all enterprise data sources within a virtual access layer. Ingesting data from rarely used sources or real-time data from Internet of Things (IoT) systems into a data lake may not make sense. However, Starburst’s single point of access allows users to explore and extract insights from the enterprise’s entire storage infrastructure.

Reduce dependence on a single data warehouse

Combining Starburst with a modern data lake breaks vendor lock-in, allowing companies to use open tables and formats to make their data truly portable. Enterprises can escape proprietary systems and build their analytics capabilities upon performant, scalable, and affordable cloud computing platforms.

Companies no longer need complex architectures of lakes, warehouses, and marts with duplicate copies of data and the resulting security and governance concerns. Instead, Starburst lets data teams leave data at the source, reducing the attack surface and improving compliance.

Benefits of a data warehouse as a component of a modern data lake

Starburst and a modern data lake create a data warehouse-like experience without the warehouse’s many challenges. All enterprise data becomes accessible to business users of any skill level. Behind the scenes, Starburst’s massively parallel query engine processes large, complex data sets at scale with optimization features that balance performance and cost. Furthermore, granular access controls streamline the enforcement of governance and security policies within Starburst’s central point of access.