Open Table Formats & CDC

The plumbing that makes the lakehouse real

This lesson is the plumbing that makes the lakehouse real: the table format that turns files into a database, and the CDC pattern that streams your OLTP changes in without corrupting them.

Why raw Parquet is not enough

Parquet is a great columnar file format, but a directory of Parquet files on S3 is not a table. There is no atomicity (a reader can see a half-written batch), no schema evolution (rename a column and every reader breaks), no way to safely delete or update rows for GDPR, and no consistent view under concurrent writers. Open table formats (Apache Iceberg, Delta Lake, Apache Hudi, Apache Paimon) add a metadata layer over the Parquet files that provides:

• ACID transactions via an atomic swap of a metadata/manifest pointer, so writers commit all-or-nothing and readers always see a consistent snapshot.
• Schema and partition evolution without rewriting data: add/rename/drop columns, and change partitioning, tracked in metadata.
• Time travel: query the table as of a past snapshot or timestamp, for audits, reproducible ML training, and rollback.
• Hidden partitioning (Iceberg): you partition by a derived value (day of event_ts) and queries prune automatically, so users never write brittle partition predicates by hand.

Which format when

Iceberg is the open standard with the broadest engine support (Spark, Trino, Flink, Snowflake, BigQuery), the right default for a multi-engine lakehouse. Delta Lake is Spark/Databricks-native and excellent inside that ecosystem. Hudi was built for CDC and record-level upserts/deletes with primary keys, strong when your workload is heavy mutation. Paimon targets unified streaming plus batch with a Flink lineage. A catalog (Iceberg REST catalog, Polaris, AWS Glue, Databricks Unity) holds the table metadata and enables governance and cross-engine access; the catalog is what lets Spark and Trino agree on what a table is.

Log-based CDC

You want every insert/update/delete in Postgres to flow to analytics and a search index in near-real-time. The right mechanism is log-based CDC: Debezium reads the database's replication log (Postgres WAL, MySQL binlog) and emits a change event per row mutation. Log-based beats the alternatives: query-based polling (WHERE updated_at > x) misses deletes and hard-hits the DB, and trigger-based CDC adds write-path latency. Reading the log is low impact and captures every change including deletes, in commit order.

The dual-write problem and the outbox

The trap: your service writes to Postgres and then also writes to Kafka (or directly to the search index). These are two systems with no shared transaction, so a crash between them leaves you inconsistent forever: the order is in the DB but the event never published, or published but the DB rolled back. You cannot fix this with retries because you do not know which write succeeded.

The fix is the transactional outbox: within the same DB transaction that writes the order, insert a row into an outbox table. The business write and the event are now atomic (one transaction). CDC then tails the WAL, sees the outbox insert, and publishes it to Kafka. There is exactly one source of truth (the DB log) and no distributed transaction.

  service --tx--> [orders row + outbox row]  (one Postgres commit)
                          |
                   Debezium reads WAL
                          v
                        Kafka --> search index (Elasticsearch)
                              --> lakehouse sink (Iceberg via Flink)

Because delivery is at-least-once (a connector can replay after a crash), downstream consumers must be idempotent: upsert by primary key into the search index and the Iceberg table so a redelivered event does not duplicate. Iceberg/Hudi upserts (merge-on-read or copy-on-write) handle this on the lake side.

Interview nuance: the outbox does not give you exactly-once end-to-end, it gives you at-least-once with an atomic source write, and you achieve effective exactly-once by making consumers idempotent. Claiming true exactly-once across DB, Kafka, and a search index without idempotency is the tell of someone who has not run this in production.

Recap: table formats (Iceberg/Delta/Hudi/Paimon) add ACID, schema/partition evolution, time travel, and hidden partitioning over Parquet, coordinated by a catalog; pick Iceberg for multi-engine, Hudi for upsert-heavy CDC; use log-based CDC (Debezium on the WAL/binlog) plus a transactional outbox to avoid the dual-write problem; delivery is at-least-once so make consumers idempotent for effective exactly-once.