In a pipeline where the same source file can arrive twice downstream, how would you design the pipeline to ensure exactly one clean result is produced? What strategies would you use to handle replay and maintain idempotency?

To guarantee a single clean result, I design the pipeline to be idempotent by using a deterministic key for each record, such as a hash of the file name and a unique record identifier. I store processed keys in a transactional table (e.g., DynamoDB or a Delta Lake table) with a unique constraint. When a file arrives, the pipeline first checks the key table; if the key exists, the file is skipped. For replay handling, I use a message queue (Kafka or SQS) with exactly-once semantics and enable idempotent writes to the destination (e.g., S3 with versioning or a Delta Lake table). I also implement a watermarking strategy to discard late-arriving duplicates after a certain time window. The pipeline stages include: ingestion → de-duplication (key lookup) → transformation → write with idempotent commit. This architecture ensures that even if the same source file is replayed, only one clean result is produced, and the system can recover from failures without data corruption.