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.

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.

A data warehouse stores curated, structured data that's been cleaned and modeled ahead of time so business queries run fast and consistently. A data lake stores data closer to its raw form (structured, semi-structured, or unstructured) and defers structure until someone actually reads it. The practical consequence: a warehouse trades flexibility for speed and consistency; a lake trades speed and consistency for flexibility and scale.

The core comparison

The underlying reason for both approaches to exist is workload shape, not one being a strictly better version of the other. A warehouse is built around Online Analytical Processing, meaning many people running similar, well-known aggregation queries against a stable schema. A lake is built around the opposite assumption: you don't fully know the query shape yet, or the data doesn't have a stable shape to begin with.

Worked example

Say a company wants two things: a finance dashboard showing daily revenue by region, and a churn model trained on raw clickstream and support-ticket text.

The finance dashboard is Online Analytical Processing (OLAP) focused: the questions are known in advance ('revenue by region, by day'), the source data (orders, refunds) is already structured, and the business needs a single trustworthy number every day. That's a warehouse: model orders and refunds into a small star schema, enforce the schema so a bad refund record can't silently corrupt the total, and let the BI tool query modeled tables directly.

The churn model is ML-focused: the useful signal might be in raw event sequences or unstructured ticket text that nobody has modeled yet, the feature set will change every time someone retrains, and enforcing a rigid schema up front would throw away exactly the raw detail the model needs. That's a lake: land the raw events and ticket text as-is, and let the data science team iterate on feature extraction without waiting on a schema migration each time.

Trade-offs and pitfalls

The most common mistake is treating the lake as a substitute for governance rather than a different governance problem. Because a lake accepts anything, it's easy to end up with an ungoverned pile of files nobody trusts (sometimes called a 'data swamp'): if nobody owns metadata, lineage, or quality checks on the lake side, exploratory work slows down instead of speeding up.

The second common mistake is assuming a warehouse can't hold semi-structured data at all. Most modern warehouses support semi-structured columns (JSON or similar variant types), so the real dividing line isn't 'structured versus everything else,' it's whether the schema is enforced and stable versus deferred and evolving. Most real organizations end up running both: a lake for raw ingestion and exploration, and a warehouse (or a curated layer within a lakehouse) for the numbers the business depends on every day.

At real enterprise scale, the general lakehouse-versus-managed-warehouse trade-off sharpens around three things specifically: how concurrency actually behaves under load, what Atomicity, Consistency, Isolation, Durability (ACID) guarantees really mean for complex, high-volume writes, and how well each side handles mixed streaming-plus-batch ingestion without extra engineering effort.

What actually changes at scale

Concurrency. At moderate scale, both a lakehouse and a managed warehouse can serve many concurrent business intelligence (BI) users acceptably. At enterprise scale, a managed warehouse's concurrency handling (workload isolation, automatic scaling of independent compute clusters) tends to be more turnkey: the vendor has already solved the noisy-neighbor problem. A lakehouse can match this, but it usually requires deliberate compute-pool separation and tuning that the team has to design and maintain, rather than getting it largely for free from the platform.

Atomicity, consistency, isolation, and durability guarantees. Open table formats provide table-level ACID guarantees, which is a real and important upgrade over plain files. At enterprise scale, though, the volume and concurrency of writers (many pipelines committing to the same tables simultaneously) stresses that guarantee harder: metadata operations that are effortless with a handful of writers can become a real bottleneck with hundreds, and this is a genuine engineering problem a lakehouse team has to actively manage. A managed warehouse's transactional model is generally simpler to reason about (its query engine and storage are one integrated system), which is part of why it's easier to operate at scale with less specialized tuning, even though it offers less flexibility for complex multi-writer merge patterns.

Mixed streaming-plus-batch ingestion. A lakehouse's storage model is a natural fit for combining continuous streaming writes with periodic large batch loads into the same tables, because both are just writers committing to the same underlying table format. Managed warehouses have added streaming ingestion paths as well, but historically their strength was batch-oriented loading, so mixing a high-volume continuous stream with large batch jobs against the same warehouse tables is more likely to need careful workload management to avoid one interfering with the other.

Worked example

Consider an enterprise ingesting both a continuous stream of transaction events and nightly batch corrections from a legacy system into the same customer-activity table, served to hundreds of concurrent BI analysts. On a lakehouse, this is architecturally natural (both are writers against an ACID table), but the team needs mature compaction and metadata management to keep query performance from degrading as writer volume grows. On a managed warehouse, the batch and streaming paths might need to be more explicitly separated and reconciled, but the query-serving side to hundreds of concurrent analysts is more likely to just work at that scale without as much bespoke tuning.

Trade-offs and pitfalls

The pitfall at this scale is picking a side based on the same general trade-offs that applied at moderate scale, and being surprised when metadata scaling, writer concurrency, or workload isolation becomes the actual bottleneck rather than storage cost or basic query speed. Enterprise-scale lakehouse deployments succeed when the team genuinely invests in the operational discipline (compaction schedules, metadata monitoring, workload-isolated compute) the pattern requires; they struggle when it's adopted purely for the cost story without that investment.

Online Transaction Processing (OLTP) systems are built to handle many small, fast reads and writes for a single business transaction, while Online Analytical Processing (OLAP) systems are built to handle fewer, heavier queries that scan and aggregate across large volumes of historical data. For a startup at roughly 1,000 transactions per second needing both order processing and analytics, I'd recommend two separate systems rather than one combined one.

The distinction, dimension by dimension

Worked example: the recommendation

At 1,000 transactions per second, the order-processing system has to guarantee that a payment and an inventory decrement either both happen or neither does, with response times the customer will notice if they're slow. That's squarely an OLTP workload, and it needs a database built for exactly that: row-level locking, strong consistency, and indexes tuned for 'find this one order.'

The analytics side is a different shape of problem entirely: 'total revenue by category, by day, for the last quarter' touches millions of rows and doesn't care if it's a few minutes stale. Running that query directly against the transactional database competes for the same resources the checkout flow needs, and a single slow-running analytical query can visibly degrade checkout latency for customers, which is the actual failure mode that pushes teams toward separating the two.

So the practical setup is: keep a dedicated OLTP database for order processing, and periodically move data (via a change feed or scheduled batch job) into a separate warehouse or OLAP-oriented store for reporting. This isolates the two workloads so a reporting query never threatens checkout latency, and each side can be scaled independently: the OLTP side for transaction throughput, the analytics side for data volume and query concurrency.

For a data scientist doing exploratory analysis, the OLAP side is also the right place to work from: it's already structured for aggregation, and it won't compete with production traffic the way querying the live transactional database would.

Trade-offs and pitfalls

The main pitfall at small scale is assuming you need this separation from day one when a single well-indexed database would genuinely be fine for a while; premature separation adds operational complexity (a second system, and a pipeline keeping it in sync) before the workload actually demands it. The opposite pitfall, more relevant at 1,000 transactions per second, is running analytical queries directly against the production OLTP database 'just this once' and having that become a habit: it's the single most common way an analytics need turns into a production incident.