Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Rate of change: append-only vs mutable data

Pipelines & scenarios · Core Concepts Not Yet Covered

Rate of change: append-only vs mutable data

Mediumpipelines-64
append-onlymutable-datacdcincrementalmerge

Question

Why does it matter whether source data is append-only or mutable?

Solution

How the source data changes decides how you can ingest it. Append-only data only ever gets new rows, and mutable data gets rows that change or disappear. The first is simple, and the second needs more machinery.

Append-only data

Events, logs, clickstream, sensor readings: once written, a row never changes. You can load incrementally by a time column or an offset (everything after the last loaded id or timestamp), and you can store data as plain appended files. There is no need for MERGE, and replays are safe if you deduplicate by event id. Partitioning by event date works naturally, and old partitions never change, so downstream tables only need to process new partitions.

Mutable data

Orders move from placed to shipped to refunded. Customers change address. Rows are updated, and sometimes deleted. If you only pull new rows, you will never see the updates. Your options:

  • Pull changed rows using an updated_at column (needs a trustworthy column and misses hard deletes).
  • Use CDC from the database log, which gives every insert, update and delete.
  • Take full snapshots and compare them (simple, but costly for big tables).

Then apply the changes with MERGE into a current-state table, and often keep a history, using SCD Type 2 or an append-only change log.

Deletes

Deletes are the hardest part. A time-based incremental pull never shows them. You need CDC delete events, a soft-delete flag in the source, or a periodic comparison of keys. Decide what a delete means downstream: remove the row, mark it, or keep history.

Why it affects the design

  • Storage and modelling: append-only tables can be facts that never change. Mutable entities need either a current-state table, a history table, or both.
  • Cost: MERGE and CDC are more expensive than appends, so using them only where needed saves money.
  • Correctness: treating mutable data as append-only gives you stale rows, with several versions of one order counted separately.
  • Reprocessing: with an append-only change log, you can rebuild any state; with only the current state, you cannot see the past.

How to answer

Ask for each source: can rows change, and can they be deleted? Then pick the ingestion method that matches, and design downstream tables for that behaviour.

PreviousNext