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.
Gerador de upsert
Upsert no Amazon S3 Data Lake: append partition + latest-version view
Nomeie tabela, chave e colunas e receba a instrução que o Amazon S3 Data Lake realmente aceita, com uma guarda para que uma linha antiga nunca sobrescreva uma nova. Abaixo: como a mecânica funciona, o que quebra e como a Datrise carrega o Amazon S3 Data Lake de forma incremental.
As notas técnicas desta página estão em inglês.
Gere a instrução
Instrução · 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;Como o upsert funciona no 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 executar
- 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.
Perguntas frequentes
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.
O mesmo gerador para outros destinos
Pule a escrita do merge
A Datrise entrega entidades de CRM e SaaS no Amazon S3 Data Lake com exatamente esta mecânica, uma marca d'água sobre updated-at e colunas tipadas, então a instrução acima é a que roda por você. Entre na lista de espera para acesso antecipado.
Explorar o catálogo de integrações