중첩·반정형 데이터
JSON, STRUCT, LIST, VARIANT를 분석 가능한 스키마로 바꾸는 DuckDB 모델링 기준을 정리합니다.
핵심 요약
- DuckDB는 JSON, STRUCT, LIST, MAP, 1.5의 VARIANT를 제공하지만 publish mart는 해석 가능한 typed column으로 고정해야 합니다.
- JSON은 보관 후 path 추출, 동일 key 구조는 STRUCT, 반복 값은 LIST, 매우 가변 스키마는 VARIANT처럼 임시 분석과 publish mart의 타입 선택을 분리합니다.
- STRUCT는 모든 row가 같은 key layout을 쓰는 제약 덕에 분석 성능과 타입 일관성이 좋고, struct_pack과 dot 접근으로 다룹니다.
- 배열은 UNNEST 전에 list_count 분위수로 길이 분포를 보고 row explosion을 미리 가늠합니다.
- raw payload 보존, mart에 typed projection, key registry, JSON(0-based)·LIST(1-based) indexing 차이 문서화, try_cast 실패 카운트 병행이 운영 규칙입니다.
분석 데이터는 갈수록 반정형으로 들어옵니다. 이벤트 properties, webhook payload, product metadata, API response는 애초에 flat table이 아닙니다.
DuckDB가 JSON, STRUCT, LIST, MAP, 1.5의 VARIANT 같은 타입을 제공하긴 하지만, publish mart는 해석 가능한 컬럼으로 고정해야 합니다.
타입 선택
| 입력 | 임시 분석 | publish mart |
|---|---|---|
| JSON payload | JSON으로 보관 후 path 추출 | 필요한 key를 typed column으로 승격 |
| 동일 key 구조 | STRUCT | STRUCT 또는 flat column |
| 반복 값 | LIST | explode table 또는 list 유지 |
| schema가 매우 가변 | VARIANT 검토 | 소비자별 typed projection 제공 |
JSON 추출
SELECT
event_id,
payload->>'$.plan' AS plan,
try_cast(payload->>'$.amount' AS DECIMAL(18,2)) AS amount,
payload->'$.items' AS items_json
FROM raw_events;JSONPath는 field lookup과 array lookup에만 씁니다. 복잡한 필터 로직은 JSONPath에 숨기지 말고 SQL로 드러냅니다.
STRUCT
DuckDB STRUCT는 모든 row가 같은 key layout을 쓰는 중첩 타입입니다. 이 제약 덕에 분석 성능과 타입 일관성이 좋아집니다.
SELECT
customer_id,
struct_pack(
plan := plan,
billing_cycle := billing_cycle,
mrr := mrr
) AS subscription
FROM customer_snapshot;SELECT
customer_id,
subscription.plan,
subscription.mrr
FROM customer_snapshot_struct;Unnest 기준
반복 배열은 "row를 늘릴지"를 먼저 결정합니다.
SELECT
order_id,
item.item_id,
item.quantity
FROM orders,
UNNEST(items) AS t(item);row explosion이 예상되면 먼저 배열 길이 분포를 봅니다.
SELECT
quantile_cont(list_count(items), [0.5, 0.9, 0.99]) AS item_count_quantiles
FROM orders;반정형 운영 규칙
| 규칙 | 이유 |
|---|---|
| raw payload를 보존한다 | 새 key 재처리와 디버깅 |
| mart에는 typed projection을 둔다 | BI와 downstream 안정성 |
| key registry를 관리한다 | payload drift 감지 |
| JSON indexing 차이를 문서화한다 | JSON은 0-based, LIST/ARRAY는 1-based |
try_cast와 실패 카운트를 함께 둔다 | 조용한 null 전환 방지 |