Generador de upsert

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

Nombra tu tabla, clave y columnas y obtén la sentencia que Azure Data Lake Storage 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 Azure Data Lake Storage 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 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 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

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.

El mismo generador para otros destinos

Sáltate escribir el merge

Datrise aterriza entidades de CRM y SaaS en Azure Data Lake Storage 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