Upsert generator

Upsert into Amazon Athena: MERGE INTO (Iceberg table, engine v3)

Name your table, key and columns and get the statement Amazon Athena actually accepts, with a guard so an older row never overwrites a newer one. Below it: how the mechanic works, what breaks, and how Datrise loads Amazon Athena incrementally.

Generate the statement

Statement · MERGE INTO (Iceberg table, engine v3)

-- Target must be an Iceberg table (TBLPROPERTIES ('table_type'='ICEBERG')).
MERGE INTO deals AS t
USING (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rn
    FROM deals_staging
  ) WHERE rn = 1
) AS s
ON t.id = s.id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET
  name = s.name,
  stage = s.stage,
  amount = s.amount,
  owner_id = s.owner_id,
  updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (id, name, stage, amount, owner_id, updated_at)
  VALUES (s.id, s.name, s.stage, s.amount, s.owner_id, s.updated_at)

How the upsert works in Amazon Athena

Athena can MERGE, but only into Apache Iceberg tables on engine version 3. Classic Hive-style tables over Parquet are append-only: INSERT INTO adds files and nothing edits a row. So the first decision is the table format. With Iceberg, the generated MERGE INTO joins a deduped staging select to the target and updates or inserts per key; with Hive tables, append a partition and dedupe at read time instead.

Athena bills per byte scanned, and a MERGE scans the target to find matches, so a partition predicate in the ON clause matters as much as it does in BigQuery. Every merge writes new small files; schedule OPTIMIZE … REWRITE DATA USING BIN_PACK to compact them and VACUUM to expire old snapshots, or scan cost creeps up batch by batch.

Before you run it

Plain Hive/Parquet tables in Athena are append-only: MERGE, UPDATE and DELETE exist only for Iceberg tables on engine version 3. Convert with CREATE TABLE … TBLPROPERTIES ('table_type'='ICEBERG') AS SELECT.
Athena bills per byte scanned, so put a partition predicate on the target inside the ON clause; otherwise every MERGE reads the whole table.
Merges create small files. Schedule OPTIMIZE table REWRITE DATA USING BIN_PACK and VACUUM to compact and expire old snapshots.

Questions people ask

Why does Athena reject my MERGE?

The target is not an Iceberg table, or the workgroup runs engine version 2. Create the table with TBLPROPERTIES ('table_type' = 'ICEBERG') and switch the workgroup to engine version 3.

Can I convert an existing Parquet table to Iceberg?

Yes: CREATE TABLE new_table WITH (table_type = 'ICEBERG', location = '…') AS SELECT * FROM old_table, then point consumers at the new name.

How often should I compact?

After every few merges on a busy table. OPTIMIZE rewrites small files into larger ones, VACUUM removes snapshots older than the retention you set; both are Athena SQL statements you can schedule.

The same generator for other destinations

Skip writing the merge at all

Datrise lands CRM and SaaS entities into Amazon Athena with this exact mechanic, a watermark on updated-at, and typed columns, so the statement above is what runs on your behalf. Join the waitlist to get early access.

Browse the integration catalog