Trace where a data pipeline went wrong

현재 증상만으로 원인을 단정할 수는 없습니다. 다만 재처리 후 fct 매출이 약 1.8배이므로, 가장 먼저 재처리의 append/overwrite와 fact 적재의 중복 여부를 확인해야 합니다.

1. 단계별로 먼저 캡처할 값

대상 날짜를 동일하게 맞춰 <D>로 표시합니다. 금액 컬럼은 <amount>, 주문 키는 <order_id>로 대체하세요.

SELECT
    COUNT(*) AS row_count,
    SUM(<amount>) AS amount_sum
FROM raw_orders
WHERE <order_date> = DATE '<D>';

SELECT
    COUNT(*) AS row_count,
    SUM(<amount>) AS amount_sum
FROM stg_orders
WHERE <order_date> = DATE '<D>';

SELECT
    COUNT(*) AS row_count,
    SUM(<amount>) AS amount_sum
FROM fct_orders
WHERE <order_date> = DATE '<D>';

각 단계의 값을 같은 실행 시점에 별도 저장하세요. 특히 재처리 전에 존재했던 fct 상태가 필요합니다.

2. 각 단계가 바꿔도 되는 것

• raw_orders → stg_orders

  • 중복 제거로 행 수 감소: 가능
  • 유효하지 않은 행 필터링으로 행 수 감소: 가능
  • 정상적인 중복 제거라면 금액 합계도 줄 수 있지만, 제거된 주문 목록으로 설명 가능해야 함
  • 원본 한 건의 금액을 임의로 증가시키면 안 됨

• stg_orders → fct_orders

  • 일별 집계라면 행 수 감소: 가능
  • 합계는 해당 일자의 유효한 주문 금액 합계와 같아야 함
  • 차원/lookup 조인으로 행 수가 늘면 위험
  • 재처리 시 기존 날짜 데이터를 교체해야 하며, 기존 데이터에 다시 더하면 안 됨

• fct_orders → BI

  • 필터, 날짜 범위, 환율·통화 변환 등으로 값이 달라질 수 있음
  • 대시보드가 fact를 다시 조인해 행을 증식시키면 안 됨

3. 확인 순서

가장 저렴하게 확인할 순서는 다음과 같습니다.

1. fct_orders에 재처리 배치가 두 번 들어갔는지 확인
2. stg_orders → fct_orders 적재 쿼리의 append/overwrite 동작 확인
3. fct_orders의 동일 주문 중복 확인
4. fact 생성 쿼리의 조인별 행 수·금액 변화 확인
5. stg_orders의 dedup 결과와 raw 대비 비교
6. BI 집계 쿼리에서 fact가 다시 증식되는지 확인

raw 행 수가 평소와 비슷하다는 사실만으로는 raw 단계가 정상이라고 결론낼 수 없습니다. 행 수뿐 아니라 금액 합계, 주문 키 중복, 해당 날짜의 실제 입력 범위를 함께 확인해야 합니다.

4. 현재 증상이 이미 배제하는 것

현재 확인된 사실로 확실히 배제되는 것은 거의 없습니다.

• raw 행 수가 평소와 비슷함
  → raw의 행 수 이상 가능성은 낮아 보이지만, 금액 중복·중복 이벤트·늦게 도착한 로그는 아직 배제하지 못합니다.
• stg 중복 제거 코드가 변경되지 않음
  → 코드 변경에 의한 회귀 가능성은 낮아 보이지만, 입력 데이터 변화와 재실행 방식은 여전히 확인해야 합니다.
• 1.8배 높음
  → 누락, nullable filter, 미로드 partition보다는 중복 적재나 조인 fan-out 계열을 우선 점검할 근거는 되지만, 이것도 원인 확정은 아닙니다.

5. 후보별 확인 쿼리

A. 재처리가 append되어 기존 fact를 다시 더했는지

배치 실행 이력이나 적재 메타데이터가 있다면:

SELECT
    <business_date>,
    <batch_id>,
    <run_id>,
    <load_mode>,
    COUNT(*) AS rows_loaded,
    SUM(<amount>) AS amount_loaded,
    MIN(<loaded_at>) AS first_loaded_at,
    MAX(<loaded_at>) AS last_loaded_at
FROM fct_orders
WHERE <business_date> = DATE '<D>'
GROUP BY <business_date>, <batch_id>, <run_id>, <load_mode>
ORDER BY last_loaded_at;

같은 날짜에 두 개의 적재 실행이 있고 둘 다 금액을 포함하면 append 가능성이 있습니다.

재처리 스크립트의 핵심 동작도 확인해야 합니다.

-- 위험한 형태
INSERT INTO fct_orders
SELECT ...
FROM stg_orders
WHERE <business_date> = DATE '<D>';

-- 날짜 파티션을 교체하는 형태인지 확인
DELETE FROM fct_orders
WHERE <business_date> = DATE '<D>';

INSERT INTO fct_orders
SELECT ...
FROM stg_orders
WHERE <business_date> = DATE '<D>';

파티션 교체, MERGE, overwrite 옵션 없이 단순 INSERT라면 재실행에 안전하지 않을 수 있습니다.

B. fact에 같은 주문이 두 번 들어갔는지

SELECT
    <order_id>,
    COUNT(*) AS cnt,
    SUM(<amount>) AS amount_sum
FROM fct_orders
WHERE <business_date> = DATE '<D>'
GROUP BY <order_id>
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

재처리 실행별로 중복이 나뉘어 있다면:

SELECT
    <order_id>,
    COUNT(DISTINCT <run_id>) AS run_count,
    COUNT(*) AS row_count,
    SUM(<amount>) AS amount_sum
FROM fct_orders
WHERE <business_date> = DATE '<D>'
GROUP BY <order_id>
HAVING COUNT(DISTINCT <run_id>) > 1;

C. fact 생성 조인의 fan-out

각 조인을 한 번에 포함하지 말고 단계별로 측정합니다.

WITH base AS (
    SELECT <order_id>, <amount>
    FROM stg_orders
    WHERE <business_date> = DATE '<D>'
),
joined AS (
    SELECT
        b.<order_id>,
        b.<amount>,
        l.<lookup_key>
    FROM base b
    LEFT JOIN <lookup_table> l
      ON b.<join_key> = l.<join_key>
)
SELECT
    COUNT(*) AS row_count,
    COUNT(DISTINCT <order_id>) AS distinct_orders,
    SUM(<amount>) AS amount_sum
FROM joined;

COUNT(*)와 SUM(<amount>)가 조인 전보다 증가하고 COUNT(DISTINCT <order_id>)는 그대로라면 해당 조인이 fan-out입니다.

lookup 키 자체도 확인합니다.

SELECT
    <join_key>,
    COUNT(*) AS lookup_rows
FROM <lookup_table>
GROUP BY <join_key>
HAVING COUNT(*) > 1;

D. stg가 재처리 입력을 중복으로 보존했는지

SELECT
    <order_id>,
    COUNT(*) AS cnt,
    SUM(<amount>) AS amount_sum
FROM stg_orders
WHERE <business_date> = DATE '<D>'
GROUP BY <order_id>
HAVING COUNT(*) > 1;

raw와 stg의 금액을 비교합니다.

SELECT
    'raw' AS layer,
    COUNT(*) AS row_count,
    COUNT(DISTINCT <order_id>) AS distinct_orders,
    SUM(<amount>) AS amount_sum
FROM raw_orders
WHERE <business_date> = DATE '<D>'

UNION ALL

SELECT
    'stg',
    COUNT(*),
    COUNT(DISTINCT <order_id>),
    SUM(<amount>)
FROM stg_orders
WHERE <business_date> = DATE '<D>';

E. raw에 늦게 도착한 이벤트가 정상적으로 중복 포함됐는지

SELECT
    <order_id>,
    COUNT(*) AS raw_rows,
    MIN(<event_time>) AS first_event_time,
    MAX(<ingested_at>) AS last_ingested_at,
    SUM(<amount>) AS amount_sum
FROM raw_orders
WHERE <business_date> = DATE '<D>'
GROUP BY <order_id>
HAVING COUNT(*) > 1;

동일 주문의 재전송 이벤트인지, 실제 추가 주문 이벤트인지 이벤트 식별자와 상태 컬럼까지 비교해야 합니다.

F. BI에서 fact를 다시 증식시키는지

대시보드 쿼리를 그대로 떼어내어 fact 단독 집계와 비교합니다.

SELECT SUM(<amount>)
FROM fct_orders
WHERE <business_date> = DATE '<D>';

SELECT SUM(f.<amount>)
FROM fct_orders f
JOIN <bi_dimension_or_lookup> d
  ON f.<join_key> = d.<join_key>
WHERE f.<business_date> = DATE '<D>';

두 번째 값만 커지면 BI 조인의 fan-out입니다.

6. 재처리 전에 보존할 증거

다시 재처리하기 전에 다음을 별도 스냅샷 테이블이나 파일로 보존하세요.

CREATE TABLE investigation_fct_orders_<D> AS
SELECT *
FROM fct_orders
WHERE <business_date> = DATE '<D>';

가능하면 다음도 저장합니다.

• 현재 fct_orders의 행 수, 주문 수, 금액 합계
• 해당 날짜의 stg_orders 스냅샷
• raw/stg/fct의 중복 주문 목록
• 재처리 실행 ID, 시작·종료 시각, 실행 파라미터
• 재처리 스크립트 버전과 실제 SQL
• 해당 파티션의 생성·수정 시각

현재 상태를 보존하지 않고 overwrite나 delete 후 재처리하면, “재처리 전부터 잘못됐는지”와 “재처리 때문에 잘못됐는지”를 구분할 수 없게 됩니다.

7. 멱등성 판단

현재 정보만으로 이 파이프라인이 멱등적이라고 말할 수 없습니다. 다음 중 하나가 보장되어야 멱등적입니다.

• 날짜 파티션을 매번 overwrite
• MERGE가 안정적인 business key로 기존 행을 갱신
• batch/run key에 unique 제약 또는 중복 방지 로직 존재
• 재처리 시 동일 입력에 대해 동일한 최종 상태 생성

재처리 스크립트가 단순 INSERT INTO fct_orders SELECT ...라면 비멱등적이며, 조사를 위해 다시 실행할수록 매출이 더 증가할 수 있습니다. 따라서 먼저 fct_orders와 실행 메타데이터를 스냅샷한 뒤, append인지 overwrite인지 확인하는 것이 첫 번째 점검입니다.