Architect incremental load in ADF + Databricks with idempotency, late-arrival handling, and cost/scalability implications of watermark vs. change data capture.

Pattern: Process only new/changed data by tracking last processed boundary (watermark) or using CDC.

ADF approach: Watermark via lookup/stored procedure storing max(modified_date); filter source WHERE modified_date > @lastRun; parameterize pipeline for lastRun; write to sink. Use triggers for scheduling.

Databricks approach: Delta Lake MERGE INTO for upserts; Change Data Feed (CDF) for CDC; read CDF or filter on _change_version. Pattern: (1) Read max(modified) from target, (2) Extract source WHERE modified > last_modified, (3) MERGE INTO target on business key.

Architectural choices: Watermark (time-based) is simpler, cheaper, but misses out-of-order or late arrivals unless you add lookback. CDC captures every change with higher fidelity and operational cost (DB triggers, Debezium, etc.).

Scalability: Incremental reduces scan size—cost scales with delta, not full table. But watermark table/lookup becomes hotspot at scale; consider partitioning or caching. For very high churn, full scan occasionally may be cheaper than complex CDC.

Cost implications: Watermark = one extra query per run (cheap). CDC = log shipping, storage, and processing overhead. Delta OPTIMIZE + Z-order after incremental loads improves read performance but adds compaction cost.

Idempotency: Use surrogate keys; MERGE handles updates; design for re-running same batch without duplicates.