중첩·반정형 데이터

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 전환 방지

참고 자료

중첩·반정형 데이터

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 실패 카운트 병행이 운영 규칙입니다.

타입 선택

입력	임시 분석	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 전환 방지

타입 선택

JSON 추출

STRUCT

Unnest 기준

반정형 운영 규칙

참고 자료

목차

중첩·반정형 데이터

타입 선택

JSON 추출

STRUCT

Unnest 기준

반정형 운영 규칙

참고 자료

목차