# 배치 upsert와 실시간 CDC 구분하기

표준 DML과 MERGE 기반 배치 upsert, Storage Write API CDC의 UPSERT·DELETE 차이를 구분하고 기본 키, 순서, 파티션, 중복 제거와 검증 기준을 설명합니다.

- 카테고리: BigQuery
- 소요 시간: 약 15분
- 난이도: 중간
- 업데이트: 2026.09.02
- 원문: /wiki/playbook/bigquery-checklist/bigquery-data-write-and-cdc-guide

## 목차

- [먼저 바로잡을 표현](#truth)
- [쓰기 방식 선택](#patterns)
- [1단계 · 초기 INSERT](#step-1)
- [2단계 · MERGE upsert](#step-2)
- [3단계 · 결과 검증](#step-3)
- [Storage Write API CDC](#cdc)
- [운영 체크리스트](#production)
- [코드로 MERGE 실행](#code-examples)
- [자주 묻는 질문](#faq)

BigQuery에서 데이터를 갱신하는 방법은 하나가 아닙니다. 표준 테이블은 `INSERT`·`UPDATE`·`DELETE`·`MERGE`를 지원하고, 배치 upsert는 보통 `MERGE`로 구현합니다. 반면 Storage Write API의 Change Data Capture(CDC)는 변경 유형으로 `UPSERT`와 `DELETE`를 받습니다.

> **이 문서 핵심**
>
> - **대상.** 주문·고객·재고처럼 같은 키가 다시 들어오는 데이터를 적재하는 담당자.
>
> - **완료 후.** 배치 MERGE와 실시간 CDC를 구분하고, 중복·순서·파티션 비용을 통제합니다.
>
> - **전제.** 대상 테이블의 논리적 기본 키와 파티션 기준이 정해져 있어야 합니다.

## “BigQuery는 upsert만 제공한다”는 설명은 정확하지 않습니다

| 영역 | 제공 기능 | upsert 구현 |
| --- | --- | --- |
| **GoogleSQL DML** | INSERT, UPDATE, DELETE, TRUNCATE, MERGE | `MERGE`의 MATCHED / NOT MATCHED 분기 |
| **Storage Write API CDC** | 행 변경 유형 UPSERT, DELETE | `_CHANGE_TYPE = UPSERT` |

> **정보**
>
> 문서에는 **“배치 upsert는 MERGE, 실시간 행 변경은 Storage Write API CDC”**라고 구분해서 적는 것이 정확합니다. 두 방식은 입력 경로, 지연 시간, 오류 처리 방식이 다릅니다.

SQL 문법은 [BigQuery DML 문서](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax), CDC의 행 변경 규칙은 [BigQuery CDC 문서](https://cloud.google.com/bigquery/docs/change-data-capture)를 기준으로 합니다.

## 적재 패턴 선택

![스테이징 테이블을 사용하는 배치 MERGE와 실시간 UPSERT DELETE CDC 비교](/wiki-assets/playbook/bigquery-data-write-and-cdc-guide/00-merge-vs-cdc.png)

> **한눈에 보기.** 배치는 스테이징 데이터를 MERGE하고, 실시간 CDC는 UPSERT·DELETE 변경 이벤트를 연속으로 반영합니다.

| 상황 | 권장 패턴 | 이유 |
| --- | --- | --- |
| 하루·한 시간 단위 파일 | 스테이징 테이블 → MERGE | 한 작업에서 갱신·삽입을 검증하고 재처리하기 쉬움 |
| 작은 단발성 추가 | INSERT 또는 load job | 기존 키 갱신이 없으면 단순 |
| DB 로그 기반 지속 변경 | Storage Write API CDC | UPSERT·DELETE 변경을 낮은 지연 시간으로 반영 |
| 전체 스냅샷 교체 | CREATE OR REPLACE 또는 파티션 교체 | 행별 UPDATE보다 예측 가능할 수 있음 |

## 1단계. 초기 행을 INSERT로 적재

샘플 테이블에 서로 다른 주문 세 건을 넣습니다. 실제 운영 배치는 개별 INSERT 반복보다 load job이나 스테이징 테이블 적재를 우선합니다.

**샘플 주문 INSERT**
```sql
INSERT INTO `hurdlers-bq-dev-guide-260902.developer_guide.orders`
  (order_id, customer_id, order_status, order_amount, updated_at, order_date)
VALUES
  ('ORD-1001', 'CUS-001', 'paid',    49000, TIMESTAMP '2026-09-01 01:00:00+00', DATE '2026-09-01'),
  ('ORD-1002', 'CUS-002', 'pending', 29000, TIMESTAMP '2026-09-01 02:00:00+00', DATE '2026-09-01'),
  ('ORD-1003', 'CUS-001', 'paid',    79000, TIMESTAMP '2026-09-02 01:00:00+00', DATE '2026-09-02');
```

![세 개 주문 행을 추가하는 BigQuery INSERT 문](/wiki-assets/playbook/bigquery-data-write-and-cdc-guide/01-insert-seed-rows.png)

> **화면 1.** 대상 컬럼과 값을 명시한 초기 INSERT.

![BigQuery INSERT 작업 완료와 영향받은 세 행](/wiki-assets/playbook/bigquery-data-write-and-cdc-guide/02-insert-success.png)

> **화면 2.** 작업 성공 여부와 영향받은 행 수 확인.

## 2단계. MERGE로 기존 행 갱신과 신규 행 삽입

스테이징 입력에 기존 키 `ORD-1002`와 새 키 `ORD-1004`를 함께 넣습니다. 기존 주문은 갱신하고 새 주문은 삽입합니다.

**파티션을 제한한 MERGE**
```sql
MERGE `hurdlers-bq-dev-guide-260902.developer_guide.orders` AS target
USING (
  SELECT 'ORD-1002' AS order_id, 'CUS-002' AS customer_id,
         'paid' AS order_status, NUMERIC '32000' AS order_amount,
         TIMESTAMP '2026-09-02 03:00:00+00' AS updated_at, DATE '2026-09-01' AS order_date
  UNION ALL
  SELECT 'ORD-1004', 'CUS-003', 'paid', NUMERIC '59000',
         TIMESTAMP '2026-09-02 04:00:00+00', DATE '2026-09-02'
) AS source
ON target.order_id = source.order_id
AND target.order_date BETWEEN DATE '2026-09-01' AND DATE '2026-09-02'
WHEN MATCHED AND source.updated_at >= target.updated_at THEN
  UPDATE SET
    customer_id = source.customer_id,
    order_status = source.order_status,
    order_amount = source.order_amount,
    updated_at = source.updated_at,
    order_date = source.order_date
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, order_status, order_amount, updated_at, order_date)
  VALUES (source.order_id, source.customer_id, source.order_status,
          source.order_amount, source.updated_at, source.order_date);
```

![기존 주문 갱신과 새 주문 삽입을 수행하는 MERGE 문](/wiki-assets/playbook/bigquery-data-write-and-cdc-guide/03-merge-upsert.png)

> **화면 3.** MATCHED는 UPDATE, NOT MATCHED는 INSERT.

> **주의**
>
> 하나의 대상 행에 여러 소스 행이 매칭되면서 UPDATE·DELETE를 수행하면 MERGE가 오류를 낼 수 있습니다. 스테이징에서 `ROW_NUMBER()`로 키별 최신 한 건을 고른 뒤 MERGE하세요.

## 3단계. 영향받은 행과 최종 상태 검증

작업 완료 메시지의 영향받은 행 수를 확인하고, 키 기준 중복과 변경된 값을 별도 쿼리로 검증합니다.

![MERGE 성공 메시지와 영향받은 두 행](/wiki-assets/playbook/bigquery-data-write-and-cdc-guide/04-merge-success.png)

> **화면 4.** 기존 한 행 갱신 + 신규 한 행 삽입으로 총 두 행 영향.

![MERGE 후 네 개 행이 표시된 orders 테이블](/wiki-assets/playbook/bigquery-data-write-and-cdc-guide/05-merge-after-preview.png)

> **화면 5.** ORD-1002 금액·상태 변경과 ORD-1004 신규 행 확인.

**기본 키 중복 검증**
```sql
SELECT order_id, COUNT(*) AS row_count
FROM `hurdlers-bq-dev-guide-260902.developer_guide.orders`
GROUP BY order_id
HAVING COUNT(*) > 1;
```

결과가 0행이어야 합니다. BigQuery의 기본 키는 **NOT ENFORCED**이므로 이 검증과 파이프라인의 소스 중복 제거가 실제 무결성을 책임집니다.

## Storage Write API CDC의 특이점

CDC 입력에는 테이블 스키마 외에 의사 컬럼 `_CHANGE_TYPE`을 사용합니다. 값은 `UPSERT` 또는 `DELETE`입니다. 순서가 중요한 경우 `_CHANGE_SEQUENCE_NUMBER`를 함께 보내 동일 키 변경의 적용 순서를 표현합니다.

| 항목 | 운영 의미 |
| --- | --- |
| **기본 키 필수** | 어떤 기존 행을 바꿀지 식별. BigQuery가 유일성을 강제하지 않음 |
| **UPSERT는 전체 행** | 부분 PATCH가 아니라 대상 행을 새 값으로 대체하는 모델로 설계 |
| **DELETE** | 기본 키로 기존 행 제거 |
| **시퀀스 번호** | 지연 도착한 오래된 변경이 최신 값을 덮지 않도록 순서 제공 |
| **max staleness** | 백그라운드 적용 비용과 조회 최신성 사이의 허용 지연 시간 |

> **주의**
>
> CDC가 활성화된 테이블에는 mutating DML 사용 제약이 있습니다. 같은 테이블에 실시간 CDC와 배치 UPDATE·DELETE·MERGE를 혼용하기 전에 현재 [제한사항](https://cloud.google.com/bigquery/docs/change-data-capture)을 확인하고, 가능하면 테이블별 주 쓰기 경로를 하나로 정합니다.

CDC는 “API 요청이 성공했다”와 “모든 쿼리에서 변경이 즉시 보인다”가 같은 의미가 아닐 수 있습니다. 허용 지연, 적용 watermark, 재시도·중복 정책을 모니터링 항목으로 둡니다.

## 운영 체크리스트

- 배치인지 실시간인지에 따라 MERGE와 CDC 중 주 경로를 선택했는가
- 스테이징 소스가 키별 한 행으로 중복 제거됐는가
- 최신 이벤트만 갱신하도록 `updated_at` 또는 시퀀스를 비교하는가
- 대상 파티션 조건이 MERGE에 포함되어 전체 테이블 스캔을 피하는가
- 기본 키 NOT ENFORCED를 전제로 중복 검증 쿼리와 알림이 있는가
- 재시도 시 같은 입력을 다시 보내도 결과가 동일한가
- 영향받은 행 수, 오류 행, CDC 적용 지연을 모니터링하는가

대규모 증분 MERGE의 파티션 pruning 방식은 Google Cloud의 [증분 적재 최적화 예시](https://cloud.google.com/blog/products/data-analytics/optimizing-your-bigquery-incremental-data-ingestion-pipelines)를 참고할 수 있습니다.

기존 주문은 갱신하고 신규 주문은 추가하는 MERGE를 Query Job으로 실행한 뒤 영향받은 행 수를 확인합니다.

## 자주 묻는 질문

### MERGE 한 번이면 트랜잭션처럼 동작하나요?

MERGE 문 자체는 원자적으로 적용됩니다. 하지만 스테이징 적재부터 후속 검증까지 여러 작업을 묶는 전체 파이프라인은 별도 실패·재시도 설계가 필요합니다.

### UPDATE만 자주 실행해도 되나요?

지원되지만 분석형 저장소 특성상 대량 행을 반복 수정하는 패턴은 비용과 처리량을 검토해야 합니다. 변경 주기와 크기에 따라 MERGE, 파티션 교체, CDC 중 더 적합한 방식을 선택합니다.

### PRIMARY KEY를 선언했으니 중복 INSERT가 막히나요?

아닙니다. BigQuery의 기본 키는 NOT ENFORCED입니다. 선언을 신뢰하는 옵티마이저가 잘못된 결과를 만들지 않도록 실제 데이터 유일성은 적재 측에서 지켜야 합니다.

## Navigation

- [전체 플레이북 Markdown sitemap](/wiki/playbook/sitemap.md)

### BigQuery

- [BigQuery](/wiki/playbook/bigquery-checklist)
- [BigQuery 온보딩](/wiki/playbook/bigquery-checklist/bigquery-onboarding-guide)
- [BigQuery 인증 설정](/wiki/playbook/bigquery-checklist/how-to-set-up-bigquery-authentication)
- [BigQuery 데이터셋·테이블 설계](/wiki/playbook/bigquery-checklist/bigquery-dataset-and-table-design-guide)
- [BigQuery 쿼리·비용 제어](/wiki/playbook/bigquery-checklist/bigquery-query-and-cost-control-guide)
- [BigQuery 쓰기·MERGE·CDC](/wiki/playbook/bigquery-checklist/bigquery-data-write-and-cdc-guide) (현재 문서)
- [BigQuery 코드 예제 모음](/wiki/playbook/bigquery-checklist/bigquery-code-examples)
- [BigQuery로 들어오는 GA4 데이터](/wiki/playbook/bigquery-checklist/what-is-ga4-data-in-bigquery)
- [BigQuery Studio 인터페이스 이해하기](/wiki/playbook/bigquery-checklist/what-is-bigquery-studio-interface)
- [BigQuery 예상 비용](/wiki/playbook/bigquery-checklist/how-to-estimate-bigquery-costs)
- [무료 버전(샌드박스) 해제해야 하는 이유](/wiki/playbook/bigquery-checklist/why-upgrade-from-bigquery-sandbox)
- [GA4 BigQuery 데이터를 왜 평탄화해야 하나](/wiki/playbook/bigquery-checklist/why-flatten-ga4-bigquery-data)
- [Log Router란?](/wiki/playbook/bigquery-checklist/what-is-log-router)
- [Pub/Sub이란?](/wiki/playbook/bigquery-checklist/what-is-pubsub)
- [Cloud Functions이란?](/wiki/playbook/bigquery-checklist/what-is-cloud-function-and-run)
- [GA4 export 시점에 예약 쿼리 자동 실행하기](/wiki/playbook/bigquery-checklist/how-to-trigger-scheduled-query-on-ga4-export)
- [테이블 정의서는 왜 필요한가](/wiki/playbook/bigquery-checklist/why-table-definition-doc)
- [Event_Flat 테이블 이해하기](/wiki/playbook/bigquery-checklist/what-is-event-flat-table)
- [Item_Performance 테이블 이해하기](/wiki/playbook/bigquery-checklist/what-is-item-performance-table)