What is a data warehouse?

Summary

• A data warehouse is a centralized repository that stores integrated data from multiple sources, optimized for analytics and business intelligence queries rather than operational transactions.
• Data warehouses use a star schema (fact and dimension tables) and OLAP (Online Analytical Processing) tools to enable fast, complex queries across historical data.
• Traditional data warehouses excel at structured, pre-defined reporting but have limitations: they're expensive to scale, inflexible for ad-hoc analytics, and separate from data lakes that store raw data.
• The lakehouse model combines warehouse structure (schema, ACID transactions, SQL) with data lake flexibility (low-cost storage, support for unstructured data, direct access to raw data), offering the benefits of both without the tradeoffs.

What is a data warehouse?

A data warehouse is a centralized database designed to aggregate and organize data from multiple source systems for analytics and reporting. While transactional systems (like CRMs or ERPs) prioritize fast read-write operations on current data, data warehouses are built for complex analytical queries across historical data. They consolidate structured data into a single, governed repository where analysts and business intelligence teams can query, join, and aggregate information to answer business questions.

How data warehouses work

Data warehouses follow a distinct architectural pattern:

1. Data Integration: ETL (Extract, Transform, Load) processes pull data from source systems on a scheduled basis, cleanse and validate it, and load it into the warehouse.
2. Schema Design: Data is organized into a schema, typically a star schema with central fact tables (events, transactions) and surrounding dimension tables (products, customers, time). This structure optimizes for the types of queries analysts run.
3. OLAP Queries: Analysts use SQL or business intelligence tools to query the warehouse, slicing and dicing data across dimensions ("revenue by region and product"), aggregating metrics, and generating reports.
4. Indexing and Optimization: The warehouse maintains indexes and statistics to speed up analytical queries, which often scan large volumes of historical data.

A typical data warehouse workflow:

Step	What Happens	Tool/Technique
Source data ingestion	Extract from CRM, ERP, logs, etc.	ETL, API polling, CDC
Data transformation	Cleanse, validate, join, aggregate	SQL, Python, dbt
Load into warehouse	Write to fact/dimension tables	Batch ELT, scheduled jobs
User queries	Analysts/BI tools query the schema	SQL, BI tools (dashboards, self-service)
Reporting	Generate reports, dashboards, alerts	BI layer (charts, alerts)

The limitations of traditional data warehouses

Data warehouses have served analytics for decades, but they come with tradeoffs:

• High cost at scale: Proprietary warehouse engines charge by compute and storage, making large historical datasets and exploratory queries expensive.
• Rigid schema: Predefined schema design makes it hard to ingest unstructured data (images, videos, text) or adapt to new data sources quickly.
• Separation from raw data: Data warehouses transform and aggregate before storing, discarding raw data. Re-transformation is costly.
• Slow adoption of new tools: Data scientists often bypass the warehouse to land raw data in a data lake, creating a fragmented ecosystem.

The lakehouse approach

The lakehouse combines the best of both worlds: the structure and SQL interface of a data warehouse with the flexibility and cost efficiency of a data lake. In a lakehouse:

• Schema is optional but available: Store raw data in open formats (Parquet, JSON), then apply schema for governed access. Schemas can evolve without re-ingesting.
• ACID transactions: Delta tables (and similar technologies) provide ACID guarantees, so data scientists and analysts can safely read and write in parallel.
• Cost-effective storage: Use object storage (S3, Azure Blob) for raw data; compute and warehouse features add intelligence on top.
• Unified access: Analysts query with SQL (warehouse-like), data scientists use Spark or Python (lake-like), ML engineers access raw features.

Databricks provides a lakehouse platform that combines open formats (Delta tables on cloud storage) with SQL warehouses, enabling organizations to retire separate data lake and warehouse infrastructures and consolidate on a single system.

When to use each approach

Data Warehouse works well for:

• Mature analytics workloads with stable, well-defined schemas.
• Organizations heavily invested in BI tools and self-service reporting.
• Use cases where cost per query matters (small dataset, frequent queries).

Lakehouse works well for:

• Workloads mixing analytics, data science, and machine learning.
• Organizations ingesting new data sources frequently or experimenting with unstructured data.
• Use cases where raw data accessibility and flexibility matter.
• Scaling analytics: object storage provides cost efficiency for large-scale analytics workloads.

FAQs

What's the difference between a data warehouse and a data lake?

A data lake stores raw data in its original format; a data warehouse stores structured, processed data optimized for queries. A lakehouse provides both: raw data in object storage with schema and governance layered on top.

Do I need a data warehouse if I have a data lake?

Not separately. A lakehouse gives you warehouse capabilities (SQL, ACID, schema) on top of your data lake, so you don't need a separate system. If you're building new, a lakehouse provides flexibility and cost efficiency in a single platform.

Can I migrate from a traditional data warehouse to a lakehouse?

Yes. Databricks provides migration tools and patterns to lift data from legacy systems and consolidate into a lakehouse. You retain SQL expertise and BI tools; the infrastructure is simpler and more flexible.

What BI tools work with a lakehouse?

Most BI tools connect via SQL (ODBC, JDBC). Databricks SQL warehouses support connections from major BI platforms, so your existing tools often work without change.