혼돈을 이익으로 전환: ETL 프로세스 이해

게시 됨: 2022-03-11

기업이 비즈니스 사용자에게 중요한 의사 결정 정보를 제공하지 못하게 하는 가장 중요한 실수 중 하나는 하나 이상의 데이터 소스에서 신뢰할 수 있는 데이터가 없다는 것입니다.

이 시나리오를 상상해 보십시오. 귀하는 미국 전역에 소매점을 두고 있는 휴대폰 케이스 회사의 IT 관리자입니다. 귀하의 이사회는 정보를 사용할 수 있지만 위치와 형식이 다르기 때문에 매출을 기반으로 결정을 내리는 데 어려움을 겪고 있습니다. 이사 중 한 명이 다음 이사회 회의에서 발표할 모든 판매 시점의 판매 정보가 포함된 대시보드를 생성하도록 요청합니다.

다양한 형식과 구조로 인해 정보를 통합하는 것이 거의 불가능하다는 것을 이미 알고 있습니다. 일부 소매 키오스크는 여전히 Oracle 데이터베이스의 독점 시스템을 사용하고 있습니다. 더 큰 매장에서는 새로운 Salesforce 시스템을 사용하고 있습니다. 시스템 전환 중에 운영을 시작한 최신 키오스크에는 매출 계산에 사용되는 공유 스프레드시트가 있습니다.

다양한 위치, 형식 및 구조의 모든 데이터를 대시보드 생성을 위해 액세스할 수 있는 고유한 데이터베이스로 어떻게 조합하시겠습니까?

ETL 기초

ETL은 Extract, Transform, Load 의 약자입니다. ETL은 이 복잡한 데이터 저장소를 조직적이고 안정적이며 복제 가능한 프로세스로 전환하여 이미 보유하고 있는 데이터로 더 많은 매출을 창출할 수 있도록 설계된 프로세스 그룹입니다.

우리의 경우 Oracle 데이터베이스(대부분의 키오스크), Salesforce(상점) 및 스프레드시트(최신 키오스크)에서 데이터를 수신하고, 데이터를 추출하고, 필요한 경우 변환하고, 단일 데이터 웨어하우스 데이터베이스에 로드하여 보고 도구에서 액세스하고 대시보드 및 스코어카드를 생성하는 데 사용됩니다.

프로세스를 자세히 설명하기 위해 ETL의 세 단계를 살펴보겠습니다.

추출

추출은 단일 또는 여러 소스에서 데이터를 수신하는 프로세스입니다. 소스는 문서, 스프레드시트, CSV 파일, 플랫 파일, Oracle, MySQL, SQL Server와 같은 관계형 데이터베이스, 비관계형 데이터베이스 등과 같은 다양한 형식과 구조를 가질 수 있습니다.

추출에는 전체 및 부분 의 두 가지 주요 유형이 있습니다.

  • 전체 추출 은 초기 추출에 사용되거나 데이터의 양과 결과적으로 추출 시간이 허용될 때 사용됩니다.
  • 전체 데이터를 다시 추출할 필요가 없거나 데이터 양이 너무 많아 전체 추출이 불가능한 경우 부분 추출 을 권장합니다. 부분 추출에서는 업데이트된 데이터나 새 데이터만 추출됩니다.

이러한 측면 외에도 전체 또는 부분 추출 중에서 선택할 때 몇 가지 다른 고려 사항이 필요하며 그 중 하나인 데이터 가용성 및 무결성 을 설명하고자 합니다.

이는 완료된 트랜잭션만 추출을 위해 고려되어 무결성 오류를 유발할 수 있는 데이터를 제거함을 의미합니다. 예를 들어, 10개의 질문으로 엔지니어의 지식을 식별하는 온라인 테스트입니다. 엔지니어가 테스트 중이고 몇 가지 질문에 답했지만 아직 완료하지 않은 경우 추출 프로세스는 완료되지 않은 테스트에서 질문을 읽을 수 없습니다. 무결성 오류가 발생할 수 있습니다.

변환

데이터를 추출한 후 변환 프로세스를 시작할 수 있습니다. 허용 가능한 수준의 데이터 품질을 유지하기 위해 비즈니스 규칙 및 기술 기준에 따라 데이터를 정리, 조작 및 변환합니다.

여러 요인에 따라 스테이징 영역을 사용해야 할 수도 있습니다. 스테이징 영역은 변환할 데이터 소스에서 추출한 데이터를 임시로 저장하는 데 사용되는 중간 저장 공간입니다.

일부 프로젝트, 일반적으로 데이터 양이 적은 프로젝트에서는 스테이징 영역을 사용할 필요가 없지만 대부분의 프로젝트에서 사용합니다.

변환 단계에서 수행되는 다양한 작업이 있습니다.

  • 선택: 추출된 데이터를 선택하는 기준입니다. 추출 단계, 변환 단계 또는 두 단계 모두에서 선택할 수 있습니다.
  • 통합: 추출 단계에서 준비 영역까지의 데이터 조합. 이 조합은 추출된 데이터를 기반으로 스테이징 영역의 데이터를 추가, 삭제 및 업데이트하는 것을 의미합니다.
  • 조인: 추출된 데이터를 조인하는 데 사용되며 SQL 조인(내부 조인, 왼쪽 조인, 오른쪽 조인, 전체 조인 등)과 유사합니다.
  • 정리 또는 스크러빙: 일관성이 없거나 유효하지 않은 데이터 또는 오류가 있는 데이터를 제거하여 데이터 품질을 개선합니다. 여러 데이터 원본으로 작업하면 다음과 같이 정리가 필요한 데이터 문제가 발생할 가능성이 높아집니다.
    • 참조 무결성(카테고리가 존재하지 않는 고객)
    • 누락된 값(ID가 없는 고객)
    • 고유성(같은 SSN을 가진 두 명 이상의 사람)
    • 맞춤법 오류(Sun Diego, Cannada, L.Angeles)
    • 모순된 값(Alex dob 04.27.1974, Alex dob 04.14.2000)
    • 그리고 많은 다른 사람들
  • 요약: 사후 사용을 위한 데이터 집합 요약
  • 집계: 그룹으로 수집 및 요약된 데이터
  • 통합: 단일 데이터 세트로 통합된 여러 소스 또는 구조의 데이터

다음은 몇 가지 일반적인 변환 유형입니다.

  • 중복 데이터 삭제
  • 분할 및 병합
  • 변환(날짜, 시간, 숫자 마스크, 측정)
  • 인코딩(남에서 M으로)
  • 계산(item_value = unit_Price * 수량)
  • 키 생성

로딩 중

마지막으로 ETL의 최종 프로세스는 데이터를 대상으로 로드하는 것입니다. 로딩은 변환된 데이터(스테이징 영역이든 아니든)를 저장소(일반적으로 데이터 웨어하우스 데이터베이스)에 삽입하는 작업입니다.

데이터 로드에는 전체 또는 초기, 증분 및 새로 고침의 세 가지 주요 유형이 있습니다.

  • 전체 또는 초기 는 추출 및 변환된 데이터의 전체 로드를 의미합니다. 스테이징 영역의 모든 데이터는 비즈니스 사용자가 사용할 수 있도록 최종 대상에 로드됩니다.
  • 증분 로드 는 변환된 데이터를 최종 대상의 데이터와 비교하고 새 데이터만 로드하는 프로세스입니다. 증분 로드는 아래에서 설명하는 새로 고침 로드와 함께 사용할 수 있습니다.
  • 새로 고침 로드 는 원본 소스의 변경 사항을 반영하기 위해 최종 대상의 데이터를 업데이트하는 프로세스입니다. 새로 고침은 전체 또는 증분일 수 있습니다.

요약하면, 규모에 관계없이 모든 회사는 ETL 프로세스를 사용하여 이미 존재하는 정보를 통합하고 의사 결정을 위해 훨씬 더 많은 정보를 생성하여 이전에는 사용할 수 없었던 데이터를 새로운 수익원으로 전환할 수 있습니다.

테스트

테스트는 ETL의 가장 중요한 단계 중 하나지만 가장 간과되는 단계이기도 합니다.

다양한 소스와 구조의 데이터를 변환하고 데이터 웨어하우스에 로드하는 것은 매우 복잡하고 오류를 생성할 수 있습니다. 가장 일반적인 오류는 위의 변환 단계에서 설명되었습니다.

데이터 정확성은 성공의 열쇠이고 부정확성은 재앙의 지름길입니다. 따라서 ETL 전문가는 전체 프로세스 동안 데이터 무결성을 보장하는 임무가 있습니다. 각 단계 후에 테스트를 수행해야 합니다. 단일 소스에서 데이터를 추출하든 여러 소스에서 데이터를 추출하든 오류가 없는지 확인하기 위해 데이터를 확인해야 합니다.

변환 후에도 동일한 작업을 수행해야 합니다. 예를 들어 변환 단계에서 데이터를 요약할 때 손실된 데이터가 없고 합계가 올바른지 확인하기 위해 데이터를 확인해야 합니다.

변환된 데이터를 데이터 웨어하우스에 로드한 후 테스트 프로세스를 다시 적용해야 합니다. 로드된 데이터는 변환된 데이터와 비교한 후 추출된 데이터와 비교해야 합니다.

휴대폰 케이스 회사의 예에서는 세 가지 소스(독점 Oracle 데이터베이스, Salesforce 및 스프레드시트)와 다른 형식으로 작업하고 있습니다. 테스트 단계에서는 원본 소스의 샘플 데이터를 사용하고 이를 스테이징 영역에 있는 데이터와 비교하여 오류 없이 추출이 발생했는지 확인할 수 있습니다.

이 경우 서로 다른 세 위치(매장, 오래된 키오스크, 새 키오스크)의 판매 정보일 수 있는 샘플 데이터를 원본 소스와 비교해야 합니다. 차이점이 있는 경우 허용 가능한지 또는 오류인지 확인하기 위해 분석해야 합니다.

오류가 발견되면 수정해야 하며 수정해야 하는 경우 몇 가지 결정을 내려야 합니다. 원본 데이터를 수정해야 합니까? 그렇게 할 수 있습니까? 원본 소스에서 오류를 수정할 수 없으면 일부 변환으로 수정할 수 있습니까?

어떤 경우에는 오류가 있는 데이터를 제거하고 담당자에게 알리기 위해 경고가 발생해야 합니다.

몇 가지 테스트 예:

  • 데이터 검증이 필요합니다
  • 데이터 품질
  • 성능
  • 데이터 규칙
  • 데이터 모델링

벌채 반출

ETL 프로세스를 기록하는 것은 유지 관리가 용이하고 수정하기 쉬운 시스템이 있다는 것을 보장하는 핵심입니다.

올바른 로깅 프로세스가 있는 ETL은 전체 ETL 작업을 지속적으로 개선된 상태로 유지하여 팀이 데이터 소스, 데이터 형식, 변환, 대상 등과 관련된 버그 및 문제를 관리하는 데 도움이 됩니다.

강력한 로그 프로세스를 통해 팀은 시간을 절약하여 문제를 보다 빠르고 쉽게 식별할 수 있으며 수석 엔지니어는 문제를 직접 찾아내는 데 필요한 시간을 줄일 수 있습니다. 때로는 수많은 데이터를 추출하는 중에 오류가 발생하고 로그가 없으면 문제를 식별하는 것이 어렵습니다. 때로는 거의 불가능합니다. 로그가 없으면 전체 프로세스를 다시 실행해야 합니다. 팀은 로그를 사용하여 문제를 일으킨 파일과 행을 빠르게 식별하고 해당 데이터만 수정할 수 있습니다.

로그가 그다지 중요하지 않다고 상상할 수 있는 유일한 경우는 프로세스가 수동으로 실행되고 손으로 모니터링할 수 있는 소량의 데이터가 있는 매우 작은 자동화되지 않은 시스템입니다.

로그는 자동화를 개선합니다. 자동으로 실행되는 대용량 데이터가 있는 ETL 프로세스에는 로그 시스템이 필요합니다. 잘 계획되고 실행된다면, 로깅 시스템을 구축하기 위한 모든 노력은 더 빠른 오류 식별, 더 안정적인 데이터 및 로그 파일에서 발견되는 개선점의 형태로 배당금을 지불할 것입니다.

로그 시스템 생성에는 세 가지 주요 단계가 있습니다. 생성, 보관 및 분석 .

  • 생성 은 ETL 파이프라인을 실행하는 동안 진행 중인 작업을 문서화하는 프로세스입니다. 프로세스가 시작된 시간, 추출 중인 파일 또는 테이블, 스테이징 영역에 저장 중인 데이터, 오류 메시지 등. 엔지니어에게 도움이 될 수 있는 모든 중요한 정보는 기록되어야 합니다. 경고 : 시간과 공간만 소모하고 도움이 되지 않는 너무 많은 정보를 생성하지 않도록 주의하십시오.
  • 아카이브 로그 데이터는 오류를 식별하거나 개선을 찾기 위해 현재 시나리오와 비교하기 위해 과거 시나리오를 검색하기 위해 과거 실행의 추적 기록을 유지하는 것을 의미합니다. 저장해야 할 특정 시점의 관련성을 확인하는 것이 중요합니다. 오래 전에 구조가 여러 번 변경된 데이터는 보관할 가치가 없습니다.
  • 분석 . 로그 분석은 매우 중요합니다. 분석되지 않은 수많은 데이터를 저장하는 것은 의미가 없습니다. 데이터를 생성하고 저장하는 데만 시간과 비용이 듭니다. 로그 분석은 오류 검색에 도움이 될 뿐만 아니라 개선점을 식별하고 전반적인 데이터 품질을 높이는 데도 중요합니다.

성능

ETL 프로세스는 수많은 데이터를 처리할 수 있으며 설정에 소요되는 시간과 데이터를 처리하는 데 필요한 계산 리소스 측면에서 많은 비용이 들 수 있습니다. 통합을 계획할 때 엔지니어는 사용 중인 모든 데이터의 필요성을 염두에 두어야 합니다. 불필요한 데이터를 마이그레이션하는 데 시간과 비용을 들이지 않고 최소한의 데이터로 작업하여 목표를 달성하는 것이 가장 좋습니다. 또한 데이터 양은 시간이 지남에 따라 증가하는 경향이 있으므로 향후 요구 사항을 고려하십시오.

로깅 시스템을 설계하는 데 얼마나 많은 노력을 기울여야 할까요?

처리할 데이터의 양과 빈도와 같은 다양한 요인에 따라 다릅니다. 데이터가 거의 없는 소규모 시스템은 고급 로깅 ​​시스템에 투자할 필요 없이 수동으로 관리할 수 있습니다.

많은 양의 데이터, 다양한 프로세스, 다양한 데이터 소스 및 형식, 복잡한 파이프라인을 보유한 회사는 로깅 시스템을 만드는 데 투자해야 합니다. 이미 언급했듯이 좋은 로그 시스템은 많은 시간과 비용을 절약할 수 있습니다.

회사 규모, 데이터 양, 통합 빈도에 관계없이 보다 분명한 접근 방식을 위해서는 ETL 프로세스가 수익성이 있어야 합니다. 경제학자 Milton Friedman이 말했듯이 시간, 돈, 기술 자원에 대한 투자는 수익을 창출해야 합니다. "공짜 점심은 없습니다."

요약하면 ETL 프로세스는 기업이 이미 보유하고 있지만 올바른 방식으로 사용되지 않는 데이터로 수익을 늘리는 데 도움이 될 수 있습니다. ETL 파이프라인은 다른 시스템, 중요한 정보가 포함된 스프레드시트, 다른 부서 및 지점에 분산되어 있는 데이터의 다른 부분을 통합하여 조직이 데이터를 최대한 활용할 수 있도록 합니다.