Incremental Lakehouse Architecture

Design an enterprise data platform that unifies three disparate source types, legacy flat files (CSV/XML dropped via SFTP), CDC streams from operational databases (via Debezium), and real-time event streams (via Kafka), into a single lakehouse with a medallion architecture (bronze/silver/gold). The organization has 500 source tables, 50TB of existing historical data, and ingests ~100GB of new/changed data daily. The system must support incremental processing (no full reloads), SCD Type 2 for slowly changing dimensions, schema evolution, and data quality gates. How would you design this end to end?

How to Approach This Problem

How to Approach This Problem Most candidates treat this as a generic "design a data pipeline" question and produce a generic answer. Incremental lakehouses have three hard, specific problems that separate strong answers from weak ones. Address all three explicitly. Three Hard Problems Unique to Incremental Lakehouses 1. Late arriving data requires MERGE semantics, not INSERT. An event arrives 4 hours late due to a delayed upstream pipeline. If you INSERT it into a time partitioned Parquet table, the partition for that hour has already been closed and read by downstream jobs. Strong answers use Delta Lake MERGE INTO or Iceberg row level upserts so late data lands in the correct partition with

Clarifying Questions to Ask the Interviewer

Functional Requirements How many source systems are we unifying? (Dozens of SFTP file drops? Hundreds of CDC enabled databases? Multiple Kafka clusters?) What are the file formats and drop schedules? (CSV daily at midnight? XML hourly? Irregular ad hoc drops?) Which operational databases need CDC? (PostgreSQL, MySQL, Oracle, SQL Server?) What's the total table count? What real time event streams exist? (Clickstream, IoT telemetry, application logs, transactional events?) Do we need to support SCD Type 2 for all dimension tables, or only specific ones? (Customer, Product, Employee?) What's the acceptable data freshness for each source type? (Files: next day OK? CDC: minutes? Streaming: second

Envelope Estimation & Capacity Planning

Source Volume Breakdown Source Type Tables Daily Raw Volume Records/Day Freshness SLA Flat files (SFTP) 200 ~30 GB ~300M rows Within 1 hour of drop CDC (Debezium) 250 ~50 GB ~500M change events < 5 minutes Streaming (Kafka) 50 topics ~20 GB ~200M events < 1 minute Total 500 ~100 GB/day ~1B records/day Storage Math Metric Value Calculation Existing historical data 50 TB Given (needs one time migration) Daily incremental (raw) ~100 GB 30 GB files + 50 GB CDC + 20 GB streaming After Parquet compression ~15 GB/day ~6 7:1 compression ratio for tabular data Monthly storage growth ~450 GB 15 GB x 30 days (compressed) Bronze layer (1 year) ~55 TB 50 TB historical + 5.4 TB incremental Silver layer (1

Architecture Walkthrough

End to End Data Flow Three Source Types Source Mechanism Landing Pattern Volume SFTP flat files CSV/Excel from legacy partners, landed hourly into cloud storage File watcher triggers ingestion ~30 GB/day across 200 tables CDC streams Debezium captures row level changes from PostgreSQL, MySQL, Oracle Debezium → Kafka topics (one per source table) ~50 GB/day across 250 tables Real time events Microservices emit domain events to Kafka topics Direct Kafka produce ~20 GB/day across 50 topics "The key insight is that all three source types converge into a unified Kafka bus before touching the lakehouse. This gives us a single processing paradigm regardless of how data originated." Unified Kafka Bu

Component Deep Dive

Apache Iceberg, Why Iceberg for This Use Case "We chose Iceberg over Delta Lake and Hudi because of three capabilities critical for a multi source lakehouse: partition evolution, hidden partitioning, and engine agnostic access." Feature Iceberg Delta Lake Hudi Partition evolution Change partitioning without rewriting data Requires full rewrite Requires full rewrite Hidden partitioning Queries don't need to know partition columns Users must filter on partition columns Users must filter on partition columns Schema evolution Add/rename/reorder columns without rewrite Add columns only (rename requires overwrite) Limited support Time travel queries Snapshot based, efficient Log based, slower for

Data Modeling

Three Layer Schema Design Bronze Layer, Raw Landing (Append Only) Silver Layer, Cleaned, Deduplicated, SCD Type 2 Gold Layer, Business Aggregates Entity Relationship Summary Lineage tracking: Every silver/gold table includes source system and processed at columns for full lineage. The batch id in data quality results ties quality checks to specific processing runs.

Loading system design guide...