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 lake.claims (
 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,
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://my-data-bucket/iceberg/claims/'
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: INSERT INTO lake.claims
 2: SELECT claim_ref, umr, section_no, coverholder_id, certificate_ref,
 3:        CAST(bordereau_month AS DATE),
 4:        CAST(date_of_loss AS DATE),
 5:        UPPER(claim_status),
 6:        claimant_name,
 7:        CAST(paid_this_month     AS DECIMAL(18,2)),
 8:        CAST(outstanding_reserve AS DECIMAL(18,2)),
 9:        CAST(fees_paid           AS DECIMAL(18,2)),
10:        original_ccy,
11:        CAST(transaction_ts AS TIMESTAMP)
12: FROM   raw.claims_csv
13: WHERE  bordereau_month = '2024-11-01';

Row-level changes

1: UPDATE lake.claims
2: SET    claim_status = 'CLOSED', outstanding_reserve = 0
3: WHERE  claim_ref = 'CLM-2024-004512'
4:   AND  bordereau_month = DATE '2024-11-01';
5: 
6: UPDATE lake.claims
7: SET    claimant_name = NULL
8: 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 lake.claims t
 2: USING raw.claims_updates 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: SELECT umr, section_no,
 2:        SUM(paid_this_month)     AS paid,
 3:        SUM(outstanding_reserve) AS outstanding
 4: FROM   lake.claims
 5: FOR TIMESTAMP AS OF TIMESTAMP '2024-12-06 18:00:00 UTC'
 6: WHERE  bordereau_month = DATE '2024-11-01'
 7: GROUP  BY umr, section_no;
 8: 
 9: SELECT * FROM lake.claims FOR VERSION AS OF 4348509271362591234;
10: 
11: SELECT * FROM "lake"."claims$snapshots" ORDER BY committed_at DESC;
12: 
13: SELECT * FROM "lake"."claims$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: OPTIMIZE lake.claims REWRITE DATA USING BIN_PACK
2: WHERE transaction_ts >= TIMESTAMP '2024-11-01 00:00:00';
3: 
4: VACUUM lake.claims;

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 lake.claims SET TBLPROPERTIES (
2:   'vacuum_max_snapshot_age_seconds' = '3456000'
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 lake.claims ADD COLUMNS (cause_of_loss STRING);
2: ALTER TABLE lake.claims
3:   CHANGE COLUMN outstanding_reserve outstanding_reserve DECIMAL(20,2);

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.