Warehouse, Lake, and Lakehouse

Two classic answers to the same question

Architecture follows data variety and workload needs. Long before anyone picks a product, the platform has to answer one question: what kind of data is arriving, and what will people do with it?

A data warehouse stores integrated, cleaned, structured data optimized for SQL analytics, reporting, and historical analysis. Data flows in from transactional systems and other sources on a regular cadence; the structure and the schema are defined in advance so that SQL queries are fast and the results can be trusted as a single version of the truth. Analysts, dashboards, and BI tools read from it.

Its strengths are the direct consequence of that discipline:

• consolidated data from many sources,
• consistent, quality-checked, historical data,
• analytics kept off the transactional databases, which helps both systems.

A data lake stores structured and unstructured data at scale and can retain data before its analytical schema is defined. Relational tables from business applications sit alongside logs, images, clickstreams, and device readings. Data is kept as-is, in its original format, and the schema is not fixed at capture time — so a team can store data before it knows which questions it will be asked.

That flexibility is what makes several kinds of analysis possible over one repository: SQL queries, big-data processing, full-text search, real-time analytics, and machine learning, without moving the data into a separate system first.

The lakehouse

The two designs were long treated as rivals, which forced organizations to copy data between isolated systems: one copy for BI, another for machine learning, and a standing argument about which one was right.

A lakehouse combines low-cost lake storage with data-management and analytical capabilities associated with warehouses. Data stays in open formats in cheap object storage, and a metadata layer above those files adds the management the warehouse world took for granted: transactions, schema enforcement, versioning, lineage, and governed access.

What that combination buys:

• open, direct access to data stored in standard formats,
• managed tables with transactions and schema evolution,
• low query latency and reliability good enough for BI as well as data science,
• one governed foundation instead of redundant copies per workload.

It helps to see the three as a progression rather than a ranking.

A lakehouse is still an analytical platform. It does not become the place to record individual application transactions, and it does not give each reporting user a private database — the point is a single shared foundation.

Keep the lake from turning into a swamp

The main difficulty with a lake is the flip side of its main advantage: raw data is stored with no oversight of its contents. A repository that will accept anything will, given time, accept everything.

To stay usable, a lake needs defined mechanisms to catalog and secure its data. Crawling, cataloging, and indexing are what make it possible to know what is actually in there; access control is what keeps the assets protected. Without those elements data cannot be found or trusted, and the lake becomes a data swamp — full, expensive, and useless.

A catalog describes and indexes assets. It does not remodel every object into a relational table, it does not replace access controls, and it does not oblige every analytical engine to run on one shared compute cluster: a lake can serve several engines at once.

Choose by workload, not by label

Warehouses favor governed relational analytics, lakes favor flexible retention, and lakehouses aim to support both over shared data, so the dominant storage and workload requirements should drive the choice rather than a preferred architecture label for the platform.

Work backwards from the requirements. A team that must retain raw images and logs, run machine learning and SQL over them, and later publish governed tables for BI on the same shared storage has named every ingredient of the lakehouse pattern: varied data, several engines, and warehouse-style governance and serving on one foundation.

The alternatives each fail one clause of that list. A warehouse that admits only tabular data satisfies the BI half and rejects the images and logs. An unmanaged lake retains everything and fails the trust and reporting half, because nothing is cataloged, governed, or validated. And running all of it directly on the operational database ignores the separation that keeps transactional and analytical work from degrading each other.

Raw, validated, and serving

Inside any of these platforms, data is rarely used in the state it arrived. Layering makes changes in trust and purpose visible, so a consumer can tell what has been done to a dataset before relying on it.

An ingestion layer retains source data with minimal modification so it can be reprocessed or audited. Whatever arrives lands as it came, batch or streaming, in whatever format. Keeping it faithful is what makes reprocessing possible when a downstream rule turns out to be wrong.

A refinement layer validates, cleans, deduplicates, and combines data into trusted records. This is where missing or unexpected values are caught, duplicates collapse, and separate sources are integrated into one record per real-world thing.

A serving layer organizes refined data for specific analytics, reporting, or application use cases. The final layer serves clean, enriched data laid out for its consumers: reporting tables, feature tables for machine learning, the shapes each use case actually reads.

The responsibilities run in one direction: land raw inputs faithfully, validate and integrate them into trusted records, then publish consumer-ready data.

The order matters because each layer depends on the one before it. Publishing raw inputs straight to consumers skips the quality work; validating after publication means the wrong numbers were already read. And serving cannot bypass ingestion, because there would be nothing trustworthy to organize.

Layer names are local conventions

Raw, validated, and serving describe responsibilities; labels such as bronze, silver, and gold are conventions rather than universal requirements. The medallion vocabulary is a useful shorthand for progressive refinement, and it is genuinely common — but the words are not a specification.

Two platforms can both have a "silver" layer and mean different things by it: one deduplicated but not conformed, the other fully integrated and ready to join. The label transferred; the guarantee did not.

Consumers should select data by its quality contract and intended use, not by assuming a layer name has identical meaning everywhere. The contract is what has to be written down for each layer:

• what quality rules have been applied,
• what the schema is and how it may change,
• who may read it,
• who owns it and who to ask when it is wrong.

So when two teams with different vocabularies have to interoperate, the thing to standardize is the meaning, not the words. Agree on what each state guarantees and let each platform keep its own labels.

Layer labels are also not file formats and not compute services. They describe the state and responsibility of data, whatever it is stored in and whatever processes it.

Partition boundaries

Physical organization should reduce unnecessary work for expected access patterns. A query that has to open every file to find one day of data is paying for the layout, not for the analysis.

Partition boundaries should align with frequently used filters so readers can eliminate unrelated partitions. If nearly every query restricts the event date, partitioning by date lets the reader skip whole directories without opening them. If nearly every query restricts the country, the country is the candidate. The rule is the same in both cases: partition on what people filter on, repeatedly and durably.

Partitioning by a common time boundary is useful when queries repeatedly restrict that boundary and data volume makes pruning valuable. Both halves of that sentence matter — on a small dataset there is little to prune and the boundary buys nothing.

Cardinality is the trap at both ends.

A partition key with too many distinct values can create many small partitions and costly metadata work. An event ID is the extreme case: one partition per event, a directory tree nobody can list cheaply, and no match with how anyone actually queries. High cardinality does not lower listing cost — it is what raises it.

A partition key with too few values can leave each selected partition too large for selective queries. Partitioning by continent when all the traffic is in one of them means every query still reads almost everything.

File size and the small-file problem

Choosing the boundary is only half the layout decision. File size affects scan parallelism and opening overhead, so layout decisions must consider total volume as well as filter columns.

Files are the unit of parallel work, so very few very large files leave workers idle. But every file also has to be discovered, listed, opened, and scheduled as a task before a single useful byte is read. Multiply that fixed cost by hundreds of thousands of tiny files and the job spends its resources on coordination instead of on data.

The symptom is a job whose runtime barely improves when you add workers, because the bottleneck is metadata, not computation. Note what the problem is not: many small files do not turn a columnar format into a row-oriented one, do not remove the schema from a table, and do not eliminate parallelism — nominal task count may even rise. The cost is overhead per file.

The practical target is files large enough that the fixed cost per file disappears against the work done, and small enough that there are plenty of them to spread across the cluster. That is why compaction — rewriting many small files into fewer well-sized ones — is routine maintenance rather than an emergency measure.

Key takeaways

• Warehouse: integrated, cleaned, structured data optimized for SQL analytics, reporting, and historical analysis.
• Lake: structured and unstructured data at scale, retained before its analytical schema is defined — and dependent on cataloging, governance, and access control to stay usable rather than becoming a swamp.
• Lakehouse: low-cost lake storage plus the data-management and analytical capabilities associated with warehouses, so the dominant storage and workload requirements drive the choice rather than a preferred label.
• Layers: ingestion retains the source, refinement validates and combines it, serving lays it out for consumers; bronze, silver, and gold are conventions, so standardize the guarantees and let the names stay local.
• Layout: align partition boundaries with the filters people actually use, avoid keys with too many or too few distinct values, and size files so scans stay parallel without paying per-file overhead on a flood of tiny files.