Build an incremental load pipeline for a source that also sends late-arriving records, and the business wants the pipeline to be idempotent with no duplicate output. How would you design the watermarking, partitioning, and deduplication strategy?

I would use a streaming framework like Spark Structured Streaming or Flink. The source would be read as a stream with event-time timestamps. I’d apply a watermark (e.g., 1 hour) to allow late data while preventing unbounded state. Partitioning would be by a business key (e.g., user_id) and a time bucket (e.g., day) to enable parallelism and efficient compaction. For deduplication, I’d maintain a state store keyed by the business key and timestamp, using a windowed aggregation that keeps the latest record per key. Alternatively, I could write to a Delta Lake table with a unique constraint on the key and use upsert semantics. The pipeline would write to a staging area, then a downstream job would perform a merge into the final table, ensuring idempotence. This design handles late data, prevents duplicates, and scales horizontally.