Data lake vs warehouse vs lakehouse: Apache Iceberg, time travel and when Redshift still wins
Compare data lakes, data warehouses and lakehouses, create an Apache Iceberg table for Harbourline's curated transactions in Athena, correct late-arriving data with MERGE, query history with time travel, and decide when Amazon Redshift or S3 Tables is the better answer.
Assumes: .NET 10 LTS · AWS SDK for .NET v4 (AWSSDK.Athena 4.1.1.1) · Athena engine v3 · Apache Iceberg v2 tables · Amazon.CDK.Lib 2.277.0 · Terraform AWS provider v6 · Region us-east-1. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lesson you can:
- Define a data lake, a data warehouse and a lakehouse, and say what each is good and bad at.
- Explain what an open table format (Apache Iceberg) adds on top of Parquet files: ACID commits, row-level changes, schema and partition evolution, snapshots and time travel.
- Create and maintain an Iceberg table in Athena: hidden partitioning,
INSERT,MERGE INTO,OPTIMIZE,VACUUM, and the$snapshotsmetadata table. - Compare self-managed Iceberg tables in a general purpose bucket with Amazon S3 Tables (table buckets with automatic maintenance).
- Decide when Amazon Redshift (provisioned or Serverless) is still the right answer, and describe the cost model of each option.
Prerequisites
- File formats: CSV vs JSON vs Parquet
- S3 data lake design (the buckets and keys are used here)
- Helpful: Amazon Athena for the workgroup and scan limit; you can do that lesson first or follow the short setup here
- Tools: AWS CLI v2 (
learn-sandbox), .NET 10 SDK
Why this matters in banking
Harbourline's curated transactions table is a set of Parquet files partitioned by booking date. Three situations break that simple design:
- Late corrections. A card scheme sends a chargeback that reverses a transaction booked 12 days ago. With plain files you must rewrite the whole partition for that day, and if two jobs do it at once, one silently overwrites the other.
- "What did the report say on Tuesday?" An auditor asks to reproduce the regulatory return exactly as submitted, before Wednesday's late file changed the totals. With plain files the old state is gone unless you copied it.
- Right to erasure. A customer's data must be deleted from analytical stores (where no other legal basis to keep it applies). Finding and rewriting every file that contains their rows, without corrupting concurrent reads, is hard with plain Parquet.
Data warehouses handled these for decades with transactions and UPDATE/DELETE. Lakehouse table formats bring the same guarantees to files in S3. Choosing the right place for each workload is one of the most common data-architecture decisions you will make.
The idea in plain language
- A data lake stores files in object storage and lets many engines read them. Cheap, flexible, open. But a "table" is just a folder of files: no transactions, no updates, no history.
- A data warehouse (Redshift, Synapse dedicated pool, SQL Server) stores data in its own managed format, optimised for SQL. Transactions, updates, fast joins, concurrency management, mature security. But storage and compute are coupled to one engine, and loading data in is a step of its own.
- A lakehouse keeps data as open files in S3, but adds a table format layer that records exactly which files make up the table at each moment. Engines read that metadata to get transactions, updates, deletes and history, while the data stays open to any engine that understands the format.
Apache Iceberg is the table format with the broadest support on AWS: Athena, Glue (Spark), EMR, Redshift, Firehose, S3 Tables and Lake Formation all work with it.
Analogy: A data lake is a filing cabinet: anyone can add or take out folders, and there is no record of what was in it yesterday. A warehouse is a bank vault with its own clerks: very safe and fast, but everything must go through them. A lakehouse is the filing cabinet plus a ledger on top that records, for every change, exactly which folders make up the official file set. Readers follow the ledger, writers append ledger entries atomically, and you can read the ledger as of any past page to see the cabinet as it was.
What problem the AWS service solves
How Iceberg works
- Data files are ordinary Parquet (or ORC/Avro).
- Manifests list data files with per-file column statistics and partition values, so engines can prune without listing S3 prefixes.
- A snapshot is the complete list of files that make up the table at one commit.
- The metadata file records the schema, partition spec and snapshot history. The catalogue (Glue) stores a pointer to the current metadata file. A commit is an atomic swap of that pointer, using Glue's optimistic locking: if two writers race, one fails and retries instead of corrupting the table.
Features that follow:
| Feature | What it means for Harbourline |
|---|---|
| ACID commits | A job writing 50 files publishes all or nothing; readers never see half a day |
Row-level UPDATE, DELETE, MERGE | Apply chargebacks and late corrections without rewriting whole partitions by hand |
Time travel (FOR TIMESTAMP AS OF, FOR VERSION AS OF) | Reproduce Tuesday's report exactly as it was |
| Schema evolution by column ID | Rename or reorder columns safely |
Hidden partitioning (day(booking_date), bucket(16, account_id)) | Queries filter on booking_date directly; no separate year/month/day columns to keep in sync |
| Partition evolution | Change from monthly to daily partitioning without rewriting old data |
What you take on:
- Maintenance. Each commit adds files. Row-level changes add delete files. Without compaction (
OPTIMIZE) and snapshot expiry (VACUUM) tables get slower and storage grows. S3 Tables and the Glue Data Catalog's table optimizers can automate this. - Engine differences. Athena creates and writes Iceberg v2 tables and supports Iceberg version 1.4.2 features; newer Glue versions (5.1, 6.0) support Iceberg format version 3. Mixing engines means agreeing on the lowest common feature set.
- Lake Formation limits. Athena does not support DDL on Iceberg tables registered with Lake Formation, and Iceberg metadata tables are restricted when row or cell filters apply.
- Time travel is not a retention policy. Expiring snapshots (needed for cost and erasure) removes history. For records you must keep, write explicit report versions or use Object Lock copies.
The three options compared
| Data lake (plain Parquet + Glue catalogue) | Lakehouse (Iceberg on S3, or S3 Tables) | Data warehouse (Redshift) | |
|---|---|---|---|
| Storage | S3, open files | S3, open files + Iceberg metadata | Redshift managed storage (columnar, internal) |
| Engines | Athena, Spark, Redshift Spectrum, many others | Same, if they support Iceberg | Redshift (plus data sharing, Spectrum to read the lake) |
| Updates and deletes | Rewrite files or partitions yourself | UPDATE, DELETE, MERGE | Full SQL DML |
| Transactions | None across files | Snapshot isolation per table | Full, multi-table |
| History | Only what you copy | Snapshots until expired | Snapshots for backup, not queryable history (use your own versioning) |
| Concurrency for BI | Engine-dependent; Athena has per-account query concurrency quotas | Same | Designed for many concurrent BI users; workload management, concurrency scaling |
| Joins on large tables, repeated dashboards | Fine for moderate use | Fine for moderate use | Best: sort keys, distribution, materialised views, result caching |
| Cost model | S3 storage + per-TB-scanned (Athena) or DPU-hours (Glue) | Same, plus maintenance compute (or S3 Tables fees) | Serverless: per RPU-hour while queries run (about USD 0.375 per RPU-hour in us-east-1, 4 RPU minimum base capacity); provisioned: per node-hour; plus managed storage (about USD 0.024 per GB-month) ⚠ VERIFY current prices |
| Typical Harbourline use | Raw zone, append-only curated datasets | Curated transactions (corrections, erasure, audit reproduction) | Finance data mart with heavy, repeated, multi-table BI |
When Redshift is still the answer
Choose Redshift (often Serverless, which bills only while queries run) when most of these are true:
- Many concurrent BI users and dashboards run the same complex joins all day.
- You need predictable sub-second to seconds latency on star schemas.
- The team works SQL-first with stored procedures, materialised views and workload management.
- You need multi-table transactions or very frequent small updates.
Redshift can also read the lake: Redshift Spectrum queries external tables in the Glue catalogue (including Iceberg), so the warehouse and the lake share curated data rather than copying all of it. Redshift is covered fully in the Redshift lesson listed in the module overview.
Amazon S3 Tables
S3 Tables is a separate bucket type, the table bucket, that stores Iceberg tables as first-class resources:
- Namespaces group tables (like databases); permissions use the
s3tablesIAM namespace and can target a bucket, namespace or single table. - S3 runs automatic maintenance: compaction, snapshot management and removal of unreferenced files, configurable per table.
- Table buckets offer higher transactions per second than self-managed Iceberg in general purpose buckets, and support Iceberg V3 features.
- Block Public Access is always on and cannot be disabled.
- Integration with the Glue Data Catalog (enabled from the S3 console) makes tables visible to Athena, Redshift, EMR, Glue and Quick Sight, with Lake Formation for fine-grained access.
- Pricing adds per-object monitoring and per-object/per-GB compaction charges on top of storage; check the S3 pricing page before choosing it for large, frequently changing tables.
Use S3 Tables when you want Iceberg without running maintenance jobs yourself. Use self-managed Iceberg in your existing curated bucket when you need full control (for example a single KMS key and bucket policy model across the lake), or when an engine you rely on does not yet support table buckets.
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| SQL Server data warehouse / Synapse dedicated SQL pool | Amazon Redshift (provisioned or Serverless) | Serverless bills per RPU-hour only while running; distribution and sort keys instead of distribution and indexes |
| Delta Lake tables in Fabric / Databricks | Apache Iceberg tables (self-managed or S3 Tables) | Iceberg is the default open table format across AWS services; Delta is supported by Glue and EMR |
| Fabric OneLake | S3 general purpose buckets + S3 Tables | Several services, one catalogue (Glue Data Catalog) |
Delta VERSION AS OF / TIMESTAMP AS OF | Iceberg FOR VERSION AS OF / FOR TIMESTAMP AS OF in Athena | Snapshot IDs are bigint values |
OPTIMIZE / VACUUM in Delta | OPTIMIZE ... REWRITE DATA USING BIN_PACK / VACUUM in Athena; automatic in S3 Tables | Same concepts, different syntax |
| SQL Server temporal tables | Iceberg snapshots (until expired) | Snapshots are table-level, not row-level validity periods |
| Synapse serverless reading Delta | Athena / Redshift Spectrum reading Iceberg | Same "query the lake in place" idea |
Architecture
Harbourline's chosen split:
Security-boundary view: the Iceberg table's data and metadata files live under s3://hl-m07-curated-<account-id>/iceberg/transactions/, encrypted with the curated CMK. Only the pipeline role may write (it needs glue:UpdateTable to commit snapshots). Analysts read through Athena; with Lake Formation, column and row permissions apply to Iceberg tables as to any catalogue table.
Step-by-step implementation
This exercise runs in Athena on the 1,012-row sample. Each query scans far less than 10 MB, so each is billed at the 10 MB minimum: about USD 0.00005 per query.
Prepare: curated Parquet and a source table
You need the curated bucket from S3 data lake design and an Athena workgroup (hl-m07-analysts, see Amazon Athena; the CLI section below creates a minimal one). Upload the clean CSV sample as a temporary staging table:
aws s3 cp datasets/samples/csv/transactions.csv \
"s3://hl-m07-raw-$ACCOUNT/transactions/source=core/ingest_date=2026-11-16/transactions.csv"Then run the CREATE DATABASE and CREATE EXTERNAL TABLE statements in Complete code (file 01-staging.sql).
Create the Iceberg table
Run 02-create-iceberg.sql. Note PARTITIONED BY (day(booking_date)): a hidden partition transform. There are no year, month, day columns; queries filter on booking_date and Iceberg prunes automatically.
Load with INSERT, then inspect the snapshot
Run 03-load.sql, then query "transactions_iceberg$snapshots". You will see one snapshot with operation append. Note its snapshot_id and committed_at.
Apply a late correction with MERGE
Run 04-merge-chargeback.sql. It reverses one card payment and inserts a new chargeback credit in one atomic commit. Query $snapshots again: a second snapshot (overwrite) appears.
Time travel
Run 05-time-travel.sql. The first query returns the daily total before the MERGE (using the first snapshot ID or a timestamp), the second returns the current total. This is how you reproduce "the report as it was on Tuesday".
Maintain
Run 06-maintain.sql: OPTIMIZE compacts small files and folds delete files into data files; VACUUM expires old snapshots according to table properties and removes orphan files. After VACUUM, time travel to expired snapshots fails: history and erasure pull in opposite directions, which is why retention is a policy decision.
AWS Console instructions
- Console → Athena → Query editor: choose workgroup
hl-m07-analysts, databasehl_m07_curated, and run the SQL files in order. After each query, the Query stats panel shows Data scanned. - Console → AWS Glue → Data Catalog → Tables → transactions_iceberg: the table type shows Iceberg; the Table properties include
metadata_location, the pointer to the current metadata file. - Console → AWS Glue → Data Catalog → Tables → transactions_iceberg → Table optimization: where you can enable managed compaction, snapshot retention and orphan file deletion instead of running
OPTIMIZE/VACUUMyourself (charged per DPU-hour of optimizer time ⚠ VERIFY current rate). - Console → S3 → Table buckets: where S3 Tables live, separate from general purpose buckets. Creating one is optional in this lesson.
AWS CLI
export AWS_PROFILE=learn-sandbox AWS_REGION=us-east-1
ACCOUNT=$(aws sts get-caller-identity --query Account --output text)
# Minimal workgroup with a 100 MB per-query scan limit and encrypted results
aws athena create-work-group --name hl-m07-analysts \
--configuration "ResultConfiguration={OutputLocation=s3://hl-m07-athena-results-$ACCOUNT/,EncryptionConfiguration={EncryptionOption=SSE_KMS,KmsKey=alias/hl-m07-curated}},EnforceWorkGroupConfiguration=true,BytesScannedCutoffPerQuery=104857600,EngineVersion={SelectedEngineVersion=AUTO}" \
--tags Key=Project,Value=cea-lab Key=Lab,Value=m07-transaction-pipeline Key=Environment,Value=learn Key=Owner,Value=your-name
# Run one SQL file and print the execution id
run_sql () {
aws athena start-query-execution --work-group hl-m07-analysts \
--query-string "$(sed "s/ACCOUNT_ID/$ACCOUNT/g" "$1")" \
--query QueryExecutionId --output text
}
QID=$(run_sql 02-create-iceberg.sql)
aws athena get-query-execution --query-execution-id "$QID" \
--query 'QueryExecution.{State:Status.State,Scanned:Statistics.DataScannedInBytes,Error:Status.StateChangeReason}'
# Look at the Iceberg files S3 now holds
aws s3 ls "s3://hl-m07-curated-$ACCOUNT/iceberg/transactions/" --recursive | head
# Optional: S3 Tables (table bucket + namespace) instead of self-managed Iceberg
aws s3tables create-table-bucket --name hl-m07-tables-$ACCOUNT
aws s3tables create-namespace --table-bucket-arn "arn:aws:s3tables:us-east-1:$ACCOUNT:bucket/hl-m07-tables-$ACCOUNT" --namespace curatedEngineVersion=AUTO lets Athena keep the workgroup on the current engine (v3).
Infrastructure as Code
Production teams create the Iceberg table either with Athena DDL run by a deployment step, or declaratively in the catalogue. Both CDK and Terraform can declare it:
using Amazon.CDK;
using Amazon.CDK.AWS.Glue;
using Constructs;
namespace Harbourline.DataLake.Infra;
public sealed class IcebergTransactionsTable : Construct
{
public IcebergTransactionsTable(Construct scope, string id, string curatedBucket) : base(scope, id)
{
_ = new CfnTable(this, "Table", new CfnTableProps
{
CatalogId = Aws.ACCOUNT_ID,
DatabaseName = "hl_m07_curated",
// Glue creates the initial Iceberg metadata for us.
OpenTableFormatInput = new CfnTable.OpenTableFormatInputProperty
{
IcebergInput = new CfnTable.IcebergInputProperty { MetadataOperation = "CREATE", Version = "2" },
},
TableInput = new CfnTable.TableInputProperty
{
Name = "transactions_iceberg",
TableType = "EXTERNAL_TABLE",
StorageDescriptor = new CfnTable.StorageDescriptorProperty
{
Location = $"s3://{curatedBucket}/iceberg/transactions/",
Columns = new object[]
{
new CfnTable.ColumnProperty { Name = "transaction_id", Type = "string" },
new CfnTable.ColumnProperty { Name = "account_id", Type = "string" },
new CfnTable.ColumnProperty { Name = "booking_date", Type = "date" },
new CfnTable.ColumnProperty { Name = "amount", Type = "decimal(18,2)" },
new CfnTable.ColumnProperty { Name = "currency", Type = "string" },
new CfnTable.ColumnProperty { Name = "type", Type = "string" },
new CfnTable.ColumnProperty { Name = "status", Type = "string" },
new CfnTable.ColumnProperty { Name = "created_at", Type = "timestamp" },
},
},
},
});
}
}Declaring Iceberg partition transforms (day(booking_date)) is not possible through every IaC path ⚠ VERIFY for CfnTable; teams often create the table with Athena DDL in a deployment step and manage only the database and permissions in IaC. For S3 Tables, the L1 constructs live in Amazon.CDK.AWS.S3Tables (CfnTableBucket, CfnNamespace, CfnTable).
Complete code
SQL (run in order in workgroup hl-m07-analysts; replace ACCOUNT_ID)
CREATE DATABASE IF NOT EXISTS hl_m07_raw;
CREATE DATABASE IF NOT EXISTS hl_m07_curated;
-- Staging: the raw CSV sample read as typed columns (schema-on-read).
CREATE EXTERNAL TABLE IF NOT EXISTS hl_m07_raw.transactions_core_csv (
transaction_id string, account_id string, booking_date date, value_date date,
amount decimal(18,2), currency string, type string, channel string,
counterparty_name string, counterparty_iban string, merchant_category_code string,
reference string, status string, idempotency_key string, created_at string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ('separatorChar' = ',', 'quoteChar' = '"')
LOCATION 's3://hl-m07-raw-ACCOUNT_ID/transactions/source=core/ingest_date=2026-11-16/'
TBLPROPERTIES ('skip.header.line.count' = '1');CREATE TABLE hl_m07_curated.transactions_iceberg (
transaction_id string,
account_id string,
booking_date date,
value_date date,
amount decimal(18,2),
currency string,
type string,
channel string,
merchant_category_code string,
reference string,
status string,
created_at timestamp
)
PARTITIONED BY (day(booking_date))
LOCATION 's3://hl-m07-curated-ACCOUNT_ID/iceberg/transactions/'
TBLPROPERTIES (
'table_type' = 'ICEBERG',
'format' = 'parquet',
'write_compression' = 'zstd',
'vacuum_max_snapshot_age_seconds' = '86400'
);INSERT INTO hl_m07_curated.transactions_iceberg
SELECT transaction_id, account_id, booking_date, value_date, amount, currency, type, channel,
merchant_category_code, reference, status,
from_iso8601_timestamp(created_at) -- '2026-10-20T18:02:54Z' -> timestamp
FROM hl_m07_raw.transactions_core_csv;
-- Inspect the table history
SELECT snapshot_id, committed_at, operation, summary['added-records'] AS added
FROM hl_m07_curated."transactions_iceberg$snapshots"
ORDER BY committed_at;-- A chargeback arrives late: reverse the original card payment and add a credit, atomically.
MERGE INTO hl_m07_curated.transactions_iceberg t
USING (
SELECT '01a172d5-d5f8-75f5-b05e-314cfea1cd18' AS transaction_id, 'reverse' AS action
UNION ALL
SELECT '01a2c0de-0000-7000-8000-00000000cb01', 'insert'
) c
ON t.transaction_id = c.transaction_id
WHEN MATCHED AND c.action = 'reverse' THEN
UPDATE SET status = 'reversed'
WHEN NOT MATCHED AND c.action = 'insert' THEN
INSERT (transaction_id, account_id, booking_date, value_date, amount, currency, type, channel,
merchant_category_code, reference, status, created_at)
VALUES (c.transaction_id, 'ACC-1000000001', DATE '2026-11-16', DATE '2026-10-25', 2.13, 'GBP',
'transfer_in', 'batch', NULL, 'CHARGEBACK POS 1507 RIVERSIDE RAIL', 'booked',
TIMESTAMP '2026-11-16 09:15:00');-- Booked net movement for ACC-1000000001 as of the first snapshot (replace with your id) ...
SELECT SUM(amount) AS net_before
FROM hl_m07_curated.transactions_iceberg FOR VERSION AS OF 1234567890123456789
WHERE account_id = 'ACC-1000000001' AND status = 'booked';
-- ... and now. The difference is the chargeback effect (+2.13 credit, and the -2.13 debit no longer booked).
SELECT SUM(amount) AS net_now
FROM hl_m07_curated.transactions_iceberg
WHERE account_id = 'ACC-1000000001' AND status = 'booked';
-- Or by time: the table as it was ten minutes ago
SELECT COUNT(*) FROM hl_m07_curated.transactions_iceberg
FOR TIMESTAMP AS OF (current_timestamp - interval '10' minute);-- Compact small files and apply delete files for the affected partition
OPTIMIZE hl_m07_curated.transactions_iceberg REWRITE DATA USING BIN_PACK
WHERE booking_date >= DATE '2026-10-25';
-- Expire snapshots older than vacuum_max_snapshot_age_seconds and remove orphan files
VACUUM hl_m07_curated.transactions_iceberg;C#: run the scripts and print scan statistics
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net10.0</TargetFramework>
<Nullable>enable</Nullable>
<ImplicitUsings>enable</ImplicitUsings>
<TreatWarningsAsErrors>true</TreatWarningsAsErrors>
<RootNamespace>Harbourline.DataLake.IcebergRunner</RootNamespace>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="AWSSDK.Athena" Version="4.1.1.1" />
</ItemGroup>
</Project>using Amazon.Athena;
using Amazon.Athena.Model;
namespace Harbourline.DataLake.IcebergRunner;
public static class Program
{
private const string WorkGroup = "hl-m07-analysts";
public static async Task<int> Main(string[] args)
{
if (args.Length < 2)
{
Console.Error.WriteLine("usage: IcebergRunner <accountId> <file.sql> [file2.sql ...]");
return 2;
}
using var athena = new AmazonAthenaClient(Amazon.RegionEndpoint.USEast1);
foreach (var file in args.Skip(1))
{
var sql = (await File.ReadAllTextAsync(file)).Replace("ACCOUNT_ID", args[0]);
// Athena runs one statement per call: split on ';' at line ends.
foreach (var statement in sql.Split(";\n", StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries)
.Select(s => s.TrimEnd(';'))
.Where(s => s.Split('\n').Any(l => l.Trim().Length > 0 && !l.TrimStart().StartsWith("--"))))
{
await RunAsync(athena, statement);
}
}
return 0;
}
private static async Task RunAsync(IAmazonAthena athena, string statement)
{
var start = await athena.StartQueryExecutionAsync(new StartQueryExecutionRequest
{
QueryString = statement,
WorkGroup = WorkGroup, // output location and encryption are enforced by the workgroup
});
QueryExecution execution;
var delay = TimeSpan.FromMilliseconds(500);
while (true)
{
execution = (await athena.GetQueryExecutionAsync(new GetQueryExecutionRequest
{
QueryExecutionId = start.QueryExecutionId,
})).QueryExecution;
var state = execution.Status.State;
if (state == QueryExecutionState.SUCCEEDED || state == QueryExecutionState.FAILED || state == QueryExecutionState.CANCELLED)
break;
await Task.Delay(delay);
delay = TimeSpan.FromMilliseconds(Math.Min(delay.TotalMilliseconds * 2, 5000)); // back off
}
var firstLine = statement.Split('\n').First(l => !l.TrimStart().StartsWith("--")).Trim();
var scannedMb = (execution.Statistics?.DataScannedInBytes ?? 0) / 1_048_576.0;
Console.WriteLine($"{execution.Status.State,-9} {scannedMb,8:F3} MB {firstLine}");
if (execution.Status.State != QueryExecutionState.SUCCEEDED)
{
Console.WriteLine($" {execution.Status.StateChangeReason}");
return;
}
if (execution.StatementType == StatementType.DML)
{
var results = await athena.GetQueryResultsAsync(new GetQueryResultsRequest
{
QueryExecutionId = start.QueryExecutionId,
MaxResults = 20,
});
foreach (var row in results.ResultSet.Rows)
Console.WriteLine(" " + string.Join(" | ", row.Data.Select(d => d.VarCharValue ?? "NULL")));
}
}
}Run it:
aws sso login --profile learn-sandbox && export AWS_PROFILE=learn-sandbox
dotnet run --project IcebergRunner -- "$ACCOUNT" 01-staging.sql 02-create-iceberg.sql 03-load.sqlCode explained line by line
SQL
OpenCSVSerdein01-staging.sql— reads quoted CSV correctly. It is lenient on types:created_atis declaredstringand converted later withfrom_iso8601_timestamp, because the CSV form (...T18:02:54Z) is not Hive's default timestamp format.PARTITIONED BY (day(booking_date))— a partition transform. Iceberg stores the derived day value in metadata; users never see or maintain partition columns, and queries onbooking_dateprune automatically.'table_type' = 'ICEBERG'— tells Athena to create Iceberg metadata (v2) instead of a Hive table.'write_compression' = 'zstd'— Parquet codec for data files written by Athena.'vacuum_max_snapshot_age_seconds' = '86400'— snapshots older than one day are removable byVACUUM. A learning value; production retention is a policy decision."transactions_iceberg$snapshots"— a metadata table. The quotes are required because of the$.MERGE INTO ... WHEN MATCHED ... UPDATE ... WHEN NOT MATCHED ... INSERT— one atomic commit applies the reversal and the new credit. Readers see both or neither.FOR VERSION AS OF <snapshot_id>/FOR TIMESTAMP AS OF <timestamp>— Athena engine v3 time-travel syntax.OPTIMIZE ... REWRITE DATA USING BIN_PACK WHERE ...— compacts files and merges delete files for the selected partitions only, limiting cost.VACUUM— expires snapshots and deletes unreferenced files. After this, those snapshots cannot be time-travelled to.
C#
new AmazonAthenaClient(RegionEndpoint.USEast1)— credentials from the SSO profile; nothing hard-coded.sql.Split(";\n", ...)— Athena accepts one statement perStartQueryExecution; the runner splits files and skips comment-only fragments.WorkGroup = WorkGroupand noResultConfiguration— the workgroup enforces output location, encryption and the scan limit, so client code cannot weaken them.- The polling loop with exponential back-off up to 5 seconds — Athena is asynchronous; polling too fast wastes API quota (
GetQueryExecutionis throttled per account). execution.Statistics.DataScannedInBytes— what you pay for. Printing it on every statement builds the habit of watching scan cost.execution.StatementType == StatementType.DML— onlySELECT-style statements have result rows worth printing; DDL returns none.GetQueryResultsAsync(... MaxResults = 20)— the first page only; the Athena lesson shows full pagination.
Security considerations
⚠ Learning shortcut — not for production
You run MERGE, DELETE and VACUUM as your SSO administrator. In production, only the pipeline role (and a tightly controlled data-operations role for approved corrections and erasure) may write to curated Iceberg tables. Analysts get SELECT only, through Lake Formation. Every change is made by a reviewed job, not by hand in the console.
- Commit permissions. Writing an Iceberg table requires S3 write on the table location and
glue:UpdateTableon the catalogue table (the commit). Denyglue:UpdateTableto analyst roles even if they can write elsewhere. - Erasure and time travel. A
DELETEremoves rows from the current snapshot, but old snapshots still reference files containing them untilVACUUMexpires those snapshots and deletes the files. An erasure procedure must include snapshot expiry and orphan-file cleanup, and must account for S3 versioning (noncurrent versions) and backups. Get the DPO's agreement on the end-to-end procedure. - Encryption. Iceberg data and metadata files are ordinary S3 objects encrypted with the bucket's CMK. Metadata files contain column statistics (min/max values): treat them as sensitive.
- S3 Tables permissions use the separate
s3tables:*IAM namespace; existings3:*denies in SCPs do not cover table buckets. Review guardrails before adopting them.
Banking use case
Harbourline's architecture decision record (ADR-017, fictional) for curated transactions:
- Decision: curated transactions become an Iceberg table in the curated bucket, partitioned by
day(booking_date), written only by the pipeline. Chargebacks and late corrections are applied withMERGEby a reviewed job; approved erasure requests run aDELETEfollowed by a scheduled snapshot expiry. - Regulatory reproduction: each submitted regulatory return stores the Iceberg snapshot ID it was computed from, plus a copy of the output in an Object Lock bucket. Snapshots are kept for 35 days (learning value); beyond that, the stored output is the record.
- Finance mart: Redshift Serverless (4 RPU base) hosts the finance star schema used by 60 concurrent dashboard users, loaded nightly from gold and reading curated Iceberg through Spectrum for drill-through.
- Not chosen: S3 Tables for now, because the security team wants one bucket-policy and KMS model across the lake and is still reviewing
s3tablesguardrails. To be revisited.
Common mistakes
- Never running maintenance → thousands of small files and delete files, slower and costlier queries → schedule
OPTIMIZE/VACUUM, enable Glue table optimizers, or use S3 Tables. - Treating snapshots as an archive → history disappears at the next
VACUUM→ store explicit report outputs for records you must keep. - Forgetting that
DELETEis not erasure → personal data remains in old snapshots, noncurrent S3 versions and backups → expire snapshots, clean orphans, and handle versions and backups in the procedure. - Moving everything to Redshift "for performance" → paying for a warehouse to serve ad hoc queries on cold data → keep the lake as the system of record; put only hot, heavily joined marts in Redshift.
- Mixing engines with different Iceberg versions → one engine cannot read tables written with newer features → agree on a format version (v2 for Athena writes).
- Manual edits in the console → no review, no audit trail → all writes through jobs in version control.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
ICEBERG_COMMIT_ERROR / commit conflict | Two writers committed at the same time | Retry the job; serialise writers (Step Functions) for the same table |
NOT_SUPPORTED: Partitioning by nested field is unsupported | Partition transform on a struct field | Partition by a top-level column |
| Time-travel query fails with "snapshot not found" | Snapshot expired by VACUUM | Use a newer snapshot; adjust retention properties if history is required |
HIVE_METASTORE_ERROR / access denied on UpdateTable | Role lacks glue:UpdateTable (or Lake Formation ALTER) | Grant to the writer role only |
MERGE is slow and scans a lot | ON condition does not allow partition pruning | Add a booking_date predicate to the merge source and condition |
| Athena DDL on the Iceberg table fails after Lake Formation registration | Athena does not support DDL on LF-registered Iceberg tables | Run DDL before registration, or with Spark/Glue |
from_iso8601_timestamp returns timestamp with time zone mismatch | Inserting into a timestamp column | Wrap with CAST(... AS timestamp) if your engine version requires it |
Cost and cleanup
Expected cost: about 12 Athena queries at the 10 MB minimum ≈ 12 × 10 MB × USD 5/TB ≈ USD 0.0006. OPTIMIZE and VACUUM are billed as queries by bytes scanned. S3 storage for a few hundred KB of Iceberg files and metadata: effectively USD 0.00. An S3 table bucket with an empty namespace costs nothing until tables hold data. Redshift is not deployed in this lesson.
export AWS_PROFILE=learn-sandbox AWS_REGION=us-east-1
ACCOUNT=$(aws sts get-caller-identity --query Account --output text)
# Drop tables (Iceberg DROP TABLE in Athena also deletes the table's data files)
for SQL in "DROP TABLE IF EXISTS hl_m07_curated.transactions_iceberg" \
"DROP TABLE IF EXISTS hl_m07_raw.transactions_core_csv"; do
aws athena start-query-execution --work-group hl-m07-analysts --query-string "$SQL"
done
# Remove any remaining Iceberg files and the staging copy
aws s3 rm "s3://hl-m07-curated-$ACCOUNT/iceberg/" --recursive
aws s3 rm "s3://hl-m07-raw-$ACCOUNT/transactions/source=core/" --recursive
# Optional S3 Tables resources
aws s3tables delete-namespace --table-bucket-arn "arn:aws:s3tables:us-east-1:$ACCOUNT:bucket/hl-m07-tables-$ACCOUNT" --namespace curated
aws s3tables delete-table-bucket --table-bucket-arn "arn:aws:s3tables:us-east-1:$ACCOUNT:bucket/hl-m07-tables-$ACCOUNT"
# Keep the workgroup if you continue with the Athena lesson; otherwise:
aws athena delete-work-group --work-group hl-m07-analysts --recursive-delete-option
aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab \
--query "ResourceTagMappingList[].ResourceARN"Bucket and key teardown is in S3 data lake design.
Exercises
- 🟢 Guided: Run the six SQL files. Record each snapshot ID, the
net_beforeandnet_nowvalues, and the bytes scanned per query. - 🟡 Challenge: Add a column
chargeback_reason stringto the Iceberg table withALTER TABLE ... ADD COLUMNS, then rename it todispute_reasonwithALTER TABLE ... CHANGE COLUMN. Show that time travel to the first snapshot still works and that the renamed column keeps its data. Explain why the same rename on a plain Parquet table would lose data. - 🔴 Live problem: The DPO approves an erasure request for fictional customer
CUS-00010007. Their transactions are in curated Iceberg, gold aggregates, quarantine JSON files, S3 noncurrent versions and AWS Backup snapshots. Write the step-by-step procedure, the evidence you would keep, and which parts need legal input.
Assignment
Write an architecture decision record for Harbourline: which datasets stay as plain Parquet, which become Iceberg (self-managed or S3 Tables), and what goes to Redshift Serverless. Include the decision drivers (corrections, erasure, BI concurrency, cost), the maintenance plan and the snapshot retention rule. Commit it with your SQL files.
Solutions
You should see two snapshots after step 4 (append, then overwrite), and a third after OPTIMIZE (replace). net_now - net_before equals +4.26: the reversed card payment no longer counts (+2.13 compared with before) and the chargeback credit adds +2.13. Every query reports a few KB to a few hundred KB scanned, billed at the 10 MB minimum. Why: the snapshot list is your audit trail of table changes, and time travel lets you prove both numbers.
ALTER TABLE hl_m07_curated.transactions_iceberg ADD COLUMNS (chargeback_reason string);
UPDATE hl_m07_curated.transactions_iceberg SET chargeback_reason = 'fraud_claim'
WHERE transaction_id = '01a2c0de-0000-7000-8000-00000000cb01';
ALTER TABLE hl_m07_curated.transactions_iceberg CHANGE COLUMN chargeback_reason dispute_reason string;
SELECT transaction_id, dispute_reason FROM hl_m07_curated.transactions_iceberg WHERE dispute_reason IS NOT NULL;The value survives the rename because Iceberg identifies columns by ID, and the name is just metadata. On a plain Parquet table, Athena maps columns by name, so after a rename old files have no column called dispute_reason and return null. Time travel to the first snapshot uses that snapshot's schema, which has neither column.
- Scope: list every store holding the customer's data (via their account IDs
ACC-…fromaccounts): curated Iceberg, gold (aggregates — check whether any row is small enough to identify them), quarantine, raw (if still within retention), noncurrent versions, backups, Athena results, local extracts. 2. Legal input: confirm no other legal basis (for example AML record-keeping) requires retention of some records; the DPO decides what is erased and what is restricted. 3. Curated:DELETE FROM ... WHERE account_id IN (...), then expire snapshots older than the delete (VACUUMwith a shortvacuum_max_snapshot_age_seconds, or Glue optimizer), then remove orphan files. 4. Quarantine/raw: delete affected objects; delete noncurrent versions explicitly. 5. Backups: follow the backup erasure policy (often "put beyond use" until backups expire, documented). 6. Evidence: ticket ID, DPO approval, queries run with execution IDs, row counts before and after, snapshot IDs, S3 delete logs from CloudTrail, and a final verification query returning zero rows. Never include the personal data itself in the evidence.
Knowledge check
Answer: A metadata layer (metadata files, manifest lists, manifests) with atomic snapshot commits, giving ACID writes, row-level updates and deletes, schema and partition evolution, file-level statistics for pruning, and time travel.
Answer: The table declares a transform such as day(booking_date); Iceberg derives and stores partition values in metadata. Users filter on the source column and pruning happens automatically, with no separate partition columns to maintain.
Answer: VACUUM expires snapshots older than the configured age and deletes files only they referenced; those versions no longer exist.
Answer: For heavy, repeated, concurrent BI workloads with complex joins and strict latency, where distribution and sort keys, materialised views, result caching and workload management pay off.
Answer: Iceberg tables in a table bucket with automatic compaction, snapshot management and unreferenced-file removal, plus table-level IAM permissions in the s3tables namespace.
Interview questions
- "Lake, warehouse or lakehouse for a bank?" — Usually all three roles: the lake as the cheap, open system of record for raw and curated data; Iceberg tables where you need corrections, erasure and reproducibility; a warehouse (Redshift) for the hot, high-concurrency BI marts. Decide per dataset using update patterns, concurrency and cost.
- "How do you handle late-arriving corrections in a data lake?" — Iceberg
MERGEkeyed on the business ID, with partition predicates to limit rewrite cost; record the correction with the run ID; reproduce previous reports by snapshot ID. - "What maintenance does Iceberg need?" — Compaction of small and delete files, snapshot expiry, orphan-file removal, and sometimes manifest rewrites. Automate it (Glue table optimizers or S3 Tables) and monitor file counts.
Certification practice
A data engineer must apply daily corrections (updates and deletes of individual records) to a 10 TB transaction table in S3 queried by Athena, while readers must never see partial results. What should the engineer do?
A. Store the data as CSV and rewrite affected files with a Lambda function.
B. Convert the table to an Apache Iceberg table and apply corrections with MERGE INTO in Athena.
C. Store the data in Amazon DynamoDB and query it with Athena federated queries.
D. Partition by transaction_id so each correction rewrites one small file.
Answer: B. Iceberg provides atomic commits and row-level MERGE. A gives no atomicity and rewrites are error-prone. C changes the storage model and makes large analytical scans expensive. D creates billions of tiny partitions.
Auditors require the ability to re-run a regulatory report exactly as it was produced up to 30 days ago, even though source tables receive late corrections. The tables are Iceberg tables queried by Athena. What is the LEAST operationally complex approach?
A. Copy the whole table to a dated prefix every day.
B. Record the snapshot ID used by each report run, keep snapshots for at least 30 days, and re-run with FOR VERSION AS OF.
C. Disable all corrections for 30 days.
D. Export the report output only and recompute from raw if needed.
Answer: B. Snapshot IDs plus retention make reproduction a single query. A duplicates storage daily. C is not acceptable to the business. D cannot reproduce intermediate states reliably once raw has been reprocessed or expired.
A finance team runs the same complex star-schema dashboards with 80 concurrent users throughout the business day. Queries on Athena are slower than required and costs grow with each refresh. Which service is MOST appropriate?
A. Amazon Redshift Serverless with the finance mart loaded from the data lake. B. Amazon DynamoDB with global secondary indexes. C. Larger Glue jobs that precompute every possible chart. D. Amazon RDS for SQL Server with read replicas.
Answer: A. Redshift is built for concurrent, repeated analytical joins, and Serverless bills while queries run. B is not an analytical store. C cannot anticipate ad hoc filtering. D is an OLTP engine that does not scale analytically like a columnar MPP warehouse.
Completion checklist
- I can explain lake vs warehouse vs lakehouse with the filing-cabinet analogy
- I created an Iceberg table with hidden partitioning and loaded it
- I applied a MERGE and proved the change with time travel
- I ran OPTIMIZE and VACUUM and understood what history I lost
- I dropped the tables and confirmed nothing tagged
Project=cea-labis left that I did not intend to keep
Key takeaways
- Data lakes are cheap and open but have no transactions or history; warehouses have both but couple storage to one engine.
- Iceberg turns a folder of Parquet files into a transactional table with time travel and safe schema evolution.
- Iceberg needs maintenance: compaction and snapshot expiry, done by you, by Glue optimizers, or by S3 Tables.
- Time travel is not retention, and
DELETEalone is not erasure. - Redshift remains the best fit for high-concurrency, repeated BI over joined marts; keep the lake as the system of record.
Further reading
Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.
File formats: CSV vs JSON vs Parquet, compression and schema evolution
Measure how CSV, JSON Lines and Parquet store the same Harbourline transactions, understand row groups, column statistics and compression codecs, handle schema evolution safely, and see why Parquet makes Athena faster and cheaper.
AWS Glue Data Catalog and crawlers: tables, partitions and schema drift
Catalogue Harbourline's raw and curated data in the AWS Glue Data Catalog, run a crawler safely with the right schema-change and recrawl policies, and know when to define schemas yourself, register partitions explicitly or use Athena partition projection instead.