Data Warehousing and Data Lakes Questions

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.

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.

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.

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.

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.