Upsert generator

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

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

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

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.

The same generator for other destinations

Skip writing the merge at all

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