Hive MERGE Failure Due to Partition Column in SCD Type-2 Implementation

[#1]

Dears,

The Hive MERGE is failing with the error below. Since yearmonth is a partition column, implemented a Type-2 SCD script to track changes. However, even after including the partition column in the join condition, the same error persists.

Error while compiling statement: FAILED: SemanticException [Error 10007]: Ambiguous column reference deal_yearnumber in tgt.

Script

MERGE INTO accrued_interest_metric tgt
USING temp_ACCRUED_INTEREST_METRIC src
ON tgt.deal_number = src.deal_number
AND tgt.deal_metric_end_date = DATE '9999-12-31'
WHEN MATCHED
AND tgt.deal_hash <> src.deal_hash
THEN UPDATE SET
deal_metric_end_date = CURRENT_DATE - 1,
update_date = current_timestamp()
WHEN NOT MATCHED THEN
INSERT (
deal_number,
deal_metric_start_date,
deal_metric_end_date,
deal_branch_cd,
deal_type_cd,
deal_reference_number,
currency_cd,
deal_maturity_date,
deal_last_rollover_date,
deal_next_rollover_date,
deal_term_type_cd,
deal_accrued_interest_rate,
status_cd,
status_description,
deal_account_number,
deal_interset_account_number,
period_type_cd,
interset_type_cd,
deal_hash,
insert_date,
update_date,
deal_yyyymm
)
VALUES (
src.deal_number,
src.deal_metric_start_date,
src.deal_metric_end_date,
src.deal_branch_cd,
src.deal_type_cd,
src.deal_reference_number,
src.currency_cd,
src.deal_maturity_date,
src.deal_last_rollover_date,
src.deal_next_rollover_date,
src.deal_term_type_cd,
src.deal_accrued_interest_rate,
src.status_cd,
src.status_description,
src.deal_account_number,
src.deal_interset_account_number,
src.period_type_cd,
src.interset_type_cd,
src.deal_hash,
current_timestamp(),
current_timestamp(),
src.src_deal_yyyymm
);

[#2]

@APentyala 

This error is not caused by the SCD Type-2 logic or the MERGE syntax itself. The message “Ambiguous column reference deal_yearnumber in tgt” usually indicates that the column exists more than once in the target table metadata.

Please check whether deal_yearnumber (or the partition column) is defined both as a regular column and in the PARTITIONED BY section. Run DESCRIBE FORMATTED and SHOW CREATE TABLE to verify the schema.

If the column appears twice or was altered previously, Hive may treat it as ambiguous during MERGE compilation.

Recreating the table with a clean schema (ensuring the partition column is defined only once) typically resolves the issue.

[#3]

Hello @APentyala 

Could be please let us know if the solution provided by @RAGHUY fixed your problem? If you still face same issue, let me know so we can help you.