Trade-offs: Data Warehouse vs Lake vs Lakehouse

The Fundamental Trade-off: You are always trading structure and performance for flexibility and cost. A highly structured data warehouse gives you fast queries and strong governance but costs more and requires upfront schema design. A flexible data lake gives you cheap storage and schema on read but slower queries and weaker governance. Lakehouses try to split the difference. When to Choose a Data Warehouse: Use a warehouse when you have high concurrency analytical workloads with low latency requirements. If 500 business analysts need to run dashboard queries during business hours with p95 latency under 10 seconds, a warehouse is the right choice. The compute cost (typically $2 to $10 per compute hour depending on size) is justified by the productivity gain. Warehouses also make sense when you need strong schema enforcement and governance. Financial reporting, compliance dashboards, and any use case where incorrect data has significant consequences benefits from the validation and access control warehouses provide. The typical profile: 70%+ read workloads, queries that scan aggregated or filtered data (not full raw tables), need for sub 5 second interactive latency, and willingness to pay 2x to 5x more for storage to get structured, optimized formats. When to Choose a Data Lake: Use a lake when storage cost dominates and query latency is flexible. If you need to retain 5 years of raw logs for compliance but only query them occasionally, storing in a lake at $0.02 per GB per month versus $0.10 per GB in a warehouse saves 80% on storage. Lakes are also better for exploratory data science where schemas are evolving. Data scientists can dump JSON, CSV, or Parquet files into the lake, iterate on transformations in notebooks, and only move to structured tables once the model stabilizes. The typical profile: mostly write or append workloads, infrequent queries (daily or weekly batch jobs), tolerance for 30 second to 5 minute query latency, schemas that change frequently, or very large volumes (multi petabyte) where storage cost is the primary concern. The Lakehouse Compromise: Lakehouses (like Delta Lake on Databricks or Iceberg) try to give you warehouse performance on lake storage. They add transaction support, schema enforcement, indexing, and caching on top of cheap object storage. This reduces duplication: you store data once in the lake and serve both batch jobs and interactive queries from the same tables. The catch is complexity. You are now responsible for table compaction, managing metadata, tuning file sizes, and handling concurrency control. In a managed warehouse like BigQuery, this is automatic. In a lakehouse, you configure and monitor it. Lakehouses work well when you have strong data engineering teams and want control over storage layout and compute. They are less ideal when you need to onboard hundreds of SQL analysts who expect "it just works" warehouse behavior. Cost Reality Check: For a 100 TB dataset with 10,000 queries per day: Data lake storage: 100 TB × $0.02/GB = $2,000/month. Queries via a compute engine like Spark or Presto might cost $5,000 to $10,000/month depending on query complexity. Data warehouse: 100 TB × $0.10/GB = $10,000/month storage, plus $10,000 to $30,000/month compute depending on concurrency and query patterns. Total $20,000 to $40,000/month. Lakehouse: $2,000/month storage, but $8,000 to $20,000/month for compute and metadata services. Total $10,000 to $22,000/month, sitting between lake and warehouse. The warehouse costs 2x to 4x more but delivers 10x better query latency and concurrency. For business critical dashboards, that is often worth it. For archival data and batch ML training, the lake saves significantly.