Course contentsShow
← System Design
Lesson 762 of 1,91920. Data Pipelines and ETLFree lesson

Query Performance Tradeoffs

Warehouse pre-aggregation and indexing vs lake flexibility, and when each approach delivers better performance.

Query Performance Tradeoffs

You'll learn when pre-aggregated warehouse data beats flexible lake storage — and when raw flexibility wins.

Now that you understand warehouses and lakes architecturally, let's see how their design philosophies create different performance tradeoffs.

The Core Tradeoff

Data Warehouses use pre-aggregation and indexing to optimize query speed. They store summarized data (like monthly sales totals) and build specialized indexes on frequently-queried columns. Think of it like a restaurant with a set menu — the chef pre-prepares popular dishes, so service is lightning-fast for those specific items.

Data Lakes prioritize flexibility by storing raw, unprocessed data. They let you ask any question, slice data any way imaginable — but each query might need to scan massive raw datasets. It's like a kitchen with unlimited ingredients but no pre-made meals; you can cook anything, but it takes longer.

When Each Approach Wins

Warehouse wins for:

  • Predictable queries: dashboards, monthly reports, KPIs you run repeatedly
  • Speed requirements: sub-second executive dashboards
  • Pre-aggregated metrics: "total revenue by region" when you've already rolled up the data

Lake wins for:

  • Exploratory analysis: data scientists discovering patterns
  • Unpredictable questions: ad-hoc queries you can't anticipate
  • Full historical detail: when you need every raw event, not just summaries

The Reality

Modern systems often blend both: compute aggregations in the warehouse for known queries, but keep the raw lake accessible when someone asks an unexpected question. The warehouse gives you speed for the 80% of queries you know about; the lake gives you flexibility for the 20% you don't.

Key Takeaway: Warehouses trade flexibility for speed through pre-aggregation and indexing; lakes trade speed for flexibility by keeping raw data — choose based on whether your queries are predictable or exploratory.