AWS Certified Data Engineer Associate DEA-C01 Practice Question

Use a MERGE statement with a subquery that selects the latest row per txn_id, for example: MERGE INTO prod_txn AS p USING (SELECT * FROM stage_txn QUALIFY ROW_NUMBER() OVER (PARTITION BY txn_id ORDER BY updated_at DESC)=1) AS s ON p.txn_id = s.txn_id WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ...;

Create a view on stage_txn that filters duplicates with QUALIFY and schedule separate UPDATE and INSERT statements against prod_txn inside a transaction.

Run DELETE FROM prod_txn USING stage_txn WHERE prod_txn.txn_id = stage_txn.txn_id; then INSERT INTO prod_txn SELECT * FROM stage_txn;

Insert DISTINCT txn_id and MAX(updated_at) from stage_txn into a temporary table, join it back to stage_txn to find full rows, insert those into prod_txn, and run VACUUM DELETE on prod_txn afterwards.