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.
Generador de upsert
Upsert en Amazon S3 Data Lake: append partition + latest-version view
Nombra tu tabla, clave y columnas y obtén la sentencia que Amazon S3 Data Lake acepta de verdad, con una guarda para que una fila antigua nunca sobrescriba una nueva. Debajo: cómo funciona la mecánica, qué se rompe y cómo Datrise carga Amazon S3 Data Lake de forma incremental.
Las notas técnicas de esta página están en inglés.
Genera la sentencia
Sentencia · 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;Cómo funciona el upsert en 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.
Antes de ejecutarla
- 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.
Preguntas frecuentes
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.
El mismo generador para otros destinos
Sáltate escribir el merge
Datrise aterriza entidades de CRM y SaaS en Amazon S3 Data Lake con esta misma mecánica, una marca de agua sobre updated-at y columnas tipadas, así que la sentencia de arriba es la que corre por ti. Únete a la lista de espera para acceso anticipado.
Explorar el catálogo de integraciones