Whenever you're ready, walk me through how you'd approach this.
Design an ETL pipeline that takes a SQL dump into Hive with daily partitions, given two tables and a target query to run.
Explain how you would extract the dump, stage and validate both tables, create Hive-compatible schemas, load daily partitions, and execute the target query. Cover partition derivation, reruns and idempotency, data type handling, orchestration, and validation. Provide representative Hive SQL or shell commands, and describe how you would monitor failures and support backfills.