Warehouse vs Lake vs Lakehouse
Podcast · Warehouse vs Lake vs Lakehouse

The Physics of Modern Data Architectures

by ZumiLabs · 54m
0:00 54m

Transcript

Made automatically from the audio, so it may contain mistakes. Click a line to play from there.

Host 1

Picture this. It is Monday morning. You sit down at your desk, coffee in hand, feeling, you know, pretty good about the weekend. You open your cloud billing console just doing a routine check, and your stomach completely drops.

Host 2

Oh no. I know exactly where this is going.

Host 1

Right. So over the weekend, this well-meaning front end developer deployed a new internal dashboard for the sales team. But this dashboard had a loop in it. It was running an unpartitioned select query against a fifty terabyte historical events table, refreshing every five minutes. Wow. Exactly.

Host 2

Every five minutes on fifty terabytes.

Host 1

Exactly. And because you're using an on-demand pricing model that charges per byte scanned, that single weekend mistake just burned through forty thousand dollars. You're now staring at the kind of bill that triggers an immediate, very uncomfortable meeting with the CTO.

Host 2

That is a uniquely terrifying meeting. But I mean, let me offer you a slightly different, equally painful Monday morning scenario.

Host 1

Go for it.

Host 2

So a platform engineering team decides they are gonna be heroes, right? They want to save the company money by avoiding proprietary data warehouses altogether. So they set up a pipeline that takes raw change data capture streams, like CDC streams from their production Postgres database. And they dump those raw JSON and parquet files directly into an Amazon S3 bucket.

Host 1

Just raw files straight to the bucket.

Host 2

Yeah. Millions of tiny one megabyte files trickling in every minute of every day. Monday morning rolls around and you get paged. Your Trino query engine is pausing for 40 minutes just to plan a basic query.

Host 1

Forty minutes just for the planning phase.

Host 2

Right. Because the engine's memory is maxing out, desperately trying to list and parse the metadata across two million tiny files before it even looks at a single row of data. Meanwhile, your downstream data scientists are screaming because their reports are actively crashing. They're hitting broken, half-written files left behind by weekend jobs that just, you know, failed mid right.

Host 1

Total chaos. Just two catastrophic production failure modes, and both are rooted in the physics of how we architect modern analytical data stores. So welcome to our deep dive into the underlying source material on modern data architectures.

Host 2

And this is gonna be a fun one.

Host 1

Yeah. In our last deep dive, we covered the absolute baseline of analytical storage. We established the physical reality of why columnar formats dominate. We are assuming you are already intimately familiar with parquet column chunks, dictionary encoding, run length encoding, and why SIMD vectorized scans allow a CPU to process data batches in parallel.

Host 2

Right. So we are not spending a single minute today reexplaining why columnar beats row storage for aggregation queries. We are past that.

Host 1

Exactly. We're skipping the basics.

Host 2

We are starting exactly where that local storage paradigm physically breaks down. We are starting at the petabyte tier. When your analytical data retention requirements hit tens or hundreds of terabytes, you can no longer fit that data onto the NVMe drives physically attached to your compute nodes.

Host 1

You just run out of physical space.

Host 2

Yeah. So today we are unpacking the mechanics of decoupled cloud storage. We are dissecting the rise of open table formats like Apache Iceberg, Delta Lake, and Apache Hootie. Most importantly, we're going to expose the ruthless, unforgiving mechanics of cloud computing costs.

Host 1

And a quick reality check on who this is for. If you are an experienced software engineer, a tech lead, or a data architect, this is tailored for you. Our mission is to slice completely through the vendor marketing hype surrounding the word lakehouse.

Host 2

There's so much hype right now.

Host 1

So much. We will name real products today: BigQuery, Snowflake, Databricks, but we are using them purely as concrete architectural examples, never as endorsements. We are going to spell out the exact terabyte thresholds, the query concurrency limits, and the brutal operational headcount numbers behind every single time an architect gives you the answer. Well, it depends.

Host 2

I mean, the phrase it depends is technically accurate, but it offers zero value without quantifiable boundaries. We are going to define the physics of those boundaries today.

Host 1

So moving from local solid state drives to decoupled cloud storage is a violent paradigm shift. Think about your personal filing system. When the files are on your desk, you just reach out and grab them. That is local NVMe storage.

Host 2

Very fast, very accessible.

Host 1

Right. Decoupling storage is like moving your entire filing cabinet to a massive windowless warehouse five states away. If you have a perfectly indexed microscopic ledger of exactly what piece of paper is in which box on which shelf in that warehouse, you can send a truck to retrieve exactly what you need.

Host 2

But without that perfect index.

Host 1

Without it, finding a single folder takes weeks, and you bankrupt yourself paying for the truck's fuel just driving up and down the aisles.

Host 2

That is a great analogy. And the physical limitation of local disks is actually what drove the evolution of the modern cloud data warehouse. You simply cannot physically attach enough hard drives to a tightly coupled cluster to hold a petabyte of data economically without stranding massive amounts of compute power.

Host 1

Because you'd have all these idle CPUs just to keep the hard drives online.

Host 2

Exactly. If you scale up the cluster just to get more disk space, you are paying for CPUs you aren't using. The major cloud players realized they had to physically sever the compute layer from the storage layer. But as you pointed out, moving the data to an off-site warehouse introduces physical distance. In computing terms, that distance manifests as severe network latency.

Host 1

So how did they actually solve the physics problem of that latency? Let's break down the proprietary cloud data warehouses first, because they really laid the blueprint. The source material provides this brilliant architectural teardown of how systems like Snowflake and BigQuery separate state, the persistent data, from stateless compute. Let's look at Snowflake first.

Host 2

Yeah, according to their engineering white papers, they rely on a concept called micropartitions. Snowflake's design is arguably the most elegant implementation of this decoupled philosophy.

Host 1

How does that actually work under the hood?

Host 2

Well, when you load data into a Snowflake table, it doesn't just dump a massive file onto an S3 bucket. The system automatically and continuously divides your data into these micro partitions. These are contiguous units of columnar storage, and each one contains between 50 megabytes and 500 megabytes of uncompressed data.

Host 1

Okay, so a very specific size range.

Host 2

Right. And the actual physical footprint on the object store is much smaller, of course, because the data is aggressively compressed.

Host 1

And these micro partitions are strictly immutable, right? Like once the bytes are written to the cloud bucket, they are never ever modified in place.

Host 2

Immutability is a non-negotiable constraint of cloud object storage. You cannot execute an update-in-place operation on an S3 object. If you want to change a single byte, you must rewrite the entire object. So Snowflake writes these immutable blocks. But the real ingenuity lies in the compute layer.

Host 1

Right. They use ephemeral virtual warehouses.

Host 2

Exactly. Snowflake processes this data using these ephemeral virtual warehouses. And when we say virtual warehouse, we aren't talking about storage.

Host 1

We are talking about a dynamically provisioned cluster of EC2 instances or compute nodes.

Host 2

They are pure stateless compute clusters. You can spin up a massive cluster of 128 nodes to crunch a heavy end of month financial report and then immediately suspend the cluster when the query finishes. You only bring compute credits while the nodes are actively running.

Host 1

Which is great for cost control.

Host 2

It is. But to address that massive network latency penalty, the truck's driving to the offsite warehouse in your analogy, Snowflake employs an aggressive caching strategy. These compute nodes are provisioned with fast local NVMe SSDs. When a query pulls a micro partition down from S3, it caches it on the local SSD of that specific compute node.

Host 1

Wait, if the compute cluster is ephemeral and dynamically scales, how does it ensure the cache is actually useful? I mean, if I run a query and node A pulls the data, and then I run a similar query five minutes later, how does the system ensure that second query gets routed to node A instead of node B?

Host 2

Because node B would just have to fetch the data from S3 all over again.

Host 1

Exactly. Right. That defeats the purpose.

Host 2

Well, they solve that using consistent hashing. The query planner maps specific micro partitions to specific compute nodes in the cluster based on the partition's unique identifier. So if you query the same data repeatedly while that virtual warehouse is active, the system consistently routes the work to the node that already holds that micropartition in its warm NVMe cache.

Host 1

Oh wow. So it completely bypasses the network latency of the object store for subsequent queries. Precisely. Okay, that handles the caching, but we still need the index. Where is the brain that knows which of these millions of micropartitions actually contains the data for, say, November 2023, so we don't pull all of them into the cache in the first place?

Host 2

That is the third and really most critical layer of Snowflake's architecture, the cloud services layer. This is a multi-tenant centralized brain. It manages authentication, query parsing, access control, and vitally the metadata and transactional state.

Host 1

So it's constantly watching the data coming in.

Host 2

Yes. As your data is ingested, this cloud services layer automatically analyzes every single micro partition before it even lands in storage. It extracts the minimum and maximum values for every column, counts the number of distinct values, and logs all of this into a centralized transactional metadata store.

Host 1

This means the user is entirely abstracted away from partitioning schemes.

Host 2

Right. The system just observes the natural clustering of the data as it flows in and derives the boundaries automatically.

Host 1

It's a massive operational relief. Historically, you know, if you hard-coded a partitioning scheme and three years later your query patterns changed, you are facing a multi-month migration project to rewrite your entire dataset to a new partition layout.

Host 2

Nobody wants to do that. Snowflake's micropartitions can overlap in their range of values, and the cloud services layer just tracks the math.

Host 1

Let's pivot to BigQuery, because Google tackled the exact same compute and storage separation problem, but engineered a fundamentally different architecture. They drew heavily on their internal Dremel paper.

Host 2

Yeah. BigQuery relies on a proprietary column or format called capacitor. That is the storage format, and it sits on top of Colossus.

Host 1

Colossus being the file system.

Host 2

Right. Colossus is Google's globally distributed file system. It's the successor to the original Google file system in Borg. That handles the persistent state. The compute is entirely separate, driven by the Dremel distributed execution tree.

Host 1

The execution tree is what fascinates me in the source material. It's not a flat cluster of workers pulling tasks from a queue. It is this massive multi-level hierarchy designed for petabyte scale fan out.

Host 2

Imagine a massive corporate organizational chart. When you submit a SQL query to BigQuery, it arrives at a root server. Think of that as the CEO. The root server parses the query, rewrites it, and realizes it needs to scan billions of rows. But it doesn't do the work itself.

Host 1

It delegates.

Host 2

Exactly. It shatters that query into smaller fragments and pushes them down to the next level of the tree, the intermediate servers, the vice presidents. Those servers further shadow the query and push it down to the leaf nodes, the thousands of individual workers.

Host 1

And the leaf nodes are the only ones actually making contact with the Colossus storage layer to read the capacitor files.

Host 2

Yes. They read the columnar data, apply the initial filters, like pulling out only the sales from a specific region, and then they have to send the results back up the chain. But here's the critical part.

Host 1

They do it in memory, right? Over the network.

Host 2

Right. BigQuery leverages Google's petabit scale Jupyter network to perform in-memory shuffles.

Host 1

An in-memory shuffle across thousands of machines just sounds like a networking nightmare. I mean, how does the network not just collapse under the weight of thousands of nodes talking to each other simultaneously?

Host 2

They built dedicated specialized networking hardware just for shuffling data. When the leaf nodes finish their filtering, they push the intermediate data directly into a massive memory fabric shuffle tier. The intermediate servers pull from that shuffle tier, perform the next level of aggregations, like a group by operation, and push their results further up.

Host 1

So it's just this constant stream of aggregation.

Host 2

Yeah, the data flows back up the tree, continually being aggregated and reduced at each level until a tiny finalized result set is handed back to the root server and ultimately to you.

Host 1

So both Snowflake and BigQuery conquered the petabyte scale by decoupling storage and compute. And they both inserted a highly intelligent proprietary metadata brain in the middle.

Host 2

They did.

Host 1

But to really appreciate why that brain is necessary, we have to look at the evolutionary dead end that tried to skip it. The raw data lake.

Host 2

Oh, the raw data lake era. This was a painful period where the industry collectively looked at the cheap storage pricing of Amazon S3 and thought, you know, why are we paying for proprietary data warehouses?

Host 1

It sounds so tempting on paper.

Host 2

It does. They thought, let's just dump all our raw parquet files into an S3 bucket, point a massively parallel processing engine like Presto or Trino at the bucket, and query it directly.

Host 1

And you can see why. The economics look incredible. You have zero vendor lock-in on your storage format because parquet is open source. You have cheap object storage, and you have open source compute. So why did this architecture devolve into such a disaster for nearly every team that tried to scale it?

Host 2

It fails spectacularly because it lacked that centralized transactional metadata brain. In a raw data lake, your table structure was dictated entirely by hive style directory partitioning. You would create a literal folder path in S3, like year equals 2023, inside that a folder for month equals 10, inside that a folder for day equals 31, and inside that you would dump your parquet files.

Host 1

And when you look at that in a visual browser, it looks beautifully organized. It looks exactly like a file system on a Mac or a Windows machine.

Host 2

But cloud object stores are not POSIX compliant file systems. They are distributed key value stores. To find data, the query engine had to make an S3 list API call on those directory prefixes.

Host 1

Which is agonizingly slow.

Host 2

Brutally slow. If you had an automated pipeline writing files every five minutes, you might have 10,000 files in a single daily partition. A list call against 10,000 files on S3 takes an excruciatingly long time. The engine is paralyzed, just waiting for the network to tell it what files exist.

Host 1

And the performance penalty of those list calls wasn't even the fatal flaw. The fatal flaw was the complete absence of asset guarantees. Automasticity, consistency, isolation, and durability.

Host 2

That's traits of concrete scenario. You have an Apache Spark job running an ETL pipeline. It is tasked with writing 100 new parquet files into a partition. It writes 50 files successfully, but then one of the Spark executor nodes runs out of memory and crashes.

Host 1

So the job fails.

Host 2

The job fails. And because S3 does not natively support atomic multi-file transactions, there was no rollback. You now have 50 half-written corrupted files sitting in your production bucket.

Host 1

And because there's no metadata layer isolating the readers from the writers, the moment those files physically land in the bucket, they are instantly visible, right?

Host 2

Instantly visible.

Host 1

So a data analyst running a dashboard query on Trino will hit those 50 corrupted files. Trino will attempt to parse them, and the entire dashboard will crash with a file corruption error.

Host 2

There was no concept of a commit. There was no mechanism to say, do not let anyone read these files until I guarantee the entire batch is finished and turn pristine data lakes into toxic data swamps.

Host 1

What a mess.

Host 2

Furthermore, those directory structures were completely rigid. If you partitioned your petabytes of data by date, and a year later the business decided they needed to heavily filter queries by customer ID, your partition scheme was useless.

Host 1

The engine would have to scan everything.

Host 2

Exactly. To fix it, you had to spin up a massive compute cluster and physically rewrite the entire multi-petabyte data set into a new directory structure.

Host 1

This proves that the actual secret weapon of Snowflake and BigQuery was never just their raw execution speed. It was the proprietary metadata layer acting as a bouncer. The bouncer ensures that a query engine only ever sees files that belong to fully completed successful transactions. It completely obscures the half-written files, the tombstones of deleted records, and the inherent messiness of the distributed object store.

Host 2

If we pull the lens back, we realize that to survive the network latency penalty of decoupled storage, a query engine's primary job is no longer reading data. Its primary, most critical job is actively avoiding reading data. We have to examine how metadata allows these engines to skip 99% of the bytes on disk.

Host 1

Right, because if you are scanning all your data, your architecture has failed. I want to get extremely tactical here. I want to walk step by step through the exact mechanical sequence of how a query engine pars a parquet file over an S3 HTTP connection.

Host 2

It's a very different process.

Host 1

Yeah. Reading a file over the web is not like reading a file from a local hard drive where the operating system handles the block loading.

Host 2

Let's trace it. Imagine you're using a modern engine like DuckDB to query a specific parquet file residing on an S3 bucket. The engine does not issue a command to download the one gigabyte file. Parquet is explicitly engineered for decoupled network access. The engine issues a very specific HTTP range get request. It asks the S3 server to send back only the final few kilobytes of the file.

Host 1

It's just fetching the parquet footer.

Host 2

Yes, it is fetching the parquet footer.

Host 1

What exactly is packed into that footer that makes it so valuable?

Host 2

The footer is the master index for that specific file. It contains the magic bytes that identify it as a parquet file, the structural schema, and the metadata length. But most importantly, it contains the page index and the exact byte ranges for every single row group and column chunk inside the file.

Host 1

Okay, so it maps out where everything is physically located.

Host 2

Exactly. And alongside those byte ranges, it stores statistical metadata, the absolute minimum and maximum values, and the null counts for every single column within every row group.

Host 1

Let's apply that to a real query. Suppose I write a SQL statement, like select customer name from sales where transaction date equals 2023-10-31. The DuckDB engine fetches the footer first. It looks at the statistical metadata for the transaction date column inside the very first row group. Right. And if the metadata states that the minimum date in this row group is November 1st and the maximum is November 30th, the engine instantly knows that my target date of October 31st cannot possibly exist in that chunk of bytes.

Host 2

And because it knows that with absolute certainty, the engine simply skips it. It does not issue the subsequent HTTP range get request to fetch the raw bytes of that row group. The compute layer never touches the data. It drops the row group entirely based solely on the mathematical boundaries found in the footer.

Host 1

So that is the definition of byte range pruning.

Host 2

That's byte range pruning.

Host 1

It is functionally identical to walking into a library, grabbing the index of a 10,000-page encyclopedia, locating the exact three pages that mention your topic, and then physically tearing only those three pages out of the book to take back to your desk, completely ignoring the other 9,997 pages.

Host 2

It is. And this architectural concept scales up exponentially. In Snowflake, those 50 to 500 megabyte micropartitions track their minimum, maximum, and distinct values globally up in the cloud services layer. Before a virtual warehouse even spins up its compute nodes to full utilization, the cloud services layer has already evaluated your where clause against the metadata of millions of micropartitions.

Host 1

So it filters before compute even happens?

Host 2

Right. It acts as a massive filter, pruning away 99% of the micropartitions before the query execution plan is even handed down to the compute nodes.

Host 1

BigQuery executes a similar strategy through the Drummel tree, right? The root and intermediate servers utilize metadata catalogs to ruthlessly prune partitions and block ranges long before the leaf nodes are instructed to initiate reads against Colossus. Pruning happens at the metadata tier before the compute slots engage with the storage.

Host 2

This is where the terrifying cloud cost mechanics come into play. Specifically with big queries on-demand model. If the metadata layer is intelligent enough to prune a 50 terabyte table down to 40 gigabytes before the query executes, you're only billed for scanning 40 gigabytes. That is the difference between a query costing 12 cents and a query costing 250 dollars.

Host 1

But wait, I have to challenge the library index analogy here. An index only works if the information in the book is actually organized. If the data is completely chaotic, say user sign-up events scattered randomly across millions of files because they were written by a hundred concurrent streaming pipelines.

Host 2

That is a very real problem.

Host 1

The min max ranges for every file will overlap entirely. In that scenario, pruning is physically impossible. The engine's metadata brain will say, well, the date might be in every single one of these files, and it will be forced to scan everything.

Host 2

You've identified the absolute physical limitation of statistical pruning. If the data distribution is random, the minimum and maximum ranges overlap completely, and the engine cannot confidently skip anything. To achieve the performance we are talking about, you must physically cluster and sort the data on disk.

Host 1

So if you sort the records by transaction date before you write the parkade files, then file one exclusively contains January records, file two contains February records, and so on. The min max ranges become incredibly tight and distinct. Zero overlap.

Host 2

Sorting is the fuel that makes metadata pruning work. Snowflake manages this clustering entirely in the background as data is ingested, continually reorganizing the micropartitions to maintain tight value ranges. BigQuery allows engineers to explicitly define clustering keys to achieve the same physical alignment on Colossus.

Host 1

So you have to be intentional about it.

Host 2

You do. If you do not cluster your data, you are executing full table scans. And your cloud bill will reflect it, no matter how sophisticated your query engine is. Physics is undefeated.

Host 1

So the proprietary cloud warehouses prove the blueprint. A transactional metadata layer combined with physical clustering and intelligent bite range pruning is the only way to achieve high performance and acid guarantees over decoupled object storage.

Host 2

They proved the architecture was bulletproof. And once the broader industry understood the mechanics of that blueprint, the open source community realized they didn't need to pay a vendor premium for it. They wanted those exact same transactional guarantees, but they wanted them built on open standards without being locked into proprietary storage formats like capacitor or Snowflake's hidden micropartitions.

Host 1

When you realize that the metadata layer is just a software brain, you realize you can just build that brain yourself directly on top of your own S3 buckets and your own open parquet files. And that realization birthed the lakehouse architecture and the open table formats.

Host 2

The lakehouse architecture is essentially the democratization of the proprietary warehouse brain. It extracts the transactional metadata layer of a Snowflake or a big query and formalizes it into an open specification. Any query engine, Trino, Spark, Flink, DuckDB can read and write to it.

Host 1

And there are three main players here, right?

Host 2

Yeah. The three undisputed giants dominating the space are Apache Iceberg, Delta Lake, and Apache Hooty. They all solve the exact same foundational problem, turning a chaotic bucket of immutable parquet files into a strictly ACID compliant database. But they engineered different internal structures to achieve it.

Host 1

Let's dissect Apache Iceberg first. The source material goes into extensive detail on its architecture and it is a masterclass in indirection. Iceberg utilizes a strict four-tier metadata hierarchy. Let's trace a transaction through it from the top down. Tier one is the catalog. What is the fitnical mechanism of the catalog?

Host 2

The catalog is the absolute singular source of truth for the current state of the database table. Because multiple distributed engines might try to write to the table simultaneously, the catalog is the only component in the entire architecture that requires a centralized locking mechanism or an atomic operation. Its sole responsibility is to store a single pointer.

Host 1

Just a pointer.

Host 2

Just a pointer. That pointer directs the system to the current metadata.json file.

Host 1

But if I have a Spark cluster inserting a million rows, how does it safely commit that write to the catalog without colliding with another job?

Host 2

It uses optimistic concurrency control, or OCC. Let's say your Spark job starts writing. It checks the catalog and sees the table is currently at version four. Spark goes off and writes the new parquet data files to S3 and builds all the new metadata files.

Host 1

Okay, so it does the work first.

Host 2

Right. When it is ready to commit, it goes back to the catalog and attempts a check input operation. It says, I built my new state based on version four. If the current pointer is still version four, atomically swap the pointer to my new version five. But if someone else already updated it to version five while I was working, fail my commit entirely. I will reread the new state and retry.

Host 1

That atomic swap is often implemented using a conditional put in DynamoDB or a transactional row update in a Postgres database acting as the catalog, right?

Host 2

Exactly. That single atomic pointer swap is the bedrock of serializable isolation in Iceberg. Once the pointer swaps, the new data is instantly visible to all raiders.

Host 1

Okay, so the catalog pointer swaps. What is it pointing to? Tier 2, the metadata.json file?

Host 2

The metadata JSON file defines the absolute state of the table in one specific microsecond in time. It contains the schema definition, the partitioning rules, and an array of all historical snapshots of the table. Most importantly, it defines the current snapshot dash ID.

Host 1

Which leads us to tier three.

Host 2

The manifest list.

Host 1

The metadata. The source material notes that this manifest list is written in Avro format. Why Avro? Why wouldn't they use parquet for the metadata too?

Host 2

That is a crucial architectural decision. Parquet is a columnar format. It is designed for analytical queries, scanning millions of rows of a few specific columns. It has a heavy memory overhead to write because it has to buffer entire row groups in memory to compress the columns.

Host 1

And Avro is different.

Host 2

Very different. Avro is a row-based format. It is incredibly efficient for writing and sequentially reading thousands of small complete records, which is exactly what metadata is. Iceberg uses Avro because a query engine needs to read the entire metadata record sequentially to plan the query. And Avro is splitable and fast for that specific access pattern.

Host 1

That makes perfect sense. So the Avro manifest list tracks which manifest files belong to the snapshot. To optimize the pruning, the manifest list also stores partition statistics. It tracks the upper and lower bounds for the partition columns of every manifest file it references.

Host 2

Right, which allows the query planner to drop entire manifest files from its execution plan without ever opening them.

Host 1

And finally, tier four, the manifest files themselves, also in Avro. These are the files where the metadata meets the actual storage. A manifest file contains a ledger of the individual immutable parquet data files.

Host 2

Right. It alongs the S3 URI path, the partitioned it belongs to, and the file level column statistics, the minimum and maximum values, and the null count for every single column inside that specific parquet file. The data you see when you query a snapshot is simply the union of all the live parquet files listed in its active manifests.

Host 1

When you lay it all out like that, I mean the sheer volume of indirection is staggering. We've gone from a simple folder of files to a table that is fundamentally just a pointer in a dynamo DB catalog, which points to a JSON file on S3, which points to an Avro manifest list, which points to hundreds of Avro manifest files, which finally, eventually, point to the actual parquet data blocks.

Host 2

It sounds overly complex, but that heavy indirection is the exact mechanism that enables lock-free concurrent operations at petabyte scale. When a Trino cluster executes a read query, it queries the catalog, grabs the current snapshot, and starts reading the associated files. Meanwhile, a Flink streaming job can be simultaneously writing new parquet data files and building new Avro manifests completely in the background.

Host 1

So they don't step on each other's toes.

Host 2

Exactly. The writers never lock the data files, they never block the readers. It is only in the final millisecond when the writer successfully executes the atomic pointer swap in the catalog that the new data materializes for subsequent queries.

Host 1

And because every historical state is preserved in this metadata tree, it unlocks incredible capabilities. Take schema evolution. In the old hive days, changing a column name across a petabyte of data was a nightmare.

Host 2

Oh, an absolute nightmare.

Host 1

But in Iceberg, it's a metadata operation. The metadata JSON tracks an internal ID for every column. If you rename user ID to customer ID, Iceberg just updates the JSON to map the new name to the existing ID. The engine knows how to map it at query time. It requires zero rewriting of the underlying parquet files.

Host 2

Iceberg also solved one of the most frustrating aspects of data engineering, hidden partitioning.

Host 1

Right. In older architectures, if the business wanted the data partitioned by month, but the raw data only contained a granular timestamp column, the data engineers had to physically alter the pipeline. They had to inject a transformation step to extract the month string from the timestamp, create a brand new physical column called month, and write the data into partitions based on that new column.

Host 2

And the inevitable failure mode of that architecture was user error. An analyst would write a query filtering on the timestamp column, completely oblivious to the fact that the physical partition was built on the month column. The query engine, lacking a direct partition filter, would execute a catastrophic full table scan across years of data.

Host 1

Iceberg completely hides the structural complexity. You define the table schema with just the timestamp column. Then you tell the iceberg metadata layer, partition this table by month, timestamp. When a writer inserts data, the iceberg library automatically derives the month value on the fly and routes the parquet file to the correct logical partition.

Host 2

And more importantly, when the analyst queries the timestamp column, the iceberg query planner intercepts the filter. It mathematically translates the timestamp range into the corresponding month partitions automatically. The user never needs to know the physical storage layout. The system protects the user from themselves.

Host 1

It also enables write audit publish or WAP. You can tell Iceberg to create a branch of the table, just like Git. You run your heavy ETL pipelines to ingest data into that branch. It is completely invisible to production users. You then run automated data quality audits against the branch.

Host 2

Checking for nulls or anomalies.

Host 1

Right, like did revenue drop by 50%. Only if the audits pass cleanly do you execute a fast-forward operation on the catalog pointer to merge the branch into the main production trunk.

Host 2

It introduces rigorous software engineering CICD practices to petabyte scale data pipelines. Now, Iceberg's four-tier tree is brilliant, but it is not the only way to build a lake house. Let's contrast it with Delta Lake, which was originally architected by Databreaks before being open sourced. Delta engineered a fundamentally different metadata structure.

Host 1

According to the Delta Protocol specifications, there is no centralized catalog pointer swap acting as the primary commit mechanism. Instead, Delta relies on a delta log directory physically located right alongside the data files in the S3 bucket.

Host 2

Yes. Delta leans heavily on the file system itself. Instead of a nested tree of manifests, Delta maintains a sequential chronological transaction log inside that Delta log directory. Every single commit to the table is written as a brand new JSON file, numbered sequentially in ascending order. You will see 00000.json, 00001.json, 00002.json, and so forth.

Host 1

What exactly is stored inside those sequential JSON files?

Host 2

They contain arrays of discrete actions. When a transaction occurs, the JSON logs the actions taken. It lists the physical paths of new parquet files being added to the table. It lists the tombstones of old parquet files being logically removed, and it logs any changes to the schema or table properties. To determine the current state of the table, a query engine simply lists the delta log directory, reads all the JSON files in order, and replays the sequence of actions in its memory.

Host 1

Having a query engine read and replay a million JSON files just to figure out what data exists sounds like the exact same S3 list and read bottleneck that killed the raw data lake.

Host 2

It would be, which is why Delta implemented periodic checkpoints. By default, after every 10 commits, the Delta writer takes the entire aggregated state of the table and writes it out as a single highly optimized parquet file, the checkpoint.

Host 1

Oh, clever.

Host 2

So when an engine wants to read the table, it doesn't read a million JSONs. It finds the most recent Farquay checkpoint file, reads the consolidated state, and then only has to read the handful of JSON commit files that have been generated since that checkpoint was created.

Host 1

The Delta source material also heavily emphasizes a feature called liquid clustering. We discussed earlier how static, rigid partition directories eventually break down. How does liquid clustering approach data layout differently?

Host 2

Liquid clustering is a radical departure from traditional hive style static partitioning. Instead of defining fixed partition columns up front, you define clustering keys, the columns you filter on most frequently. As data is written and subsequently compacted, Delta's internal algorithms dynamically group related data into files using space-filling curves like Z order curves. It intelligently adapts to changing data patterns.

Host 1

So you don't have to guess the perfect partition size forever.

Host 2

Exactly. It completely eradicates the classic problems of overpartitioning, where you end up with millions of tiny files because your partition key is too granular, or data skew, where one partition has 10 terabytes and another has 10 megabytes. It dynamically ensures that metadata pruning remains highly effective regardless of how the data volume shifts over time.

Host 1

Okay, we've covered the metadata trees of iceberg and the transaction logs of Delta. What about Apache Hoodie? The sources highlight Hoodie's unique, almost obsessive focus on streaming ingestion and upserts.

Host 2

Hooty, which stands for Hadoop Upserts, Deletes and Incrementals, was birthed at Uber. Uber didn't need to load massive batch files once a day. They needed to ingest millions of continuous real-time events, ride updates, location pings, financial transactions, directly from their operational databases into their data lake. They needed a system designed specifically to handle a relentless barrage of change data capture or CDC streams.

Host 1

But executing an upsert, updating an existing row if it exists or inserting it if it doesn't, is notoriously punishing on cloud object storage. As we established, S3 files are immutable. You cannot open a parquet file and overwrite a single customer's address.

Host 2

Correct. To execute an upsert efficiently, the engine first has to identify exactly which physical parquet file currently houses the older version of the row you are trying to update. If you have a petabyte of data, doing a full scan to find one row is impossible. Hooty solved this by introducing advanced embedded indexing techniques directly into the table format. It utilizes Bloom filters and record level indexes.

Host 1

Let's break down how a Bloom filter actually works in this context, because it's a fascinating piece of computer science that almost feels like magic.

Host 2

A Bloom filter is a probabilistic data structure. It's essentially a massive array of bits, all initially set to zero. When Hootie writes a parquet file, it takes the unique primary key of every row in that file, runs it through several different hash functions, and uses the resulting numbers to flip specific bits in the Bloom filter to one. This Bloom filter is then saved in the metadata for that specific file.

Host 1

So when a new upsert arrives for user ID 456, how does the Bloom filter prevent a massive read operation?

Host 2

The Hootie engine takes user ID 456, runs it through the exact same hash functions, and checks those specific bit positions in a file's Bloom filter. If even one of those bits is still a zero, the engine knows with absolute mathematical certainty that user ID 456 does not exist in that file. You can skip the file entirely without ever reading a byte of data.

Host 1

But what if all the bits are ones?

Host 2

Now, because hash collisions occur, if all the bits are ones, it means the row might be in the file. It's probabilistic. But the ability to definitively say this row is not here allows Hootie to bypass thousands of files during a massive CDC upsert batch.

Host 1

But finding the file is only half the battle. Once you find it, you still have to deal with the immutability problem. This leads us directly into Hootie's defining architectural choice, and really the perfect transition into the harsh realities of our next section. Trade-offs and production failure modes. Because while transforming an S3 bucket into a transactional database sounds miraculous, you cannot cheat the physics of distributed systems. Hootie exposes this by offering two distinct table types to handle updates. Copy on write and merge on read.

Host 2

This is the fundamental inescapable trade-off in modern data architectures. You have to choose where you want to pay the compute tax during the write or during the read. Let's examine copy on write or cow first. In a cow table, every single time you update or delete a record, the engine is forced to read the entire existing base parquet file into memory, apply the single row update, and rewrite a completely new parquet file containing the merged data.

Host 1

Let's put concrete numbers on that. If I have a highly optimized 100 megabyte parquet file containing two million rows, and a CDC stream sends an update, changing a single Boolean flag on exactly one row, I have to rewrite the entire 100 megabytes.

Host 2

Yes. That phenomenon is called massive write amplification. You are rewriting hundreds of megabytes just to modify a few bytes. If your architecture relies on frequent continuous micro batch updates, like a streaming CDC pipeline syncing every 60 seconds, copy on write will utterly destroy your write latency. Your pipelines will back up, and your compute costs will skyrocket because your clusters are spending all their CPU cycles constantly rewriting massive files.

Host 1

However, the immense benefit of copy on write is experienced by the consumers. Because the data on disk is always stored in fully consolidated, perfectly optimized parquet files.

Host 2

Extremely fast.

Host 1

The query engine just reads the files and returns the map. There is zero complex merging required at query time. So CAW is the perfect architecture for read-heavy workloads, like traditional batch ETLs that load once a night but are queried by hundreds of dashboards all day.

Host 2

Exactly. But for those high-frequency streaming use cases that Uber faced, Hoodie and subsequently Iceberg and Delta engineered the alternative. In an MOR table, when an update arrives, the engine absolutely refuses to rewrite the base parquet file. Instead, it writes the updated row to a very lightweight delta file or a log file, which is typically row-based like Avro.

Host 1

So the write operation is nearly instantaneous. You're just appending small changes to a sequential log. Your write latency drops to milliseconds and your write amplification is virtually eliminated.

Host 2

Yes, the writers are incredibly happy. But you have merely shifted the compute tax to the readers. When an analyst runs a query against a merge on read table, the query engine must pull down the base parquet files and pull down all the associated delta log files, and it must execute a complex CPU-intensive merge operation in memory at query time to construct the correct latest view of the data.

Host 1

Ouch.

Host 2

This causes severe read amplification and adds massive compute overhead and latency to every single query.

Host 1

It's like managing your personal finances by letting your house fill up with thousands of sticky notes instead of writing in a ledger. The writing part is fast. A bill comes in, you scribble it on a sticky note and slap it on the wall. But when you finally want to know your current bank balance, you have to read the main ledger and then walk around the house, finding and mentally calculating thousands of sticky notes to arrive at the final number. It is an exhausting read operation.

Host 2

It is exhausting, and it leads directly to the single most dangerous operational trap of the entire Lake House architecture, the small file problem. If you are streaming one-minute micro batches into a merge on read table, or even just concantly appending new data to a copy on write table, you are generating millions upon millions of tiny one megabyte parquet or Avro files.

Host 1

A lot of engineers assume that because S3 storage space is practically free, having millions of files isn't a problem. Why is it fatal?

Host 2

Because S3 API requests are not free. Every single file requires an individual HTTP GET request. If your query planner determines it needs to scan a million tiny files, the network overhead completely paralyzes the system. The query engine's connection pools max out. The JVM heap memory on the Trino or Sparkmaster node explodes as it tries to hold the metadata for a million files in RAM. The execution workers spend 95% of their time just waiting for network connections to open rather than actually processing data. Your query grinds to a halt.

Host 1

To fix the sticky note problem in your house, you eventually have to sit down, spend a weekend consolidating all the notes into the main ledger, and throw the sticky notes in the trash. In lakehouse terms, this requires mandatory maintenance. Exposing this reality is critical. The marketing term open storage does not mean zero operations.

Host 2

If you deploy a lake house, you are committing your engineering team to operating three mandatory continuous maintenance loops in the background. First, you must execute bin packing compaction, most commonly invoked via the optimize command. This job spins up a compute cluster specifically to physically read all those millions of tiny files and rewrite them into tightly packed, optimized 100 to 500 megabyte files. This physically resolves the small file problem and merges the MOR delta logs into the base files.

Host 1

But wait, because of the transactional metadata layer we discussed earlier, when the optimize job rewrites those files, the old tiny files are not physically deleted from S3, right? Because an older snapshot might still need them for time travel queries.

Host 2

Correct. The metadata layer simply marks the new large consolidated file as active in the current snapshot. It marks the old tiny files as logically removed from the current snapshot, but they remain physically sitting on the S3 bucket to preserve history. This necessitates the second mandatory maintenance loop, expiring snapshots. You must run a scheduled job that tells the catalog, delete any snapshot history older than seven days.

Host 1

And even after you expire the snapshot, the physical files are still sitting on S3, costing you money.

Host 2

Yes. Expiring the snapshot just deletes the metadata reference. The final crucial maintenance loop is cleaning up the orphaned data bytes invoked via the vacuum command. The vacuum job scans the physical S3 bucket, compares every physical file against the files legitimately referenced by active snapshots, and physically issues the S3 delete API calls for the unreferenced orphans.

Host 1

The iceberg source material highlights a massive hidden trap here. Unexpired snapshots will block the vacuum process. If your team forgets to schedule the snapshot expiration job, your vacuum job will run. See that millions of old files are technically still referenced by an ancient snapshot from six months ago, and it won't delete a single byte. Your S3 storage bill will just grow exponentially in the background until accounting flags it.

Host 2

It is a massive hidden cost. And we haven't even discussed concurrency conflicts. If two writers attempt to modify the exact same partition at the exact same time, one of them is going to fail.

Host 1

Let's paint a vivid scenario of how this actually breaks in production. Imagine it's 2 a.m. Your background optimized job kicks off, reading a partition of small files to consolidate them. While it is doing the heavy lifting, a completely separate high priority GDPR compliance pipeline kicks off to delete a user's record from that exact same partition.

Host 2

The GDPR pipeline finishes first.

Host 1

It writes a new file without the user, commits the new snapshot to the catalog, and logs that it logically removed the old file. Ten minutes later, the optimized job finishes consolidating the data and attempts to commit its changes to the catalog. And the Delta Lake documentation explicitly defines what happens next. The catalog sees the optimized commit, realizes that the state of the files the job based its calculations on has already been altered by the GDPR pipeline and throws a concurrent delete read exception. The entire massive optimized job fails and has to be retried.

Host 2

So concurrent jobs are modifying entirely disjoint sets of files. But if you overpartition just to avoid write conflicts, you instantly recreate the small file problem. It is a relentless continuous balancing act.

Host 1

This demands significant human intervention. Operating an open table format at scale demands dedicated data engineering headcount. You need engineers to write the terraform, orchestrate the airflow DAGs, monitor the compaction scripts, tune the memory of the vacuum jobs, and debug the OCC conflict retries. Which brings me to a major architectural question. If an open lake house requires a dedicated platform team just to keep the lights on, isn't a managed proprietary warehouse like Snowflake or BigQuery actually cheaper in total cost of ownership for most companies? Sure, you pay a premium on the vendor invoice, but Snowflake handles all the compaction, clustering, and garbage collection entirely invisibly in the background. Zero engineers required.

Host 2

This is the crux of the entire architectural debate. You are trading a vendor bill for an engineering payroll. Let's revisit our opening scenario. BigQuery's on demand model charges roughly $5 per terabyte scanned. A select on 50 terabytes costs $250 per execution. If you partition and cluster perfectly, that same query scans 40 gigabytes and costs 20 cents. But if your users write sloppy queries, your bill explodes instantly. Snowflake charges for the time the virtual warehouse is active. If you provision an Excel warehouse and set it to auto-suspend after 10 minutes of idle time, but a tiny automated cron job query hits it every nine minutes, that warehouse never suspends. You burn massive compute credits 24 hours a day for almost zero actual compute work.

Host 1

So managed warehouses completely abstract the operational complexity, but they expose you to massive abrupt cost cliffs if you do not aggressively monitor and govern user behavior. Lake houses dramatically reduce the raw compute costs by allowing you to utilize cheaper compute engines, like spinning up transient spot instances for Spark, and they eliminate vendor lock-in on your storage layer. But they shift that cost directly onto your operational headcount. Exactly.

Host 2

And that leads us to the ultimate architectural decision. Given the physics of pruning, the reality of compute costs, and the heavy operational burden of lakehouse maintenance, how do technical leads actually decide where their data belongs?

Host 1

Well, based on the source material, we can lay down three quantified, concrete decision boundaries based on data scale, engine diversity, and your organization's platform engineering capability. Boundary number one. If your entire analytical data set is under 500 gigabytes to one terabyte.

Host 2

If your entire analytical footprint is under one terabyte, do not build a distributed system. Do not spin up a Spark cluster. Do not sign a contract for a managed warehouse. You should utilize a single node engine like DuckDB. You can query Iceberg or Raw Parquet files directly from S3 using a single EC2 instance or even a powerful MacBook. At that scale, the data fits entirely in RAM or on local NVMe cache. The complexity, network latency, and operational overhead of distributed compute are entirely unwarranted. Keep it simple.

Host 1

Boundary number two. For workloads sitting between one terabyte and roughly 20 terabytes, managed by lean engineering teams where your dedicated database administrator or DBA headcount is effectively zero.

Host 2

This is the absolute sweet spot for managed warehouses like Snowflake or BigQuery. Yes, you pay a proprietary premium on compute. But for a lean team, that premium is entirely justified to acquire automated compaction, automatic data clustering, and zero maintenance garbage collection. The loaded cost of hiring a single dedicated data engineer to manage an open lake house will far exceed the vendor premium at the 10 terabyte scale. Let the vendor handle the physics.

Host 1

But once you cross that threshold, boundary number three, for multi terabyte to petabyte scale fleets, anything significantly over 20 terabytes that require multi engine access.

Host 2

This is where open lake houses, iceberg Delta Hoodie, transition from a luxury to a mandate. When you are harboring hundreds of terabytes of data, you cannot afford to lock it inside a proprietary warehouse where you are forced to pay their specific compute premium just to read your own data. You require engine diversity. You need Apache Spark for massive heavy ETL processing. You need Trino for low latency ad hoc BI dashboards. You need Flink for real-time streaming, and you need DuckDB for local data science experiments. A lake house allows all of those distinct engines to read the exact same centralized data seamlessly without copying it. At the petabyte scale, the massive compute savings and the total avoidance of vendor lock-in easily outweigh the payroll cost of staffing and dedicated platform team to manage the compaction and vacuum operations.

Host 1

The architecture must fundamentally match the scale of the data and the operational capability of the organization. Well, that wraps up our deep dive into the petabyte tier of modern data stores. It's fascinating how the inescapable physics of network latency drove the industry to invent these incredibly complex, highly decoupled metadata brains.

Host 2

It really is. And in our next deep dive, we will shift gears entirely. We are moving away from analytical stores to explore no SQL families, examining operational, non-relational data stores and detailing exactly how they handle high throughput, low latency transactional workloads.

Host 1

Before we go, I want to leave you with a final provocative thought to mull over. We have seen how open table formats, specifically Apache Iceberg, are rapidly solidifying into ubiquitous industry standards, natively supported by almost every major analytical engine on the market. If cloud storage costs continue their race to the absolute bottom, and compute becomes entirely and seamlessly decoupled from storage, will we eventually witness the complete death of the proprietary storage format? Are we heading toward a future where giants like Snowflake, BigQuery, and Databricks stop competing on how they organize bytes on disk and simply compete entirely on who can execute the math the fastest over the exact same universally shared open iceberg bucket? Think about what that commoditization does to the entire software industry. Until next time.

Save to playlist

Manage playlists →

Share