Warehouse, Lake or Lakehouse: Where Should Your ML Data Live?
Last updated:
A different question from the usual comparison
There is no shortage of articles comparing data warehouses, data lakes and lakehouses. We have written one ourselves: data warehouse vs data lake vs lakehouse. Most of them are framed around reporting and business intelligence.
This post asks a narrower question. If you are starting to build machine learning models, does that change the answer? Sometimes it does. More often, it does not, and the most expensive mistake we see is a business building a lakehouse for one churn model that would have run happily off its existing database.
What ML workloads need that reporting does not
- History, not just current state. Training data needs to know what things looked like on past dates, so snapshots and change history matter more than in a dashboard.
- Reproducible versions. You need to be able to rebuild the exact dataset a model was trained on months later, for debugging or audit.
- Access from Python. Data scientists pull large extracts into Python tools rather than clicking through a BI tool.
- Unstructured files, sometimes. Documents, images, audio or sensor readings that do not fit neatly into tables.
- Occasional heavy scans. Training jobs read whole tables at once, which can be expensive on platforms that charge per query.
If your models only use tabular business data and modest volumes, the first three can be handled inside any decent database. The last two are what push people towards lakes and lakehouses.
How the options compare for machine learning
| Option | Strengths for ML | Weaknesses for ML | Typical fit |
|---|---|---|---|
| PostgreSQL or similar | Cheap, familiar, easy Python access, fine for millions of rows | Struggles with very large scans and unstructured files | One to a few tabular models, modest data |
| Cloud data warehouse | Excellent for joined business data, SQL feature building, good governance | Large extracts can be costly; awkward for images and documents | Businesses already using it for reporting |
| Data lake on object storage | Very cheap storage for files and raw history, any format | Easy to turn into a disorganised dumping ground; weak on consistency | Large volumes of logs, images or sensor data |
| Lakehouse | Table-style reliability and versioning over cheap file storage; good for Python and SQL | More moving parts and skills required | Mixed structured and unstructured data at scale |
Start with what you already have
Our default recommendation for a business beginning with machine learning is to use the data platform that already feeds its reporting. If that is a warehouse, build feature tables there. If it is a PostgreSQL database behind your application, a read replica with a separate schema for modelling is often enough.
A 50-person distributor with five years of orders, a CRM and a few million rows in total does not have a big data problem. Loading that into a lakehouse adds platform cost, new skills to learn and a new system to secure, in exchange for capabilities it will not use. If you are unsure whether you need a warehouse at all, read do you need a data warehouse first.
Choose storage for the data you will realistically have in two years, not the data a vendor's reference architecture assumes.
Signs you have outgrown a warehouse for ML
- You want to train models on documents, images, call recordings or raw sensor data, not just tables
- Raw event or log data runs into billions of rows and storing it all in the warehouse is getting expensive
- Training jobs repeatedly extract huge tables and the query bills are noticeable
- You need to keep many years of raw history cheaply for future models you have not designed yet
- Several teams want to use Spark, Python and SQL on the same data without copying it around
One or two of these can often be handled by adding object storage alongside the warehouse. Several together is when a lakehouse, using open table formats such as Delta Lake or Apache Iceberg, starts to make sense.
Versioning the data a model was trained on
Whichever option you choose, you need to be able to answer 'what data exactly was this model trained on?' It matters when a model misbehaves, when a customer challenges a decision and, increasingly, for regulatory documentation.
Lakehouse table formats offer time travel, letting you query a table as it was at a past version. Warehouses often have similar features for a limited period. The simplest reliable approach, available everywhere, is to save each training dataset as a dated, read-only file in object storage and record its location alongside the model. It is not elegant, and it works.
Costs that catch people out
Storage is cheap on every platform. The bills come from elsewhere: compute charges for full-table scans during training, data transfer when pulling large extracts out of a cloud region, and idle clusters someone forgot to switch off. On lakehouse platforms, the cost of people who know how to run them is frequently larger than the platform itself.
At SpiderHunts we usually model the monthly cost of two or three options against a client's real volumes before recommending one. Often the cheapest option is also the right one. If you want that analysis done for your data, it is a normal part of our data science engagements.
Frequently asked questions
What is the best database for machine learning data?
Is a data lake necessary for machine learning?
What is a lakehouse?
Can I train models directly from Snowflake or BigQuery?
How do I version training data?
Choosing where your data should live?
Tell us what data you have, how much, and what you want to do with it. We will recommend the simplest setup that will still work in two years.