ZrobDev/데이터 모델링

데이터 모델링은(는) 무엇을 하나요?

대상: PostgreSQL, BigQuery, Snowflake. 변환: dbt. 로컬 프로토타이핑: DuckDB 2.0. dbt 메커니즘(모델 설정, 테스트 YAML, 스냅샷, 의미 계층, v2 마이그레이션)은 이 리포지토리의 de-vendors 스킬이 가져오는 공식 dbt Labs 스킬을 사용하세요. 이 스킬은 dbt가 어떻게 표현하는지가 아니라 무엇을 모델링할지 결정합니다.

절차

1. 비즈니스 질문과 소비자(대시보드, ML 기능, 재무 마감)를 명시합니다.
2. 모든 컬럼보다 먼저 Grain을 한 문장으로 작성합니다: "주문 라인당 하루에 하나의 행." 문장에 "또는"이 필요하면 두 개의 테이블입니다.
3. 팩트 유형(아래 참조)을 선택한 다음, 해당 Grain에서 참인 차원을 나열합니다.
4. Grain 키(자연 컬럼 또는 이들의 해시)를 지정합니다. 여기에는 unique + not_null이 적용됩니다.
5. 샘플에서 DuckDB로 프로토타이핑하고 팬아웃 검사를 실행한 다음 포팅합니다.
6. 완료로 선언하기 전에 "테스트할 사항" 아래에 나열된 테스트를 추가합니다.

Grain

• Grain이 먼저이고, 컬럼은 그 다음입니다. 모든 측정값과 모든 외래 키는 해당 Grain에서 단일 값을 가져야 합니다. 그렇지 않은 컬럼은 다른 테이블에 속하며, 행을 조용히 곱하기 때문입니다.
• 소스가 지원하는 가장 낮은 Grain을 유지하세요. 집계는 상세 데이터에서 파생 가능하지만, 상세 데이터는 집계에서 파생될 수 없습니다. 롤업은 별도의 마트로 구축하세요.
• 하나의 테이블에서 Grain을 혼합하지 마세요(주문 라인 행에 반복되는 주문 헤더 합계). 라인 행에 걸쳐 합산된 헤더 금액은 이중 계산됩니다. 헤더 및 라인 팩트는 별도로 모델링하세요.

팩트 vs 차원

• 팩트: 이벤트 또는 측정값입니다. 가능하면 숫자이고 가산적이며, 차원 키와 비정규 차원(주문 번호)으로 키가 지정됩니다.
• 차원: 필터링 및 그룹화에 사용되는 설명적 컨텍스트입니다. 넓고 비정규화되어도 괜찮습니다.
• 각 측정값을 분류합니다: 가산적(모든 차원에 걸쳐 합산 가능), 반가산적(잔고: 시간에 따라 합산 불가), 비가산적(비율: 분자와 분모를 저장하고 집계 후 나눗셈).
• 팩트 유형:
  • 트랜잭션: 이벤트당 하나의 행. 기본 선택.
  • 주기적 스냅샷: 기간당 엔티티당 하나의 행(일일 잔고). 상태가 중요하고 이벤트가 드물거나 늦을 때 사용합니다.
  • 누적 스냅샷: 마일스톤당 날짜 컬럼이 있는 프로세스 인스턴스당 하나의 행으로, 제자리에서 업데이트됩니다. 알려진 라이프사이클이 있는 파이프라인(주문 접수, 배송, 배달 완료)에 사용합니다. 업데이트를 예상하고 로드를 멱등성(idempotent) 있게 만드세요.
  • 팩트리스 팩트: 측정값이 없는 이벤트 또는 커버리지(출석, 자격). 행을 계산합니다.

스키마 형태

• 기본적으로 스타 스키마: 하나의 팩트, 평면 차원. 눈꽃송이 스키마(정규화된 차원)는 하위 차원이 공유되고 크거나 다른 팀에서 유지 관리하는 경우에만 사용합니다. 추가 조인은 가독성과 BI 도구 호환성을 떨어뜨립니다.
• 적합 차원: 모든 팩트가 공유하는 하나의 dim_customer, dim_date로, 동일한 키와 속성 의미를 가집니다. 한 번 구축하세요. 다른 규칙을 가진 두 번째 고객 테이블은 결함입니다. 교차 팩트 보고서가 일치하지 않기 때문입니다.
• 역할 수행 차원(order_date, ship_date)은 별도의 FK 컬럼을 통해 하나의 dim_date를 재사용합니다.
• 정크/소형 차원: 낮은 카디널리티 플래그용. 팩트에 8개의 부울을 남기지 마세요.
• 알 수 없는 멤버: 각 차원에 행(키 -1 또는 'unknown'의 해시)을 유지하여 늦게 도착하는 팩트가 행을 유지하도록 합니다. 차원에 대한 내부 조인은 팩트를 조용히 삭제합니다. 왼쪽 조인과 알 수 없는 키로의 병합(coalesce)을 사용하세요.

키

• 자연 키: 소스 시스템의 비즈니스 식별자. 항상 속성으로 유지하세요.
• 대리 키: 자연 키가 복합적이거나, 변경될 수 있거나, 여러 소스에서 오거나, 차원이 SCD2(하나의 엔티티에 여러 버전이 있음)인 경우 추가합니다. dbt 스타일 웨어하우스에서는 시퀀스보다 자연 키(SCD2의 경우 valid_from 추가)의 결정론적 해시를 선호하여 재실행 및 병렬 빌드가 일치하도록 합니다.
• 대리 키를 비즈니스 사용자에게 식별자로 노출하지 마세요. 재구축 시 변경됩니다.

느리게 변경되는 차원(SCD)

• 유형 1(덮어쓰기): 기록에 분석적 가치가 없거나 변경이 수정인 경우.
• 유형 2(버전당 새 행, valid_from / valid_to / is_current): 이벤트 시간(구매 시 고객 등급) 기준 속성 값으로 팩트를 보고해야 하는 경우.
• 스냅샷은 소스가 기록을 유지하지 않고 앞으로의 변경 사항만 캡처하면 되는 경우에 충분합니다. 스냅샷은 캡처 메커니즘이며, SCD2 차원은 여전히 그 위의 모델입니다. 소스에 이미 기록이나 이벤트 로그가 있는 경우 스냅샷 대신 여기에서 유효 기간을 파생하세요.
• 팩트를 SCD2에 키와 event_ts >= valid_from and event_ts < valid_to로 조인합니다(반개방 범위, 중복 없음). 자연 키당 중복되거나 간격이 있는 범위가 없는지 테스트합니다.
• 유형 3 또는 속성별 유형 2: 단일 "이전 값" 컬럼이 질문에 답하는 경우에만 사용합니다. 그렇지 않으면 건너뜁니다.

팬아웃 및 이중 계산

원인: 일치하지 않는 Grain의 테이블을 조인하여 "일" 쪽이 실제로는 다(many)가 되는 경우. 증상: 소스보다 큰 합계, 왼쪽 조인 후 count(*) 증가, 평균 이동.

• 탐지: 모든 조인 전후에 count(*) 및 count(distinct grain_key)를 비교합니다. 차원에 조인한 후에는 동일해야 합니다. 차원 측의 조인 키에 대한 고유성 테스트(select key from dim group by 1 having count(*) > 1).
• 조정: 동일한 필터에 대해 마트의 측정값 합계가 소스의 합계와 같아야 합니다.
• 수정: 조인 전에 다(many) 쪽을 팩트의 Grain으로 집계하거나, 할당 가중치가 있는 브리지 테이블을 통해 조인합니다. 결과를 distinct하여 숨기지 마세요.
• 다대다(고객 대 계좌, 제품 대 카테고리): 가중치 요소가 있는 브리지 테이블을 사용하거나, 기본값을 선택하고 명시합니다.
• 이러한 검사를 위한 SQL: references/fan-out-checks.md를 참조하세요.

계층화

• raw/staging: 소스 테이블당 하나의 스테이징 모델. 이름 변경, 캐스트, 중복 제거, 조인 없음, 비즈니스 로직 없음. raw는 변경 불가능하게 유지합니다.
• intermediate: 조인, 재구성, 2개 이상의 마트에서 재사용되는 공유 로직. BI에 노출되지 않습니다.
• marts: 팩트 및 차원, 마트당 하나의 비즈니스 프로세스, 문서화, 테스트, 계약됨. 마트만 공개됩니다.
• 종속성은 단방향으로 흐릅니다. 마트는 raw를 읽지 않고, 스테이징은 스테이징을 읽지 않습니다.
• 스테이징은 뷰로, 마트는 테이블로 구체화합니다(크고 Grain 키가 안정적인 경우 증분).

명명 규칙

• stg_<source>__<entity>, int_<entity>_<verb>, fct_<process>, dim_<entity>; snake_case; 단수 또는 복수 일관되게 사용(하나 선택).
• 키 <entity>_id (자연), <entity>_key (대리); 타임스탬프는 UTC 기준 <event>_at; 날짜는 <event>_date; 부울은 is_ / has_; 금액은 단위 또는 통화 포함(amount_usd).

테스트할 사항

• Grain 키: 모든 모델에 대해 unique + not_null; 복합 Grain의 경우 조합을 테스트합니다.
• 팩트의 모든 FK: not_null(또는 알 수 없는 멤버) 및 차원에 대한 관계(relationships) 테스트.
• 상태/유형 컬럼에 대한 허용 값(accepted_values) 테스트. 새 값은 잘못 분류되는 대신 명확하게 실패합니다.
• 각 마트의 소스에 대한 행 개수 및 합계 조정 테스트, 명시된 허용 오차 포함(재무의 경우 0).
• SCD2: 중복 범위 없음, 자연 키당 정확히 하나의 현재 행.
• 소스에 대한 신선도(freshness) 테스트. 실패할 수 있음을 증명하기 위해 샘플 데이터에서 각 테스트를 한 번씩 중단시킵니다.

OLTP 스키마 검토(PostgreSQL)

• 트랜잭션 테이블의 경우 3NF로 정규화합니다. 측정된 읽기 경로에 대해서만 역정규화합니다. 모든 테이블에는 기본 키가 있습니다.
• 외래 키 컬럼에 인덱스를 생성합니다. Postgres는 자동으로 생성하지 않으며, 인덱싱되지 않은 FK는 부모 삭제 및 조인이 자식 테이블을 스캔하게 만듭니다.
• timestamptz, 금액에는 numeric, 임의의 varchar 제한 대신 text를 사용합니다. 앱 코드만으로가 아니라 NOT NULL, CHECK, UNIQUE, FK 제약 조건으로 규칙을 적용합니다.
• 대량 삽입이 많은 테이블의 경우 무작위 UUIDv4보다 bigint ID 또는 UUIDv7 키를 선호합니다(인덱스 지역성).
• 멀티 테넌트: 모든 테넌트 소유 테이블에 tenant_id를 배치하고, 복합 키 및 인덱스의 선행 컬럼으로 만들고, FK(복합 FK)에 포함시켜 교차 테넌트 참조가 불가능하도록 하고, 행 수준 보안으로 지원합니다. 기본적으로 tenant_id가 있는 공유 스키마를 사용합니다. 스키마 또는 데이터베이스별 테넌트는 엄격한 격리 또는 테넌트별 복원이 필요한 경우에만 사용합니다.
• 소프트 삭제: 부분 고유 인덱스(where deleted_at is null)를 사용하여 고유성을 유지합니다.

함정 및 관찰된 실패 모드: references/gotchas.md