Designing a Data Lakehouse: Unifying Data Lakes and Warehouses with Iceberg, Delta Lake, and Modern Query Engines

The standard path for analytics infrastructure used to be: dump data into S3, call it a data lake, then add a warehouse (Snowflake, BigQuery, Redshift) when you need reliable queries. The problem is the gap between the two. Your lake has raw history, but the warehouse only has what you remembered to load. Your warehouse has ACID guarantees, but S3 has none. Keeping them synchronized requires a pipeline that is perpetually slightly wrong.

The lakehouse architecture closes this gap by pushing warehouse-grade semantics down into the storage layer. You keep data in open columnar formats on object storage, but you add a table format layer that provides transactions, schema enforcement, and metadata management. The result is a single system that can serve both the ELT pipelines that write raw events and the BI tools that need consistent, queryable snapshots.

This article covers how to build that system: the storage formats, the three major table format options, catalog services, query engine integration, and the operational concerns that appear once you are running this in production.

---

The Storage Layer: Parquet and ORC

Both Apache Parquet and Apache ORC are columnar storage formats designed for analytical workloads. The core property they share: data is stored column by column rather than row by row, so a query that reads only three columns from a table with forty columns avoids reading the other thirty-seven entirely. At scale, this is the difference between a query scanning 40GB and one scanning 3GB.

Parquet is the default for most lakehouse implementations. It is supported natively by Spark, Trino, DuckDB, Pandas, Arrow, and every table format discussed in this article. Its internal structure is built around row groups (typically 128MB), column chunks within each row group, and pages within each column chunk. Min/max statistics are stored per row group per column. A predicate like WHERE event_date = '2025-03-01' can skip entire row groups whose date ranges do not overlap, without reading any data.

ORC was developed at Hortonworks for Hive and carries stronger built-in statistics (bloom filters per stripe, per-column min/max/count). It performs comparably to Parquet for most query patterns. The practical reason to choose Parquet over ORC today is ecosystem breadth: DuckDB’s native ORC support is limited, and most new tooling assumes Parquet.

What neither format provides on its own: transactions, schema enforcement, or any concept of “the current state of this table.” A directory full of Parquet files is not a table. It is a pile of files. The table format layer is what turns it into a table.

---

Table Formats: What They Actually Do

A table format is a specification for how metadata is organized around a set of data files. It tracks which files belong to a table, what the current schema is, how data is partitioned, and what the history of changes looks like. The three active formats are Apache Iceberg, Delta Lake, and Apache Hudi. They solve the same core problem with meaningfully different designs.

Apache Iceberg

Iceberg organizes metadata as a tree: each table has a current metadata file (JSON) that points to a snapshot, which points to a set of manifest lists, each of which points to manifest files that enumerate the actual data files with their statistics.

The snapshot model is what makes Iceberg’s time travel and concurrent writes tractable. Each write creates a new snapshot by adding a new manifest list and updating the metadata pointer atomically. Readers take a snapshot reference at query start and read from that snapshot for the duration of the query, regardless of concurrent writes. No locks required.

Partition evolution is a property unique to Iceberg among the three formats. Iceberg tracks which partition spec was in effect when each data file was written. This means you can change the partition scheme of a table (from date to date, hour) without rewriting historical data. The query engine knows to use the old partition spec for old files and the new one for new files.

Schema evolution in Iceberg is based on column IDs, not column names. When you add a column, Iceberg assigns it an ID. When you rename a column, the ID stays the same. Parquet files written before the rename still have data under the old name, but Iceberg maps them correctly via the column ID. This means renaming a column in Iceberg does not require rewriting any data files.

Delta Lake

Delta Lake, originated at Databricks, uses a transaction log stored as a sequence of JSON files in a _delta_log directory alongside the data files. Each file in the transaction log is a commit: it records which files were added, which were removed, and what the schema was at that point.

The transaction log design makes Delta Lake very fast for write-heavy workloads: each commit is a single JSON file append. The tradeoff is that reading the current state of the table requires replaying the log from the last checkpoint (written every 10 commits by default). For tables with very high commit rates, checkpoint lag can cause reader latency.

Delta Lake’s strength is the Spark ecosystem. If your write path is Spark, Delta Lake has the tightest integration: MERGE INTO, OPTIMIZE, VACUUM, and Z-ordering are all available as first-class SQL operations in Databricks and open-source Delta Lake 3.x.

Schema evolution in Delta Lake is column-name based. Renaming a column without rewriting data requires a schema migration entry in the transaction log, but the underlying Parquet files still use the old name. Delta Lake handles the mapping at read time via column mapping metadata, enabled explicitly with ALTER TABLE SET TBLPROPERTIES ('delta.columnMapping.mode' = 'name'). This is not enabled by default, which catches teams that assume rename-equals-rewrite.

Apache Hudi

Hudi (Hadoop Upsert Delete and Incremental) was designed at Uber for the specific use case of high-frequency upserts into large tables. It organizes tables into two storage types: Copy-on-Write (CoW), which rewrites entire data files on every upsert, and Merge-on-Read (MoR), which appends upsert deltas to log files and merges them at read time.

CoW reads are fast (no merge step), but writes are expensive (full file rewrite). MoR writes are fast, but reads incur a merge overhead. For CDC-driven pipelines that ingest database change events and need low write latency, MoR is the right choice. For pipelines where read latency matters more than write latency, CoW is correct.

Hudi’s incremental query mode is the property that sets it apart for CDC use cases: you can query only the records that changed between two instants, without scanning the entire table. For pipelines that propagate changes downstream (updating a serving layer from a raw events table), this dramatically reduces query cost compared to Iceberg or Delta Lake, which require scanning a full snapshot diff.

---

Format Comparison

Property	Iceberg	Delta Lake	Hudi
Metadata model	Snapshot tree (JSON + manifest files)	Transaction log (JSON sequence)	Timeline (commits as files)
Partition evolution	Yes, without rewriting data	No	No
Schema column rename	Column ID-based, no rewrite	Requires column mapping mode enabled	Column name-based
Concurrent writers	Optimistic concurrency with OCC	Optimistic with log append	Optimistic with timeline locks
Best query engine fit	Trino, Spark, DuckDB	Spark, Databricks SQL	Spark, Flink (for streaming)
CDC / incremental query	Snapshot diff only	Change data feed (opt-in)	Incremental query (native)
Compaction model	Explicit rewrite jobs	OPTIMIZE command	Inline compaction (MoR)
When to use	General analytics, multi-engine	Databricks-centric stack	High-frequency upsert, CDC sink

---

Catalog Services

The catalog is the registry that maps table names to their metadata file locations. Without a catalog, every query engine would need to know the exact S3 path of each table’s metadata file. The catalog provides the namespace (database name, table name) to metadata location mapping, and it enforces atomic metadata pointer updates.

AWS Glue Data Catalog is the most common choice for AWS-hosted lakehouses. It provides a managed Hive Metastore-compatible API and integrates with Athena, EMR, and Glue ETL. Iceberg, Delta Lake, and Hudi all support Glue as a catalog. The limitation: Glue catalog locking for Iceberg’s optimistic concurrency uses DynamoDB, which requires separate setup and adds latency per commit.

Project Nessie is an open-source catalog that provides Git-like semantics: branches, tags, and commits at the catalog level. You can create a branch of your entire data catalog, run a transformation pipeline on the branch, validate the results, and merge the branch back. This is useful for pipeline testing: run your new Spark job against a catalog branch, compare output to the main branch, and merge only if validation passes.

Apache Polaris (formerly Snowflake Open Catalog) and Unity Catalog (Databricks) are newer entrants that implement the Iceberg REST Catalog specification. The REST Catalog is the right abstraction: any engine that speaks the Iceberg REST protocol can connect to any compliant catalog without vendor lock-in.

// Iceberg REST Catalog client wrapper for metadata inspection
interface IcebergNamespace {
  namespace: string[];
}

interface IcebergTable {
  tableIdentifier: { namespace: string[]; name: string };
  metadataLocation: string;
  metadata: {
    currentSnapshotId: number;
    snapshots: Array<{
      snapshotId: number;
      timestampMs: number;
      summary: Record<string, string>;
    }>;
    schema: {
      fields: Array<{
        id: number;
        name: string;
        type: string;
        required: boolean;
      }>;
    };
    partitionSpec: {
      fields: Array<{
        sourceId: number;
        name: string;
        transform: string;
      }>;
    };
  };
}

class IcebergCatalogClient {
  constructor(
    private readonly baseUrl: string,
    private readonly warehouse: string,
    private readonly token: string
  ) {}

  private async request<T>(path: string): Promise<T> {
    const response = await fetch(`${this.baseUrl}/v1/${this.warehouse}${path}`, {
      headers: {
        Authorization: `Bearer ${this.token}`,
        "Content-Type": "application/json",
      },
    });

    if (!response.ok) {
      throw new Error(`Catalog request failed: ${response.status} ${path}`);
    }

    return response.json() as Promise<T>;
  }

  async listNamespaces(): Promise<string[]> {
    const data = await this.request<{ namespaces: string[][] }>("/namespaces");
    return data.namespaces.map((ns) => ns.join("."));
  }

  async listTables(namespace: string): Promise<string[]> {
    const encoded = encodeURIComponent(namespace);
    const data = await this.request<{ identifiers: Array<{ name: string }> }>(
      `/namespaces/${encoded}/tables`
    );
    return data.identifiers.map((t) => t.name);
  }

  async getTableMetadata(namespace: string, table: string): Promise<IcebergTable> {
    const encoded = encodeURIComponent(namespace);
    return this.request<IcebergTable>(`/namespaces/${encoded}/tables/${table}`);
  }

  async getSnapshotHistory(
    namespace: string,
    table: string
  ): Promise<Array<{ snapshotId: number; timestampMs: number; operation: string }>> {
    const meta = await this.getTableMetadata(namespace, table);
    return meta.metadata.snapshots.map((snap) => ({
      snapshotId: snap.snapshotId,
      timestampMs: snap.timestampMs,
      operation: snap.summary["operation"] ?? "unknown",
    }));
  }
}

This client gives you a queryable view of your lakehouse catalog without depending on Spark or any JVM tooling. Useful for observability dashboards, schema drift detection jobs, and CI checks that verify table metadata before promoting a pipeline deployment.

---

Query Engine Integration

Three query engines cover the vast majority of lakehouse query workloads: Spark for heavy transformations and writes, Trino for interactive ad-hoc SQL, and DuckDB for single-node analytics.

Spark has the deepest integration with all three table formats. For Iceberg, add the Iceberg Spark runtime jar and configure the catalog:

spark.sql.catalog.my_catalog = org.apache.iceberg.spark.SparkCatalog
spark.sql.catalog.my_catalog.type = glue
spark.sql.catalog.my_catalog.warehouse = s3://my-bucket/warehouse

From that point, spark.table("my_catalog.analytics.events") returns a DataFrame backed by the current Iceberg snapshot.

Trino supports Iceberg natively via its Iceberg connector and supports Delta Lake via the Delta connector. Trino does not support Hudi natively. For a multi-engine lakehouse, Iceberg is the path of least resistance if you want both Spark writes and Trino reads.

DuckDB added Iceberg support in version 0.10. For exploratory analysis and small-to-medium tables (under ~50GB), DuckDB’s ability to read Iceberg tables directly from S3 without any infrastructure is valuable:

-- DuckDB: read an Iceberg table directly from S3
INSTALL iceberg;
LOAD iceberg;

SELECT
  event_type,
  COUNT(*) as event_count,
  DATE_TRUNC('day', occurred_at) as day
FROM iceberg_scan('s3://my-bucket/warehouse/analytics/events', version = 'latest')
WHERE occurred_at >= CURRENT_DATE - INTERVAL 7 DAY
GROUP BY event_type, day
ORDER BY day DESC, event_count DESC;

The version = 'latest' argument tells DuckDB to resolve the current snapshot from the Iceberg metadata. You can also pass a snapshot ID for time travel:

SELECT COUNT(*) FROM iceberg_scan(
  's3://my-bucket/warehouse/analytics/events',
  version = '8234761092384012345'
);

---

Schema Evolution and Time Travel

Schema evolution is the property that lets tables grow and change without breaking downstream queries or requiring data rewrites. The practical scenarios:

Adding a column. In Iceberg, add a column to the metadata schema. Existing Parquet files that predate the column will return NULL for that column when queried. No file rewrite required.

Dropping a column. Mark the column as dropped in the schema. Existing data files still contain the column’s bytes, but query engines will not return it. If storage reclamation matters, schedule a compaction job after dropping the column.

Changing a column type. Only safe promotions are allowed without rewriting: INT to LONG, FLOAT to DOUBLE, STRING widening. Narrowing conversions require rewriting affected files. The table format will reject an unsafe type change at the metadata commit level, before any data is touched.

For time travel, Iceberg and Delta Lake both let you query a past state of the table. The use cases are debugging (what did this table look like before the bad pipeline run?), compliance (produce the state of this record as of a specific audit date), and incremental processing (what changed since yesterday’s snapshot?).

// Orchestrating time travel queries via a metadata-aware query runner
interface TimeRangeQuery {
  catalog: string;
  namespace: string;
  table: string;
  asOfTimestampMs: number;
  sql: string;
}

async function runTimeRangeQuery(
  query: TimeRangeQuery,
  catalog: IcebergCatalogClient,
  queryEngine: TrinoClient
): Promise<unknown[]> {
  // Resolve the correct snapshot for the target timestamp
  const history = await catalog.getSnapshotHistory(
    query.namespace,
    query.table
  );

  // Find the latest snapshot at or before the target timestamp
  const targetSnapshot = history
    .filter((s) => s.timestampMs <= query.asOfTimestampMs)
    .sort((a, b) => b.timestampMs - a.timestampMs)[0];

  if (!targetSnapshot) {
    throw new Error(
      `No snapshot found before ${new Date(query.asOfTimestampMs).toISOString()}`
    );
  }

  // Inject the snapshot reference into the query via Trino's FOR VERSION AS OF syntax
  const versionedSql = `
    SELECT * FROM (${query.sql})
    FOR VERSION AS OF ${targetSnapshot.snapshotId}
  `;

  return queryEngine.execute(versionedSql);
}

Time travel retention is bounded by your snapshot expiration policy. Iceberg’s expireSnapshots procedure removes old snapshot metadata. Once a snapshot is expired, its data files become candidates for deleteOrphanFiles. Set snapshot expiration to match your audit retention requirement (typically 30-90 days), not to a short interval that limits your debugging window.

---

Partition Evolution in Practice

Partition evolution is the problem that most teams encounter six months after initial deployment. You partitioned your events table by date. Traffic grew, and now each date partition is 200GB. Queries that filter on (date, region) still scan the full date partition because region is not in the partition spec.

In Delta Lake and Hudi, changing the partition scheme requires rewriting the table or creating a new table and backfilling. In Iceberg, you add a new partition field to the spec. Going forward, new data files are written with (date, region) partitioning. Old data files remain partitioned by date only, with their original partition metadata intact. A query with WHERE date = '2025-03-01' AND region = 'us-east-1' uses the new spec to prune files written after the partition change, and falls back to the old spec (scanning all files in the date partition) for older data.

The older data still incurs the full date partition scan for the combined predicate. This is the correct tradeoff: you gain partition pruning on new data immediately without any rewrite cost. For tables where historical data is rarely queried, this is essentially free. For tables with uniform query patterns across all time ranges, eventually schedule a compaction job to rewrite the historical files with the new partition spec.

---

Production Considerations

Small file accumulation is the most common performance problem in lakehouses. Streaming writes or high-frequency batch jobs create many small Parquet files (under 32MB). Small files increase planning time (the query engine must open and read metadata for each file), reduce compression efficiency, and cause S3 LIST operations to slow down. Run a compaction job on a schedule: Iceberg’s rewriteDataFiles procedure merges small files into target-size files (128MB is a reasonable default) without affecting query results.

// Compaction job metadata tracker
interface CompactionRun {
  tableId: string;
  startedAt: Date;
  filesScanned: number;
  filesRewritten: number;
  bytesBeforeCompaction: number;
  bytesAfterCompaction: number;
  durationMs: number;
}

async function recordCompactionRun(
  run: CompactionRun,
  metricsStore: MetricsClient
): Promise<void> {
  const compressionRatio =
    run.bytesBeforeCompaction > 0
      ? run.bytesAfterCompaction / run.bytesBeforeCompaction
      : 1;

  await metricsStore.gauge("lakehouse.compaction.files_rewritten", run.filesRewritten, {
    table: run.tableId,
  });
  await metricsStore.gauge("lakehouse.compaction.compression_ratio", compressionRatio, {
    table: run.tableId,
  });
  await metricsStore.gauge("lakehouse.compaction.duration_ms", run.durationMs, {
    table: run.tableId,
  });

  if (compressionRatio > 0.95) {
    // Near-zero compression means files were already well-sized; reduce compaction frequency
    console.warn(`Compaction on ${run.tableId} achieved <5% reduction — consider increasing interval`);
  }
}

Concurrent writer conflicts. All three formats use optimistic concurrency: writers read the current metadata version, apply their change, and attempt to commit. If another writer committed between the read and the commit, the write fails and must retry. For Iceberg, the retry resolves the conflict by re-reading the latest snapshot and reapplying the operation. For high-concurrency write workloads (many parallel Spark jobs writing to the same table), use partitioned writes where each job writes to a distinct partition range. Conflicts only occur when two writers attempt to commit metadata changes that overlap.

Catalog availability. If your catalog is unavailable, writes fail and reads may fall back to stale metadata. For AWS Glue, this is rarely a problem in practice, but plan for it: test your write path’s behavior when the catalog returns 503, and make sure retries with backoff are implemented at the job level, not just the query level.

Data retention and storage costs. Object storage is cheap per GB but scales linearly with your snapshot and compaction strategy. Expired snapshots remove metadata but not data files until deleteOrphanFiles runs. For a table that receives daily compaction and has 90-day snapshot retention, the storage overhead from unreferenced pre-compaction files can be significant. Run deleteOrphanFiles after expireSnapshots, and check S3 storage class usage: data files older than 30 days that are only accessed for time travel are good candidates for S3 Intelligent-Tiering.

Query engine version alignment. Iceberg table format versions and Spark/Trino reader versions must be compatible. Iceberg format version 2 (required for row-level deletes, used by upsert and delete operations) requires Trino 398+ and Spark 3.4+. Upgrading the table format version of a table written by an older Spark cluster can make the table unreadable by that cluster. Pin format versions explicitly in your catalog configuration and upgrade query engines before upgrading table format versions.

---

Architecture Summary

A minimal production lakehouse for a startup with analytics requirements:

Ingestion layer: Kafka + Spark Structured Streaming
  writes Parquet files in Iceberg format to S3

Table format: Apache Iceberg
  metadata in AWS Glue Data Catalog

Compaction: scheduled Spark job (daily, per table)
Snapshot expiry: 60-day retention

Query engines:
  - Trino for BI tools and analysts (interactive SQL)
  - DuckDB for engineering exploratory analysis (local or serverless)
  - Spark for pipeline transformations (batch or streaming)

Observability:
  - Catalog metadata API polled for snapshot age and file count per table
  - Compaction metrics tracked per run
  - Query engine wall-clock time by table and query pattern

The key decision: Iceberg as the table format if you want multi-engine flexibility, Delta Lake if your stack is Databricks end-to-end, Hudi if you are building a CDC sink with very high upsert rates.

---

The lakehouse is not a product you buy. It is a set of architectural choices around open formats and open protocols. The storage is Parquet on S3. The table format is a spec you implement or consume. The catalog is an API. The query engines are interchangeable once you commit to the format. What this buys you is the ability to use the right engine for each workload, avoid warehouse vendor lock-in, and keep full raw history at object storage costs without giving up the reliability properties your analytics consumers expect.

The setup cost is real. Small file management, compaction scheduling, catalog availability, and query engine compatibility are ongoing operational concerns. But the operational cost is front-loaded; the scaling cost is not. A warehouse that handles 10TB today will cost dramatically more at 100TB. An Iceberg table on S3 at 100TB costs the same per GB as it does at 10TB.