moomindani opened a new pull request, #17765:
URL: https://github.com/apache/iceberg/pull/17765
## Summary
Closes #15593
This revives #15594, which the stale bot closed on 2026-05-21 after ten
weeks without a single review. GitHub does not offer a reopen on that PR, so
this is a fresh PR from the same branch, rebased onto current `main`. Spark 3.4
support was removed upstream in #14122, so the v3.4 copies are dropped — this
now touches Spark 3.5, 4.0 and 4.1 (6 files instead of 8). Nothing else changed
from the closed PR.
Introduces a new `scd_type2 => boolean` parameter to the
`create_changelog_view` Spark procedure that natively produces a Slowly
Changing Dimensions Type-2 (SCD Type-2) history view from an Iceberg changelog
table, without requiring users to write custom SQL.
### New columns added to the view
| Column | Type | Nullable | Description |
|---|---|---|---|
| `_valid_from` | TIMESTAMP | no | Commit timestamp when this version became
active |
| `_valid_to` | TIMESTAMP | yes | Commit timestamp when superseded; `NULL`
if still active |
| `_is_current` | BOOLEAN | no | `true` when `_valid_to IS NULL AND
_change_type != 'DELETE'` |
### Example
```sql
CALL catalog.system.create_changelog_view(
table => 'db.products',
changelog_view => 'products_history',
identifier_columns => array('product_id'),
scd_type2 => true
)
```
Given inserts and updates to `products`, the resulting view:
| product_id | name | _change_type | _valid_from | _valid_to | _is_current |
|---|---|---|---|---|---|
| 1 | Widget | INSERT | 2024-01-01 | 2024-02-01 | false |
| 1 | Widget Pro | UPDATE_AFTER | 2024-02-01 | NULL | true |
### Demo output (from actual Spark 4.1 test run)
**Setup:**
```sql
CREATE TABLE products (id INT NOT NULL, data STRING) USING iceberg;
ALTER TABLE products SET IDENTIFIER FIELDS id;
ALTER TABLE products ADD PARTITION FIELD id;
INSERT INTO products VALUES (1, 'Widget');
INSERT INTO products VALUES (2, 'Gadget');
INSERT OVERWRITE products VALUES (1, 'Widget Pro'); -- update entity 1
```
**Without `scd_type2` (existing behavior):**
```sql
CALL catalog.system.create_changelog_view(
table => 'db.products',
identifier_columns => array('id')
)
```
```
id data _change_type _change_ordinal _commit_snapshot_id
---------------------------------------------------------------------------
1 Widget INSERT 0 5325177678658542000
1 Widget Pro UPDATE_AFTER 1 263949438103749135
1 Widget UPDATE_BEFORE 1 263949438103749135
2 Gadget INSERT 1 1299271826648018743
```
Users must manually write window functions to derive validity intervals.
**With `scd_type2 => true` (this PR):**
```sql
CALL catalog.system.create_changelog_view(
table => 'db.products',
identifier_columns => array('id'),
scd_type2 => true
)
```
```
id data _change_type _change_ordinal _commit_snapshot_id
_valid_from _valid_to _is_current
-------------------------------------------------------------------------------------------------------------------------------------------------
1 Widget INSERT 0 5325177678658542000
2026-04-13 11:23:44.977 2026-04-13 11:23:45.543 false
1 Widget Pro UPDATE_AFTER 2 263949438103749135
2026-04-13 11:23:45.543 null true
2 Gadget INSERT 1 1299271826648018743
2026-04-13 11:23:45.269 null true
```
Key differences:
- `UPDATE_BEFORE` rows filtered out (intermediate artifacts not meaningful
in SCD Type-2)
- `_valid_from` / `_valid_to` computed automatically via `LEAD()` window
over snapshot timestamps
- `_is_current` convenience flag: `true` only when `_valid_to IS NULL AND
_change_type != 'DELETE'`
**Querying current state:**
```sql
SELECT id, data FROM products_changelog WHERE _is_current = true ORDER BY id
```
```
id data
------------------
1 Widget Pro
2 Gadget
```
No custom window logic needed.
### Design choices
- `NULL` sentinel for open-ended rows (not `9999-12-31`) — aligns with dbt
and Databricks DLT
- `_valid_from`/`_valid_to` naming — aligns with dbt
`dbt_valid_from`/`dbt_valid_to` and Kimball semantics
- `UPDATE_BEFORE` rows filtered out — intermediate artifacts not meaningful
in an SCD Type-2 view
- DELETE rows kept with `_is_current = false` — tracks hard deletes
- `_is_current` convenience flag added — avoids the two-condition footgun
(`_valid_to IS NULL AND _change_type != 'DELETE'`)
### Constraints
- Requires `identifier_columns` (explicit or from table schema)
- Incompatible with `net_changes = true`
- Implicitly forces `compute_updates = true`
### Note on snapshot expiration
The `scd_type2` mode inherits the same snapshot expiration behavior as the
existing `create_changelog_view`. If snapshots have been expired, the changelog
scan itself omits the corresponding rows — the SCD Type-2 layer does not
introduce any additional data loss or new failure modes. Specifically:
- **Expired snapshot, data files still present**: changelog scan silently
omits rows from the expired snapshot. Both base and `scd_type2` modes return
the same reduced set of rows. `_valid_from`/`_valid_to`/`_is_current` are
correct relative to the rows the changelog scan returns.
- **Expired snapshot, data files garbage-collected**: changelog scan fails
with `FileNotFoundException`. Both base and `scd_type2` modes fail identically.
## Changes
- `CreateChangelogViewProcedure.java` — Spark v3.5, v4.0, v4.1
- `TestCreateChangelogViewProcedure.java` — Spark v3.5, v4.0, v4.1
## Test plan
- [x] `testScdType2BasicInsertUpdateDelete` — INSERT → UPDATE → DELETE
lifecycle, verifies all 3 new columns
- [x] `testScdType2CurrentRows` — only non-deleted, most-recent rows have
`_is_current = true`
- [x] `testScdType2HardDelete` — DELETE row has `_valid_to = NULL`,
`_is_current = false`
- [x] `testScdType2RequiresIdentifierColumns` — error when no identifier
columns
- [x] `testScdType2IncompatibleWithNetChanges` — error when combined with
`net_changes = true`
- [x] `testScdType2OutputSchema` — verifies all 8 output columns in correct
order
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
