Data Warehousing and Data Lakes Questions

Deciding whether to actually move a workload between managed cloud warehouses comes down to whether the new platform meaningfully improves something the current one is failing at (performance, cost, or a capability gap), because a migration is expensive enough that 'roughly comparable' isn't a good enough reason to do it.

What I'd actually look at

Performance for your real workload, not a generic benchmark: how the specific queries your dashboards and reports run today perform on the candidate platform, at your actual data volumes and concurrency levels.

Cost, modeled against your real usage pattern, not list pricing: whether your workload is steady (favoring predictable, reserved-style pricing) or bursty (favoring on-demand, pay-per-query pricing), since the two platforms may price these patterns very differently.

Feature and ecosystem fit: whether your existing Business Intelligence (BI) tools, orchestration, and data-loading tools connect natively, and whether any platform-specific feature your team actually relies on today (a particular semi-structured data type, a specific data-sharing capability) has an equivalent on the other side.

Operational overhead: how much day-to-day tuning, maintenance, and specialized expertise each platform demands from your team, since a technically superior platform that needs skills your team doesn't have yet has a real, ongoing cost.

Worked example: structuring the pilot

Rather than a broad, open-ended trial, I'd scope a pilot around a small number of representative, real workloads, not synthetic benchmarks: for instance, one high-concurrency dashboard, one heavy nightly aggregation job, and one workload involving your messiest semi-structured data.

Run both platforms side by side against the same real queries and data for a few weeks, and measure the things that actually matter for the decision: query latency at your typical concurrency, the modeled cost for your specific usage pattern (not vendor list pricing), and how much manual tuning or troubleshooting effort each platform required from your engineers to hit acceptable performance.

At the end, the honest output isn't just 'platform B was 20 percent faster'; it's whether that improvement, weighed against migration cost, cost difference, and any features you'd gain or lose, clears the bar to justify moving a live production workload.

Trade-offs and pitfalls

The most common mistake is running the pilot on a clean, synthetic dataset that doesn't reflect your real data's messiness (skew, semi-structured fields, unusual query patterns), which can make a migration look like a clear win in the pilot and then underperform once real production traffic and data quirks hit it. The second is underweighting operational overhead: a platform that benchmarks faster but requires ongoing specialized tuning your team doesn't have the bandwidth for can end up costing more in engineering time than it saves in compute cost.

For this kind of production ML system, I'd reach for a data lake when the value is in raw, high-volume, or not-yet-modeled data (like clickstream events or sensor telemetry), and for a data warehouse when I need consistent, trustworthy business numbers that multiple teams and models rely on. Most production setups actually use both, at different points in the pipeline.

Where each one fits

Raw clickstream logs and sensor telemetry are exactly the kind of data a lake is built for: high volume, evolving structure, and genuinely useful in their raw form for feature engineering that hasn't been designed yet. Forcing a rigid schema onto this data before you know which features will matter would throw away information a model might need later. So the lake is where this raw signal lands and where a data scientist iterates on feature extraction.

Structured sales records, and any aggregate or feature that multiple models or business reports depend on, benefit from living in a warehouse (or a curated layer within a lakehouse): consistent definitions, enforced schema, and fast, predictable queries. If two different models are both training on 'customer lifetime value,' you want one trustworthy, versioned definition of that feature, not each model computing it slightly differently from raw data on its own.

Worked example

Consider a credit-risk model that needs three kinds of input: raw event traces (how a customer actually used the product), derived features (aggregates like 'average transaction size over the last 90 days'), and aggregated business reports (portfolio-level risk exposure for compliance). The raw traces belong in the lake, kept in full fidelity because a future model version might need a feature nobody has thought of yet. The derived features belong in a curated layer, computed once and reused across model versions and teams, specifically so two different models don't silently disagree about what '90-day average transaction size' means. The aggregated business reports belong in the warehouse, because compliance needs one consistent, auditable number, not a query result that could differ depending on who ran it.

As the model matures and gets retrained regularly, the important shift is from 'training directly off raw lake data every time' to 'training off a stable, versioned feature layer,' precisely so retraining is reproducible and doesn't silently change behavior because someone tweaked an upstream raw-data query.

Trade-offs and pitfalls

The most common mistake is letting every model query raw lake data directly for anything it needs, which works early on but quietly creates a different, slightly inconsistent version of the same feature in every model. The second is over-correcting the other way: forcing every piece of raw signal through a heavyweight curation pipeline before a model can touch it, which can slow down legitimate exploration of genuinely new signal. The right balance is usually raw access for exploration and prototyping, and a curated, shared layer for anything that ships to production or that more than one model depends on.

Schema-on-write means you define and enforce a schema before data is loaded; anything that doesn't fit gets rejected or transformed at load time. Schema-on-read means you store the data as-is and only apply structure when a query actually reads it. The trade-off is agility and ingestion speed versus upfront quality guarantees.

What you gain and give up

Worked example

Say a new upstream system starts sending event data tomorrow, and nobody has fully agreed on its final field list yet. Under schema-on-write, you'd have to design a table schema, build and test a load job, and only then let anyone query it: useful once it's done, but it blocks any exploration until that's finished. Under schema-on-read, you land the raw events immediately and let an analyst start poking at them the same day, at the cost of not knowing yet whether every record is well-formed.

In practice, the two aren't mutually exclusive within one platform. A common pattern is to apply schema-on-read at ingestion (land raw data immediately, so nothing blocks exploration) and then promote a schema-on-write, validated version of the same data for anything the business depends on daily. That gives you the ingestion speed of one model and the quality guarantee of the other, on the same underlying data, at different points in its lifecycle.

Trade-offs and pitfalls

The most common mistake is picking one model as a blanket policy for an entire platform instead of matching it to the actual use case: forcing every new source through a heavyweight schema-on-write pipeline slows down legitimate exploration, while leaving everything permanently schema-on-read (never promoting anything to a validated layer) means business-critical reports are only as reliable as whatever query someone happened to write. The second pitfall is assuming schema-on-read has no cost: without a curated layer or documented conventions, different analysts querying the same raw data can apply subtly different parsing logic and land on different numbers for what should be the same metric.

A data lakehouse is an architecture that adds warehouse-like reliability directly on top of a data lake's cheap, flexible storage, so you don't have to choose between the two or maintain both separately. It exists because neither pure lake nor pure warehouse fully served a team that needed both governed business intelligence (BI) reporting and large-scale ML work off the same data.

What it borrows from each side

From the data warehouse, a lakehouse borrows the guarantees that make data trustworthy for reporting: atomic writes (a query never sees a half-finished update), consistent snapshots you can query repeatably, and enough schema enforcement that a malformed record doesn't silently corrupt downstream numbers. These guarantees are commonly summarized as ACID (atomicity, consistency, isolation, durability), the same property a traditional warehouse or database provides.

From the data lake, it borrows the storage model: cheap, elastic object storage that can hold structured, semi-structured, and unstructured data at scale, with compute that's decoupled from storage so you're not paying warehouse-grade compute prices just to keep data sitting around.

The technical enabler that makes this combination possible is an open table format sitting on top of the raw files (this is a distinct, deeper topic on its own; the point here is only that some layer has to track which files currently make up a table's consistent state, so readers and writers agree on what 'the table' looks like at any moment, the same job a database's transaction log does).

Worked example

Before lakehouses, a team doing both business intelligence (BI) and ML typically ran two pipelines: raw events landed in a lake, and a separate ETL (Extract, Transform, Load) job copied a cleaned subset into a warehouse for reporting. Two copies of the data existed, and keeping them consistent, especially when correcting historical records, was manual and error-prone.

With a lakehouse, the same physical storage serves both: the BI team queries a curated, schema-enforced view of the data with the same reliability a warehouse would have offered, while the ML team can read the exact same underlying files at an earlier raw or intermediate stage for feature engineering, without waiting for a second copy to be built and synced.

Trade-offs and pitfalls

The main thing people get wrong is treating 'lakehouse' as a product you buy rather than a pattern you have to implement well: simply landing files in object storage with a table format on top doesn't automatically give you warehouse-grade query performance. You still need file-layout discipline (avoiding a huge number of tiny files, keeping tables compacted) to get the performance the pattern promises. It's also not a free upgrade over a mature managed warehouse for a team whose workload is purely well-known BI reporting; the benefit shows up specifically when you need both reliable reporting and large-scale, flexible ML work off the same underlying data.

Separating compute from storage means the query engines that process data and the system that persists it are two independent components that can each scale on their own, rather than being bundled into one tightly coupled machine. For a cloud warehouse, this buys cost flexibility and concurrency; the real trade-off it introduces is that not every situation benefits from that decoupling.

What the separation actually buys you

Cost flexibility. Storage is billed continuously and is comparatively cheap, since it's just persisted, compressed data sitting in object storage. Compute only needs to run while queries are actually executing, so a team can spin up compute for a big nightly job and shut it down afterward, rather than paying for a fixed, always-on machine sized for peak load.

Concurrency. Because compute is decoupled from storage, you can run multiple independent compute clusters (or virtual warehouses) against the same underlying storage at once. A heavy overnight batch job and a set of interactive business intelligence (BI) users can each get their own compute, reading the same data, without competing for the same CPU and memory the way they would on a single coupled system.

Independent scaling. Storage scales with how much data you keep; compute scales with how much querying you're doing right now. Decoupling them means you're not forced to over-provision one to satisfy the other, for example buying more compute than you need just because your data volume grew.

Worked example

Say a warehouse holds five years of order history (large, cheap-to-store data) but most days only a handful of business intelligence (BI) analysts run moderate queries against the last quarter. With compute and storage separated, storage cost stays roughly proportional to the five years of data (cheap, since it's just sitting there), while compute cost stays proportional to actual query activity, a small, right-sized cluster most days, scaled up temporarily if someone runs a big historical analysis. With a tightly coupled system, you'd effectively have to size one machine for both the full data volume and for the occasional heavy query burst, paying for capacity you rarely use.

The one trade-off worth naming honestly: separating compute and storage generally introduces a network hop between them, since compute isn't reading off locally attached disk anymore. For most analytical workloads that's a fine trade for the flexibility gained. It matters more for a workload with extremely tight, latency-sensitive access patterns, where avoiding that network round trip and keeping compute and storage physically co-located would actually perform better.

Trade-offs and pitfalls

The most common misunderstanding is treating this as a free win with no downside: for genuinely latency-critical, tightly-coupled access patterns, like an embedded system reading and writing to local storage with sub-millisecond expectations, the network overhead of a decoupled architecture is a real cost, not a rounding error. For the vast majority of analytical BI and reporting workloads, though, the concurrency and cost benefits outweigh that overhead by a wide margin, which is exactly why every major cloud warehouse has moved to this model.