[데이터 엔지니어링] - 데이터엔지니어로서 SQL 실력을 향상시키는법

1. 서론

SQL은 데이터 엔지니어링의 핵심이다. SQL을 숙달하고 SQL로 무엇을 할수 있는지 이해하면 더나은 데이터 엔지니어가 될수 있다. 만약 내가

• SQL 기초를 넘어 실력을 향상시키는 방법을 잘 모르겠다.
• 온라인 튜토리얼이 충분히 심도있게 다루지 않았다
• SQL 관련 핵심 기술 몇가지가 부족한데, 정확히 무엇이 문제인지 짚어내지 못한다.

그렇다면 내가 고민해보고 SQL실력을 늘리는 길에대해서 혼자 연구한 비법을 작성해 보려한다.

이러한 개념들을 이해하고, 적용하고, 연습하다보면 SQL 실력을 한단계 끌어올릴수 있을것이다.

 

사전학습 : https://www.w3schools.com/sql/

W3Schools.com

 

SQL 기술

데이터 엔지니어에게 SQL 기술은 데이터 모델링 및/또는 분석 작업에 매우 중요하다. 이글에서는 데이터 웨어하우스 에만 집중하겠다.

데이터 모델링

데이터엔지니어로서 당신은 사용자가 데이터를 쿼리할수 있도록 만드는 업무를 맡게 된다. 이 업무에서는 원시 데이터 수집, 정재, 모델링을 거쳐 최종 사용자가 활용할수 있도록 데이터를 제공하는 작업이 포함된다.

 

수집 요구사항

데이터 웨어하우스 테이블을 설계하기 전에 항상 최종 목표를 명확하게 잡는것이 중요하다

답변/탐구해야 할 몇가지 질문은 다음과 같다.

 

1. 이 데이터는 무엇을 나타내며 왜 필요한가?

2. 해당 테이블의 최종 사용자 는 누구인가?

3. 이 데이터를 생성하는 비즈니스 프로세스는 무엇인가? 이 데이터는 어떻게 생성되는가?

4. 최종 사용자가 실행할 것으로 예상되는 몇 가지(3개 이상)의 서로 다른 예시 쿼리는 무엇인가?

5. 분당 예상되는 읽기 쿼리 수는 몇건 인가?

6. 테이블에서 데이터를 읽는 데 허용 가능한 쿼리 실행 시간은 얼마인가?

7. 일일 예상 기록 건수는 몇건 인가?

8. 읽기 쿼리에 대한 일반적인 날짜범위(및/또는 기타) 필터는 무엇인가?

9. 조회에 필요한 과거 데이터의 범위는 무엇인가?

 

이 질문들에 대한 답은 데이터를 모델링하고 변환하는 방법을 결정할 것이다.

탐색

다음 단계는 데이터를 탐색하고, 데이터 문제를 확인하고, 가정을 검증하고, 데이터 크기 증가를 예측하고, 비즈니스 규칙을 검증하고, 조인 시 누락되거나 중복된 행을 확인하는 등의 작업을 수행하는 것이다.

데이터 웨어하우스에 원시 데이터를 로드해야 한다. 데이터 웨어하우스에 데이터를 가져오는 방법은 여러가지 있다. 탐색 목적으로는 데이터를 클라우드 스토리지 시스템에 저장한 후, COPY INTO 명령어를 사용하여 원시 데이터를 데이터 웨어하우스로 로드할 수 있다.

 

답변/검토가 필요한 몇가지 사항은 다음과 같다.

1. Data schema checks

• 데이터 형식이 열의 형식과 일치한가?
• 열 이름이 일관적인가?

2. Data quality checks

• 원본 파일의 모든 레코드가 원본 테이블에 로드되었나?

wc -l input_data_file.csv

 

입력 데이터의 행 수를 세는 데 사용하자.

• NULL, null,'null', '', N/A, 열 값(예: 등)이 없는지 확인하자.
• 해당 열 값에 필드 구분 기호가 포함되어 있나? 대부분의 데이터 웨어하우스는 이를 처리하는 옵션 (ex: quote_character, FIELD_OPTIONALLY_ENCLOSED_BY)을 제공한다.
• Validate business assumptions
  • 1. 데이터를 다른 업무 관련 테이블과 조인할 때 예상치 못한 중복 데이터나 누락된 행이 발생하는가? 그렇다면 그 이유는 무엇인가?
  • 2. 특정 ID를 기준으로 집계하고 팩트 테이블의 숫자 열을 집계할 경우, 집계 결과가 정확한가? 아니면 중복이나 누락이 발생하는가? 만약 그렇다면, 어떻게 방지할 수 있을까?
  • 3. 일일 행 수(및/또는 기타 사업체)에 명확한 패턴(계절성 포함)이 나타나나?
  • 4. 모든테이블에 고유 ID가 있나?
  • 5. 각 비즈니스 엔티티 테이블(일명 차원 테이블)마다 해당 테이블에 대한 모든 업데이트 내용을 기록하는 테이블이 있나?
  • 6. 특정한 의미를 가진 값들에 유의하자 예를 들어, -9999(또는 이와 유사한 값)는 때때로 NULL 또는 다른 값을 나타내는데 사용 될 수 있다.

이는 지속적인 과정이다. 상위 단계의 데이터 생성 프로세스가 변경될 수 있으므로 추가적인 데이터 문제 등이 발생할 수 있다.

모델링

요구사항과 데이터 관련 문제를 파악했다면 이제 최종 사용자 테이블을 모델링할 준비가 되었다. 일반적인 접근 방식은 팩트 테이블과 차원테이블을 사용하는 것이다. 이러한 데이터 모델링 방식은 대부분의 쿼리에 응답할 수 있다는 장점이 있다. 하지만 여러번의 조인이 필요할 수 있고, 관리하기에 상당한 노력이 필요하나는 단점이 있다.

 

답변/검토가 필요한 몇 가지 사항은 다음과 같다.

 

1. Naming conventions - 각 회사마다 고유한 명명 규칙이 있다. 만약 없다면, 반드시 이 규칙을 정립하자.

2. Slowly changing dimensions - 대부분의 비즈니스 엔티티 테이블(차원테이블)에는 시간이 지남에 따라 변경되는 속성이 있다. 과거 변경 사항을 기록하기 위해 SCD2 테이블을 생성하는 것을 고려해 보자.

3. In-correct aggregates - 팩트 테이블의 숫자 값에 대해 집계를 실행해도 중복되거나 부정확한 결과가 생성되어서는 안된다. 이는 일반적으로 서로 다른 열을 나타내는 데이터가 하나의 열에 포함되어 있기 때문에 발생하는 문제다.

4. Pre-aggregating data - 때때로 예상되는 쿼리 패턴에 따라 더 높은 세분성으로 데이터를 집계해야 하는 경우가 있다. 이러한 경우 읽기 시간이 요구 사항보다 길다면, 정해진 일정에 따라 데이터를 미리 집계하는 것이 좋다. 데이터를 미리 집계하면 "읽기 쿼리" 속도가 훨씬 빨라지지만, 데이터 파이프라인을 생성, 예약 및 유지 관리하는 데 추가적인 오버헤드가 발생한다.

5. Flat tables - 킴볼 모델은 매우 널리 사용되지만, 최종 사용자가 여러 테이블을 쿼리하고 조인하는 과정이 번거로울 수 있다. 데이터 팀이 최종 사용자에게 깔끔한 인터페이스를 제공하는 한 가지 방법은 모든 팩트 열과 차원 열을 포함하는 와이드 플랫 테이블(또는 뷰)을 만드는 것입니다. 플랫 테이블은 최종 사용자가 여러 테이블을 조인하는 것에 대해 걱정할 필요 없이 데이터 분석에 집중할 수 있도록 해준다.

 

참고 : 플랫 테이블에서 일부 차원 속성이 시간에 따라 변경될 경우, 해당 속성을 기준으로 그룹화 쿼리를 실행하면 정확하지 않은 결과가 나올 수 있습니다. 이 문제를 해결하려면 특정 시점의 차원 속성을 저장하는 테이블/뷰와 가장 최근의 차원 속성을 저장하는 테이블/뷰, 이렇게 두 개의 테이블/뷰를 사용하는 것이 좋습니다.

데이터 저장

데이터를 올바른 형식으로 저장하는 것은 쿼리 성능에 상당한 영향을 미칠 수 있다. 최종 사용자 테이블을 모델링 할때 데이터 저장 방식이 읽기 유형 쿼리에 미치는 영향을 반드시 고려해야 한다.

 

1. 파티셔닝 - 파티셔닝 / 클러스터링을 통해 스캔하는 데이터 양을 크게 줄일수 있으므로 비용을 절감할 수 있다.

2. 스토리지 포맷 - parquet 또는 ORC 형식과 같은 형식을 사용하면 데이터 크기를 크게 줄이고 변환 속도를 높일수 있다.

3. Sorting - 정렬은 읽어야 할 데이터 양을 줄이고 변환 작업을 효율적으로 만드는 데에도 도움이 될 수 있다.

4. Cloud storage - 외부 테이블을 사용하면 데이터를 클라우드 스토리지 시스템에 저장하고 필요할 때 읽을 수 있다.

 

각 데이터 웨어하우스는 위와 관련된 명명 방식/구현 방식/주의사항이 다르다. 예를들어 Snowflake는 이러한 작업 대부분을 자동으로 처리해 주지만, Redshift는 보다 세심한 접근 방식이 필요하다.

 

데이터 파이프라인

데이터 파이프라인을 설정할때 모범차례를 숙지하는것이 중요하다 데이터웨어하우스의 모범사례, 명명규칙, 데이터 품질 테스트에 대한 내용과 CI/CD 권한 구조에 대한 내용을 숙지하자.

 

TPC-H 는 데이터 웨어하우스 성능 벤치마킹에 널리 사용되는 데이터셋입니다. 데이터 스키마는 아래와 같습니다.

TPCH ERD

 

결론

이 글이 여러분의 SQL 실력 향상에 도움이 되었기를 바랍니다.

요약하자면, SQL 실력 향상은 다음과 같은 과정을 통해 이루어집니다.

1. Data modeling - 요구사항 수집, 데이터 탐색, 모델링 및 저장.
2. Data transformation-  데이터 웨어하우스 내 데이터 이동을 최소화하고, 쿼리 계획을 최적화하며, 데이터 접근을 관리합니다.
3. Data analytics - 데이터 생성 프로세스, 데이터 웨어하우스 테이블, 비즈니스 용어를 이해하고, 명확한 질문을 하는 능력.
4. Deliberate practice - 실습에서는 TPC-H 데이터셋을 사용하지만, 실제 데이터는 훨씬 복잡하고 불규칙적일 것입니다. 데이터 생성 방식을 파악하고, 비즈니스 가정을 검증하고, 더 많은 데이터를 수집하는 프로세스를 구축하고, 변화율이 높은 차원 테이블을 생성하는 등의 작업을 직접 수행해야 합니다.