Warehouse vs Lakehouse
Transcript
Made automatically from the video's sound, so it may contain mistakes. Click a line to play from there.
Welcome back to The Explainer. Today we're jumping straight into a deep dive systems engineering analysis of how compute storage separation evolved to solve petabyte scale analytical workloads. Now, if you recall from our very first explainer in this series, we explored the inner workings of OLTP versus OLAP columnar internals, you know, row versus column storage, parquet chunks, dictionary compression, and SIMD vectorized scans.
Well, today we are taking those fundamental building blocks, skipping the basic primers, and scaling the whole thing up to the cloud. We'll cover the anatomy of production failures, the evolution of analytical storage, open table format internals, query execution and pruning, the hidden ops trap, and finally a concrete architectural decision framework.
Right, let's get straight into section one. The anatomy of production failures. We have to start with two incredibly painful engineering realities. And honestly, you might have experienced these firsthand.
On one side, picture a developer's accidental big query loop, firing off an unpartitioned select star query over a massive dashboard all weekend long. Because BigQuery operates on a paperbyte scanned model, that tiny simple mistake, yeah, that can literally burn thousands of dollars in a matter of days.
On the flip side, imagine a data team that just dumped raw CDC JSON and Parquet files straight into Amazon S3. When they actually go to query it, they get hit with a 40-minute Trino planning port.
Why? Because their data lake is absolutely clogged with two million tiny files. These aren't just flukes, they are symptoms of architectures completely misaligned with their storage layers.
Building on our breakdown of columnar execution in the previous explainer, we have to recognise a hard truth. Coupling compute and local NVMe storage completely breaks down when your analytical retention hits tens of terabytes or even petabytes.
You simply cannot keep bolting local SSDs onto your compute clusters forever. At a certain point, you have to separate the two. The evolution of analytical storage. We've essentially witnessed a three-generation shift here.
It started back in the 2010s with decoupled cloud data warehouses, vendors decobbled proprietary columnar storage from stateless MPP compute. So for example, and no, we aren't endorsing any specific tools here, these are just concrete architectural examples.
Meanwhile, BigQuery developed its capacitor storage format and highly efficient shuffle fabric. Then by the mid-2010s, the pendulum swung towards raw data lakes using S3 and Google Cloud Storage.
Teams were just dropping parquet files into buckets using the Hive Metastore. But, well, these quickly turned into acid-free data swamps. A failed job would leave half-written files visible to any reader, and those excruciatingly slow S3 list calls just cripple performance.
That brings us to the 2020s and the rise of the open lake house. This architecture brilliantly solves that data lake corruption problem. Instead of relying on those slow, frankly, unreliable directory listings, the lake house inserts a transactional metadata layer directly on top of those open parquet files in object storage.
It basically gives you the robust management features of a proprietary data warehouse, things like acid transactions, strict governance, versioning, time travel, but the raw data files themselves stay in an open format. That means your diverse machine learning, BI, and data science engines can all query the exact same data without being locked into a single vendor's compute engine.
Okay, section three. Open table format internals. How does this actually work under the hood? Let's look at the exact byte-level metadata hierarchy of Apache Iceberg, because this is the anatomical structure that makes a lake house possible.
It maps a query systematically from the top down. First, a catalogue atomic pointer swap ensures readers only ever see fully committed transactions, no dirty reads. That pointer leads to a metadata.json file, which points to a manifest list, which then points to individual manifest files.
Now, those manifest files are crucial because they contain vital column statistics like minimum and maximum values. Finally, those stats point all the way down to the immutable parquet row groups and pages.
It's an incredibly elegant tree structure. And when we explicitly contrast the three major open table formats dominating the space, Apache Iceberg, Delta Lake, and Apache Hoodie, we see they all achieve the same goal but take very distinct approaches.
Iceberg uses that strict metadata tree we just talked about and offers hidden partitioning, meaning your SQL queries don't actually need to know how the data is physically partitioned to be highly efficient. Delta Lake, on the other hand, relies on a continuous delta log of JSON commits and brings liquid clustering to the table, dynamically managing data layout without massive rewrites.
Hoodie, meanwhile, has its deep roots in handling mutable change data capture, or CDC upserts. It offers both copy on write and merge on read configurations, alongside Bloom and Record Inxes.
Query execution and pruning. That is the absolutely crucial takeaway here. Because of these architectures, catalogue and manifest metadata can prune 99% of S3 HTTP range get requests before your compute engines even touch the raw bytes.
When you run a query, the engine reads the metadata first. Because it has those minimum and maximum column stats, the engine knows exactly which parquet files do not contain your data, and it just skips them entirely. It's a massive performance game changer.
And this metadata pruning powers execution across the board, no matter what engine you use. In Dramel and DigQuery, it optimizes exactly what gets sent across their capacitor shuffle networks.
In Trino and Presto, the coordinator uses this metadata to aggressively filter down the list of splits before it even thinks about scheduling tasks to workers. Over in Snowflake, execution is further optimized by something called file stealing. This is brilliant.
Workers that finish processing their 50 to 500 megabyte micropartitions early will literally take over files from slower straggler nodes to perfectly balance the load. And remarkably, thanks to these open formats, a single node engine like DockDB can read an iceberg table directly from S3.
You don't even need a massive distributed cluster anymore. But wait, it's not all magic. Section 5, the hidden ops trap. Because these systems rely on optimistic concurrency control, or OCC, they are safe, but they do require tuning.
OCC works by reading the latest table version, staging your new files, and then checking for conflicts right before committing. If two concurrent writers try to manipulate the exact same unpartitioned data, the system triggers a conflict exception.
Now, fundamentally this is a good thing. It stops silent data corruption. But if your data pipelines aren't explicitly designed to handle these commit retries, your jobs are gonna fail hard.
This leads us directly to the mandatory maintenance loop. Look, if you're streaming one-minute micro batches, you will quickly generate millions of tiny one-megabyte parquet files. This is the dreaded small file problem.
It'll completely tank your query latency and wildly inflate your S3 API costs. Open storage does not mean zero operations. Your data engineering team is now fully on the hook for a continuous maintenance loop.
You have to run Optimize for bin packing compaction, merging those tiny files into larger ones. You have to aggressively enforce snapshot expiration policy so your metadata doesn't grow to infinity, and you absolutely must run vacuum to delete orphaned S3 files.
Neglect this and your performance will drop off an absolute cliff. Finally, section six, our architectural decision framework. Let's make this actionable. First, let's look at the underlying cost maths.
Storage in S3 is wonderfully cheap, around $23 per terabyte a month. But compute, compute is where you pay the premium. If you have a 50 terabyte event table and you run an unpartitioned select star query, a paper byte engine is going to bill you for scanning all 50 terabytes.
Ouch. However, if your data is partitioned and clustered correctly, and your queries project specific columns, you might only scan, say, 40 gigabytes. Ignoring partitioning doesn't just slow things down.
It will explode your monthly cloud bill exponentially. Ultimately, your architectural choice comes down to these quantified thresholds. If your dataset is under 500 gigabytes, honestly, you probably just need DuckDB reading directly over parquet files.
Simple, cheap, effective. If you're sitting between one and twenty terabytes, you're mostly doing SQL, and your platform ops headcount is essentially zero, a managed cloud warehouse like Snowflake or BigQuery is ideal.
They handle the compaction and vacuuming for you automatically. You should really only transition to an open iceberg or Delta Lakehouse when your scale crosses that 20 terabyte mark, and the need for multi engine access, like pointing Trino, Spark, and Flink all at the exact same data, outweighs the heavy operational burden of managing that compaction and metadata yourself.
So, as you review these thresholds for your own engineering systems, consider this. What happens when ACID transactions and strict columnar layouts simply aren't enough for your operational read and write scale? What actually happens when the relational model breaks completely?
Well, that teasers up perfectly for the next explainer in our series, where we'll be diving deep into NoSQL families. Thank you so much for joining me, and I'll catch you in the next one.