Lakehouse Architect

Lakehouse Architect

Design data lakehouse architectures using Delta Lake, Apache Iceberg, or Apache Hudi for ACID transactions, time travel, and schema evolution on object storage.

Activation Triggers

Activate on: "lakehouse", "Delta Lake", "Apache Iceberg", "Hudi", "table format", "ACID on S3", "time travel", "data lake", "open table format", "Databricks", "catalog"

NOT for: Warehouse query tuning → data-warehouse-optimizer | Streaming pipeline design → streaming-pipeline-architect | Data quality rules → data-quality-guardian

Quick Start

1. Choose table format — Iceberg (vendor-neutral, widest engine support), Delta Lake (Databricks ecosystem), Hudi (Uber/AWS)
2. Set up catalog — Unity Catalog, AWS Glue, Nessie, or Hive Metastore for table discovery
3. Design medallion layers — Bronze (raw), Silver (cleaned), Gold (business-ready)
4. Enable compaction — schedule file compaction to avoid small file problem
5. Configure retention — time travel retention + vacuum/expire snapshots for cost control

Core Capabilities

Architecture Patterns

Medallion Architecture

Bronze (Raw)                Silver (Cleaned)           Gold (Business)
─────────────               ────────────────           ───────────────
Raw JSON/CSV/Parquet  →     Typed, deduplicated   →    Aggregated, modeled
Append-only           →     Merge/upsert          →    Materialized views
Schema-on-read        →     Schema enforced        →    Star schema
Full history          →     Latest + SCD Type 2    →    Pre-aggregated metrics

Storage: S3/GCS               All layers use Iceberg/Delta
Format:  Parquet               ACID transactions at each layer

Apache Iceberg Table Management

-- Create Iceberg table with partitioning
CREATE TABLE catalog.silver.orders (
  order_id    STRING,
  customer_id STRING,
  amount      DECIMAL(10,2),
  status      STRING,
  order_date  DATE,
  _loaded_at  TIMESTAMP
)
USING iceberg
PARTITIONED BY (days(order_date))
TBLPROPERTIES (
  'write.metadata.delete-after-commit.enabled' = 'true',
  'write.metadata.previous-versions-max' = '100'
);

-- Upsert (merge) new data
MERGE INTO catalog.silver.orders t
USING staging.new_orders s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

-- Time travel: query as of yesterday
SELECT * FROM catalog.silver.orders
  FOR SYSTEM_TIME AS OF TIMESTAMP '2026-03-19 00:00:00';

-- Maintenance: compact small files
CALL catalog.system.rewrite_data_files('silver.orders');
CALL catalog.system.expire_snapshots('silver.orders', TIMESTAMP '2026-03-01');

Table Format Comparison

Feature              Iceberg         Delta Lake      Hudi
──────────           ───────         ──────────      ────
ACID transactions    Yes             Yes             Yes
Time travel          Yes             Yes             Yes
Schema evolution     Full            Full            Full
Partition evolution  Yes (hidden)    No (requires    Limited
                                     rewrite)
Engine support       Widest          Spark/Databricks Spark/Flink
Catalog              REST/Nessie     Unity/Hive      Hive
Community            Apache          Linux Foundation Apache
Best for             Multi-engine    Databricks users CDC workloads

Anti-Patterns

1. Small files — ingesting many tiny files (< 128MB) destroys read performance; run compaction on schedule
2. No vacuum/expiration — time travel metadata accumulates forever; set retention policy and schedule cleanup
3. Partitioning by high cardinality — partitioning by user_id creates millions of partitions; use date or low-cardinality columns
4. Ignoring catalog — without a catalog, tables are just files on S3 that nobody can discover; always register in a catalog
5. Mixing table formats — pick one format per lakehouse; mixing Iceberg and Delta creates tooling and governance complexity

Quality Checklist

• Table format chosen consistently (Iceberg recommended for multi-engine)
• Catalog registered for all tables (discovery and governance)
• Medallion architecture: Bronze → Silver → Gold layers
• Partition strategy uses low-cardinality columns (date, region)
• File compaction scheduled (daily or hourly for high-volume tables)
• Snapshot expiration configured (retain 7-30 days of time travel)
• Schema evolution tested (add columns, change types where safe)
• ACID merge/upsert used instead of overwrite for Silver layer
• Storage costs monitored per layer with lifecycle policies
• Query engines tested against lakehouse tables (Spark, Trino, DuckDB)