멱등한 upsert 파이프라인

목표

같은 입력을 몇 번을 넣어도 결과가 변하지 않고, 내용이 실제로 바뀐 행만 갱신되는 적재 파이프라인을 만듭니다.

왜 중요한가

파이프라인은 반드시 실패합니다. 네트워크가 끊기고, 배포 중에 죽고, 원천이 늦게 데이터를 보냅니다. 그때마다 사람이 "지금까지 어디까지 들어갔지"를 판단해야 한다면 언젠가 실수가 납니다.

멱등하게 설계하면 그 판단이 필요 없어집니다. 실패했으면 그냥 다시 돌리면 됩니다. 핵심 장치는 두 가지입니다. 자연 키에 유일 제약을 걸어 중복을 데이터베이스가 막게 하고, 내용 해시로 실제 변경 여부를 판단해 값이 그대로인 행은 건드리지 않는 것입니다.

두 번째가 특히 중요합니다. 조건 없이 갱신하면 무변경 재실행에서도 모든 행의 갱신 시각이 올라갑니다. 그러면 "무엇이 실제로 바뀌었는가"를 알 수 없게 되고, 하류에서 변경분만 가져가는 증분 소비가 망가집니다.

단계

대상은 staging.orders_raw 이고, 정규화 규칙은 앞 실습과 같습니다.

1. orders_final 표를 만듭니다. 컬럼은 order_ref, order_date, amount, status, content_hash, created_at, updated_at 순서이며 order_ref 가 기본 키여야 합니다. 시각 컬럼의 기본값은 현재 시각입니다.
2. v_raw_dedup 뷰를 만듭니다. 전표별로 raw_id 가 가장 큰 행 하나만 남기고, raw_id 컬럼을 포함하며 날짜와 금액과 상태는 정규화된 값이어야 합니다.
3. v_raw_dedup 을 orders_final 에 적재합니다. 전표가 이미 있으면 값을 갱신하도록 합니다. 적재 후 건수는 서로 다른 전표 수와 같아야 합니다.
4. content_hash 를 md5(coalesce(order_date::text,'') || '|' || coalesce(amount::text,'') || '|' || status) 규칙으로 채웁니다.
5. 같은 적재를 한 번 더 실행하고, 실행 전후의 orders_final 건수를 /root/etl/upsert_rerun.txt 에 한 줄씩 두 줄로 기록합니다.
6. staging.orders_raw 에서 ORD-000100 전표의 status 를 다른 값으로 바꾼 뒤 적재를 다시 실행합니다. 이때 updated_at 이 created_at 보다 나중인 행이 정확히 1건이어야 합니다.
7. etl_run_log 표를 만듭니다. 컬럼은 run_id, started_at, inserted_rows, updated_rows 순서이며 실행마다 한 행씩 기록해 최소 3행이 있어야 합니다. 무변경 실행은 삽입과 갱신이 모두 0 이어야 합니다.
8. v_final_check 뷰를 만듭니다. 컬럼은 metric, value 이며 total_rows, distinct_refs, changed_rows 세 행을 냅니다.

참고

• 충돌 시 갱신: INSERT ... ON CONFLICT (order_ref) DO UPDATE SET ... WHERE 대상.content_hash IS DISTINCT FROM EXCLUDED.content_hash
• 삽입과 갱신 구분: RETURNING (xmax = 0) AS inserted 를 CTE 로 받아 세면 됩니다.
• 전표별 최신 행: SELECT DISTINCT ON (order_ref) * FROM ... ORDER BY order_ref, raw_id DESC
• 흔한 실수 1: DO UPDATE 에 조건을 안 걸면 무변경 재실행에서도 전 행의 갱신 시각이 올라갑니다.
• 흔한 실수 2: 중복 제거 없이 적재하면 같은 전표가 한 배치 안에서 두 번 나와 오류가 납니다.

단계 8개

1. 자연 키로 대상 표 만들기
2. 중복 전표 제거하기
3. 충돌 시 갱신하는 적재 만들기
4. 내용 해시 계산하기
5. 무변경 재실행 확인하기
6. 늦게 도착한 변경 반영하기
7. 실행 로그 남기기
8. 점검 지표 뷰 만들기