데이터 엔지니어링/SQL

[SQL] 변화하는 유저 DB 관리, 어떤 sql 문법을 써야할까? (feat. SCD)

허니비 honeybee 2023. 8. 14. 15:47

유저 DB, 경영정보 테이블 등 변동이 심한 DB 는 어떻게 관리하면 좋을까? 예를 들어, 어떤 회사의 A 직원이 개발팀에서 근무하다 마케팅팀으로 옮겼다면? DB 정보를 업데이트하고 지우기 위해서 단순히 INSERT INTO 라던지 DELETE 구문을 이용해 개발팀_table 과 마케팅팀_table 의 DB 를 수정한다면 인원이 늘어남에 따라 관리가 복잡해짐은 물론이며, 과거의 데이터를 트래킹하기 어렵다는 치명적인 한계가 있다. 

 

이럴 때 사용하는 SQL 로직으로 SCD (Slowly Changing Dimension).. 라는 녀석이 있다. 데이터 엔지니어링 분야에서 자주 쓰이는 로직과 문법인데, SCD type 1 - 6 까지 경우에 따라 다양하게 적용할 수 있다. 

 

지금부터 SCD 로직을 적용하는 경우의 수 들을 살펴보겠다.

어떤 것을 선택하느냐는 DB 상황 등에 맞추어 선택하면 될 듯 하다. 

 

1. SCD type1 

 

type1 은 단순히 기존의 데이터를 덮어씌우는 것이다. 과거의 데이터가 중요하지 않고 현재 기준으로만 데이터를 관리할 때 선택할 수 있는 방법이겠다. 

 

| customer_id | name   | address     |
|-------------|--------|-------------|
| 1           | Alice  | Address_A   |
| customer_id | name   | address     |
|-------------|--------|-------------|
| 1           | Alice  | Address_B   |

이런 경우에는 단순히 UPDATE 쿼리를 이용해 처리할 수 있겠다.

 

이 방법을 사용하면 유지는 쉽겠지만, 과거 데이터가 관리되지 않고 계속해서 업데이트되는 컬럼들은 계산을 해주어야 한

 

 

 

2. SCD type2 (가장 일반적인 방법이다!) 

 

type 2의 경우는 특정 컬럼을 이용해 status(상태) 를 보여주는 방법이다. (이때 보통 FLAG 라는 컬럼명을 쓴다)  즉, 과거의 상태에 해당하는 행에는 0의 값을, 현재의 상태에 해당하는 행에는 1의 값을 부여한다. 

 

이 예시의 경우에는 status 컬럼이 0, 1로 설정되어 있어 현재 상태에 해당하는 컬럼이 무엇인지 알 수 있다. 또한, 기존 DB 의 형태를 해치치 않는 방법이다. 

| customer_id | name   | address     | status | start_date  | end_date    |
|-------------|--------|-------------|--------|-------------|-------------|
| 1           | Alice  | Address_A   |    0    | 2023-01-01  | 2023-07-15  |
| 1           | Alice  | Address_B   |    1    | 2023-07-16  | 9999-12-31  |

이 방법의 장점으로는, 계속해서 변화를 하나의 테이블에서 추적할 수 있다는 것이겠다. 하지만, 변동이 너무 잦은 DB 에서 사용하기에는 적합하지 않다 

 

 

 

 

3. SCD type 3

 

SCD type 3 는 변경된 상태를 알려줄 수 있는 컬럼을 하나 추가하는 방식이다. 이전의 값과 현재의 값을 하나의 행에서 볼 수 있게 만든다. 

 

| customer_id | name   | address     | previous_address   |
|-------------|--------|-------------|--------------------|
| 1           | Alice  | Address_A   |                    |

주소가 변경된다면 previous_adress 컬럼에 변경 전의 값을 추가하면 된다 

 

| customer_id | name   | address     | previous_address   |
|-------------|--------|-------------|--------------------|
| 1           | Alice  | Address_B   | Address_A          |

 

이 방법은 계속해서 변화하는 과정을 추적하기엔 역시 적합하진 않다. (가장 최근의 변동사항을 고려하는 방법이기 때문에) 

 

 

 

 

4. SCD type 4 

 

type 4 는 변경 사항을 또 다른 테이블 하나에 저장하는 방식이다. (테이블 형태를 바꾸기 곤란할 때 적합할 것 같다. 하지만 조인하고 추출할 때 조금 귀찮을 듯..) 

 

기본 테이블에는 변경 내용을 담지 않고

 

| customer_id | name   | address_id  |
|-------------|--------|-------------|
| 1           | Alice  | 1           |

변경 내용은 다른 테이블에 따로 저장해준다. 

 

단, 이때 adress_id 처럼 변경 사항의 시간 순서를 알 수 있는 키를 한가지 부여해야 한다. 

| address_id | address      | start_datetime     | end_datetime       |
|------------|--------------|--------------------|--------------------|
| 1          | Address_A    | 2023-01-01         | 2023-07-15         |
| 2          | Address_B    | 2023-07-16         | 9999-12-31         |

 

 

 

 

 

5. SCD type 6 

 

마지막으로, SCD type 6은 변경 내용을 구분할 수 있는 컬럼, 날짜, 현재값과 이전값 모두를 포함한 하이브리드 형식이다. type 1, 2,3 을 합해 만든것이라고 한다. (가장 안정적으로 보인다!) 

 

| customer_id | name   | address     | previous_address   | start_datetime     | end_datetime       |
|-------------|--------|-------------|--------------------|--------------------|--------------------|
| 1           | Alice  | Address_A   |                    | 2023-01-01         | 2023-07-15         |
| 1           | Alice  | Address_B   | Address_A          | 2023-07-16         | 9999-12-31         |

이 방법은 복잡한 분석을 해야 할 때 가장 적합한 방법이다. 

728x90
저작자표시