Data Warehousing and Data Lakes Questions

Analytical warehouses prefer columnar formats like Parquet or ORC because most analytical queries touch a small number of columns across a huge number of rows, and columnar storage is built specifically to exploit that pattern. Row-oriented storage, by contrast, is built for the opposite pattern: reading or writing one whole record at a time.

Why columnar wins for analytics

In row-oriented storage, all the columns for a single row sit next to each other on disk, so reading even one column means reading the whole row. In columnar storage, all the values for a single column sit together instead. That single layout change explains most of the benefit:

Less data read off disk. If a query only needs three columns out of fifty, columnar storage lets the engine read only those three columns' worth of bytes; row storage would have to read all fifty columns for every row, then discard the ones it didn't need.

Compression. Values within a single column tend to be far more similar to each other than a mixed row of different columns is (a status column might have only a handful of distinct values, for instance). That similarity compresses much better, and formats like Parquet and ORC take advantage of it with column-specific encodings, which further reduces the bytes actually read from disk.

Skipping data that can't match a filter. Columnar file formats keep summary statistics (like the minimum and maximum value) for chunks of each column. If a query filters on a date range, the engine can check those statistics and skip entire chunks that fall outside the range without reading them at all, on top of the column-level savings already described.

Worked example

Consider a query that computes total revenue for one product category last month, against a wide events table with fifty columns. In row-oriented storage, computing that aggregate still means reading every column of every row in the relevant time range, most of which the query never uses. In columnar storage, the engine reads only the category and revenue columns (plus whatever it needs to apply the date filter), and if the data is organized so most chunks clearly fall outside last month's date range, it can skip those chunks entirely. The net effect is that a query touching two or three columns out of fifty can end up reading a small fraction of the bytes a row-oriented equivalent would.

Trade-offs and pitfalls

Columnar formats aren't universally better: for a workload that reads or writes entire records at a time, like an online transaction touching every field of a single order, row-oriented storage is the better fit, because columnar formats pay a real cost when you need to reconstruct a whole row from scattered column files. This is exactly why transactional (OLTP) systems still use row-oriented storage even though analytical (OLAP) systems have moved to columnar: the two are optimized for genuinely different access patterns, not one being an outdated version of the other.

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.

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.

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.