MERGE INTO is often the most readable way to express keyed changes against an Apache Iceberg table, but reliable upserts start upstream of the statement. You must deduplicate source rows to one action per key, declare late-arrival handling, and make your job idempotent and replayable. For SCD Type 2 you must close the current version and insert a new version together, then plan for compaction of the many small delete and data files that row-level history generates. This article gives SQL patterns, operational rules, worked examples, and a rollout checklist you can use in production.
Apache Iceberg is a table format that stores immutable data files and metadata files describing snapshots. Its semantics are defined in the Iceberg specification, and the writer behavior varies by engine and connector. The spec documents manifest lists and snapshot isolation. The implementation doc for Spark writes explains how writers create files and commit snapshots. Because Iceberg writes immutable files, MERGE into an Iceberg table resolves to writes that add new files and snapshots, not in-place row updates. That design gives you atomic snapshot semantics, but it also produces extra files that need lifecycle maintenance for production workloads.
Two operational consequences follow. First, different query engines implement MERGE syntax and optimizer behavior differently. Test the exact MERGE syntax and the optimizer’s assumptions for the engine and connector you use before you rely on performance or conflict resolution semantics. Second, row-level changes increase later maintenance, such as snapshot expiration, file cleanup, and compaction. Plan and measure for that, do not assume it is free.
These rules address the required technical cautions. MERGE syntax and optimizer behavior vary by engine; duplicate source keys cause ambiguous results; and row-level changes create ongoing maintenance work.
An upsert is a decision by key to insert, update, or delete. Think of the decision as a simple state machine: if the target key exists and the incoming row indicates update, apply change; if not exists and the incoming row indicates create, insert. Include decisions for tombstones and late-arriving events. The following diagram shows the per-key decision points you need to encode in your SQL and tests.
Below is a practical approach that makes a MERGE idempotent and deterministic. The pattern expects you to prepare a deduplicated staging table with one row per business key, and a batch_id so the same batch can be replayed without double applying. This example uses Spark SQL style MERGE; verify syntax and optimizer behavior for your engine first, because implementations vary.
<!-- wp:code -->
MERGE INTO catalog.db.dim_customers t
USING (
SELECT key, value, batch_id
FROM staging.batch_data
WHERE batch_id = 20260920
-- deduplicate: keep the last event per key by event_time or sequence
QUALIFY ROW_NUMBER() OVER (PARTITION BY key ORDER BY event_time DESC, seq DESC) = 1
) s
ON t.key = s.key
WHEN MATCHED AND s.value IS NULL THEN
DELETE
WHEN MATCHED AND t.value != s.value THEN
UPDATE SET t.value = s.value, t.batch_id = s.batch_id
WHEN NOT MATCHED AND s.value IS NOT NULL THEN
INSERT (key, value, batch_id) VALUES (s.key, s.value, s.batch_id);
<!-- /wp:code -->Notes you must verify for your environment:
SCD Type 2 maintains full history for dimensional entities by closing the current active row and inserting a new version. In Iceberg that means you will write a delete of the current file rows and an insert of new file rows that include the new version. Because Iceberg appends files and creates snapshots, you must update the validity columns you use, such as effective_from, effective_to, and current_flag, in the same transactional operation so readers never see partial state.
The two-step transactional pattern below implements a clean SCD Type 2 change. It does two things: mark the prior row as closed, and write a new active row. Depending on your engine you can express both actions in a single MERGE or as two statements inside a transactional job. Confirm whether your engine supports multi-statement transactional commit to Iceberg the way you expect; behavior varies. The Iceberg spec documents snapshot semantics and atomic commit behavior you should consult when in doubt.
<!-- wp:code -->
-- Step 1: expire current active row(s) for keys that changed
MERGE INTO catalog.db.dim_customers t
USING (
SELECT key, new_value, batch_id
FROM staging.customer_changes
QUALIFY ROW_NUMBER() OVER (PARTITION BY key ORDER BY event_time DESC) = 1
) s
ON t.key = s.key AND t.current_flag = true
WHEN MATCHED AND t.value != s.new_value THEN
UPDATE SET t.current_flag = false, t.effective_to = s.batch_id
; -- commit snapshot
-- Step 2: insert the new active rows
INSERT INTO catalog.db.dim_customers (key, value, effective_from, effective_to, current_flag, batch_id)
SELECT key, new_value, batch_id, NULL, true, batch_id
FROM staging.customer_changes
QUALIFY ROW_NUMBER() OVER (PARTITION BY key ORDER BY event_time DESC) = 1;
<!-- /wp:code -->Why two steps? Some engines let you publish both the close and insert in a single MERGE. In others, executing the two statements as a single job that commits only when both steps succeed is safer. If you cannot run both statements in a single atomic commit, run them sequentially but implement idempotence checks and an operation marker so failure between steps can be detected and corrected.
Late-arriving events are common. Define a policy: accept-and-rewrite history, accept-as-new-version, or quarantine for manual review. Each has trade-offs for upstream consumers, downstream analytics, and storage cost.
Quarantine is the most conservative option. It avoids accidental rewrites of history. Below is a quarantine query pattern that selects suspect rows for review. It uses a tolerance window, here 7 days, and isolates rows where the event_time is older than the table’s latest applied watermark. The exact watermark source depends on your pipeline design.
<!-- wp:code -->
SELECT s.*
FROM staging.incoming_events s
LEFT JOIN (
SELECT key, MAX(event_time) AS last_seen
FROM catalog.db.fact_events
GROUP BY key
) t ON s.key = t.key
WHERE s.event_time < coalesce(t.last_seen, TIMESTAMP '1970-01-01') - INTERVAL '7' DAY
OR (s.event_time < current_timestamp - INTERVAL '30' DAY AND s.event_time < s.source_ingest_time - INTERVAL '1' DAY)
-- write these offending rows to a quarantine table for human review
;
<!-- /wp:code -->When you quarantine, record why the row was quarantined and the batch_id that attempted to apply it. That makes remediation and backfill deterministic. If you accept-and-rewrite history, document that downstream SLAs may be violated and ensure your compute can handle the reprocessing load.
Every MERGE that updates a small number of rows frequently creates new small data files and new delete files in Iceberg. Over time small files increase scan overhead and metadata size. The Iceberg maintenance docs recommend planning compaction strategies and snapshot expiration to manage this. Left unchecked, write amplification will slow queries and increase storage costs.
Two levers reduce the problem: write-side batching and scheduled compaction. Batch multiple upserts into a single job so the writer can combine updates. Then run a compaction job that rewrites small files into larger ones and coalesces deletes. Use Iceberg’s rewrite-manifest and rewrite-data utilities where available. Also expire old snapshots on a cadence that preserves your recovery window but removes unnecessary metadata growth. For a deeper practical walkthrough of snapshot expiration, see Dremio’s guide on snapshot expiration which explains the timing and safety concerns for expiring old snapshots in Iceberg.
Follow this sequence when you implement upserts or SCD Type 2 on Iceberg. It is the sequence I use when operating production systems.
During evaluation, capture these operational metrics so you can reason about cost and performance.
These are the failure modes I have seen, with detection and remediation steps.
Measure these after deployment so you can iterate on performance and cost.
Three caveats deserve emphasis. First, MERGE syntax and optimizer behavior vary by engine and connector. The Spark write docs for Iceberg describe writer behavior for that engine, but your connector may behave differently. Second, if duplicate keys exist in the source, MERGE semantics are ambiguous unless deduplication or deterministic ordering is applied. Third, row-level changes create maintenance work; Iceberg itself will not automatically compact or expire snapshots without your explicit jobs and configuration. Read the Iceberg maintenance documentation for the utilities and recommended procedures you should use.
Labelled facts checked on September 22, 2026: the Iceberg specification, the Spark writer documentation, and the maintenance documentation provide the behaviors and recommended maintenance operations referenced in this article. Confirm engine-specific MERGE support and transactional commit semantics in your engine’s connector documentation before you assume identical behavior.
Dremio has several guides that help operating Iceberg in production. The snapshot expiration guide explains how long you can safely keep snapshots and how expiration reduces metadata and storage overhead. The orphan file cleanup article describes patterns and tooling for cleaning up files that are no longer reachable by any snapshot. If you need to introspect metadata and manifests, the guide to querying metadata tables walks through how to inspect manifests, data files, and partition-level statistics. Finally, Dremio’s platform page about Apache Iceberg describes Dremio’s capabilities for querying and managing Iceberg tables. Read these pages for operational detail and examples you can use with the patterns in this article.
Links used above for operational context:
This section walks through an implementation plan you can run in a staging environment, using the three required examples: an idempotent upsert MERGE, the SCD Type 2 two-step transaction, and the late-arrival quarantine query. The goal is reproducible steps you can copy into Spark or the SQL engine you run with Iceberg. Verify syntax against your engine, because MERGE syntax and optimizer behavior vary by engine and version. The commands below are illustrative and must be adapted to your SQL dialect and client shell.
Before you run any write, confirm these layout checks against your Iceberg table and catalog. These checks are minimal, practical verification steps.
Now, a compacted working dataset so tests run predictably. If you have a compact command available via your engine, run a maintenance compaction so data files are fewer and larger. Record snapshot id before and after compaction. This controls the baseline for change metrics and failure injection.
Idempotence here means repeated application of the same source batch does not produce duplicates, nor does it leave the table in an inconsistent state. That requires deterministic matching criteria and deterministic update expressions. Use a guaranteed unique source key per batch, or deduplicate the batch before MERGE.
Script outline, in three steps. Replace keywords with your engine SQL when required.
<!--
-- Step 0: prepare a deduplicated staging dataset
CREATE OR REPLACE TEMP VIEW staging_batch AS
SELECT src_key, col1, col2, event_ts
FROM ( -- deduplicate by keeping latest event_ts per key
SELECT *, ROW_NUMBER() OVER (PARTITION BY src_key ORDER BY event_ts DESC) rn
FROM raw_input_batch
) t
WHERE rn = 1;
-- Step 1: record the current snapshot id and manifest counts
-- Use your engine catalog commands to show snapshot id and file count
-- Step 2: MERGE from staging_batch into target_table
MERGE INTO target_table AS tgt
USING staging_batch AS src
ON tgt.src_key = src.src_key
WHEN MATCHED AND src.col1 IS NOT NULL THEN
UPDATE SET col1 = src.col1, col2 = src.col2, last_updated = src.event_ts
WHEN NOT MATCHED THEN
INSERT (src_key, col1, col2, created_ts, last_updated)
VALUES (src.src_key, src.col1, src.col2, src.event_ts, src.event_ts);
-- Step 3: validate idempotence by re-running the same MERGE and comparing snapshot differences
-- Query row counts, or compute checksums on (src_key, col1, col2, last_updated)
-- Optionally, compare manifest file lists between snapshots
-- -->Operational notes. Deduplicate upstream. If your engine does not guarantee deterministic evaluation for MATCHED clauses when multiple target rows match, ensure the target side has unique rows per key at the snapshot you are operating on. Re-running the MERGE should produce no net row-count change for the same input batch. If you see extra files or unexpected rows, inspect the manifest and snapshot history to find whether the engine appended new files rather than rewriting the few expected files.
The two-step SCD Type 2 approach is an intentionally explicit pattern: first close existing current rows for keys that will change, second insert new current rows. That avoids attempting a single MERGE that both updates and inserts active flags, which can leave ambiguous transient states depending on engine isolation and statement planning. This pattern assumes your table stores a validity range with columns like record_id, business_key, valid_from, valid_to, is_current.
<!--
-- Step A: identify keys to expire and create an expire set
CREATE OR REPLACE TEMP VIEW keys_to_expire AS
SELECT src_key, max(event_ts) as batch_ts
FROM raw_input_batch
GROUP BY src_key;
-- Step B: expire current rows for those keys
MERGE INTO scd_table AS tgt
USING keys_to_expire AS src
ON tgt.business_key = src.src_key AND tgt.is_current = true
WHEN MATCHED AND tgt.valid_from <= src.batch_ts THEN
UPDATE SET valid_to = src.batch_ts, is_current = false;
-- Step C: insert new current rows from deduplicated batch
INSERT INTO scd_table (record_id, business_key, attrs..., valid_from, valid_to, is_current)
SELECT uuid(), src_key, col1, src.batch_ts, NULL, true
FROM (
SELECT src_key, col1, ROW_NUMBER() OVER (PARTITION BY src_key ORDER BY event_ts DESC) rn
FROM raw_input_batch
) t
WHERE rn = 1;
-- Verify: check that for each business_key there is exactly one row with is_current = true
-- -->Notes on atomicity and phenomena to expect. When you run the expire MERGE, you will produce a snapshot that marks rows as non-current. That snapshot may be visible to concurrent readers depending on your engine isolation. The insert step creates a subsequent snapshot. Between those snapshots there is a window where queries that combine state across snapshots in a single read may see expired rows or the new rows depending on snapshot selection. If you need exact point-in-time consistency for downstream consumers, coordinate snapshot id pinning or use a read isolation mechanism your engine offers.
Late-arrival events are a perennial problem for SCD and MERGE. The quarantine query isolates rows outside the ingestion window so you can surface them for human review or automated routing. The quarantine set should be small and monitored. The query below finds events with event_ts earlier than the latest seen for that business key, which indicates an out-of-order update that would otherwise rollback current row timestamps.
<!--
-- Build a quarantine view for late events relative to the current table state
CREATE OR REPLACE TEMP VIEW quarantine AS
SELECT b.*
FROM raw_input_batch b
LEFT JOIN (
SELECT business_key, max(valid_from) AS latest_from
FROM scd_table
WHERE is_current = true
GROUP BY business_key
) t
ON b.src_key = t.business_key
WHERE t.latest_from IS NOT NULL
AND b.event_ts < t.latest_from;
-- Take action: inspect, route to a manual review table, or apply a reconciliation MERGE
INSERT INTO late_event_review SELECT * FROM quarantine;
-- Optionally, build a reconciler that can either TTL these events, flag them, or apply backfills
-- -->Operational choices. Decide a retention window for quarantine rows. Small window sizes reduce manual burden. If a late event represents a correction that must be applied, document the canonical backfill procedure. Do not automatically reapply late events into the main MERGE without human or automated checks that verify their validity. Automatic application risks corrupting SCD histories and inflating write amplification.
Run failure injection tests in staging before deploying to production. The tests below exercise partial commits, snapshot conflicts, and file churn. The goal is to see and interpret what Iceberg snapshot history and manifest changes look like under failure, so you can build monitoring and automated recovery steps.
Test harness prerequisites. Use a known compacted baseline snapshot. Capture the starting snapshot id and the active manifest list. Run tests with realistic batch sizes, for example 100k to 1M rows, to surface file churn behavior.
What to inspect after each injected failure.
Recovery steps to test. Practice the following in staging and document the exact commands for production operators. The exact commands depend on your engine and catalog, so verify them against your environment.
This table replaces vague advice with a short decision flow. Answer these questions in order to pick whether you use single-statement MERGE, two-step SCD transactions, or staged quarantines. The answers depend on update frequency, acceptable read windows for downstream consumers, and your tolerance for maintenance work.
Maintenance cadence guidance. These are operating suggestions to convert the decision outputs into a practical schedule. Verify all timing against observed metrics and Iceberg maintenance behavior documented for the version you run.
Decision consequences. If you pick single MERGE for simplicity, expect more file rewrites and the need for more aggressive compaction. If you pick two-step SCD, expect additional snapshots and a small temporal window that requires attention for strict-consistency readers. Quarantine reduces accidental corruption of history, but increases manual operational load to triage late events.
You already have a section listing what to measure. This expands that with concrete query examples, targets to aim for, and how to interpret deviations. These checks are practical monitoring probes you should automate in your observability system. Where freshness matters, these facts were checked on September 22, 2026 against the Iceberg maintenance and spec pages linked in Sources.
Metric 1, snapshot commit rate and snapshot size. Query the table history view or catalog to get commits per hour and rows affected per commit. Target: for a table with steady traffic, snapshot commits per hour should match your batch cadence. Unexpected bursts indicate noisy jobs or retries causing write amplification. A good anomaly threshold is 3x the moving average snapshot rate over a 1 hour window.
<!--
-- Example: count snapshots in last 24 hours
SELECT count(*) FROM table_history_view
WHERE committed_at >= current_timestamp - interval '24' hour;
-- -->Metric 2, average data file size and distribution. Use the manifest file lists to compute avg, median, and 90th percentile file size. Target: average file size above your storage provider practical threshold, often 100 MB or higher for cloud object storage. If average is under 50 MB, increase compaction frequency.
<!--
-- Example: aggregate file sizes from manifest entries
SELECT
avg(file_size) as avg_size,
approx_percentile(file_size, 0.5) as median_size,
approx_percentile(file_size, 0.9) as p90_size
FROM manifest_files_view
WHERE snapshot_id IN (recent_snapshots);
-- -->Metric 3, duplicate business key count in current rows. Run a periodic dedupe check. Target: zero duplicates for current flags. If duplicates appear, raise an alert and run the corrective MERGE workflow.
<!--
-- Example: detect duplicate current rows
SELECT business_key, count(*) as cnt
FROM scd_table
WHERE is_current = true
GROUP BY business_key
HAVING cnt > 1
LIMIT 10;
-- -->Metric 4, quarantine growth rate. Count rows entering quarantine per hour. Target: low and stable rates. A spike means upstream ordering or producer problems. Set an alert threshold at, for example, 5x the historical moving average.
<!--
-- Example: quarantine rate
SELECT count(*) FROM late_event_review
WHERE ingested_at >= current_timestamp - interval '1' hour;
-- -->Metric 5, MERGE latency and task failures. Measure wall-clock time of MERGE statements and the number of worker task failures. Target MERGE latency should match your SLA. If latency doubles after an engine upgrade, capture execution plans and file statistics immediately and revert to a safe version if necessary.
How to interpret deviations. High snapshot rate plus low avg file size equals write amplification. High quarantine rate signals ordering issues. Duplicate current rows indicates either broken key uniqueness or concurrent write conflict behavior you must address. Use snapshot history and manifest diffs to determine whether excessive small files are from many small commits or a single large rewrite producing many temp files.
Suggested automated alerts to implement.
When you get an alert, follow a short incident checklist: pin snapshot id for critical consumers if possible, examine snapshot history for the last 3 commits, run the duplicate key query and the manifest file size aggregation, and if necessary, run a corrective compaction or rollback after a well-documented postmortem decision.
These operational probes and alert thresholds are intentionally conservative. Tune them to your workload. Always verify the maintenance and write semantics against the Iceberg documentation for the version you run, because maintenance behavior and optimization heuristics have changed across releases as noted in the linked spec and maintenance pages in Sources.
Check the MERGE explain plan immediately. Some optimizer changes alter join or update strategies. If the plan changed, either pin the previous connector version until you tune the query or rewrite the operation as write-then-swap for a partitioned subset. Re-run the test suite that measures MERGE latency during upgrades.
Deduplicate in staging, using ROW_NUMBER with a deterministic ordering key like event_time and sequence. If your engine lacks QUALIFY, write a separate dedupe query that produces a staging table with one row per key per batch. Always include an ordering column to break ties.
Some engines support expressing the close-and-insert in one statement, but behavior depends on your engine and its ICEBERG connector. Test atomicity and snapshot output. If in doubt, run the close and insert as steps inside a job that performs its own atomic commit validation and remediation on failure.
It depends on your write pattern. Measure file churn and small-file percentage. If small files make up more than 10 to 20 percent of reads or if file counts grow quickly, run daily or hourly compaction for hot partitions and weekly for cold data. Use your test workload to pick thresholds.
Track end-to-end latency from ingest to visibility, MERGE latency percentiles, and the failure rate for replayed batches. Also monitor file churn and snapshot growth, because they affect query latency indirectly.
Refer to the Iceberg project documentation. The Spark writes document explains how Spark writes behave with Iceberg. The Iceberg specification states snapshot, manifest, and atomic commit semantics. The maintenance docs provide recommended utilities for compaction, rewriting manifests, and snapshot expiration. Check those pages for implementation details and recommended commands for your environment.
For a deeper treatment, download Apache Iceberg: The Definitive Guide, co-authored by Alex Merced and available free from Dremio.
To put the catalog and table-format ideas into practice, explore Dremio's Apache Iceberg platform.
Deploy agentic analytics directly on Apache Iceberg data with no pipelines and no added overhead.
Editor’s note, September 2026. This post was published in September 2023 and some product details have changed since. Nessie is still available and self-deployable under the Apache-2.0 licence, and it remains the clearest implementation of catalog-level branching. It is not an Apache Software Foundation project, and its development has slowed considerably. For how the current […]
With its capabilities in on-prem to cloud migration, data warehouse offload, data virtualization, upgrading data lakes and lakehouses, and building customer-facing analytics applications, Dremio provides the tools and functionalities to streamline operations and unlock the full potential of data assets.
This is Part 15, the final article of a 15-part Apache Iceberg Masterclass. Part 14 covered hands-on Dremio Cloud. This article covers the three migration strategies and how to execute a zero-downtime migration using the view swap pattern. Most organizations do not start with Iceberg. They have years of data in Hive tables, data warehouses, CSV files, databases, […]