📒 DBT(1)

📌 ELT의 미래

• ETL은 결국 ELT를 하기 위함이다. 이 때 데이터 품질 검증이 중요하다.

• 데이터 품질의 중요성 증대

  • 입출력 체크

  • 더 다양한 품질 검사

  • 리니지 체크

  • 데이터 히스토리 파악

• 데이터 품질 유지 -> 비용/노력 감소와 생산성 증대의 지름길

---

📌 DB Normalization

• DB를 좀더 조직적이고 일관된 방법으로 디자인하려는 방법
  -> DB 접합성을 쉽게 유지하고 레코드들을 수정/삭제/적재를 용이하게 하는 것

• Normalization에 사용되는 개념

  • Primary Key
  • Composite Key
  • Foreign Key

• 1NF(First Normal Form)

  • 한 셀에는하나의 값만 있어야한다. (atomicity)
  • Primary Key가 있어야한다.
  • 중복된 키나 레코드들이 없어야한다.

-> 즉 목표는 중복을 제거하고 atomicity를 갖는 것이다.

• 2NF(Second Nomal Form)
  • 1NF를 만족
  • Primary Key를 중심으로 의존 결과를 알 수 있어야한다.
  • 부분적인 의존도가 없어야 한다.
    • 즉 모든 부가 속성들은 Primary Key를 가지고 찾을 수 있어야한다.

-> 중복을 제거하고 atomicity를 갖는 것이 목표이다.

• 3NF(Third Normal Form)
  • 2NF를 만족
  • 전이적 부분 종속성이 없어야한다.

• Slowly Changing Dimensions

  • DW나 DL에서는 모든 테이블들의 히스토리를 유지하는 것이 중요하다.

    • 보통 두 개의 timestamp필드를 갖는 것이 좋다.

      • created_at(생성시간으로 한번 만들어지면 고정된다.)

      • updated_at(꼭 필요하다, 마지막 수정 시간을 나타낸다.)

  • 이 경우 컬럼의 성격에 따라 어떻게 유지할지 방법이 달라진다.

    • SCD Type 0
    • SCD Type 1
    • ...
    • SCD Type 4

• SCD Type 0

  • 한 번 쓰고 나면 바꿀 이유가 없는 경우들
  • 한 번 정해지면 갱신되지 않고 고정되는 필드들
  • ex) 고객 테이블이라면 회원 등록일, 제품첫 구매일

• SCD Type 1

  • 데이터가 새로 생기면 덮어쓰면 되는 컬럼들
  • 처음 레코드 생성시에는 존재하지 않았지만 나중에 생기면서 채우는 경우
  • ex) 고객 테이블이라면 연간 소득 필드

• SCD Type 2

  • 특정 entity에 대한 데이터가 새로운 레코드로 추가되어야하는 경우
  • ex) 고객 테이블에서 고객의 등급 변화
    • tier라는 컬럼의 값이 "regular"에서 "vip"로 변화하는 경우
    • 변경 시간도같이 추가되어야 한다.

• SCD Type 3

  • SCD Type 2의 대안으로 특정 entity 데이터가 새로운 컬럼으로 추가되는 경우
  • ex) 고객 테이블에서 tier라는 컬럼의 값이 "regular"에서 "vip"로 변화하는 경우
    • previous_tier라는 컬럼 생성
    • 변경 시간도 별도 컬럼으로 존재해야한다.

• SCD Type 4

  • 특정 entity에 대한 데이터를 새로운 Dimension 테이블에 저장하는 경우
  • SCD Type 2의 변종
  • ex) 별도의 테이블로 저장하고 이 경우 아예 일반화할 수도 있다.

---

📌 DBT(Data Build Tool)

• Data Build Tool

  • ELT용 오픈소스 : In-warehouse data transformation
  • DBT Labs라는 회사가 상용화함
  • Analytics Engineer라는 말을 만들어 냈다.

• 다양한 데이터 웨어하우스를 지원

  • Redshift, Snowflake, Bigquery, Spark

• 클라우드 버전도 존재한다.

  • dbt Cloud

• DBT가 지원하는 데이터 시스템

  • BigQuery
  • Redshift
  • Snowflake
  • Spark
  • ...

• DBT 구성 컴포넌트
  • 데이터 모델(models)
    • 테이블들을 몇 개의 티어로 관리한다.
      -> 일종의 CTAS(SELECT 문들), Lineage 트래킹
    • Table, View, CTE 등등
  • 데이터 품질 검증(tests)
  • 스탭샷(snapshots)

---

📌 DBT 사용 시나리오

• DBT가 어떻게 사용될 수 있는지 가상 환경을 생각해보자.

• 요구조건

  • 데이터 변경 사항을 이해하기 쉽고 필요하다면 롤백 가능
  • 데이터간리니지 확인 가능
  • 데이터 품질 테스트 및 에러 보고
  • Fact 테이블의 증분 로드(Incremental Update)
  • Dimension 테이블 변경 추적(History Table)
  • 용이한 문서 작성

• 보통 사용하는 Tech Stack

  • Redshift/Spark/Snowflake/BigQuery
  • DBT
  • Airflow

• 실습

  • Redshift 사용
  • AB테스트 분석을 쉽게 하기 위한 ELT 테이블 생성
  • 입력 테이블
    • user_event
    • user_variant
    • user_metadata
  • 생성 테이블 : Variant별 사용자별 일별 요약 테이블
    • variant_id
    • user_id
    • datestamp
    • age
    • gender
    • Total impression
    • Total click
    • Total purchase
    • Total revenue

Input Data

• Production DB에 저장되는 정보들을 Data Warehouse로 적재했다고 가정
• raw_data.user_event
  • 사용자/날짜/아이템별로 impression이 있는 경우 그 정보를 기록
  • impression으로부터 클릭, 구매, 구매시 금액을 기록
  • 실제 환경에서는 이런 aggregate 정보를 로그 파일등의 소스로부터 만들어내는 프로세스가 필요하다.

CREATE TABLE raw_data.user_event(
	user_id int,
	datestamp timestamp,
	item_id int,
	clicked int,
	purchased int,
	paidamountint
);

• raw_data.user_variant
  • 사용자가 소속한 AB test variant를 기록한파일(control vs. test)

CREATE TABLE raw_data.user_variant(
	user_id int,
    variant_id varchar(32) -- control vs. test
);

• 보통 experiment와 variant 테이블이 별도로 존재한다.

• 언제 variant_id로 소속되었는지 Timestamp필드가 존재하는 것이 일반적이다.

• raw_data.user_metadata

  • 사용자에 관한 metadata 정보가 기록된 파일(성별, 나이 등등)

CREATE TABLE raw_data.user_metadata(
	user_id int,
    age varchar(16),
    gender varchar(16)
);

Fact Table

• 분석의 초점이 되는 양적 정보를 포함하는 중앙 테이블
• 일반적으로 매출 수익, 판매량, 이익과 같은 측정 항목이 포함된다.
• 비즈니스 결정에 사용된다.
• Fact 테이블은 일반적으로 외래 키를 통해 여러 Dimension 테이블과 연결된다.
• 보통 Fact 테이블의 크기가 훨씬 더 크다.

Dimension Table

• Fact 테이블에 대한 상세 정보를 제공하는 테이블
• 고객, 제품과 같은 테이블로 Fact테이블에 대한 상세 정보를 제공한다.
• Fact 테이블의 데이터에 맥락을 제공하여 다양한 방식으로 분석 가능하게 해준다.
• Dimension 테이블은 Primary Key를 가지며, Fact테이블에서 참조한다.(Foreign Key)
• 보통 Dimension 테이블의 크기는 훨씬 더 작다.

최종 생성 데이터 (ELT Table)

SELECT
	varian_id,
    ue.user_id,
    datestamp,
    age,
    gender,
    COUNT(DISTINCT item_id) num_of_items, -- Total impression
    COUNT(DISTINCT CASE WHEN clicked THEN item_id END) num_of_clicks, -- Total click
    SUM(purchased) num_of_purchases, -- Total purchase
    SUM(paidamount) revenue -- Total revenue
FROMraw_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;

---

📌 DBT 설치/환경설정

• 사용절차
  • dbt 설치
    • dbt Cloud vs. dbtCore
    • git을 보통 사용한다.
      -> pip install dbt-redshift
      -> dbt-core 모듈도 같이 설치된다.
  • dbt 환경설정
  • Connector 설정
    • Connector가 바로 바탕이 되는 데이터 시스템(Redshift, Spark, ...)
  • 데이터모델링(tier)
    • Raw Data -> Staging -> Core
  • 테스트 코드 작성
  • Snapshot 설정

• 설치

  • pip install dbt-redshift
    -> 여기서 많은 오류가 발생할 것이다.. 하나씩 따라해보자.
  • sudo apt-get update
  • get-pip.py 설치
    • curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py
    • python3 get-pip.py
    • pip install --upgrade pip
  • pip 설치 디렉토리를 PATH에 추가하기
    • nano ~/.bashrc
    • 맨 아래에 아래 코드 작성
    • export PATH=$PATH:/home/urface/.local/bin
    • urface 대신 당신 local이름 작성
    • source ~/.bashrc (저장)
    • pip3 --version (확인)
  • PostgreSQL 설치
    • sudo apt-get install postgresql libpq-dev
  • gcc등 필수 도구 설치
    • sudo apt-get install build-essential
  • 만약 psycopg2 오류가 발생하면 아래 명령어 입력
    • pip3 install --user psycopg2-binary
  • Python 개발패키지
    • sudo apt-get install python3-dev
  • DBT - Redshift 설치
    • pip install --user dbt-redshift

• 환경 설정

  • dbt라는 디렉토리를 하나 만들고 진행하자.
    • mkdir dbt -> cd dbt
  • init으로 환경설정을 진행해주자.
    • dbt init dbt_name
  • 확인해보자.
    • ls -tl dbt_name
      
  • dbt_project.yml과 profiles.yml 파일은 중요하다.

• dbt 파일과 폴더

  • dbt_project.yml : 메인 환경 설정 파일
  • models
  • seeds
  • tests
  • snapshots
  • macros
  • analyses
  • README.md

• dbt_project.yml

  • name
  • version
  • config-version
  • profile
  • 아래 내용은 폴더 이름들과 일치해야한다.
  • model-paths:["models"]
  • analysis-paths:["analyses"]
  • test-paths:["tests"]
  • seed-paths:["seeds"]
  • macro-paths:["macros"]
  • snapshot-paths:["snapshots"]
  • target-path: "target" -> 결과들이 저장되는 폴더
  • clean-targets:
    • "target"
    • "dbt_packages"
  • models:
    • learn_dbt:
      • example:
        • +materialized: view
  • 여기서 example이하 2줄은 삭제하며 models에 있는 example 서브폴더도 삭제한다.

• dbt_profiles.yml

  • Project_name
  • dev:
    • dbname
    • host
    • password
    • port
    • schema
    • threads
    • type
    • user
  • target : dev

📌 DBT Model

1. Model이란?

• ELT 테이블을 만듬에 있어서 기본이 되는 빌딩블록이다.
  -> Table, View, CTE의 형태로 존재한다.
• 입력, 중간, 최종 테이블을 정의하는 곳이다.
  • Tier(raw, staging,core,...)
  • raw => staging(src) => core

2. View란?

• SELECT 결과를 기반으로 만들어진 가상 테이블
  • 기존 테이블의 일부 혹은 여러 테이블들을 조인한 결과를 제공한다.
  • CREATE VIEW 이름 AS SELECT ...
• View의 장점
  • 데이터의 추상화 : 사용자는 View를 통해 필요 데이터에 직접 접근한다.
  • 원본 데이터를 알 필요가 없다.
  • 데이터보안 : View를 통해 사용자에게 필요한 데이터만 제공한다.
  • 원본데이터 접근 불필요
  • 복잡한 쿼리의 간소화 : SQL(View)를 사용하면 복잡한 쿼리를 단순화한다.
• View의 단점
  • 매번 쿼리가 실행되므로 시간이 걸릴 수 있다.
  • 원본 데이터의 변경을 모르면 실행이 실패한다.

3. Model 구성요소

• Input
  • 입력(raw)과 중간(staging, src)데이터 정의
  • raw는 CTE로 정의
  • staging은 View로 정의
• Output
  • 최종(core) 데이터 정의
  • core는 Table로 정의
• model 폴더 아래 sql 파일로 존재
  • 기본적으로는 SELECT + Jinja 템플릿과 매크로
  • 다른 테이블들을 사용 가능하다.(reference)
    -> 이를 통해 리니지 파악

4. Model 구성 - input

• src 폴더 생성
  -> mkdir src
  ->cd src

• models/sec/src_user_event.sql

WITH src_user_evernt AS(
	SELECT * FROM raw_data.user_event
)
SELECT
	user_id,
    datestamp,
    item_id,
    clicked,
    purchased,
    paidamount
FROM
	src_user_event

• models/sec/src_user_metadata.sql

WITH src_user_metadata AS(
	SELECT * FROM raw_data.user_metadata
)
SELECT
	user_id,
    age,
    gender,
    updated_at
FROM
	src_user_metadata

• models/sec/src_user_variant.sql

WITH src_user_variant AS(
	SELECT * FROM raw_data.user_variant
)
SELECT
	user_id,
    variant_id
FROM
	src_user_variant

5. Model Building : dbt run

6. Model Building Check

• 해당 스키마 밑에 테이블 생성 여부 확인
• dbt run은 프로젝트의 다양한 SQL을 실행한다.
  -> 이 SQL들은 DAG로 구성되었다.
• dbt run은 보통 다른 더 큰 명령의 일부로 실행한다.
  • dbt test
  • dbt docs generate

7. Model 구성 - output

• Materialization

  • 입력 데이터(Table)들을 연결하여 새로운 데이터(Table)을 생성하는 것이다.
    -> 보통 여기서 추가 transformation이나 데이터 clean-up을 수행한다.
  • 4가지의 내장 materialization이 제공된다.
  • 파일이나 프로젝트레벨에서 가능하다.
  • dbt run을 기타 파라미터를 가지고 실행한다.

• Materialization 종류

  • View
    • 데이터를 자주 사용하지 않는 경우
  • Table
    • 데이터를 반복해서 자주 사용하는 경우
  • Incremental (Table Appends)
    • Fact Table
    • 과거 레코드를 수정할 필요가 없는 경우
  • Ephemeral(CTE)
    • 한 SELECT에서 자주 사용되는 데이터를 모듈화 하는데 사용한다.

• Models 아래에 core Table들을 위한 폴더 생성

  • dim 폴더와 fact 폴더 생성
    • dim 아래 각각 dim_user_variant.sql, dim_user_metadata.sql 생성
    • fact 아래 fact_user_event.sql 생성
  • 이 모두를 physicaltable로 생성

• models/dim/dim_user_variant.sql
  -> Jinja 템플릿과 ref 태그를 사용하여 dbt 내 다른 테이블들을 액세스 한다.

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

• models/dim/dim_user_metadata.sql
  -> 설정에따라 view/table/CTE 등으로 만들어서 사용된다.
  -> meterialized라는 키워드로 설정한다.

WITH src_user_metadata AS(
	SELECT * FROM {{ ref('src_user_metadata') }}
)
SELECT
	user_id,
    age,
    gender,
    updated_at
FROM
	src_user_metadata

• models/fact/fact_user_event.sql
  -> Incremental Table로 빌드 (materialized = 'incremental')

{{
	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
WHERE datestamp is not NULL
{% if is_incremental() %}
	AND datestamp> (SELECT max(datestamp) FROM {{ this }})
{% endif %}

• incremental_strategy도 사용가능

  • append
  • merge
  • insert_overwrite
    ->이 경우 unique_key와 merge_update_columns 필드를 사용하기도 함

• 스키마가 바뀐 경우 대응 방법 지정

  • append_new_columns
  • ignore
  • sync_all_columns
  • fail

• model의 materialized format 결정

  • 최종 Core Table들은 View가 아닌 Table로 빌드한다.
  • dbt_project.yml 편집

• dbt compile vs. dbt run

  • dbt compile : SQL 코드까지만 생성, 실행X
  • dbt run : 생성된 코드를 실제 실행한다.