INSERTED

Returns rows that exist in a Delta table at one version but did not exist at an earlier version, by diffing two time-travel snapshots. Symmetric counterpart of DELETED. One of the DELETED / UPDATED / INSERTED change-extraction trio.

Category: time-travelReturns: SETOF row-of(table)Dialect: DeltaForgePrivilege: readDeltaForge extension

Syntax

INSERTED(table_name TEXT [, from_version BIGINT, to_version BIGINT [, key_array TEXT[]]])

Description

## Overview Returns rows that exist in a Delta table at `to_version` but did not exist at `from_version`. Symmetric counterpart of `DELETED`. Implemented as a snapshot diff: the engine loads two time-travel snapshots of the same table and computes the set difference between them in the V_to → V_from direction. The result schema matches the source table's row schema, so the function composes naturally with regular SELECT, JOIN, INSERT, and DELETE. INSERTED is one of the **DELETED / UPDATED / INSERTED** change-extraction trio. Together they let you partition any commit (or version range) into deletes, updates, and inserts using the same KEY columns; each operator surfaces exactly one slice and the three slices are non-overlapping. This is the standard primitive for incremental ETL on Delta tables. Unlike CDF-based change extraction, INSERTED works on every Delta table without requiring `delta.enableChangeDataFeed = true` to have been set at write time. ## Behavior - The 1-arg form `INSERTED('schema.table')` returns rows added by the most recent commit (versions `latest - 1` → `latest`). - The 3-arg form `INSERTED('schema.table', from, to)` returns rows present at `to` but absent at `from`. With no KEY argument and no declared PRIMARY KEY, the diff is a full-row EXCEPT in the V_to → V_from direction. - The 4-arg form `INSERTED('schema.table', from, to, ARRAY['id'])` uses anti-join semantics on the named key columns. A row is returned if its key value is at `to` and has no matching key at `from`. This distinguishes hard inserts from UPDATE post-images. - A declared PRIMARY KEY constraint is auto-used when the user does not pass an explicit KEY argument. Explicit KEY always wins. - Versions older than the table's log-retention horizon are not readable. After VACUUM removes log files past the retention period, those snapshots become inaccessible and INSERTED raises a planning error. ## Identity resolution The function resolves identity in this order: 1. Explicit `KEY` argument: `INSERTED(..., ARRAY['col1', 'col2'])`. Always wins. 2. The source table's PRIMARY KEY constraint (declared via `delta.constraints.pk` or `ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY`). 3. None: the function falls back to full-row EXCEPT. Full-row EXCEPT is structurally correct (`(SELECT * FROM t VERSION AS OF :to) EXCEPT (SELECT * FROM t VERSION AS OF :from)`) but treats two duplicate rows as a single row, so the count of returned rows can be smaller than the number of physically-inserted rows on a non-unique schema. ## Versioning semantics Version bounds are inclusive on both ends. `INSERTED('t', 5, 5)` returns rows added by the single commit that produced version 5 (i.e. absent at version 4, present at version 5). ## Composition with the trio DELETED, UPDATED, and INSERTED share an identical surface (table_name, from_version, to_version, key_array) and identical identity-resolution rules. Used together on the same version range and the same KEY they cleanly partition every change in the commit window: - A row whose key exists at `from` and not at `to` → DELETED. - A row whose key exists at both versions, with at least one non-key column differing → UPDATED. - A row whose key exists at `to` and not at `from` → INSERTED. - A row whose key exists at both versions with identical non-key columns → no operator returns it (genuinely unchanged). This property is exploited by the canonical incremental-ETL template: run all three against `(:last_seen, :current)`, route each result set to the corresponding sink action (DELETE, MERGE/UPSERT, INSERT), advance `:last_seen` to `:current`, repeat. ## Compatibility DeltaForge-native. The semantic equivalent in standard SQL is `(SELECT * FROM t VERSION AS OF :to) EXCEPT (SELECT * FROM t VERSION AS OF :from)` for the no-key path, or `SELECT v_to.* FROM t VERSION AS OF :to v_to LEFT ANTI JOIN t VERSION AS OF :from v_from ON v_to.id = v_from.id` for the key path. INSERTED is the readable shorthand. ## Access Control | Privilege | Object | Notes | |-----------|--------|-------| | read | Table | Required on the source table. The function reads two snapshots of the same table; a single read grant suffices. |

Parameters

Name	Type	Description
`table_name`		Specifies the source Delta table as a string literal. Accepts a one-, two-, or three-part name (table, schema.table, or zone.schema.table). The table must be a Delta table; external tables and in-memory tables are rejected because they have no version history.
`from_version`		Inclusive lower-bound version of the range to diff. The function compares the snapshot at to_version against the snapshot at from_version and returns rows present at to_version but absent at from_version. When omitted (1-arg form), defaults to latest - 1.
`to_version`		Inclusive upper-bound version of the range to diff. Must be greater than or equal to from_version. When omitted (1-arg form), defaults to the table's current latest version.
`key_array`		Identity columns used for anti-join semantics. When supplied, the function returns each V_to row whose key has no match at V_from (a hard insert; UPDATE post-images are excluded because the key already existed at V_from). When omitted and the table has a declared PRIMARY KEY constraint, the constraint columns are used. When omitted and there is no PRIMARY KEY, the function falls back to full-row EXCEPT in the V_to → V_from direction.

Examples

-- Audit which customers were added in commit 17
SELECT * FROM INSERTED('silver.customer', 17, 17);

-- Latest commit shorthand: rows added by the most recent commit
SELECT * FROM INSERTED('silver.events');

-- ETL pattern: stream new rows into a downstream warehouse stage
INSERT INTO warehouse.dim_customer_stage
SELECT * FROM INSERTED('silver.customer', :last_seen, :current, ARRAY['id']);

-- Anti-join with explicit key: distinguishes hard inserts from update post-images
SELECT *
  FROM INSERTED('silver.customer', 100, 110, ARRAY['customer_id']);

-- Compose with a JOIN to enrich the audit trail
SELECT i.customer_id, r.region
  FROM INSERTED('silver.customer', 1, 2, ARRAY['customer_id']) i
  JOIN silver.region r ON i.region_id = r.id;

-- Canonical incremental-ETL template using the full DELETED / UPDATED / INSERTED trio.
-- Each operator covers one slice of the commit's change set; together they form a
-- complete, non-overlapping partition.
DELETE FROM warehouse.dim_customer
 WHERE id IN (SELECT id FROM DELETED('silver.customer', :last_seen, :current, ARRAY['id']));

INSERT INTO warehouse.stage_updates
 SELECT * FROM UPDATED('silver.customer', :last_seen, :current, ARRAY['id']);

INSERT INTO warehouse.stage_inserts
 SELECT * FROM INSERTED('silver.customer', :last_seen, :current, ARRAY['id']);

Pitfalls

Full-row EXCEPT (the no-KEY path) collapses duplicate rows and can be surprising on schemas without a unique constraint. Pass an explicit KEY array or declare a PRIMARY KEY constraint for predictable per-row semantics.
Versions before the table's log-retention horizon are not readable. After VACUUM removes log files past the retention period, those snapshots become inaccessible and INSERTED raises a planning error rather than returning silently incomplete results.
Schema evolution between the two versions (column added, dropped, or renamed) is rejected with a clear error in v1. Pin the version range to a window with stable schema.
Cost scales with the size of both version snapshots, not with the size of the change set. For large tables with small commits, the future DV-pinpoint optimization (when `delta.enableDeletionVectors = true`) reads orders of magnitude less data; this is planned but not in the v1 implementation.
Table names are passed as string literals, not bare identifiers, so the catalog/schema search path is not applied. Always pass a fully-qualified `'zone.schema.table'` name in production scripts.
Used alone, INSERTED gives only one slice of a commit's change set. For a complete view of incremental change, run `DELETED`, `UPDATED`, and `INSERTED` against the same version range with the same KEY: together they partition the change set with no overlap. Mixing different KEYs across the trio yields inconsistent slicing and should be avoided.