✏️ 오늘 학습한 내용
1. Database Normalization
2. DBT
🔎 ELT의 미래
ETL을 하는 이유는 결국 ELT를 하기 위함이며,
이때 데이터 품질에 대한 중요성이 점점 증대되고 있습니다.
데이터 품질을 유지하는 것이 바로 비용/노력 감소와 생산성 증대로 이어지는 지름길이기 때문입니다.
🔎 Database Normalization
Database Normalization이란?
데이터베이스를 보다 조직적이고 일관된 방법으로 디자인하려는 방법
( 데이터베이스 정합성을 쉽게 유지하고 레코드들을 수정/적재/삭제를 용이하게 하는 것 )
- Normalization에서 사용되는 개념
- Primary Key
- Composite Key ( PK가 여러 컬럼인 경우 )
- Foreign Key
Normal Form
1NF (First Normal Form)
아래 조건을 만족하면 First Normal Form을 만족한다고 할 수 있습니다.
( 주 목표는 중복을 제거하고 atomicity를 갖는 것! )
- 한 셀에는 하나의 값만 있어야함 (atomicity)
- Primary Key가 있어야함
- 중복된 키나 레코드들이 없어야함
2NF (Second Normal Form)
2NF는 기본적으로 1NF를 모두 만족해야하고
추가로 아래 조건까지 만족해야합니다.
-
Primary Key를 중심으로 의존결과를 알 수 있어야함
( Star Schema가 만들어져야 함 ) -
부분적인 의존도가 없어야함
-> 즉 모든 부가 속성들을 Primary key를 가지고 찾을 수 있어야함
3NF (Third Normal Form)
-
2NF를 만족해야함
-
전이적 부분 종속성을 없애야함
( 떼어낼 수 있는 종속성은 다 떼어내서 별도의 테이블로 분리 )
SCD ( Slowly Changing Dimensions )
SCD란
Fact 테이블과 Dimension 테이블이 있습니다.
팩트 테이블은 간단하게 값을 나타내는 테이블이고,
디멘션 테이블은 이 팩트테이블들을 설명하는 정보 테이블입니다.
그러면 이 디멘션 테이블의 데이터가 바뀌어야 하는 상황이 온다면 어떻게 될까요?
팩트와 팩트를 설명하는 디멘션이 엉켜버려 정확한 분석을 하지못하게 될 수 있습니다.
이러한 상황을 해결하기 위해 탄생한 개념이 SCD입니다.
SCD는 데이터 웨어하우스에서 시간 경과에 따른 현재 및 과거 데이터를 저장하고 관리하는 디멘션입니다.
즉, 팩트 테이블과 디멘션 테이블을 1대1로 운영하는 것이 아니라,
그 팩트에 해당하는 디멘션을 시간의 흐름에 따라 과거와 현재 데이터를 모두 기록해두겠다는 것이죠.
SCD의 특징
-
DW이나 DL에서 모든 테이블의 히스토리(과거기록)를 유지하는 것이 중요
-
히스토리 유지하는 것이 분석에 도움이 되고 롤백에 사용될 수도 있음
-
보통 2개의 timestamp 필드를 갖는 것이 좋음
created_atupdated_at
-
-
컬럼의 성격에 따라 히스토리를 어떻게 유지할지 방법이 달라짐
- SCD Type 0
- SCD Type 1
- SCD Type 2
- SCD Type 3
- SCD Type 4
분석이라는 관점에서 히스토리를 유지하는 것이 중요합니다.
그런데, 일부 속성들은 시간을 두고 변하게 되기 때문에 DataWarehouse 테이블 쪽에 어떻게 반영해야할지 고민을 해봐야합니다.
( 현재 데이터에만 히스토리 유지할 것인지,
과거의 모든 데이터까지 히스토리 유지할 것인지 )
DBT가 없을 때는 이 기능들을 모두 구현을 해주었어야 했으나,
현재는 SCD Type의 일부를 DBT가 지원해주기에,
DBT 세팅을 한다면 특정 테이블에 알아서 히스토리가 쌓입니다.
SCD의 여러 유형
SCD Type 0
한번 사용하고 나면 바꿀 이유가 없는 경우들입니다.
- 한번 정해지면 갱신되지 않고 고정되는 필드
ex) 회원 등록일, 제품 첫 구매일
SCD Type 1
데이터가 새로 생길 때마다 새로 덮어쓰면 되는 컬럼들입니다.
- 처음 레코드 생성시에는 존재하지 않았지만 나중에 생기면서 채우는 경우
ex) 연간소득 필드
SCD Type 2
특정 entity에 대한 데이터가 새로운 레코드로 추가하는 방식입니다.
( 과거 및 현재가 모두 기록되어야 하는 경우, 덮어씌우는 것이 아니라 레코드를 추가하고 그 시간을 기록 )
ex) 고객의 등급 변화 :
고객 등급을 뜻하는 컬럼의 값이 “regular”에서 “vip”로 변화하는 경우
( 레코드를 추가 후 변경시간도 같이 추가되어야합니다. )
SCD Type 3
Type 2에서는 레코드로 추가했지만
Type 3는 컬럼으로 추가하는 방식입니다.
- SCD Type 2의 대안으로 특정 entity 데이터가 새로운 컬럼으로 추가되는 경우
ex) 고객의 등급 변화
-
고객 등급을 뜻하는 컬럼의 값이 “regular”에서 “vip”로 변화하는 경우
-> previous_tier라는 컬럼을 새로 생성
( 이전 등급을 컬럼으로 만들어 기록 )-> 변경시간도 별도 컬럼으로 존재해야함
SCD Type 4
가장 많이 사용되는 방법으로,
테이블에 현재 상황만을 기록하고,
이 테이블에 내용이 바뀐 모든 기록을 별도의 새로운 Dimesion 테이블에 저장하는 방식입니다.
- 특정 entity에 대한 데이터를 새로운 Dimension 테이블에 저장
-> SCD Type 2의 변종
🔎 DBT (Data Build Tool)란
데이터의 품질을 높이고 변화를 잘 트래킹할 수 있다면,
그것이 결국 비용/노력 감소로 이어지고 생산성이 증대가 될 것입니다.
이러한 문제를 해결하기 위해 나온 툴이 DBT입니다.
또한, 가볍기 때문에 클라우드에서 돌려도 부담이 없습니다.
DBT가 서포트해주는 데이터 시스템
- BigQuery
- Redshift
- Snowflake
- Spark
- ...
DBT 구성 컴포넌트
DBT에 프로젝트를 세팅하면 폴더들이 여러 개가 생성되는데
이 폴더들의 역할을 이해하는 것이 DBT 컴포넌트를 이해하는 것입니다.
-
데이터 모델 (models)
-
테이블들을 몇개의 티어로 관리
( 테이블의 품질, Raw Table, Staging Table, Core Table )- 일종의 CTAS (SELECT 문들), Lineage 트래킹
-
Table, View, CTE 등
( 위 형태로 존재할 수 있음, Configuration하기 나름 )
-
-
데이터 품질 검증 (tests)
-
스냅샷 (snapshots)
=> 폴더 내부 모든 파일은 SQL 이거나 yml/yaml 파일
DBT를 사용하기 좋은 경우
-
데이터 변경 사항을 이해하기 쉽고 필요하다면 롤백 가능
( snapshots ) -
데이터간 리니지 확인 가능
( DBT가 리니지를 체크해주기 때문에 Data catalog로 그대로 읽어서 검색 가능 ) -
데이터 품질 테스트 및 에러 보고
-
Fact 테이블의 증분 로드 (Incremental Update)
기존에 있던 내 코드의 변경이 아니라,
계속해서 추가만 하는 형태(삭제, 수정 불가)의 Fact 테이블이 있는 경우
DBT에서 SQL만 작성해주면 알아서 Incremental Update -
Dimension 테이블 변경 추적 (히스토리 테이블)
-
용이한 문서 작성
보통 사용하는 테크 스택
DBT 사용 시나리오
목표 : AB 테스트 분석을 쉽게 하기 위한 ELT 테이블을 만들기
-
Redshift 사용
-
입력 테이블:
user_event,user_variant,user_metadata
-
생성 테이블: Variant별 사용자별 일별 요약 테이블
( 입력 테이블 3개를 join )- GROUP BY : (variant_id, user_id, datestamp, age, gender)
- SUM -> 총 impression, 총 click, 총 purchase, 총 revenue
추출한 raw data
위 테이블로 입력해야할 데이터들이 이미 Redshift에 raw_data 스키마에 적재되었다고 가정하겠습니다.
raw_data.user_event
raw_data.user_variant
raw_data.user_metadata
Fact 테이블과 Dimension 테이블
Fact 테이블
분석의 초점이 되는 양적 정보를 포함하는 중앙 테이블
일반적으로 매출 수익, 판매량, 이익과 같은 측정 항목이 포함되며, 주로 비즈니스 결정에 사용되는 테이블입니다.
Fact 테이블은 일반적으로 외래 키를 통해 여러 Dimension 테이블과 연결됩니다.
( 보통 Fact 테이블의 크기가 Dimension 테이블보다 훨씬 더 큼 )
Dimension 테이블
Fact 테이블에 대한 상세 정보를 제공하는 테이블
고객, 제품과 같은 테이블로 Fact 테이블에 대한 상세 정보 제공하는 테이블입니다.
Fact 테이블의 데이터에 맥락을 제공하여 다양한 방식으로 분석 가능하게 하는데 사용됩니다.
Dimension 테이블은 primary key를 가지며, fact 테이블에서 참조 (foreign key)
( 보통 Dimension 테이블의 크기는 훨씬 더 작음 )
DBT 설치 및 환경 설정
dbt 설치
로컬 개발 버전인 dbt-core를 통해 설치를 진행,
dbt-core와 dbt connector(Redshift, BigQuery, ...)가 포함된 dbt-redshift를 설치
pip3 install dbt-redshift
dbt 생성
dbt init learn_dbt ( learn_dbt는 생성할 dbt 이름 )
생성과 동시에 Redshift 연결 정보를 입력
혹은 ~/.dbt/profiles.yml를 통해 연결 정보를 수정
ex)
# profiles.yml
learn_dbt:
outputs:
dev:
dbname: dev
host: ...
password: ...
port: ...
schema: ...
threads: 1
type: redshift
user: ...
target: devdbt 설정
생성한 dbt폴더 안에 있는 dbt_project.yml에서 설정 내용을 수정 가능
ex)
name: 'learn_dbt'
version: '1.0.0'
# ~/.dbt/profiles.yml에 learn_dbt가 존재해야함
profile: 'learn_dbt'
# 폴더 정보 -> 폴더 이름과 동일해야함
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]
# 결과들이 저장되는 폴더 : target
target-path: "target"
# dbt-clean 명령어를 사용했을 때 삭제하는 폴더
clean-targets:
- "target"
- "dbt_packages"
# dbt 모델들의 table형태를 정의
models:
# 기본적으로 별도의 설정이 없는 모델은 테이블이 view 테이블으로 생성됨
learn_dbt:
+materialized: view
# dim 폴더 내의 모델들은 테이블이 table로 생성됨
dim:
+materialized: table
# src 폴더 내의 모델들은 view도 아닌 휘발성 임시 테이블로 생성됨 (CTE)
src:
+materialized: ephemeral
analytics:
+materialized: tableModel이란?
ETL 테이블을 생성하는 데 있어 기본이 되는 빌딩 블록입니다.
( Table, View, CTE의 형태로 존재합니다. )
입력, 중간, 최종 테이블을 정의하는 곳이라고도 볼 수 있습니다.
Models 폴더 내에서 .sql 파일을 작성하면 dbt를 실행시킬 때 그 sql문이 동작하여 모델을 만듭니다.
View란?
SELECT 결과를 기반으로 만들어진 가상 테이블으로 기존 테이블의 일부 혹은 여러 테이블들을 조인한 결과를 제공합니다.
ex) CREATE VIEW table_name AS SELECT ...
View의 장점
-
데이터의 추상화
- 사용자는 View를 통해 필요 데이터에 직접 접근
( 원본 데이터를 알 필요가 없음 )
- 사용자는 View를 통해 필요 데이터에 직접 접근
-
데이터 보안 :
- View를 통해 사용자에게 필요한 데이터만 제공
- 원본 데이터 접근 불필요
-
복잡한 쿼리의 간소화
View의 단점
-
매번 쿼리가 실행되므로 어느정도 시간이 소요될 수 있습니다.
-
원본 데이터의 변경을 알 수 없게되면 실행이 실패합니다.
Materialization이란?
입력 데이터 (테이블)들을 연결해서 새로운 데이터(테이블)를 생성하는 것을 뜻합니다.
보통 여기서 추가 transformation이나 데이터 클린업 수행을 진행하며 4가지 내장 materialization이 제공됩니다.
-
View
( 데이터를 자주 사용하지 않는 경우 ) -
Ephemeral (CTE)
( 한 SELECT에서 자주 사용되는 데이터를 모듈화 ) -
Table
( 데이터를 반복해서 자주 사용하는 경우 ) -
Incremental (Table Appends)
( 과거 레코드를 수정할 필요가 없는 경우 )
-> 갱신이 필요없고 추가만 필요한 경우- Fact 테이블이 그 대상
Model 예시
# dim_user_variant.sql
WITH src_user_variant AS (
SELECT * FROM {{ ref('src_user_variant') }}
)
SELECT
user_id,
variant_id
FROM
src_user_variant여기서 사용한 {{ ref('src_user_variant') }}은
Jinja 템플릿 + ref태그를 사용하여
dbt 내 다른 테이블들을 액세스하는 방법
Incremental Table로 빌드
dbt_profile.yml에서 materialized = 'incremental'로 설정한 경우
Incremental Table로 빌드하는 경우 추가해야하는 코드가 있습니다.
ex)
#fact_user_event.sql
# 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
# incremental, 즉 추가가 된 데이터가 있는 경우
{% if is_incremental() %}
# 이전 datestamp보다 이후 날짜만 받아서 처리하겠다.
AND datestamp > (SELECT max(datestamp) from {{ this }})
{% endif %}데이터 빌딩 프로세스
dbt run을 입력하면 Model 빌딩과 함께 dbt가 실행됩니다.
-
dbt compile: SQL 코드까지만 생성하고 실행하지는 않음 -
dbt run: 생성된 코드를 실제 실행
