How would you handle duplicate or corrupted data in a batch ETL job?

Handling duplicate or corrupted data in batch ETL involves multiple layers. First, implement idempotency—use MERGE/UPSERT or truncate-and-reload with deterministic keys. For duplicates, apply business rules: keep most recent (MAX(updated_at)), first occurrence, or aggregate. Use ROW_NUMBER() OVER (PARTITION BY composite_key ORDER BY timestamp DESC) and filter rn=1. For corrupted data, validate schemas at ingestion, reject bad records to a quarantine table, and alert. Use TRY_CAST or exception handling in Spark. In production: enable checkpointing, use deduplication keys (e.g., event_id), and run reconciliation jobs. Example: WITH ranked AS (SELECT , ROW_NUMBER() OVER (PARTITION BY order_id, line_id ORDER BY etl_ts DESC) rn FROM raw_orders) SELECT FROM ranked WHERE rn=1 AND amount IS NOT NULL; Why it matters: Design choices compound at scale—wrong approach can cause 100× overhead. Scalability trade-offs: Profile before optimizing; validate on sample then full. Cost implications: Suboptimal choices multiply at billion-row scale.