Skip to Content
Cloud Engineering Academy
7. Data Engineering & ETL Pipelines

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.

intermediate⏱ 2 h 🟡 Low costDEA-C01SAA-C03

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 $snapshots metadata 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

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:

  1. 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.
  2. "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.
  3. 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:

FeatureWhat it means for Harbourline
ACID commitsA job writing 50 files publishes all or nothing; readers never see half a day
Row-level UPDATE, DELETE, MERGEApply 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 IDRename 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 evolutionChange 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)
StorageS3, open filesS3, open files + Iceberg metadataRedshift managed storage (columnar, internal)
EnginesAthena, Spark, Redshift Spectrum, many othersSame, if they support IcebergRedshift (plus data sharing, Spectrum to read the lake)
Updates and deletesRewrite files or partitions yourselfUPDATE, DELETE, MERGEFull SQL DML
TransactionsNone across filesSnapshot isolation per tableFull, multi-table
HistoryOnly what you copySnapshots until expiredSnapshots for backup, not queryable history (use your own versioning)
Concurrency for BIEngine-dependent; Athena has per-account query concurrency quotasSameDesigned for many concurrent BI users; workload management, concurrency scaling
Joins on large tables, repeated dashboardsFine for moderate useFine for moderate useBest: sort keys, distribution, materialised views, result caching
Cost modelS3 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 useRaw zone, append-only curated datasetsCurated 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 s3tables IAM 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 equivalentWhat's different
SQL Server data warehouse / Synapse dedicated SQL poolAmazon 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 / DatabricksApache 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 OneLakeS3 general purpose buckets + S3 TablesSeveral services, one catalogue (Glue Data Catalog)
Delta VERSION AS OF / TIMESTAMP AS OFIceberg FOR VERSION AS OF / FOR TIMESTAMP AS OF in AthenaSnapshot IDs are bigint values
OPTIMIZE / VACUUM in DeltaOPTIMIZE ... REWRITE DATA USING BIN_PACK / VACUUM in Athena; automatic in S3 TablesSame concepts, different syntax
SQL Server temporal tablesIceberg snapshots (until expired)Snapshots are table-level, not row-level validity periods
Synapse serverless reading DeltaAthena / Redshift Spectrum reading IcebergSame "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, database hl_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/VACUUM yourself (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 curated

EngineVersion=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:

IcebergTransactionsTable.cs
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)

01-staging.sql
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');
02-create-iceberg.sql
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'
);
03-load.sql
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;
04-merge-chargeback.sql
-- 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');
05-time-travel.sql
-- 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);
06-maintain.sql
-- 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

IcebergRunner/IcebergRunner.csproj
<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>
IcebergRunner/Program.cs
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.sql

Code explained line by line

SQL

  1. OpenCSVSerde in 01-staging.sql — reads quoted CSV correctly. It is lenient on types: created_at is declared string and converted later with from_iso8601_timestamp, because the CSV form (...T18:02:54Z) is not Hive's default timestamp format.
  2. 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 on booking_date prune automatically.
  3. 'table_type' = 'ICEBERG' — tells Athena to create Iceberg metadata (v2) instead of a Hive table.
  4. 'write_compression' = 'zstd' — Parquet codec for data files written by Athena.
  5. 'vacuum_max_snapshot_age_seconds' = '86400' — snapshots older than one day are removable by VACUUM. A learning value; production retention is a policy decision.
  6. "transactions_iceberg$snapshots" — a metadata table. The quotes are required because of the $.
  7. MERGE INTO ... WHEN MATCHED ... UPDATE ... WHEN NOT MATCHED ... INSERT — one atomic commit applies the reversal and the new credit. Readers see both or neither.
  8. FOR VERSION AS OF <snapshot_id> / FOR TIMESTAMP AS OF <timestamp> — Athena engine v3 time-travel syntax.
  9. OPTIMIZE ... REWRITE DATA USING BIN_PACK WHERE ... — compacts files and merges delete files for the selected partitions only, limiting cost.
  10. VACUUM — expires snapshots and deletes unreferenced files. After this, those snapshots cannot be time-travelled to.

C#

  1. new AmazonAthenaClient(RegionEndpoint.USEast1) — credentials from the SSO profile; nothing hard-coded.
  2. sql.Split(";\n", ...) — Athena accepts one statement per StartQueryExecution; the runner splits files and skips comment-only fragments.
  3. WorkGroup = WorkGroup and no ResultConfiguration — the workgroup enforces output location, encryption and the scan limit, so client code cannot weaken them.
  4. The polling loop with exponential back-off up to 5 seconds — Athena is asynchronous; polling too fast wastes API quota (GetQueryExecution is throttled per account).
  5. execution.Statistics.DataScannedInBytes — what you pay for. Printing it on every statement builds the habit of watching scan cost.
  6. execution.StatementType == StatementType.DML — only SELECT-style statements have result rows worth printing; DDL returns none.
  7. 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:UpdateTable on the catalogue table (the commit). Deny glue:UpdateTable to analyst roles even if they can write elsewhere.
  • Erasure and time travel. A DELETE removes rows from the current snapshot, but old snapshots still reference files containing them until VACUUM expires 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; existing s3:* 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 with MERGE by a reviewed job; approved erasure requests run a DELETE followed 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 s3tables guardrails. 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 DELETE is 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

SymptomLikely causeFix
ICEBERG_COMMIT_ERROR / commit conflictTwo writers committed at the same timeRetry the job; serialise writers (Step Functions) for the same table
NOT_SUPPORTED: Partitioning by nested field is unsupportedPartition transform on a struct fieldPartition by a top-level column
Time-travel query fails with "snapshot not found"Snapshot expired by VACUUMUse a newer snapshot; adjust retention properties if history is required
HIVE_METASTORE_ERROR / access denied on UpdateTableRole lacks glue:UpdateTable (or Lake Formation ALTER)Grant to the writer role only
MERGE is slow and scans a lotON condition does not allow partition pruningAdd a booking_date predicate to the merge source and condition
Athena DDL on the Iceberg table fails after Lake Formation registrationAthena does not support DDL on LF-registered Iceberg tablesRun DDL before registration, or with Spark/Glue
from_iso8601_timestamp returns timestamp with time zone mismatchInserting into a timestamp columnWrap 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_before and net_now values, and the bytes scanned per query.
  • 🟡 Challenge: Add a column chargeback_reason string to the Iceberg table with ALTER TABLE ... ADD COLUMNS, then rename it to dispute_reason with ALTER 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

Knowledge check

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 MERGE keyed 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

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-lab is 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 DELETE alone 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.

On this page