Data Vault Modeling

https://www.databricks.com/kr/glossary/data-vault

데이터 볼트 (Data Vault)

기본적인 내용은 databricks 사이트에서 잘 설명되어 있습니다.

예시와 함께 간단히 살펴보면, Data Vault 모델링은 장기적인 데이터 저장과 변화에 대한 유연성을 제공하는 데이터베이스 모델링 방법입니다. 이 방법은 여러 운영 시스템에서 들어오는 데이터를 효과적으로 관리하고 추적할 수 있도록 설계되었습니다.

Data Vault 모델링의 주요 구성 요소

Data Vault 모델은 세 가지 주요 구성 요소로 이루어져 있습니다:

1. Hub (허브)
2. Link (링크)
3. Satellite (새틀라이트)

이 구성 요소들을 사용하여 데이터 모델을 구축하는 방법을 예시와 함께 살펴보겠습니다.

예시: 온라인 쇼핑몰 데이터 모델

온라인 쇼핑몰의 주문 시스템을 Data Vault 모델링으로 구현하는 예시를 들어보겠습니다.

1. Hub (허브)

허브는 비즈니스 키를 저장하는 엔티티입니다. 예를 들어:

• H_Customer (고객 허브)
  • Customer_ID (비즈니스 키)
  • Load_Date
  • Record_Source
• H_Product (제품 허브)
  • Product_ID (비즈니스 키)
  • Load_Date
  • Record_Source
• H_Order (주문 허브)
  • Order_ID (비즈니스 키)
  • Load_Date
  • Record_Source

2. Link (링크)

링크는 허브 간의 관계를 나타냅니다. 예를 들어:

• L_Order_Customer (주문-고객 링크)
  • Order_ID (외래 키)
  • Customer_ID (외래 키)
  • Load_Date
  • Record_Source
• L_Order_Product (주문-제품 링크)
  • Order_ID (외래 키)
  • Product_ID (외래 키)
  • Quantity
  • Load_Date
  • Record_Source

3. Satellite (새틀라이트)

새틀라이트는 허브나 링크에 대한 설명적 속성을 저장합니다. 예를 들어:

• S_Customer_Info (고객 정보 새틀라이트)
  • Customer_ID (외래 키)
  • Name
  • Email
  • Address
  • Load_Date
  • Record_Source
• S_Product_Details (제품 상세 새틀라이트)
  • Product_ID (외래 키)
  • Product_Name
  • Description
  • Price
  • Load_Date
  • Record_Source
• S_Order_Status (주문 상태 새틀라이트)
  • Order_ID (외래 키)
  • Status
  • Order_Date
  • Load_Date
  • Record_Source

이 구조를 통해 Data Vault 모델은 다음과 같은 이점을 제공합니다:

1. 유연성: 새로운 데이터 소스나 속성을 쉽게 추가할 수 있습니다.
2. 추적성: 모든 데이터에 Load_Date와 Record_Source가 포함되어 있어 데이터의 출처와 변경 이력을 추적할 수 있습니다.
3. 확장성: 병렬 로딩이 가능하여 대규모 구현에서도 성능을 유지할 수 있습니다.

Data Vault 모델링은 "사실의 단일 버전"을 저장하는 접근 방식을 취하며, 이는 비즈니스 규칙에 맞지 않는 데이터도 포함하여 모든 데이터를 저장한다는 의미입니다. 이를 통해 데이터의 완전성을 유지하고 향후 분석에 활용할 수 있는 가능성을 열어둡니다.

이 다이어그램은 데이터 볼트 모델링 기법을 사용하여 고객, 상품, 가맹점 테이블을 시각화한 것입니다. 다이어그램의 주요 특징은 다음과 같습니다:

1. 허브(Hub) 테이블:
   • 허브_고객, 허브_상품, 허브_가맹점
   • 각 허브는 해시키, 비즈니스키, 데이터 출처, 적재 일시, 레코드 출처를 포함합니다.
2. 링크(Link) 테이블:
   • 링크_고객_가맹점, 링크_상품_가맹점, 링크_고객_상품
   • 각 링크는 해당하는 허브들의 해시키를 외래키로 가지며, 자체 해시키도 포함합니다.
3. 새틀라이트(Satellite) 테이블:
   • 각 허브와 링크에 대한 새틀라이트 테이블이 있습니다.
   • 새틀라이트 테이블은 변경 가능한 속성 정보를 저장하며, 유효 시작/종료 일시를 포함합니다.
4. 관계:
   • 허브와 링크 테이블 간의 관계는 "관계"로 표시되었습니다.
   • 허브/링크와 해당 새틀라이트 테이블 간의 관계는 "속성"으로 표시되었습니다.

이 데이터 모델을 가지고 매출 상위 10개 제품의 매출금액을 뽑는 쿼리를 작성해 보겠습니다.

WITH store_seoul AS (
    SELECT s.store_hash_key
    FROM HUB_STORE hs
    JOIN SAT_STORE s ON hs.store_hash_key = s.store_hash_key
    WHERE s.store_address = '서울'
    AND s.effective_to = '9999-12-31' -- 현재 유효한 레코드
),
laptop_products AS (
    SELECT p.product_hash_key
    FROM HUB_PRODUCT hp
    JOIN SAT_PRODUCT p ON hp.product_hash_key = p.product_hash_key
    WHERE p.product_category = '노트북'
    AND p.effective_to = '9999-12-31' -- 현재 유효한 레코드
),
gangnam_customers AS (
    SELECT c.customer_hash_key
    FROM HUB_CUSTOMER hc
    JOIN SAT_CUSTOMER c ON hc.customer_hash_key = c.customer_hash_key
    WHERE c.customer_address = '강남'
    AND c.effective_to = '9999-12-31' -- 현재 유효한 레코드
),
qualified_purchases AS (
    SELECT 
        lcp.customer_product_hash_key,
        lcp.product_hash_key,
        scp.purchase_count,
        sp.product_name,
        sp.product_price
    FROM LINK_CUSTOMER_PRODUCT lcp
    JOIN SAT_CUSTOMER_PRODUCT scp ON lcp.customer_product_hash_key = scp.customer_product_hash_key
    JOIN laptop_products lp ON lcp.product_hash_key = lp.product_hash_key
    JOIN gangnam_customers gc ON lcp.customer_hash_key = gc.customer_hash_key
    JOIN LINK_PRODUCT_STORE lps ON lcp.product_hash_key = lps.product_hash_key
    JOIN store_seoul ss ON lps.store_hash_key = ss.store_hash_key
    JOIN SAT_PRODUCT sp ON lcp.product_hash_key = sp.product_hash_key
    WHERE scp.effective_to = '9999-12-31' -- 현재 유효한 레코드
    AND sp.effective_to = '9999-12-31' -- 현재 유효한 레코드
)
SELECT 
    qp.product_name,
    qp.product_price,
    SUM(qp.purchase_count) as total_purchases,
    SUM(qp.purchase_count * qp.product_price) as total_revenue
FROM qualified_purchases qp
GROUP BY qp.product_hash_key, qp.product_name, qp.product_price
ORDER BY total_revenue DESC
LIMIT 10;

이 SQL 쿼리는 요청하신 정보를 추출하기 위해 여러 단계로 구성되어 있습니다. 각 단계별 설명은 다음과 같습니다:

1. store_seoul: 서울에 위치한 매장들의 hash key를 추출합니다.
2. laptop_products: '노트북' 카테고리에 속하는 제품들의 hash key를 추출합니다.
3. gangnam_customers: 강남에 거주하는 고객들의 hash key를 추출합니다.
4. qualified_purchases: 위의 조건들(서울 매장, 노트북 제품, 강남 고객)을 모두 만족하는 구매 정보를 추출합니다.
5. 마지막으로, 이 정보를 바탕으로 상품별 총 구매 횟수와 매출을 계산하고, 매출 기준으로 상위 10개 상품을 선택합니다.

이 쿼리를 실행하면 다음과 같은 결과를 얻을 수 있습니다:

• 상품 이름
• 상품 가격
• 총 구매 횟수
• 총 매출

그리고 이 결과는 총 매출을 기준으로 내림차순 정렬되어 상위 10개 상품만 표시됩니다.

데이터 볼트 모델의 특성상 이 쿼리는 복잡해 보일 수 있지만, 이 구조는 데이터의 이력 관리와 유연성 면에서 큰 장점을 가집니다. 예를 들어, 특정 시점의 데이터를 조회하거나 데이터의 변경 이력을 추적하는 것이 용이합니다.