Guide · AWS Athena · Apache Iceberg · MCP Tools

Athena Iceberg Tables for MCP Data Tools

Apache Iceberg tables in Athena give MCP data tools transactional writes, time-travel queries, schema evolution without downtime, and row-level deletes — all on S3. Unlike Hive-style external tables (which are effectively immutable append-only Parquet partitions), Iceberg tables maintain a metadata layer (manifests, snapshots) that tracks every change as an atomic commit. For MCP servers, this means: a load_data tool can write new rows without breaking concurrent query_data tool reads; a correct_record tool can issue a targeted UPDATE or DELETE without rewriting entire partitions; and a get_snapshot tool can time-travel to any point in the table's history for auditing. Three production patterns cover most MCP use cases: Iceberg table DDL and writes (CREATE, INSERT, UPDATE, DELETE, MERGE); time-travel queries for audit and reproducibility; and table maintenance (OPTIMIZE compaction + VACUUM) to prevent small-file performance degradation.

TL;DR

Create Iceberg tables with TBLPROPERTIES ('table_type'='ICEBERG') in Athena DDL. Use FOR TIMESTAMP AS OF or FOR VERSION AS OF for time-travel. Run ALTER TABLE ... ADD COLUMNS for non-breaking schema changes. Issue OPTIMIZE table REWRITE DATA periodically to compact small files. Run VACUUM table to remove expired snapshots and orphan files. For upserts, use MERGE INTO — much cheaper than rewriting partitions.

Creating and writing Iceberg tables from MCP tools

Iceberg tables are created with a TBLPROPERTIES clause. All subsequent DML operations (INSERT, UPDATE, DELETE, MERGE) are handled by Athena engine version 3+:

import boto3
import time

athena = boto3.client("athena", region_name="us-east-1")

def run_ddl(sql: str, workgroup: str = "mcp-etl-jobs") -> str:
    """Run Athena DDL or DML; return query execution ID."""
    resp = athena.start_query_execution(
        QueryString=sql,
        WorkGroup=workgroup,
    )
    qid = resp["QueryExecutionId"]
    while True:
        exec_resp = athena.get_query_execution(QueryExecutionId=qid)
        state = exec_resp["QueryExecution"]["Status"]["State"]
        if state == "SUCCEEDED":
            return qid
        elif state in ("FAILED", "CANCELLED"):
            raise RuntimeError(
                f"DDL failed: {exec_resp['QueryExecution']['Status'].get('StateChangeReason')}"
            )
        time.sleep(2)

# Create an Iceberg table — table_type='ICEBERG' triggers Iceberg metadata management
run_ddl("""
CREATE TABLE analytics.mcp_events (
    event_id       STRING,
    session_id     STRING,
    tool_name      STRING,
    user_id        STRING,
    event_ts       TIMESTAMP,
    payload        STRING,
    status         STRING,
    latency_ms     BIGINT
)
LOCATION 's3://my-datalake/iceberg/mcp_events/'
TBLPROPERTIES (
    'table_type'     = 'ICEBERG',
    'format'         = 'parquet',
    'write_compression' = 'snappy',
    'optimize_rewrite_delete_file_threshold' = '10'
)
""")

# Insert rows — Iceberg writes a new snapshot atomically
run_ddl("""
INSERT INTO analytics.mcp_events
VALUES (
    'evt-001', 'sess-abc', 'query_data', 'user-123',
    TIMESTAMP '2026-10-01 12:00:00', '{"query":"..."}', 'success', 150
)
""")

# Row-level update — Iceberg rewrites only the affected data files
run_ddl("""
UPDATE analytics.mcp_events
SET status = 'retried', latency_ms = 320
WHERE event_id = 'evt-001'
""")

# Row-level delete — no partition rewrite required
run_ddl("""
DELETE FROM analytics.mcp_events
WHERE user_id = 'user-123' AND event_ts < TIMESTAMP '2026-09-01 00:00:00'
""")

Athena Iceberg DML operations are serializable within a single engine. However, Athena does not enforce distributed concurrency across multiple simultaneous writers (e.g., two MCP server instances writing to the same Iceberg table at the same time). Concurrent INSERT operations on different non-overlapping partitions are safe; concurrent UPDATE/DELETE operations on the same row may produce optimistic-concurrency failures. Design MCP tool architectures so that only one writer path updates a given partition at a time.

Time-travel queries for audit and reproducibility

Iceberg snapshots let MCP audit tools query the table as it existed at any past point in time:

# Time-travel by timestamp — query data as of a specific point in time
run_ddl("""
SELECT * FROM analytics.mcp_events
FOR TIMESTAMP AS OF TIMESTAMP '2026-09-15 00:00:00'
WHERE tool_name = 'query_data'
""")

# Time-travel by snapshot ID — deterministic, immune to clock skew
# First: list available snapshots
rows_snapshots = run_query_and_fetch("""
SELECT snapshot_id, committed_at, operation, summary
FROM "analytics"."mcp_events$snapshots"
ORDER BY committed_at DESC
LIMIT 20
""")
# snapshot_id values look like: 5789234567890123456

# Then query by snapshot ID for exact reproducibility
snapshot_id = "5789234567890123456"
run_ddl(f"""
SELECT count(*) AS event_count, tool_name
FROM analytics.mcp_events
FOR VERSION AS OF {snapshot_id}
GROUP BY tool_name
""")

# List all Iceberg metadata tables for a table
# These are virtual tables accessible via the "$" suffix notation
# $snapshots, $history, $manifests, $partitions, $files
rows_history = run_query_and_fetch("""
SELECT * FROM "analytics"."mcp_events$history"
ORDER BY made_current_at DESC
LIMIT 10
""")

# $history shows snapshot transitions — useful for debugging UPDATE/DELETE operations
# Each row: snapshot_id, parent_id, is_current_ancestor, made_current_at

MCP audit tools that need to satisfy GDPR "right to be forgotten" requests can use Iceberg's DELETE + VACUUM workflow: issue a targeted DELETE FROM for the user's rows, then run VACUUM to expire old snapshots that still contain the deleted data. After VACUUM, the deleted rows are no longer accessible via time-travel — satisfying the erasure requirement without migrating the entire table to a new location.

Schema evolution without downtime

Iceberg tracks schema versions in metadata, so adding or renaming columns does not require rewriting data files:

# Add a new column — existing Parquet files return NULL for the new column
run_ddl("""
ALTER TABLE analytics.mcp_events
ADD COLUMNS (
    error_code  STRING,
    retry_count INT
)
""")

# Rename a column — Iceberg maps old column ID to new name in metadata
# Existing files are NOT rewritten
run_ddl("""
ALTER TABLE analytics.mcp_events
RENAME COLUMN payload TO tool_payload
""")

# Drop a column — data remains in Parquet files but is excluded from schema projection
run_ddl("""
ALTER TABLE analytics.mcp_events
DROP COLUMN latency_ms
""")

# Change column type (limited: widening only — INT → BIGINT, FLOAT → DOUBLE)
run_ddl("""
ALTER TABLE analytics.mcp_events
CHANGE COLUMN retry_count retry_count BIGINT
""")

# Evolve partitioning — add a new partition transform without rewriting existing data
# New data written after this DDL uses the new partition scheme;
# old data retains the old scheme — Athena reads both transparently
run_ddl("""
ALTER TABLE analytics.mcp_events
ADD PARTITION FIELD day(event_ts)
""")

Iceberg's hidden partitioning means partition columns are never stored in the data files as explicit columns — they're derived from data column values using transform functions (day, month, bucket, truncate). MCP tool SQL never needs to include partition filters explicitly — Iceberg's metadata pruning applies partition elimination automatically based on WHERE clause predicates on the source column (event_ts).

MERGE INTO for efficient upserts from MCP tools

MCP tools that need to synchronize rows from a source (e.g., replicate changes from an operational database) use MERGE INTO instead of DELETE+INSERT:

# MERGE INTO: update existing rows, insert new ones, delete removed ones
# Source data is a staging table or inline VALUES clause
run_ddl("""
MERGE INTO analytics.mcp_events AS target
USING (
    SELECT * FROM analytics.mcp_events_staging
) AS source
ON target.event_id = source.event_id
WHEN MATCHED AND source.status = 'deleted' THEN
    DELETE
WHEN MATCHED THEN
    UPDATE SET
        status        = source.status,
        latency_ms    = source.latency_ms,
        tool_payload  = source.tool_payload
WHEN NOT MATCHED THEN
    INSERT (event_id, session_id, tool_name, user_id, event_ts, tool_payload, status, latency_ms)
    VALUES (source.event_id, source.session_id, source.tool_name, source.user_id,
            source.event_ts, source.tool_payload, source.status, source.latency_ms)
""")

# OPTIMIZE: compact small files into larger Parquet files
# Run after periods of heavy INSERT/UPDATE/DELETE activity
# Iceberg tracks which files were rewritten — old files become orphans until VACUUM
run_ddl("""
OPTIMIZE analytics.mcp_events
REWRITE DATA USING BIN_PACK
WHERE event_ts >= TIMESTAMP '2026-10-01 00:00:00'
""")

# VACUUM: remove expired snapshots and orphan files
# Retention period must be >= query SLA (time-travel depth you need)
run_ddl("""
VACUUM analytics.mcp_events
RETAIN 7 DAYS
""")

After OPTIMIZE, the number of data files per partition drops dramatically — a table written by many small concurrent INSERT operations may have thousands of 1 MB files per partition. After compaction, these merge into a few 128 MB files, reducing the number of S3 GET requests per Athena query scan and cutting both latency and cost. Schedule OPTIMIZE as a periodic MCP tool operation or as a Glue workflow step.

Monitor Iceberg-backed MCP data tools with AliveMCP

Iceberg table maintenance failures (compaction job crashes, VACUUM errors, manifest corruption) can silently degrade query performance until tools start timing out. AliveMCP monitors every MCP endpoint every 60 seconds, alerting you before your users notice degraded query responses.

Join the waitlist →