프로그래머스 데이터분석 데브코스 1기 - 69일차

주제

1.  데이터 정규화
2. dbt 소개
3. dbt  모델의 input
4. dbt 모델의 output
5. dbt Seeds / Sources / Tests / Documentation

1. 데이터 정규화

1.1 데이터 정규화의 종류

• 데이터베이스 정합성을 쉽게 유지하고 레코드들을 수정/적재/삭제를 용이하게 하는 것
• 1NF(First Normal Form) :
  • 한 셀에는 하나의 값만 있어야 함(atomicity)
  • Primary key가 있어야 함
  • 중복된 키나 레코드들이 없어야 함
  • 목표 : 중복을 제거하고 원자성을 갖는 것
• 2NF(Second Normal Form) :
  • 1NF를 만족하면서 primary key를 중심으로 의존결과를 알 수 있어야 함
  • 부분적인 의존도가 없어야 함
  • 모든 부가 속성들은 primary key를 가지고 찾을 수 있어야 함
• 3NF(Third Normal Form) :
  • 2NF를 만족하면서 전이적 부분 종속성이 없어야 함

 

1.2 Dimension의 변화

• 데이터 웨어하우스나 데이터 레이크에서는 모든 테이블들의 히스토리를 유지하는 것이 중요함
  • 보통 두 개의 timestamp를 갖는 것이 좋음(created_at, updated_at)
• 컬럼의 성격에 따라 유지 방법이 달라짐
• Production Table(OLTP) ↔ DW Table(OLAP)
• 현재 데이터만 유지 vs. 처음부터 지금까지의 히스토리도 유지

1. SCD Type 0 
   • 한번 쓰고 나면 바꿀 이유가 없는 경우
   • 한번 정해지면 갱신되지 않고 고정되는 필드들 (회원 등록일, 제품 첫 구매일)
2. SCD Type 1
   • 데이터가 새로 생기면 덮어쓰여지는 컬럼들
   • 처음 레코드 생성 시에는 존재하지 않지만 나중에 생기면서 채우는 경우
3. SCD Type 2 
   • 특정 엔티티에 대한 데이터가 새로운 레코드로 추가되어야 하는 경우 (고객의 등급 변화)
   • 변경 시간도 같이 추가되어야 함
4. SCD Type 3
   • SCD Type 2의 대안으로 특정 엔티티 데이터가 새로운 컬럼으로 추가되는 경우
5. SCD Type 4
   • 특정 엔티티에 대한 데이터를 새로운 Dimension 테이블에 저장하는 경우 (Type 2의 변종)

 

2.  dbt 소개

• dbt(Data build tool) : ELT용 오픈소스로 다양한 데이터 웨어하우스를 지원함
• 구성 컴포넌트 :
  • 데이터 모델(models) : 테이블들을 몇 개의 티어로 관리(일종의 CTAS), Lineage 트래킹. 테이블, 뷰, CTE 등
  • 데이터 품질 검증(tests)
  • 스냅샷(snapshots)
• dbt를 왜 사용하는가?
  • 데이터 변경 사항을 이해하기 쉽고 롤백이 가능
  • 데이터간 리니지 확인 가능
  • 데이터 품질 테스트 및 에러 보고
  • Fact 테이블의 증분 로그(Incremental update)
  • Dimension 테이블 변경 추적
  • 용이한 문서 작성

2.1 dbt 실습

• Redshift를 사용하여 AB 테스트 분석을 쉽게 하기 위한 ELT 테이블 만들어보기
• 입력 테이블 : user_event, user_variant, user_metadata
• 생성 테이블 : variant별 사용자별 일별 요약 테이블
  • variant_id, user_id, datestamp, age, gender
  • 총 impression, 총 click, 총 purchase, 총 revenue
• 입력 데이터
  • 프로덕션 DB에 저장되는 정보들을 데이터 웨어하우스로 적재했다고 가정
• Fact 테이블과 Dimension 테이블

Fact 테이블	Dimension 테이블
분석의 초점이 되는 양적 정보를 포함하는 중앙 테이블	Fact 테이블에 대한 상세 정보를 제공하는 테이블
일반적으로 매출 수익, 판매량, 이익과 같은 측정 항목 포함. 비즈니스 결정에 활용	Fact 테이블의 데이터에 맥락을 제공하여 다양한 방식으로 분석 가능하게 해줌
외래 키를 통해 여러 Dimension 테이블과 연결됨(더 큰 개념)	primary key를 가지며, Fact 테이블에서 참조(더 작은 개념)

입력 데이터 요약

• 최종 생성 데이터 (ELT 테이블)

SELECT
 variant_id,
 ue.user_id,
 datestamp,
 age,
 gender,
 COUNT(DISTINCT item_id) num_of_items, -- 총 impression
 COUNT(DISTINCT CASE WHEN clicked THEN item_id END) num_of_clicks, -- 총 click
 SUM(purchased) num_of_purchases, -- 총 purchase
 SUM(paidamount) revenue -- 총 revenue
FROM raw_data.user_event ue
JOIN raw_data.user_variant uv ON ue.user_id = uv.user_id
JOIN raw_data.user_metadata um ON uv.user_id = um.user_id
GROUP by 1, 2, 3, 4, 5;

 

2.2 dbt 설치

• 터미널(윈도우는 cmd)에서 pip3 install dbt-redshift
• 그 이후에 dbt init learn_dbt(프로젝트 이름)
• redshift 연결 정보를 기입
• 설치 완료 후 learn_dbt폴더에 들어가면 이런 하위 폴더들과 yml 파일이 있다

learn_dbt폴더의 모습

 

3. dbt  모델의 input

• Model : ELT 테이블을 만들기 위해 기본이 되는 빌딩 블록(테이블이나 뷰 혹은 CTE의 형태로 존재함)
• 입력, 중간, 최종 테이블을 정의하는 곳
• Tier : raw → staging(src) → core
• 모델의 구성 요소 :
  • Input :
    • 입력(raw)과 중간(staging, src) 데이터 정의
    • raw는 CTE로 정의, staging은 view
  • Output :
    • 최종(core) 데이터 정의(테이블로 정의)
  • 이 모든 요소들은 models 폴더 밑에 sql 파일로 존재
  • 기본적으로 select + Jinja 템플릿과 매크로
  • 다른 테이블 사용 가능
• 데이터 빌딩 프로세스

데이터 빌딩 프로세스

 

• models 폴더로 이동한 뒤, src폴더를 생성한 후 테이블을 생성한다
• 각 sql 파일 안에는 미리 준비해둔 쿼리를 넣는다

vi src_user_event.sql
vi src_user_variant.sql
vi src_user_metadata.sql

• 파일 생성이 끝났으면 루트 디렉토리로 가서 dbt run 명령어를 실행한다

dbt run의 결과

 

4. dbt 모델의 output

• Materialization :
  • 입력 데이터들을 연결해서 새로운 데이터를 생성하는 것
  • 보통 여기서 추가 transformation이나 데이터 클린업을 수행함
  • 4가지 내장 materialization이 제공됨
  • 파일이나 프로젝트 레벨에서 가능
• materialization의 4가지 종류
  1. View : 데이터를 자주 사용하지 않는 경우
  2. Table : 데이터를 반복해서 자주 사용하는 경우
  3. Incremental (Table Appends) : Fact 테이블. 과거 레코드를 수정할 필요가 없는 경우
  4. Ephemeral (CTE) : 한 SELECT에서 자주 사용되는 데이터를 모듈화하는데 사용
• 데이터 빌딩 프로세스
  • models/dim폴더 안에 저장

• models 밑에 core 테이블들을 위한 폴더 생성(dim, fact)
• 각 폴더에 맞는 테이블들을 위와 같은 방식으로 추가해준다
• Jinja 템플릿과 ref 태그를 사용해서 dbt 내 다른 테이블들을 액세스
  • 설정에 따라 view/table/CTE 등으로 만들어져서 사용됨
  • materialized라는 키워드로 설정

-- dim_user_variant.sql

WITH src_user_variant AS (
 SELECT * FROM {{ ref('src_user_variant') }}
)
SELECT
 user_id,
 variant_id
FROM
 src_user_variant

 

※ Jinja 탬플릿

• 파이썬이 제공해주는 템플릿 엔진으로 Flask에서 많이 사용
• 입력 파라미터 기준으로 HTML 페이지를 동적으로 생성
• 조건문, 루프, 필터 등을 제공

 

• fact 테이블은 incremental table로 빌드. config를 통해서 스키마가 바뀐 경우 대응 방법도 지정이 가능하다

-- fact_user_event.sql
{{
 config(
 materialized = 'incremental',
 on_schema_change='fail'
 )
}}
WITH src_user_event AS (
 SELECT * FROM {{ ref("src_user_event") }}
)
SELECT
 user_id,
 datestamp,
 item_id,
 clicked,
 purchased,
 paidamount
FROM
 src_user_event

• model의 materialized format을 dbt_project.yml 파일에서 편집한다
  • 최종 core 테이블들은 table로 빌드

맨 마지막 부분 편집

• 그 이후 루트 디렉토리에서 다시 dbt run 실행

run 실행 후 결과

 

• 데이터 빌딩 프로세스

최종 데이터 빌딩 프로세스

• models 폴더에 dim이라는 폴더를 생성
• dim_user_variant와 dim_user_metadata를 조인하여 dim_user.sql 생성

-- dim_user.sql
WITH um AS (
 SELECT * FROM {{ ref("dim_user_metadata") }}
), uv AS (
 SELECT * FROM {{ ref("dim_user_variant") }}
)
SELECT
 uv.user_id,
 uv.variant_id,
 um.age,
 um.gender
FROM uv
LEFT JOIN um ON uv.user_id = um.user_id

• models 폴더에 analytics라는 폴더를 생성
• 그 안에 dim_user와 fact_user_event를 조인한 analytics_variant_user_daily.sql을 생성

-- analytics_variant_user_daily.sql
WITH u AS (
 SELECT * FROM {{ ref("dim_user") }}
), ue AS (
 SELECT * FROM {{ ref("fact_user_event") }}
)
SELECT
 variant_id,
 ue.user_id,
 datestamp,
 age,
 gender,
 COUNT(DISTINCT item_id) num_of_items, -- 총 impression
 COUNT(DISTINCT CASE WHEN clicked THEN item_id END) num_of_clicks, -- 총 click
 SUM(purchased) num_of_purchases, -- 총 purchase
 SUM(paidamount) revenue -- 총 revenue
FROM ue LEFT JOIN u ON ue.user_id = u.user_id
GROUP by 1, 2, 3, 4, 5

• 다시 한번 dbt run 실행하여 컴파일

최종 output 실행

 

5. dbt Seeds / Sources / Tests / Documentation

5.1 dbt Seeds

• Seeds는 dimension 테이블들을 파일 형태로 데이터 웨어하우스에 로드하는 방법이다
• seeds는 작은 파일 데이터를 지징함
• dbt seed를 실행해서 빌드
• 실습
  • seeds 폴더 안에 적당한 csv 파일을 하나 생성
  • dbt seed 실행
  • 이 파일 이름으로 된 테이블이 생성됨

date
2023-01-01
2023-01-02
2023-01-03
2023-01-04
2023-01-05

seeds 실행 결과

 

5.2 dbt Sources

• Staging 테이블을 만들 때 입력 테이블이 자주 바뀐다면 일일이 찾아서 바꾸지 않게 해주는 것이 Sources
• 입력 테이블에 별칭(alias)을 주고 테이블에서 사용
• 기본적으로 처음 입력이 되는 ETL 테이블을 대상으로 함
• 최신 레코드 체크 기능 제공
• 실습
  • models/sources.yml 파일 생성
  • 별칭 생성

version: 2

sources:
 - name: jyunghyechoi
 schema: raw_data
 tables:
  - name: metadata
  identifier: user_metadata
  - name: event
  identifier: user_event
  - name: variant
  identifier: user_variant

• models 밑 파일들도 적절하게 별칭을 사용하게끔 변경

WITH src_user_event AS (
 SELECT * FROM {{ source ("keeyong", "event") }} -- 변경된 부분
)
SELECT
 user_id,
 datestamp,
 item_id,
 clicked,
 purchased,
 paidamount
FROM
 src_user_event

• 최신성 체크 기능 실습
• dbt source freshness 명령으로 수행
• sources.yml 파일속 최신성 확인 파라미터를 추가(freshness)

freshness 추가

최신 데이터를 추가한지 24시간이 지났기 때문에 ERROR 발생

 

5.3 dbt Tests

• 데이터 품질을 테스트하는 방법
• 내장 일반 테스트(Generic)
  • unique, not null, accepted_values, relationships 등의 테스트 지원
  • models 폴더
• 커스텀 테스트(Singular)
  • 기본적으로 select로 간단하며 결과가 리턴되면 "실패"로 간주함
  • tests 폴더
• Generic 테스트 구현 실습
  • models에 schema.yml 파일 생성(unique, not null 확인)
  • dbt test로 실행

version: 2
models:
 - name: dim_user_metadata
   columns:
   - name: user_id
   tests:
   - unique
   - not_null

테스트 통과

• Singular test 구현 실습
  • tests 폴더에 dim_user_metadata.sql 생성
  • primary key uniqueness 확인
  • 반환되면 실패로 간주

SELECT
 *
FROM (
 SELECT
 user_id, COUNT(1) cnt
 FROM
 {{ ref("dim_user_metadata") }}
 GROUP BY 1
 ORDER BY 2 DESC
 LIMIT 1
)
WHERE cnt > 1

모두 통과

 

5.4 dbt Documentation

• dbt 문서화는 기존 yml 파일에 문서화를 추가하거나 독립적인 마크다운 파일을 생성하는 방법이 있다
• 일반적으로 전자가 더 선호됨
• 이렇게 생성된 문서를 경량 웹서버로 서빙하여 확인 가능
• dbt docs generate로 문서 만들기
• 실습 :
  • schema.yml과 sources.yml 파일에 각각 description 키를 추가한다

version: 2
models:
 - name: dim_user_metadata
   description: A dimension table with user metadata
   columns:
   - name: user_id
     description: The primary key of the table
     tests:
     - unique
     - not_null

Redshift의 권한이 제한적이기 때문에 경고나 나옴

• 문서는 dbt docs serve로 웹서버를 띄울 수 있다

dbt docs 웹서버의 모습

• 우측 하단의 아이콘을 누르면 Lineage Graph를 확인할 수 있다

Lineage Graph 확인