Upsert generator

Upsert into Amazon S3 Data Lake: append partition + latest-version view

Name your table, key and columns and get the statement Amazon S3 Data Lake 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 S3 Data Lake incrementally.

Generate the statement

Statement · append partition + latest-version view

-- 1. Write each batch as new Parquet under
--    deals/load_date=YYYY-MM-DD/part-*.parquet (never rewrite old files).

-- 2. Expose "current rows" as a view the engine (Trino, Spark, Athena,
--    Synapse serverless, DuckDB) resolves at read time:
CREATE OR REPLACE VIEW deals_current AS
SELECT id, name, stage, amount, owner_id, updated_at
FROM (
  SELECT id, name, stage, amount, owner_id, updated_at, load_date,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rn
  FROM deals
) WHERE rn = 1;

How the upsert works in Amazon S3 Data Lake

An S3 lake with plain Parquet has no upsert: objects are immutable and no engine edits a row inside one. The honest pattern is append-and-dedupe. Each batch lands as new files under a load_date partition, and a view over the table keeps one row per key with ROW_NUMBER ordered by updated_at. Trino, Spark, Athena, Synapse serverless and DuckDB all evaluate that view at read time, so the lake stays engine-neutral.

The generated view uses the portable subquery form rather than QUALIFY so it runs on all of them. Compact small files on a schedule, keep a schema manifest beside the data because nothing enforces column types, and when the read-time dedupe gets too expensive, move the table to an open format (Iceberg, Delta or Hudi) that provides a real MERGE over the same objects.

Before you run it

Object storage cannot update a row in place. The honest options are append-and-dedupe (this view) or an open table format (Iceberg, Delta, Hudi) that gives you a real MERGE on top of the same files.
Keep load_date in the path as a Hive partition so engines prune old batches, and compact many small files into few large ones on a schedule; small files dominate scan cost.
Write a schema manifest next to the data. A lake enforces nothing, so a renamed source field otherwise appears as a second column downstream.

Questions people ask

Can I upsert into plain Parquet on S3?

No. You append new files and resolve the latest version at read time, or you adopt Iceberg, Delta or Hudi, which track row-level changes in metadata and give you MERGE.

How do I delete a record from the lake?

Append a tombstone row (is_deleted = 1, newer updated_at) and filter it out in the current view. Physical removal requires rewriting the file, which table formats automate.

What partition layout should I use?

entity/load_date=YYYY-MM-DD/ in Hive style. Engines prune on it, incremental readers can pick up only new dates, and compaction can run per partition.

The same generator for other destinations

Skip writing the merge at all

Datrise lands CRM and SaaS entities into Amazon S3 Data Lake 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