Gerador de upsert

Upsert no Azure Data Lake Storage: append partition + latest-version view

Nomeie tabela, chave e colunas e receba a instrução que o Azure Data Lake Storage 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 Azure Data Lake Storage 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 Azure Data Lake Storage

ADLS Gen2 behaves like S3 for this purpose: files are immutable, so an upsert is an appended partition plus a view that keeps the latest row per key. The hierarchical namespace makes the folder layout matter more than on S3; a predictable entity/load_date path is what Synapse serverless (OPENROWSET), Databricks and Fabric mount directly.

The generated view is the read-time dedupe. If most of your reads come from Databricks, convert the folder to a Delta table and use the Delta MERGE instead; if they come from Synapse dedicated pools, COPY the partition into staging and MERGE there. The lake stays the shared storage layer either way, and the view keeps engines that cannot merge honest.

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

Should I use Delta Lake on ADLS instead of plain Parquet?

If Databricks or Synapse Spark is the main reader, yes: Delta gives you MERGE, time travel and compaction. Plain Parquet plus the view is the right choice when many different engines read the same files.

How does Synapse serverless read the current rows?

Through this view over OPENROWSET on the folder. Serverless pools cannot write or merge, so the dedupe has to happen in the query.

Does the hierarchical namespace change anything?

It makes directory operations atomic and cheap, so per-date folders and renames are safe. Keep the layout predictable; engines infer partitions from the path.

O mesmo gerador para outros destinos

Pule a escrita do merge

A Datrise entrega entidades de CRM e SaaS no Azure Data Lake Storage 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