Day 5 - Week 6 Review: Data Lakes

📌 Key Takeaways

• Lake design is a four-layer decision: layout (where and how to place data) → table format (how to change it) → permissions (who can see it) → storage (at what cost to keep it), and the choice in one layer constrains the others.
• Scan volume problems are solved with partitions and columnar formats, catalog bottlenecks with partition projection and indexes, and file count problems with compaction. Even when symptoms look similar, the prescriptions differ.
• If you need row-level updates, deletes, and time travel, use an open table format. Iceberg to write with Athena SQL, Hudi if upserts and incremental reads are central, and Delta Lake if you have Spark assets.
• Column-, row-, and cell-level control and cross-account sharing are Lake Formation. It is actually enforced only when registration, revoking IAMAllowedPrincipals, and removing direct S3 permissions are all in place.
• Standard if read often, Intelligent-Tiering if the pattern is unknown, lifecycle transitions if it clearly cools off. Do not send data to be read with Athena, or the files of a table format, to archive tiers that require a restore.

The Lake Seen as Four Layers

This week we looked separately at the layout of an S3-based data lake, Lake Formation permissions, open table formats, and storage cost optimization. In real designs, all four apply to a single table at the same time.

┌─ Permissions ───── Lake Formation: registration · LF-Tags · data filters · cross-account sharing
├─ Table format ──── Hive table (path = table)  |  Iceberg · Hudi · Delta (metadata = table)
├─ Layout ────────── zones (raw/clean/curated) · partitions · file size · Parquet/compression
└─ Storage ───────── storage classes · lifecycle · Intelligent-Tiering · versioning

Interference Between Layers: Where What You Learned Separately Meets

The content of the four days shows up as problems where the topics collide.

The Order for Sorting Out "Queries Are Slow or Expensive"

The same symptom has four prescriptions. First sort out what the bottleneck is.

① Is the amount of data scanned large?
   ├─ It reads dates that are not needed ───────► filter by partition key (partitioning, pruning)
   └─ It reads columns that are not needed ─────► Parquet/ORC + SELECT only the needed columns
 
② Is the scan volume small but the planning phase long?
   ├─ Tens of thousands of partitions ──────────► partition projection / Glue partition index
   └─ Empty or old partitions have piled up ────► clean up partition metadata
 
③ Is the file count high?
   ├─ Small files in a Hive table ──────────────► rewrite and merge, increase writer-side buffer
   └─ Small files and delete files in Iceberg ──► OPTIMIZE / Glue table optimization
 
④ Does S3 return SlowDown?
   └─ Too many files + concurrent queries ──────► merge, spread out queries

• ① is a matter of data scanned (= Athena cost), and ② and ③ are matters of time. If the question says "reduce cost," it is ①; if "catalog lookups are the bottleneck," it is ②.
• Splitting partitions more finely solves ① while creating ② and ③. If most queries are by day, do not split down to the hour; sort inside the files by timestamp instead.

Telling Look-Alikes Apart at a Glance

Week 6 Numbers and Defaults on One Page

Integrated Scenario: An Order Data Lake

Requirements: Load an RDS orders database into a data lake, show analysts only the rows of their own region with PII columns hidden, respond to GDPR deletion requests, and optimize cost.

[RDS] --(Lake Formation Incremental database blueprint)--> s3://lake-raw/orders/dt=.../
   --(Glue ETL: validate, MERGE)--> s3://lake-clean/orders/ (Iceberg table)
   --(Glue: aggregate)--> s3://lake-curated/revenue/ (consumed by Athena/QuickSight)

-- Handling a GDPR deletion request (Iceberg on Athena)
DELETE FROM lake_clean.orders WHERE customer_id = 'GDPR-REQ-2026-001';
OPTIMIZE lake_clean.orders REWRITE DATA USING BIN_PACK;
VACUUM lake_clean.orders;

# Cell-level filter for analysts: exclude PII columns + only their own region's rows
aws lakeformation create-data-cells-filter --table-data '{
  "TableCatalogId": "111122223333",
  "DatabaseName": "lake_clean",
  "TableName": "orders",
  "Name": "apne2-no-pii",
  "RowFilter": {"FilterExpression": "region = '\''ap-northeast-2'\''"},
  "ColumnWildcard": {"ExcludedColumnNames": ["customer_name", "phone"]}
}'
 
# Grant SELECT by specifying the filter
aws lakeformation grant-permissions \
  --principal DataLakePrincipalIdentifier=arn:aws:iam::111122223333:role/RegionAnalyst \
  --permissions "SELECT" \
  --resource '{"DataCellsFilter": {"TableCatalogId": "111122223333",
               "DatabaseName": "lake_clean", "TableName": "orders", "Name": "apne2-no-pii"}}'

💡 Related Theory: The easy-to-miss spot in this scenario is the Raw zone. Even if you delete a customer's rows from the Iceberg table in Clean, that customer remains in the original files in Raw. Immutability is the principle for Raw, but deletion requests alone need an exception procedure. So designs such as tokenizing PII as it enters Raw, or setting a short retention period for Raw, are considered together.

Collection of Anti-Patterns

• Over-partitioning by hour or user: A file of a few KB is created per partition. Scan volume dropped, but the query is slower.
• Running MSCK REPAIR TABLE every day to add partitions: It rescans all paths. Use ALTER TABLE ADD PARTITION for only the added partitions, or use projection.
• Granting Lake Formation permissions but leaving the S3 bucket policy as is: Control remains in two places and leaks through the broader one.
• Making a copy of the table to hide columns: Each copy brings ETL, storage cost, and synchronization problems. A data filter does this instead.
• Creating an Iceberg table and not setting up maintenance: Delete files and snapshots pile up, making it slower and more expensive.
• A blanket "IA after 30 days" for every bucket: Cost actually rises for frequently read Curated data and for prefixes with many small files.
• Turning on versioning only, with no noncurrent version rule: Invisible capacity piles up without limit.
• Transitioning queryable data to Deep Archive: It drops out of results without an error.

Scenario → Answer Lookup

Next week we cover the analytics stores that sit next to the lake, Redshift, Athena, DynamoDB, and RDS/Aurora, along with choosing a store per workload.

📖 Glossary

• Medallion architecture: A lake composition method that divides data into bronze (raw), silver (refined), and gold (aggregated) stages. The same concept as the raw, clean, and curated zones.
• Pruning and projection: Reading only the partitions that match the condition, and computing the partition list by rule. The names look alike, but they solve different problems.
• Snapshot isolation: A consistency method in which readers see only the snapshot as of their start, so reads and writes do not block each other.
• Cell-level security: Applying a column restriction and a row filter together, so that even the visible columns are controlled per row.
• Data mesh: A distributed structure in which per-domain accounts own the data and the catalog and permissions are managed centrally.
• Waterfall model: The rule that lifecycle transitions flow only from upper classes to lower classes.
• Early deletion fee: A charge for the remaining period when you delete or transition before meeting the minimum storage duration.
• Tokenization: A technique that replaces a sensitive value with a meaningless substitute value when storing it. The mapping back to the original is kept in a separate store.