Data Warehousing and Data Lakes Questions

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.

I'd do both: land the raw JSON in a lake as-is for full fidelity, and separately parse and flatten a curated subset into warehouse tables for the analytics that actually run every day. Choosing only one of the two means giving something up you'll likely need later.

The trade-off

Keeping only raw JSON in a lake preserves every field, which matters because you genuinely don't know today which fields will turn out to matter for tomorrow's question. But raw, nested JSON is expensive and awkward to query directly at scale: every analyst who wants to use it has to know its parsing logic, and there's no shared, agreed definition of 'this field means X.'

Parsing and flattening everything into warehouse tables gives fast, predictable analytics and lets a business intelligence (BI) tool query it directly, but it's inherently lossy: whatever fields the flattening logic didn't anticipate get dropped, and if the source schema changes, the flattening job has to change too, or new fields silently disappear.

Worked example: the hybrid approach

A workable pattern: ingest the raw JSON into a lake, partitioned by arrival time, and keep it immutable and cataloged so anyone can find and inspect it later. Then build an automated job that extracts the commonly used fields (the ones that power today's dashboards and reports) into a proper warehouse table with an enforced schema, refreshed on whatever cadence the business needs.

For the fields that are rarely used or still evolving, rather than dropping them, keep them accessible as a semi-structured column (most modern warehouses support a JSON-like column type) alongside the flattened table. That way, an analyst doing routine reporting queries the fast, flattened columns, while someone who needs an unusual field can still get at it without going back to raw files in the lake.

The result: day-to-day analytics run fast against curated tables, while the full-fidelity raw data (and the less commonly used fields) stay reachable for the exploratory or forensic case where you need something the flattening logic didn't anticipate.

Trade-offs and pitfalls

The most common mistake is flattening too aggressively, dropping fields nobody's using today, only to need one of them six months later and discover it was never captured going forward. The opposite mistake is refusing to flatten anything and leaving every analyst to write their own JSON-parsing logic against the raw lake data, which reliably produces inconsistent numbers between teams asking what should be the same question.

The decision mostly comes down to two questions: does this workload need managed, low-operational-overhead concurrency for live dashboards, or does it need flexibility and control for heavy, less predictable ad hoc analysis? Different compute engines are built to answer those two questions differently.

What actually drives the decision

Concurrency and latency for live dashboards. Serverless or fully managed engines (a cloud warehouse's native engine, for example) are generally built to handle many simultaneous interactive queries with automatic scaling and little manual tuning, which is exactly what a live-dashboard workload needs.

Flexibility for heavy, varied ad hoc work. Open-source, cluster-based engines (Spark SQL or a distributed SQL engine like Presto or Trino) give you more control over how compute is sized and tuned, which pays off for large, irregular analytical jobs, but that control comes with the responsibility to actually tune cluster sizing and configuration yourself.

Cost model fit. A pay-per-query model suits bursty, unpredictable usage well, since you're not paying for idle capacity between spikes. A cluster-based, always-on model suits steady, predictable usage better, since a well-utilized cluster running constantly can be cheaper per query than paying for each one individually.

Integration with your table format. If your data already lives in a particular open table format, some engines have more mature, first-class support for it than others; a mismatch here means extra connector overhead or missing features, which matters more the more your workload depends on that table format's specific capabilities (like time travel or fine-grained schema evolution).

Worked example

A team serving both a set of executive dashboards refreshed throughout the day and a data science team running large, irregular exploratory queries would reasonably split these across two different engines rather than force both onto one: the dashboards onto a managed, serverless-style engine tuned for concurrency and low operational overhead, and the exploratory workload onto a cluster-based engine where the team can size compute up for an occasional large join without that cost or contention affecting the dashboards.

The underlying reason to split rather than pick one for everything is the same reason warehouse compute gets separated by workload type in general: a live dashboard's latency requirement and a data scientist's occasional huge, unpredictable query have fundamentally different resource profiles, and a single engine tuned well for one is rarely tuned well for the other by default.

Trade-offs and pitfalls

The most common mistake is picking a single engine for the whole organization based on whichever workload was loudest at the time (usually the dashboards) and then being surprised when the data science team's ad hoc queries are either painfully slow or expensive on an engine tuned for concurrency rather than flexibility. The opposite mistake, choosing a flexible cluster-based engine for everything to avoid maintaining two systems, usually means dashboards inherit tuning and operational overhead they shouldn't need, and inconsistent latency the business notices.

There's no universal winner here: a lakehouse consolidates a lake and a warehouse into one platform with transactional guarantees on top of cheap object storage, while keeping them separate lets each system specialize. The decision comes down to how much your organization actually needs unified governance and reproducible pipelines versus how much it benefits from best-of-breed, purpose-built tools on each side.

Where the trade-offs actually show up

Consistency and reliability. A lakehouse adds transactional guarantees (atomic commits, isolation between concurrent writers, versioned snapshots) directly on top of the lake's files, so the same storage that used to be 'append and hope' now behaves more like a database table. A separate lake plus warehouse gets this reliability only on the warehouse side; the lake itself is still just files, so anything reading raw lake data directly inherits the lake's weaker guarantees.

Operational complexity. Two systems means two things to operate, two places for metadata to drift apart, and an ETL (Extract, Transform, Load) or ELT (Extract, Load, Transform) layer whose only job is keeping them in sync. A lakehouse removes that sync step, but it shifts the operational burden onto managing one more sophisticated system well: table maintenance, metadata scaling, and query-engine tuning become your responsibility (or your platform vendor's) instead of being split across two mature, narrower products.

Cost. Object storage underneath a lakehouse is cheap, and compute is decoupled from it, so storage cost stays low even as history accumulates. A separate warehouse usually costs more per byte stored because it's optimized for query performance, not just cheap persistence. Whether that matters depends on how much of your data is 'hot' (queried constantly, where warehouse performance pays for itself) versus 'cold' (rarely touched, where cheap lake storage wins).

Tooling and team skills. A managed warehouse's ecosystem (business intelligence, or BI, connectors, workload management, query optimizer) is mature and requires less specialized tuning. A lakehouse built on open table formats gives you more control and avoids vendor lock-in, but your team needs to actually understand things like compaction and file layout to get warehouse-like performance out of it.

Worked example: how you'd actually decide

A useful decision framework, applied to a mid-size company weighing a lakehouse against replacing (or supplementing) an existing warehouse: start from where the trade-offs show up, not from a platform preference.

• If most of your workload is well-known BI reporting on structured data, and your team is small, a managed warehouse alone is probably the pragmatic choice: you get performance and low operational overhead for the workload you actually have.
• If you increasingly need both governed BI numbers and large-scale ML feature engineering off the same raw history, and you're willing to invest in the operational skill to run it well, a lakehouse removes the duplicate-copy, duplicate-pipeline problem that a two-system setup creates.
• A common middle ground, and the one most organizations actually land on, is a hybrid: raw and semi-structured data lives in the lake (or lakehouse bronze layer), and a smaller, highly curated warehouse (or gold layer) serves the numbers the business depends on daily. This keeps the blast radius of 'the analytics platform is down' small and contained to the curated layer.

Trade-offs and pitfalls

The biggest pitfall is picking the lakehouse for its architectural elegance without budgeting for the operational skill it actually requires: an under-tuned lakehouse (poor file sizing, no compaction discipline) can be slower and less reliable than a plain warehouse, which erases the cost advantage once you factor in the engineering time spent firefighting. The second pitfall is the opposite: keeping two systems 'because that's how we've always done it' long after the sync overhead between them has become the single largest source of data inconsistency complaints.

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.