A few months ago, all of your startup’s data could be stored in just one database. Now, you’re overwhelmed by customer data from six separate services, and your operations team is requesting detailed analytics. Relying on manual data queries to the production database is no longer feasible, and with your engineering team already at capacity, you need a specialized layer that can deliver insights without disrupting your customer-facing production systems.
Nowadays, businesses produce data from all directions: user interactions, logs, third-party tools, and more. To generate meaningful business insights, you need data—and as such, your data architecture shapes what you can analyze. Production databases, designed for user operations, aren’t built to handle the complex queries needed for in-depth business analysis.
There are two common solutions: data lakes, which prioritize flexibility, and data warehouses, which prioritize structure. There are also hybrid solutions, like data lakehouses and Archil, which bring together the strengths of both approaches.
Data Lakes
A data lake is a centralized repository that stores raw data from various sources: images, tabular data, video, etc. are all stored in their native formats.
Databases typically follow a schema-on-read approach, meaning the data schema is interpreted at the time of the query. This results in slower query times, but offers greater flexibility compared to more structured queries in a data warehouse.
That balance works well for projects that require constant experimentation, like machine learning or data science. For example, adjusting a recommendation algorithm for user feeds: a data lake provides the flexibility for data scientists to rapidly explore different tweaks and optimizations to boost performance. A data warehouse, because of its predefined structure, would make this kind of iterative testing much slower.
Storing Data in a Data Lake
Data is frequently transferred from production databases to the data lake, sometimes in real-time. Without that data transformation bottleneck, the data in a data lake can be much more up-to-date than in a data warehouse.
Storing data is also 2-5x cheaper than in a data warehouse. The cost savings comes from compressing data in cheap object storage solutions (like S3 or Azure Blob), and only activating compute resources when necessary. The schema-on-read model also doesn’t require the persistent indexes, staging tables, materialized views, or multiple data copies needed for schema-on-write.
Querying Data in a Data Lake
Data lakes leverage query engines on top of the storage layer to interpret between user queries and the raw data in storage.
The query engine handles:
- Schema inference: Dynamically infer the data structure based on the query and file headers
- Distributed processing: Splitting the query across multiple compute nodes for parallel execution
- File scanning: Performing minimal, targeted reads of files or partitions
- In-memory processing: Loading relevant data chunks into RAM for computation
Let’s take a look at an example. Suppose a data engineer runs this query to analyze the value of a specific customer for Q4 2024:
SELECT customer_id, SUM(amount)
FROM s3://my-lake/transactions/year=2024/
WHERE date >= '2024-10-01'
To process this query, an engine would:
- Only check on the partition for ‘2024’
- Retrieve files that match date filter (after '2024-10-01')
- Parallelize the aggregation across multiple compute nodes
- Return the final results
In reality, queries are often more complex because data isn’t pre-structured, and optimizing performance requires a solid understanding of distributed computing: partitioning strategies, file formats, and cluster sizing, to name a few. It usually takes a team of data engineers and scientists with distributed computing expertise to fully leverage the potential of a data lake.
The distributed computing model of data lakes leads to more variable performance compared to data warehouses, where indexes and pre-existing schemas enable faster queries. But by structure dynamically, data lakes offer greater greater flexibility and speed for experimentation.
Key Benefits of Data Lakes
To summarize, the main advantages of a data lake include:
- Greater flexibility of data queries: No fixed schema to constrain queries
- Lower storage costs: Inexpensive object storage and pay-as-you-go compute
- Real-time data ingestion : Stream data directly without transformation bottlenecks
- Future-proofing: Preserve raw data for future use and changing analytics needs
In general, data lakes are best for scenarios where future queries are uncertain, such as ML or data science. However, for fast, reliable reporting on established business metrics, the structured design of a data warehouse can offer better performance and simpler maintenance.
Data Warehouses
Data warehouses are structured repositories built for storing data that has already been transformed and organized for specific business objectives: aggregated customer information, processed transaction data, and pre-calculated metrics, all in pre-defined table formats.
Structure is established at the time of writing the data, which is called schema-on-write. This requires data to fit into pre-defined schemas before being stored, ensuring fast and consistent queries once the data is loaded.
This approach is well suited for business intelligence and reporting use cases. For instance, when generating monthly revenue reports at regular intervals, a data warehouse allows business analysts to quickly retrieve reliable, structured data using the SQL skills they already know. In contrast, the flexibility of a data lake would be excessive for these routine, predictable reporting tasks.
Storing data in a data warehouse
A data warehouse defines schemas in advance based on business needs. Data is then pulled from production databases through an ETL (Extract, Transform, Load) process, which reshapes it to fit the warehouse schema. The data is stored as historical snapshots to help identify trends over time.
By cleaning duplicates, standardizing formats, and aggregating related information before storage, the ETL process preserves data quality and consistency. The downside is that this upfront processing introduces a bottleneck, meaning warehouse data typically lags behind production systems by 12-24 hours.
But the end result? Data that’s ready for business use by analysts immediately after the initial delay.
Querying data in a data warehouse
Since the data is structured according to a specified schema, basic SQL queries can organize the data into the necessary reporting format. Business users can write a simple query like SELECT region, SUM(revenue) FROM sales WHERE date >= '2024-01-01' without having to deal with complex joins or data preparation. Warehouses are also capable of handling more advanced queries such as window functions or statistical operations, although these more sophisticated queries can sometimes conflict with the constraints of the predefined schema.
Key Benefits of Data Warehouses
To summarize, the main advantages of a data warehouse include:
- Quick, reliable structured queries with consistent performance
- Less reliance on specialized data engineering expertise for querying data
- Ready-to-use data for business analysts, eliminating the need for additional transformation
Data warehouses are ideal when analytical requirements are clear and performance is a top priority. Data lakes stand out for exploratory analysis and handling diverse data types, at the expense of query performance and complexity. Both approaches have their limitations, which has paved the way for hybrid solutions attempting to combine the strengths of both.
Hybrid Solutions
For years, organizations had to choose between lakes and warehouses, weighing their fundamental trade-offs.
More recently, data lakehouses have emerged as a promising alternative that blends the flexibility of lakes with the performance benefits of warehouses. Similar to data lakes, data lakehouses store a wide variety of data formats in cost-effective object storage. They also introduce a metadata layer that adds key warehouse features: ACID transactions, schema enforcement, and indexing on top of the raw data files, enabling more efficient querying.
Traditional data lakehouses still need to set up new data infrastructure and deal with delays from regularly transferring data. Services like Archil address these challenges by building the metadata layer directly on top of production blob storage. This solution allows Archil to deliver many of the advantages of data lakehouses with less engineering effort.
For those who need a balance between experimentation and fast, structured queries, a hybrid solution could be more cost-effective than maintaining separate data lakes and data warehouses.
Data Lakes vs Data Warehouses: Which one is right for me?
Let’s compare how these approaches improve the structure of data for analytics:
| Criteria | Data Warehouse | Data Lake | Hybrid Solutions |
|---|---|---|---|
| Storage costs | Higher cost. Optimized analytical storage & bundled compute. | Lower cost. Inexpensive object storage, pay-per-use compute. | Moderate. More costly than data lakes, but usually cheaper than warehouses. |
| Migration cost | Higher cost. Detailed data modeling and complex ETL processes required. | Lower cost. Store raw data initially, define structure later. | Lower. Can sometimes operate directly on live production data. |
| Generating reports | Easier. Pre-joined tables, business-ready data. | Harder. Raw data must be processed dynamically. | Easier. Pre-joined tables, business-ready data like warehouses. |
| ML / Data Science | Less ideal. Limited flexibility for experimentation. | More ideal. Most freedom for experimentation. | More ideal. Maximum flexibility for experimentation like lakes. |
| Data freshness | Batch updates, hourly at best with ETL delays. | Can be real-time with direct streaming. | Can be real-time. E.g. Archil operates directly on production data. |
| Skill requirements | SQL analysts. Standardized workflows, familiar tools. | Data engineers. Distributed computing, schema design. | Variable. Lakehouses require knowledge of both warehouses and lakes, while other solutions abstract the complexity. |
Final thoughts
Data lakes and data warehouses are both widely used for enabling data analysis of production data. Data lakes provide the greatest flexibility, while data warehouses prioritize performance and speed.
If you want a balance between flexibility and performance, hybrid solutions like data lakehouses and Archil could be the ideal choice. These approaches combine the benefits of lakes and warehouses, offering a solution without diverting resources from other business priorities.
Top comments (0)