Amazon Athena and Apache Iceberg: ACID Claims Tables on S3

For a long time, the honest answer to "can I update a row in my S3 data lake?" was "sort of": rewrite the partition, overwrite the files, and hope nobody queries it at the same moment. That's a real problem for claims data. Claim transactions get corrected, reserves move every month, and claimant personal data has to be removable on request. Apache Iceberg changes that, and Amazon Athena (engine v3) supports it well. You get INSERT, UPDATE, DELETE, MERGE, time travel and schema evolution on plain files in S3, using SQL that feels close to a warehouse.

Athena is already my go-to for quick checks on data sitting in S3. With Iceberg, it becomes much more than that. And with AWS announcing S3 Tables at re:Invent last week, which is S3 storage built specifically for Iceberg, it's clear where AWS's lakehouse strategy is heading. Here's a practical introduction using Athena, the Glue Data Catalog and a claims bordereau.

Creating an Iceberg table

 1: CREATE TABLE da_lake.claim_transaction (
 2:   claim_ref            STRING,
 3:   umr                  STRING,
 4:   section_no           STRING,
 5:   coverholder_id       STRING,
 6:   certificate_ref      STRING,
 7:   bordereau_month      DATE,
 8:   date_of_loss         DATE,
 9:   claim_status         STRING,      -- OPEN, CLOSED, REOPENED, DECLINED
10:   claimant_name        STRING,
11:   paid_this_month      DECIMAL(18,2),
12:   outstanding_reserve  DECIMAL(18,2),
13:   fees_paid            DECIMAL(18,2),
14:   original_ccy         STRING,
15:   transaction_ts       TIMESTAMP
16: )
17: PARTITIONED BY (month(transaction_ts))
18: LOCATION 's3://da-lake/iceberg/claim_transaction/'
19: TBLPROPERTIES (
20:   'table_type'        = 'ICEBERG',
21:   'format'            = 'parquet',
22:   'write_compression' = 'zstd'
23: );

Two things worth noting:

• 'table_type' = 'ICEBERG' is what makes it an Iceberg table rather than a classic Hive table. The table is registered in the Glue Data Catalog, so EMR, Glue jobs and Redshift Spectrum can use it too.
• PARTITIONED BY (month(transaction_ts)) is hidden partitioning. You don't add a separate partition column and you don't have to remember to filter on it. Queries filtering on transaction_ts get partition pruning automatically. Anyone who has explained Hive's dt= partition columns to claims analysts will appreciate this.

Loading the monthly claims bordereau

 1: -- From the raw CSV table that the TPA files land in
 2: INSERT INTO da_lake.claim_transaction
 3: SELECT claim_ref, umr, section_no, coverholder_id, certificate_ref,
 4:        CAST(bordereau_month AS DATE),
 5:        CAST(date_of_loss AS DATE),
 6:        UPPER(claim_status),
 7:        claimant_name,
 8:        CAST(paid_this_month     AS DECIMAL(18,2)),
 9:        CAST(outstanding_reserve AS DECIMAL(18,2)),
10:        CAST(fees_paid           AS DECIMAL(18,2)),
11:        original_ccy,
12:        CAST(transaction_ts AS TIMESTAMP)
13: FROM   raw.claims_bdx_csv
14: WHERE  bordereau_month = '2024-11-01';

Row-level changes

 1: -- The TPA confirms a claim was closed with no further payment
 2: UPDATE da_lake.claim_transaction
 3: SET    claim_status = 'CLOSED', outstanding_reserve = 0
 4: WHERE  claim_ref = 'CLM-2024-004512'
 5:   AND  bordereau_month = DATE '2024-11-01';
 6: 
 7: -- Data subject request: remove claimant personal data
 8: UPDATE da_lake.claim_transaction
 9: SET    claimant_name = NULL
10: WHERE  claim_ref = 'CLM-2021-000193';

That second one is a big reason teams move to Iceberg. Removing one claimant's details across years of Parquet files used to be a project. Now it's a statement (plus a VACUUM to physically remove old files, covered below, since older snapshots still contain the original values).

MERGE for corrected bordereaux

When a TPA resubmits a month with corrections, MERGE applies it in one transaction:

 1: MERGE INTO da_lake.claim_transaction t
 2: USING raw.claims_bdx_resubmission s
 3:   ON  t.claim_ref = s.claim_ref
 4:   AND t.bordereau_month = s.bordereau_month
 5: WHEN MATCHED AND s.record_action = 'DELETE' THEN DELETE
 6: WHEN MATCHED THEN UPDATE SET
 7:      claim_status        = s.claim_status,
 8:      paid_this_month     = s.paid_this_month,
 9:      outstanding_reserve = s.outstanding_reserve,
10:      fees_paid           = s.fees_paid
11: WHEN NOT MATCHED THEN INSERT
12:      (claim_ref, umr, section_no, coverholder_id, certificate_ref, bordereau_month,
13:       date_of_loss, claim_status, claimant_name, paid_this_month, outstanding_reserve,
14:       fees_paid, original_ccy, transaction_ts)
15: VALUES (s.claim_ref, s.umr, s.section_no, s.coverholder_id, s.certificate_ref,
16:         s.bordereau_month, s.date_of_loss, s.claim_status, s.claimant_name,
17:         s.paid_this_month, s.outstanding_reserve, s.fees_paid, s.original_ccy,
18:         s.transaction_ts);

Time travel: reproduce a month-end

This is the feature I'd pick Iceberg for on its own. Reserving and finance teams regularly ask, "what did the claims position look like when we closed November, before the resubmission came in?"

 1: -- Incurred by contract section as at month-end close
 2: SELECT umr, section_no,
 3:        SUM(paid_this_month)     AS paid,
 4:        SUM(outstanding_reserve) AS outstanding
 5: FROM   da_lake.claim_transaction
 6: FOR TIMESTAMP AS OF TIMESTAMP '2024-12-06 18:00:00 UTC'
 7: WHERE  bordereau_month = DATE '2024-11-01'
 8: GROUP  BY umr, section_no;
 9: 
10: -- As of a specific snapshot
11: SELECT * FROM da_lake.claim_transaction FOR VERSION AS OF 4348509271362591234;
12: 
13: -- Which snapshots exist?
14: SELECT * FROM "da_lake"."claim_transaction$snapshots" ORDER BY committed_at DESC;
15: 
16: -- Which files and partitions?
17: SELECT * FROM "da_lake"."claim_transaction$partitions";

The $snapshots, $files, $partitions and $history metadata tables are great for debugging. When someone says "the outstanding figure changed overnight", you can see exactly which commit did it.

Maintenance: OPTIMIZE and VACUUM

Every UPDATE, DELETE or MERGE writes new files (Athena uses merge-on-read delete files), and monthly loads from dozens of coverholders create lots of small files. Over time reads slow down. Two commands keep a table healthy:

1: -- Compact small files and apply delete files (bin-packing)
2: OPTIMIZE da_lake.claim_transaction REWRITE DATA USING BIN_PACK
3: WHERE transaction_ts >= TIMESTAMP '2024-11-01 00:00:00';
4: 
5: -- Expire old snapshots and delete files no longer referenced
6: VACUUM da_lake.claim_transaction;

How long VACUUM keeps snapshots is controlled by table properties. Balance two needs here: reproducing month-ends (keep snapshots longer) and erasing personal data on request (old snapshots must eventually go).

1: ALTER TABLE da_lake.claim_transaction SET TBLPROPERTIES (
2:   'vacuum_max_snapshot_age_seconds' = '3456000'   -- keep 40 days of time travel
3: );

Forty days covers the current month-end and the previous one. For longer-term reproducibility, it's safer to snapshot each month-end close into its own table rather than relying on time travel forever. Glue Data Catalog also now offers automatic compaction for Iceberg tables, which is worth turning on so you don't have to schedule OPTIMIZE yourself.

Schema evolution

1: ALTER TABLE da_lake.claim_transaction ADD COLUMNS (cause_of_loss STRING);
2: ALTER TABLE da_lake.claim_transaction
3:   CHANGE COLUMN outstanding_reserve outstanding_reserve DECIMAL(20,2);  -- widening is allowed

Iceberg tracks columns by ID, not by name or position, so renames and reorders don't break existing data files. That's a real improvement on Hive tables, where renaming a column in a Parquet table could quietly return nulls, which is not what you want to discover in a claims report.

Cost and performance tips

• Athena charges per TB scanned, so partition pruning and Parquet column pruning are your cost controls. Always filter on transaction_ts or another partition source column.
• Run OPTIMIZE after each month's bordereaux and resubmissions are loaded. Too many delete files means you pay to scan rows that were already removed.
• Don't over-partition. Claims volumes in DA are modest, so month() is usually right. day() would create tiny files.
• Use Athena workgroups with per-query data scan limits. One unfiltered SELECT * on years of claims history is an expensive lesson.

Where this is heading

Iceberg has become the format the major vendors agree on. Snowflake, Databricks (after acquiring Tabular), Google, and now AWS with S3 Tables all support it. For a DA team on AWS, Athena plus Iceberg on S3 gives you a serverless claims and premium store with warehouse-style SQL and no cluster to run. I'll write more once I've had a chance to try S3 Tables.