apache / apache/iceberg

Spark: Add scd_type2 option to create_changelog_view procedure

Open
#15,593 1 comment 0 reactions 0 assignees View on GitHub
stale
Dominant language
Java
Stars
9.2k
Forks
3.5k
Avg merge
2d 16h
Merged PRs (30d)
129

Description

### Feature Request / Improvement

The `create_changelog_view` procedure lets users query changelog rows from an Iceberg table, but producing a Slowly Changing Dimensions Type-2 (SCD Type-2) history view still requires users to write non-trivial SQL manually: joining with snapshot metadata, applying LEAD window functions, and filtering intermediate rows. This is a recurring pattern across many analytical workloads.

This issue proposes adding a `scd_type2` parameter to `create_changelog_view` so Iceberg can produce an SCD Type-2 view natively.

**Proposed API**

```sql
CALL catalog.system.create_changelog_view(
table => 'db.products',
changelog_view => 'products_history',
identifier_columns => array('product_id'),
scd_type2 => true
)
```

**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'` |

**Design choices**

- `NULL` for open-ended rows — aligns with dbt (`dbt_valid_from`/`dbt_valid_to`) and Databricks DLT (`__START_AT`/`__END_AT`)
- `_valid_from`/`_valid_to` naming — closest to dbt and Kimball semantics; prefixed with `_` to match Iceberg metadata column conventions
- `_is_current` retained — DELETE rows have `_valid_to IS NULL` but are NOT current; the convenience flag avoids the two-condition footgun
- DELETE rows kept — marks hard deletes with `_is_current = false`, aligns with dbt `hard_deletes='new_record'`
- `UPDATE_BEFORE` rows filtered — they are intermediate computation artifacts

**Constraints**

- `scd_type2 = true` requires `identifier_columns` (explicit or from table schema identifier fields)
- `scd_type2 = true` and `net_changes = true` are mutually exclusive
- `scd_type2 = true` implicitly forces `compute_updates = true`

### Query engine

Spark

### Willingness to contribute

- [x] I can contribute this improvement/feature independently
- [ ] I would be willing to contribute this improvement/feature with guidance from the Iceberg community
- [ ] I cannot contribute this improvement/feature at this time

Contributor guide

Open the contributing guide

Research direction

Begin at the Spark implementation of the create_changelog_view procedure and trace its current parameters and changelog-row generation. Verify how identifier columns, net_changes, and compute_updates are handled before defining coverage for the new option. Done means the API produces the specified SCD Type-2 columns and enforces the listed constraints, including filtering UPDATE_BEFORE rows.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, spark, sql
Domain
data-engineering, databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.