moomindani commented on PR #17765:
URL: https://github.com/apache/iceberg/pull/17765#issuecomment-5419450483
Thanks @szehon-ho — that premise is right, and I think it is worth naming
the options explicitly, because "a separate SCD Type-2 table" can be reached in
more than one way and I am not sure which one you have in mind.
On the horizon: the changelog scan can only see changes whose snapshots are
still around, so a view built directly on it covers exactly the retention
window and no more. How long that window is, though, is a choice rather than a
fixed limit — `history.expire.max-snapshot-age-ms` defaults to 5 days but can
be set arbitrarily high, `min-snapshots-to-keep` also holds snapshots back, and
nothing expires at all until `expire_snapshots` actually runs, so the effective
horizon is the property plus the maintenance schedule. A table that keeps
months of snapshots would give months of SCD Type-2 view.
That said, I would not build a feature on the assumption that the window is
long. Retention gets chosen for storage cost, metadata size and compliance
deletion, not for a downstream SCD Type-2 use case, so on most tables it will
be short — which is your point, and I agree with where it lands.
As far as I can see there are three ways to end up with a long-lived SCD
Type-2 table:
1. **Dual write** — the writer appends to the main table and to an SCD
Type-2 table in the same job. No dependency on retention, but it changes every
write path, and Iceberg has no cross-table atomic commit today (there was a
dev@ thread on multi-table transactions in July), so the two tables can diverge
when one commit fails.
2. **SCD Type-2 as the base table** — store only versions and derive the
current cross-section on read. Nothing is ever lost, but every consumer then
pays for the `_is_current` / latest-per-key filter, and existing readers of the
table have to change.
3. **Derive from the changelog and store it** — the write path and the
consumers stay as they are, and a scheduled job appends the new versions to a
table you own. The cost is operational: that job has to run more often than
snapshots are expired on the source table, and history missed in that window
cannot be recovered.
This PR is the derivation step of option 3. It stores nothing itself; it
turns changelog rows into version intervals — joining snapshot timestamps,
applying the window function, dropping the `UPDATE_BEFORE` images, deciding
what a delete means — which is the part users otherwise hand-write in SQL for
every batch, and the reason this came up in the first place.
Since the option needed documentation anyway, I wrote that up rather than
just asserting it: `ef48e01a1` adds a "Building a Long-Lived SCD Type-2 Table"
section that states the retention bound and shows the loop — run with `options
=> map('start-snapshot-id', ...)`, `MERGE` to close the version that was open
in the target table, append the new ones, and record the end snapshot for the
next run.
Which of the three did you have in mind? If it is 1 or 2, that is a
different feature from this one and I would not claim this PR covers it. If it
is 3, this is the piece that makes it a few lines of SQL instead of a
hand-rolled window query.
--
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]
