A source has reliable modified timestamps. How do you load it incrementally?
Why D is the answer
Use a watermark, and load newer rows only
A watermark records progress, so each run loads only what changed.
In detail
- A watermark records the last loaded time.
- Only newer rows are loaded.
- Update the watermark only after a successful load.
Use case
An illustrative scenario, not a real incident.
A source table has a reliable modified date. Each run records the highest value it loaded, and the next run starts from there.
See it in different ways
flowchart LR A["Read the last watermark"] --> B["Select newer rows"] B --> C["Merge into the target"] C --> D["Save the new watermark"]
sequenceDiagram participant P as Pipeline participant S as Source participant T as Delta table P->>P: Reads the last watermark P->>S: Rows newer than the watermark S-->>P: Changed rows P->>T: MERGE P->>P: Saves the new watermark
flowchart LR S0["Read the watermark"] --> S1["Load newer rows"] --> S2["Save the new watermark"]
Key point: Watermarks record load progress.
Why the other options are wrong
- A. Copy each row twice to guard against failures
- Duplicates create errors.
- B. Create a new table for each day of data
- Daily tables make queries harder.
- C. Truncate and reload the full table every day
- A full reload is what you are avoiding.