Data Warehousing and Data Lakes Questions

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.

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.

For this workload, I'd build a lakehouse rather than a pure warehouse, because the requirements span both governed business intelligence (BI) reporting and downstream ML feature retrieval off the same underlying data, and a lakehouse avoids maintaining two separate copies of a terabyte a day. At the architecture level, the key pieces are: a storage and catalog layer that's the shared source of truth, a compute layer split by workload type, and a serving layer that keeps interactive dashboards fast without that load hitting the same compute as heavier analytical jobs.

High-level design

flowchart LR
  A[Sources] --> B[Object storage: bronze/raw]
  B --> C[Curated tables: silver/gold]
  C --> D[Interactive compute: BI dashboards]
  C --> E[Batch/ad-hoc compute: analysts, ML feature jobs]
  C --> F[Catalog and access control]
  D --> G[Caching / pre-aggregation layer]

Storage layout. Object storage underneath, organized into a medallion-style progression: raw data landed close to source shape, a cleaned and conformed layer, and a business-ready curated layer that both dashboards and downstream feature jobs read from. Keeping one shared curated layer, rather than a separate warehouse-only copy, is what avoids running two parallel pipelines for BI and ML.

Catalog. A central catalog is the piece that makes 'one platform, many consumers' actually work: it's where table schemas, ownership, and access rules live, so a BI tool and an ML feature job are reading the same definition of a table, not two independently maintained copies of similar logic.

Compute, split by workload. The core architectural decision for handling many concurrent BI users without ad hoc or ML workloads slowing them down is separating compute pools: one sized and tuned for many small, fast, predictable dashboard queries, and a separate one for heavier ad hoc analysis or batch feature computation. This is exactly where compute-storage separation earns its keep: both pools read the same underlying storage, but scale (and fail) independently, so a runaway analytical query doesn't degrade dashboard latency for everyone else.

Serving layer. For the sub-second, high-concurrency dashboard case specifically, raw ad hoc queries against a general-purpose engine usually aren't enough on their own. A pre-aggregation or caching layer, materialized summaries refreshed on a schedule or incrementally, sits between the curated tables and the dashboards, so most dashboard queries hit small, pre-computed results instead of scanning the full curated layer every time.

Why not a pure warehouse-centric design instead. A warehouse-centric variant of this same design is a genuine alternative, not a lesser one: skip the lake entirely, land everything straight into the warehouse, and lean on the warehouse's own compute-storage separation, a semantic or metric layer, and materialized pre-aggregations to hit the same dashboard latency and concurrency targets. This is the simpler path operationally, since there's only one system to run, and it's the right call if the ML feature-retrieval requirement is modest (a handful of models reading a few curated tables) rather than a first-class, heavy workload in its own right. The lakehouse earns its extra operational complexity specifically when the ML side needs the same raw, full-fidelity history the BI side does, so that duplicating it into a second warehouse-only copy would be wasteful; if that's not really true here, the warehouse-centric branch is the more defensible design.

Worked example: why this holds up under load

The combination of a shared curated layer (one source of truth for both BI and ML), workload-separated compute (so concurrency on one side doesn't degrade the other), and a pre-aggregation layer in front of dashboards is what lets this design serve many concurrent BI users with fast, predictable latency while still supporting ad hoc analysis and ML feature retrieval off the same data, without duplicating the curated layer for each consumer.

Trade-offs and pitfalls

The main risk is under-investing in the compute-separation decision: if dashboard queries and heavy ad hoc analysis share the same compute pool 'to save cost,' the design degrades into exactly the noisy-neighbor problem it was meant to avoid, and the first symptom is usually dashboards getting slow during someone's large ad hoc query. The second common mistake is skipping the pre-aggregation layer and expecting raw curated-table queries to hit sub-second latency for a large number of concurrent users; at that scale, some form of pre-computation in front of the dashboards is almost always necessary, not optional polish.

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.