CREATE TABLE analytics.events_silver (
  event_id STRING,
  user_id STRING,
  event_type STRING,
  timestamp TIMESTAMP,
  properties MAP<STRING, STRING>
)
USING ICEBERG
P

ARTITIONED BY (hours(timestamp), event_type) TBLPROPERTIES ( 'format-version' = '2', 'write.format.default' = 'parquet', 'write.parquet.compression-codec' = 'zstd', 'write.metadata.previous-versions-max' = '5' );

-- Schema evolution: add column without rewriting data ALTER TABLE analytics.events_silver ADD COLUMNS (session_id STRING);

**Terraform: Decoupled S3 Storage + IAM for Lakehouse**
```hcl
resource "aws_s3_bucket" "data_lake" {
  bucket = "${var.env}-data-lake-${var.region}"
  
  lifecycle_rule {
    id      = "tiering"
    enabled = true
    transition {
      days          = 30
      storage_class = "STANDARD_IA"
    }
    transition {
      days          = 180
      storage_class = "GLACIER"
    }
    expiration {
      days = 2555
    }
  }
}

resource "aws_iam_role" "spark_compute" {
  name = "${var.env}-spark-compute"
  assume_role_policy = jsonencode({
    Version = "2012-10-17"
    Statement = [{
      Action = "sts:AssumeRole"
      Effect = "Allow"
      Principal = { Service = "ec2.amazonaws.com" }
    }]
  })
}

resource "aws_iam_role_policy_attachment" "s3_access" {
  role       = aws_iam_role.spark_compute.name
  policy_arn = "arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess"
}

Architecture Decisions

• Table Format Selection: Iceberg excels in multi-engine environments (Spark, Trino, Flink, BigQuery). Delta Lake optimizes for Databricks ecosystems. Choose based on compute diversity, not vendor preference.
• Partitioning Strategy: Avoid over-partitioning. Use dynamic partition pruning and bucketing for high-cardinality columns. Partition by time (day/hour) and low-cardinality filters (region, tenant).
• Compaction & Optimization: Schedule Z-ordering (Delta) or sort-based compaction (Iceberg) weekly for frequently filtered columns. Monitor file size distribution; target 128MB-1GB per file.
• Query Routing: Route BI tools to serving layer (Trino/BigQuery). Route ML pipelines to raw/silver layers via Spark. Never allow ad-hoc queries against Bronze.

Pitfall Guide

1. Treating the Data Lake as a Dumping Ground Ingesting raw files without validation, partitioning, or catalog registration creates a data swamp. Mitigation: Enforce Bronze→Silver pipeline contracts. Reject malformed records to a quarantine bucket with alerting.

2. Schema-on-Read as an Excuse for Poor Data Quality Deferring schema enforcement until query time shifts cost to downstream consumers. Mitigation: Apply schema validation at ingestion using Avro/Protobuf contracts or Iceberg schema evolution rules.

3. Dual-Stack Duplication Without a Unified Catalog Running DW and DL in parallel without cross-platform metadata leads to version drift. Mitigation: Use AWS Glue, Unity Catalog, or Hive Metastore as the source of truth. Query DL tables via Trino, push only aggregated Gold tables to DW.

4. Ignoring Data Lifecycle & Tiering Storing all data in hot storage inflates costs by 300-500%. Mitigation: Implement automated lifecycle policies. Archive compliance data. Use materialized views for frequent aggregations instead of scanning raw partitions.

5. Over-Provisioning Compute for Ad-Hoc Queries Dedicated clusters for exploratory analysis waste credits. Mitigation: Use serverless query engines (Athena, BigQuery, Databricks SQL) for ad-hoc workloads. Reserve provisioned compute for SLA-bound pipelines.

6. Treating Governance as an Afterthought Role-based access, PII masking, and audit trails delayed until production cause security breaches and compliance failures. Mitigation: Implement column-level encryption, data masking policies, and lineage tracking before pipeline deployment.

7. Skipping Compaction & File Management Thousands of small files degrade query performance and increase metadata overhead. Mitigation: Schedule nightly compaction jobs. Monitor files_written vs files_read ratios. Target <1000 files per partition.

Production Bundle

Action Checklist

• Classify all data sources by volatility, schema stability, and consumer SLA
• Select storage paradigm (DW/DL/Lakehouse) per workload, not per team preference
• Implement Bronze→Silver→Gold medallion architecture with open table formats
• Decouple compute engines for ingestion, serving, and BI
• Configure S3/ADLS lifecycle policies and tiered storage rules
• Deploy unified catalog with row/column-level security and audit logging
• Schedule automated compaction, Z-ordering, and statistics collection
• Establish data quality gates at Silver ingestion with quarantine routing

Decision Matrix

Workload Characteristic	Recommended Approach	Why
Fixed schema, high-concurrency BI, strict SLAs	Data Warehouse	Optimized query engine, built-in governance, predictable latency
Raw ingestion, ML training, streaming, schema evolution	Data Lake	Cost-efficient storage, flexible formats, decoupled compute
Mixed SQL/ML workloads, ACID requirements, multi-engine access	Lakehouse (Iceberg/Delta)	Transactional safety, time travel, unified catalog, open format
Compliance/Archival, low access, long retention	Cold Storage + DL	Minimal cost, immutable retention, catalog-only metadata
Real-time analytics, sub-second latency, high throughput	DW + Streaming Ingest	Optimized for low-latency joins, materialized views, caching

Configuration Template

Terraform + Spark + Iceberg Lakehouse Setup

# main.tf
variable "env" { default = "prod" }
variable "region" { default = "us-east-1" }

provider "aws" { region = var.region }

resource "aws_s3_bucket" "lakehouse" {
  bucket = "${var.env}-lakehouse-${var.region}"
  acl    = "private"
}

resource "aws_glue_catalog_database" "analytics" {
  name = "${var.env}_analytics"
}

resource "aws_iam_role" "spark_role" {
  name = "${var.env}-spark-role"
  assume_role_policy = jsonencode({
    Version = "2012-10-17"
    Statement = [{
      Action = "sts:AssumeRole"
      Effect = "Allow"
      Principal = { Service = "emr.amazonaws.com" }
    }]
  })
}

resource "aws_iam_role_policy" "s3_access" {
  name = "${var.env}-s3-access"
  role = aws_iam_role.spark_role.id
  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [{
      Effect = "Allow"
      Action = ["s3:GetObject", "s3:PutObject", "s3:ListBucket"]
      Resource = [
        aws_s3_bucket.lakehouse.arn,
        "${aws_s3_bucket.lakehouse.arn}/*"
      ]
    }]
  })
}

Spark Submit Command (Iceberg + S3)

spark-submit \
  --packages org.apache.iceberg:iceberg-spark-runtime-3.3_2.12:1.4.0 \
  --conf spark.sql.catalog.spark_catalog=org.apache.iceberg.spark.SparkCatalog \
  --conf spark.sql.catalog.spark_catalog.type=hive \
  --conf spark.sql.catalog.spark_catalog.warehouse=s3a://${var.env}-lakehouse-${var.region}/ \
  --conf spark.hadoop.fs.s3a.impl=org.apache.hadoop.fs.s3a.S3AFileSystem \
  --conf spark.hadoop.fs.s3a.aws.credentials.provider=com.amazonaws.auth.DefaultAWSCredentialsProviderChain \
  --class com.codcompass.iceberg.IngestionJob \
  /opt/app/ingestion-job.jar

Quick Start Guide

1. Provision Storage & Catalog: Create S3/ADLS bucket with lifecycle rules. Register database in AWS Glue or Unity Catalog. Attach IAM roles with least-privilege S3 access.
2. Initialize Table Format: Deploy Iceberg or Delta Lake runtime. Create Bronze table with partitioning by ingestion date. Configure schema enforcement and quarantine routing.
3. Ingest & Validate: Run Spark/Flink job to land raw data. Apply Great Expectations/Deequ checks at Silver layer. Reject malformed records, log failures, and trigger alerts.
4. Serve & Optimize: Register Silver/Gold tables in unified catalog. Route BI tools to serving engine. Schedule weekly compaction, statistics collection, and tiered storage transitions.
5. Govern & Monitor: Implement column-level security, PII masking, and audit logging. Track query latency, storage costs, and pipeline success rates. Adjust partitioning and compute sizing based on telemetry.

The warehouse-lake dichotomy is a legacy framing. Modern data architecture succeeds when storage format, compute topology, and governance boundaries align with workload characteristics, not vendor roadmaps. Build for evolution, enforce contracts early, and let the catalog drive discovery.