Warehouse, Lakehouse, or Both: A Decision Framework

Level 7: Warehouses, Lakehouse & Dimensional Modelingmedium26 minwarehouse vs lakehouse tradeoffsdecision frameworkshybrid hot and cold tieringCASE classificationrequirements reading

Choose warehouse, lakehouse, or a hybrid tier for a workload using a stated framework instead of dogma.

What teams actually run

Start from the deployment reality, because it frames the stakes. In the 2026 State of Data Engineering survey (n = 1,101), 43.8% of teams run a cloud warehouse, 26.8% run a lakehouse, and 11.7% run both. The warehouse still dominates entry-level jobs, and lakehouse literacy is expected on top of it rather than instead of it. Joe Reis predicts the two categories converge by 2027, which is the polite way of saying the argument is already half over.

The five axes

A framework beats an opinion. Five questions decide the target for one workload.

Table

One workload, five axes. Answer each one out loud and the target picks itself.
axis	points to a warehouse	points to a lakehouse
data shape	clean relational tables	JSON, logs, images, wide evolving schemas
consumers	BI tools speaking SQL	many engines: Spark, Python, ML, SQL
governance and ACID	built in, mature, row and column grants	delivered by the table format plus the catalog
scale economics	fine until storage costs warehouse prices	cheap object storage, compute rented per engine
team skills	SQL-first analysts and analytics engineers	engineers comfortable with files and Spark

One workload, five axes. Answer each one out loud and the target picks itself.

Nothing forces one answer for the whole company. The unit of the decision is the workload, not the org chart.

The hybrid pattern deserves its own beat

The 11.7% running both are usually not indecisive, they are tiering. Hot aggregates live in the warehouse where the dashboards are fast, and full history lives as Iceberg or Delta files on object storage, reachable from the warehouse through external tables. On AWS that is the Redshift Spectrum pattern; on Azure it is a Fabric shortcut or a Synapse external table. The learner-visible payoff is that the dashboards stay fast and cheap while nobody has to delete history to afford it.

That is exactly what the Practice exercise asks you to assign: a hot store and a cold store per workload, which is a more honest answer than one word.

Common mistake: picking a side dogmatically. "Lakehouse is the future, warehouses are legacy" is the named junior mistake here, and so is its mirror image. The workload decides, and roughly half the teams in the survey still run a plain warehouse.

Interview nuance: when you get "would you land this in Redshift or in Iceberg on S3", interviewers are listening for a framework plus one tradeoff sentence per axis, not a vendor pitch. Say "it depends per workload, and here is how I decide", then walk the axes: shape, consumers, governance, cost, skills. Finish with the hybrid option, because naming it shows you know the answer is often both.

On a real platform this differs. Here the profile columns are handed to you as one tidy workload_profiles row. In a real intake you interview the consumers to get them, and the two columns people misreport most are the latency SLA (everyone says real time until they see the bill) and time travel (nobody mentions it until an auditor asks for last quarter's numbers as they were reported).

Sample data for this example

CREATE TABLE workload_profiles (
  workload         TEXT,
  daily_gb         INTEGER,
  data_shape       TEXT,      -- relational | semi-structured
  consumers        INTEGER,   -- how many distinct engines or teams read it
  latency_sla_min  INTEGER,   -- minutes of staleness the consumers tolerate
  needs_time_travel INTEGER,  -- 1 = must reproduce an earlier version of the data
  bi_dashboards    INTEGER    -- 1 = powers BI dashboards directly
);
INSERT INTO workload_profiles (workload, daily_gb, data_shape, consumers, latency_sla_min, needs_time_travel, bi_dashboards) VALUES
  ('finance_bi',           40, 'relational',      2,   15, 0, 1),
  ('regulatory_history',  120, 'relational',      2, 1440, 1, 1),
  ('ml_features',         300, 'semi-structured', 4,  240, 1, 0),
  ('clickstream_archive', 900, 'semi-structured', 3, 1440, 1, 0),
  ('ops_dashboards',       15, 'relational',      1,    5, 0, 1),
  ('iot_telemetry',       500, 'semi-structured', 2,   60, 0, 0),
  ('billing_exports',      25, 'relational',      1,  120, 1, 0),
  ('customer_360',         80, 'relational',      4,   60, 1, 0),
  ('event_scorecard',     100, 'semi-structured', 3,   30, 0, 1),
  ('exec_kpi_mart',        55, 'relational',      5,   30, 0, 1);

Worked example (SQL)

-- The intake sheet: one row per workload, one column per decision axis.
SELECT workload, daily_gb, data_shape, consumers, latency_sla_min, needs_time_travel, bi_dashboards
FROM workload_profiles
ORDER BY daily_gb DESC;

Apply

Your turn

The task this lesson builds to.

Write a query that classifies each workload as (workload, target), ordered by workload, over workload_profiles(workload, daily_gb, data_shape, consumers, latency_sla_min, needs_time_travel, bi_dashboards).

Apply these rules in order, first match wins:

'warehouse' when bi_dashboards = 1 and data_shape = 'relational'.
'lakehouse' when consumers > 2 or data_shape <> 'relational'.
'either' otherwise.

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, plus 2 bonus drills.

Write a query that assigns each workload a hybrid tier as (workload, hot_store, cold_store), ordered by workload, over the same workload_profiles table.

hot_store is 'warehouse' when bi_dashboards = 1 or latency_sla_min <= 60, and 'lakehouse' otherwise. cold_store is 'lakehouse' when needs_time_travel = 1 or daily_gb > 100, and 'warehouse' otherwise.

3 hints and 1 automated check are waiting in the workspace.