S3 Data Lake

Lake Architecture on S3

Treat S3 as the single source of truth and separate zones: raw/ holds immutable landing files exactly as produced; curated/ holds cleansed, typed, partitioned data ready for analysis. Convert to a columnar format — Parquet or ORC — organized Hive-style:

s3://lake/orders/dt=2026-09-01/part-00042.parquet

Partition directories let Athena prune files before scanning, and columnar files let it read three columns out of fifty. Schema lives in the Glue Data Catalog rather than in code, so Athena, Redshift Spectrum, and EMR share one definition. Storage is cheap and elastic; compute is only billed while a query runs — the lake model decouples the two.

Cataloging and Querying with Glue and Athena

A Glue crawler walks a prefix, infers schema, and writes a table (plus partitions) into the Data Catalog. Then query in place:

CREATE TABLE orders_parquet
WITH (format = 'PARQUET', parquet_compression = 'SNAPPY') AS
SELECT order_id, customer_id, total
FROM orders_raw
WHERE dt >= '2026-01-01';

CTAS migrates JSON to Parquet while you query. For fast-growing partitions, use partition projection — Athena computes partition paths from a pattern instead of listing the catalog — and organize work in workgroups so teams share settings, results, and spend limits without colliding.

Pitfalls and Cost Control

Athena bills per byte scanned, so every design choice below cuts cost:

• One file per writer creates thousands of tiny objects — slow to list and slow to read. Compact into larger files during ETL.
• Over-partitioning (hour=, user=) multiplies listings; partition on high-selectivity filters only.
• SELECT * from JSON scans everything; select explicit columns from Parquet.
• Catalog drift: files changed under a crawler-defined table return wrong results — re-crawl after loads.
• Keep Athena and the data in the same region to avoid needless transfer.

Other traps: malformed CSV SerDe settings silently null columns, and ungoverned lifecycle rules archive data your nightly job still reads.