기초 · CH02

SQL과 데이터 모델링

데이터 엔지니어의 첫 도구는 여전히 SQL입니다. 주문(사실)과 고객·상품(차원)으로 나눈 스타 스키마로 집계하고, 윈도 함수를 쓰고, 실무에서 숫자를 틀리게 만드는 대표 함정인 조인 뻥튀기를 직접 확인합니다.


사실 테이블과 차원 테이블

사실(fact)

일어난 일 한 건이 한 행. 주문, 결제, 클릭. 행이 계속 늘어납니다.

차원(dimension)

사실을 설명하는 정보. 고객의 지역, 상품의 카테고리. 상대적으로 적고 천천히 바뀝니다.

스타 스키마

사실 테이블 하나를 가운데 두고 차원 테이블을 키로 붙이는 구조. 분석 쿼리가 단순해집니다.

1. 지역×카테고리 매출

SELECT c.region, p.category, sum(o.qty * p.price) AS revenue, count(*) AS orders
FROM orders o
JOIN customers c USING (customer_id)
JOIN products  p USING (product_id)
WHERE o.status <> 'cancelled'
GROUP BY ALL ORDER BY revenue DESC LIMIT 5;
│ region │ category │ revenue  │ orders │
│ 경기   │ 주방     │ 19986500 │    815 │
│ 광주   │ 주방     │ 19385000 │    799 │
│ 경기   │ 문구     │ 17935500 │    822 │
│ 경기   │ 음료     │ 17551000 │    817 │
│ 부산   │ 주방     │ 17484000 │    744 │

GROUP BY ALL은 SELECT에서 집계하지 않은 컬럼으로 묶어 주는 DuckDB 문법입니다. Snowflake, Databricks SQL에도 같은 문법이 있습니다.

2. 윈도 함수: 고객별 몇 번째 주문인가

SELECT customer_id, order_id, ordered_at,
       row_number() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS nth_order,
       min(ordered_at) OVER (PARTITION BY customer_id) AS first_order_at
FROM orders WHERE customer_id = 42 ORDER BY ordered_at LIMIT 5;

42번 고객의 첫 주문은 11월 2일 08:18이고, 이후 주문마다 순번이 1, 2, 3…으로 붙습니다. 재구매율, 첫 구매 후 이탈 같은 지표가 모두 이 패턴에서 나옵니다.

3. 조인 뻥튀기 함정

고객 10명 중 1명꼴로 쿠폰이 2장씩 있는 coupons 테이블을 주문에 붙이고 행 수를 세 봤습니다.

세는 방법결과
주문 테이블만 count(*)32,200
쿠폰을 LEFT JOIN한 뒤 count(*)35,412
쿠폰을 붙이고 count(DISTINCT order_id)32,200

고객 한 명에 쿠폰이 여러 장이면 그 고객의 주문 행이 쿠폰 수만큼 복제됩니다. 매출 합계도 같은 비율로 부풀어 오릅니다. 1:N 관계를 붙이기 전에는 키가 유일한지 확인하거나, 먼저 집계한 뒤 붙입니다. 08장에서 이 확인을 dbt 테스트로 자동화합니다.

이것만 기억하세요

사실과 차원을 나누면 쿼리가 단순해집니다. 조인한 뒤 행 수가 늘었다면 숫자를 믿기 전에 키가 유일한지부터 확인합니다.

다음
03. CSV와 Parquet
이어보기 →