An append-only system preserves change history by inserting a new row whenever data changes rather than updating or deleting an existing row. In an incremental pipeline, this avoids repeatedly reloading all data and moves less data than full ingestion, reducing processing time, storage, and compute consumption. section 12.0 section 12.5
For historical records, store the entity identifier, changed attributes, and a validity-start timestamp. Unlike classic SCD type 2, no validity-end timestamp is stored or updated. The end of a row’s interval is derived from the next row’s start timestamp; the final row is current and receives a far-future end date when queried. section 12.2.1 section 12.2.2
Use the pattern as a simpler alternative to update-based history tracking:
- Insert an initial version with its validity-start timestamp. section 12.2.2
- Insert another version when an attribute changes, retaining the prior version unchanged. section 12.2.2
- Partition records by entity and order them by validity start to derive each validity end from the following row. section 12.2.2
- Expose the derived interval through a view, or materialize it when better query performance is needed. section 12.2.2
Deriving partner validity periods
A partner rated B from 1 June 2023 receives a new row rated A on 8 August 2023. `LEAD(valid_from) OVER (PARTITION BY partner_id ORDER BY valid_from)` yields 8 August as the B row’s end; `NVL` replaces the A row’s missing next date with `9999-12-31`. section 12.2.2
Table 12.4 Sample data in the PARTNER table with the append-only strategy
|
Partner ID
|
Partner name
|
Rating
|
Valid from
|
|---|---|---|---|
|
103
|
Crave Coffee
|
B
|
1.6.2023
|
|
103
|
Crave Coffee
|
A
|
8.8.2023
|
Compare the two retained PARTNER versions: each change creates a row with a new `Valid from` value and leaves the earlier row intact. section 12.2.2
