Building a Modern Data Lakehouse on AWS: S3, Iceberg, Glue, Athena, and Lake Formation

┌──────────────────┐  ┌──────────────────┐  ┌──────────────────────────┐
│   DATA LAKE      │  │  DATA WAREHOUSE  │  │     DATA LAKEHOUSE       │
│                  │  │                  │  │                          │
│ ✅ Cheap storage │  │ ✅ Fast queries   │  │ ✅ Cheap storage (S3)    │
│ ✅ Schema-on-read│  │ ✅ ACID txns     │  │ ✅ Fast queries (Iceberg)│
│ ✅ Any format    │  │ ✅ Governance    │  │ ✅ ACID transactions     │
│ ❌ No ACID       │  │ ❌ Expensive     │  │ ✅ Governance (LF)       │
│ ❌ No updates    │  │ ❌ Vendor lock-in│  │ ✅ Open format           │
│ ❌ Stale data    │  │ ❌ Schema-rigid  │  │ ✅ Schema evolution      │
│                  │  │                  │  │ ✅ Time travel           │
│  (S3 + Parquet)  │  │  (Redshift)      │  │ (S3 + Iceberg + Athena) │
└──────────────────┘  └──────────────────┘  └──────────────────────────┘

┌─────────────────────────────────────────────────────────────────────┐
│                         CONSUMPTION                                   │
│  Athena (SQL) | Redshift Spectrum | EMR (Spark) | QuickSight (BI)   │
├─────────────────────────────────────────────────────────────────────┤
│                         GOVERNANCE                                    │
│  Lake Formation (permissions, audit) | Glue Data Catalog (metadata) │
├─────────────────────────────────────────────────────────────────────┤
│                         TABLE FORMAT                                  │
│  Apache Iceberg (ACID, time travel, schema evolution, compaction)    │
├─────────────────────────────────────────────────────────────────────┤
│                         PROCESSING                                    │
│  Glue ETL (Spark) | Glue Streaming | EMR | Zero-ETL | Firehose     │
├─────────────────────────────────────────────────────────────────────┤
│                         INGESTION                                     │
│  Kinesis | DMS | AppFlow | S3 Transfer | Direct PUT                 │
├─────────────────────────────────────────────────────────────────────┤
│                         STORAGE                                       │
│  Amazon S3 (raw / curated / analytics zones)                         │
└─────────────────────────────────────────────────────────────────────┘

CREATE TABLE analytics.orders (
    order_id STRING,
    customer_id STRING,
    amount DECIMAL(10,2),
    status STRING,
    order_date TIMESTAMP,
    region STRING
)
PARTITIONED BY (region, month(order_date))
LOCATION 's3://my-lakehouse/analytics/orders/'
TBLPROPERTIES ('table_type' = 'ICEBERG');

-- Update order status
UPDATE analytics.orders
SET status = 'shipped'
WHERE order_id = 'ORD-12345';

-- Delete cancelled orders older than 1 year
DELETE FROM analytics.orders
WHERE status = 'cancelled' AND order_date < current_timestamp - interval '1' year;

-- Merge (upsert) from staging
MERGE INTO analytics.orders target
USING staging.new_orders source
ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET status = source.status, amount = source.amount
WHEN NOT MATCHED THEN INSERT (order_id, customer_id, amount, status, order_date, region)
VALUES (source.order_id, source.customer_id, source.amount, source.status, source.order_date, source.region);

-- Query data as it was 24 hours ago
SELECT * FROM analytics.orders
FOR TIMESTAMP AS OF (current_timestamp - interval '24' hour);

-- Query a specific snapshot
SELECT * FROM analytics.orders
FOR VERSION AS OF 12345678901234;

-- View snapshot history
SELECT * FROM "analytics"."orders$snapshots";

s3://company-lakehouse/
├── raw/                    ← Landing zone (source format, immutable)
│   ├── crm/
│   ├── erp/
│   └── clickstream/
│
├── curated/                ← Cleaned, validated, Iceberg format
│   ├── customers/
│   ├── orders/
│   └── products/
│
└── analytics/              ← Aggregated, business-ready
    ├── daily_revenue/
    ├── customer_360/
    └── product_performance/

import sys
from awsglue.transforms import *
from awsglue.context import GlueContext
from pyspark.context import SparkContext

sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session

# Read raw data
raw_df = spark.read.json("s3://company-lakehouse/raw/orders/2026/08/25/")

# Transform: clean, validate, enrich
curated_df = (raw_df
    .filter(raw_df.order_id.isNotNull())
    .withColumn("order_date", to_timestamp("order_date_str"))
    .withColumn("amount", col("amount").cast("decimal(10,2)"))
    .drop("order_date_str", "_metadata")
)

# Write to Iceberg table (append or merge)
curated_df.writeTo("glue_catalog.curated.orders") \
    .using("iceberg") \
    .append()

Lake Formation Permissions:
├── Database-level: Who can see which databases?
├── Table-level: Who can query which tables?
├── Column-level: Who can see which columns? (mask PII)
└── Row-level: Who can see which rows? (data filters by region/team)

Policy: "EU Data Residency"
├── Principal: EU-Analytics-Team
├── Table: curated.customers
├── Filter: region IN ('eu-west-1', 'eu-central-1')
└── Effect: Team can ONLY see EU customers

Tag: classification = "pii"
  → Applied to: customers.email, customers.phone, orders.billing_address
  → Grant: Only "PII-Authorized" group can see these columns
  → Everyone else: columns masked or hidden

App Events → Kinesis Data Streams → Glue Streaming ETL → Iceberg Table
                                                              │
                                              Athena (queries latest data)

-- Compact small files for better query performance
OPTIMIZE analytics.orders REWRITE DATA USING BIN_PACK;

-- Expire old snapshots (keep last 7 days)
ALTER TABLE analytics.orders SET TBLPROPERTIES (
    'vacuum_min_snapshots_to_keep' = '10',
    'vacuum_max_snapshot_age_seconds' = '604800'
);
VACUUM analytics.orders;