Databricks SQL로 차원 데이터 웨어하우스 구현하기, 파트 3

차원 모델링은 분석 준비 데이터 웨어하우스를 구축하는 데 검증된 방법입니다. 많은 조직들이 Databricks와 같은 현대적인 플랫폼으로 전환하고 있지만, 이러한 기본적인 기법들은 여전히 적용됩니다.

파트 1에서, 우리는 차원 스키마를 설계했습니다. 파트 2에서, 우리는 차원 테이블을 위한 ETL 파이프라인을 구축했습니다. 이제 파트 3에서는, 효율성과 무결성을 강조하여 사실 테이블을 위한 ETL 로직을 구현합니다.

사실 테이블과 델타 추출

첫 번째 블로그에서 에서, 우리는 사실 테이블을 정의했습니다, FactInternetSales아래와 같이 표시됩니다. 차원 테이블에 비해 사실 테이블은 레코드 길이 측면에서 상대적으로 좁으며, 차원 테이블에 대한 외래 키 참조, 사실 측정치, 우리의 퇴화 차원 필드 및 단일 메타데이터 필드만이 존재합니다:

주의: 아래 예시에서는 첫 번째 게시물에서 가져온 CREATE TABLE 문 을 수정하여 외래 키 정의를 포함시켰고, 이를 별도의 ALTER TABLE 문에서 정의하는 대신 이 사실 테이블에서 역할을 더 명확하게 하기 위해 퇴화 차원 필드에 기본 키 제약을 포함시켰습니다.

테이블 정의는 상당히 간단하지만, LastModifiedDateTime 메타데이터 필드에 대해 잠시 논의하는 것이 가치가 있습니다. 사실 테이블은 필드 수 측면에서 상대적으로 좁지만, 행 수 측면에서는 매우 깊습니다. 사실 테이블은 종종 수백만, 아니 수십억 개의 레코드를 보유하며, 이는 대부분 고용량 운영 활동에서 파생됩니다. 각 ETL 주기에서 전체 추출로 테이블을 다시 로드하려고 시도하는 대신, 우리는 일반적으로 새 레코드와 변경된 레코드에 대한 노력을 제한할 것입니다.

소스 시스템과 그에 따른 기본 인프라에 따라, 어떤 운영 레코드가 주어진 ETL 사이클로 추출되어야 하는지 식별하는 방법은 많습니다. 변경 데이터 캡처 (CDC) 기능이 운영 측에 구현되면 가장 신뢰할 수 있는 메커니즘이 됩니다. 그러나 이러한 기능이 사용할 수 없을 때, 우리는 종종 각 거래 레코드가 생성되고 수정될 때 기록된 타임스탬프에 의존합니다. 이 방법은 변경 감지에 완벽하지는 않지만, 경험이 많은 ETL 개발자라면 이것이 종종 최선의 선택이라는 것을 알게 될 것입니다.

참고: Lakeflow Connect의 도입은 관계형 데이터베이스에서 변경 데이터 캡처를 수행하는 흥미로운 옵션을 제공합니다. 이 기능은 이 블로그를 작성하는 시점에 미리보기 상태이지만, 이 기능이 점점 더 많은 RDBMS를 확장하면서 점진적인 추출을 위한 효과적이고 효율적인 메커니즘을 제공할 것으로 예상합니다.

우리의 사실 테이블에서, LastModifiedDateTime 필드는 운영 시스템에서 기록된 타임스탬프 값을 캡처합니다. 운영 시스템에서 데이터를 추출하기 전에, 우리는 사실 테이블을 검토하여 이 필드에 대해 우리가 기록한 가장 최근의 값을 식별할 것입니다. 그 값은 우리의 증분(델타) 추출의 시작점이 될 것입니다.

사실 ETL 워크플로우

우리의 사실 ETL에 대한 고수준 워크플로우는 다음과 같이 진행될 것입니다:

1. 우리의 사실 테이블에서 가장 최근의 LastModifiedDateTime 값을 검색합니다.
2. 최신의 LastModifiedDateTime 값 이후의 타임스탬프를 가진 원본 시스템에서 관련 트랜잭션 데이터를 추출합니다.
3. 추출된 데이터에 필요한 추가 데이터 클렌징 단계를 수행합니다.
4. 연관된 차원에 늦게 도착한 멤버 값들을 게시합니다.
5. 관련된 차원에서 외래 키 값을 찾습니다.
6. 데이터를 팩트 테이블에 게시합니다.

이 워크플로우를 더 쉽게 이해할 수 있도록, 다음 섹션에서 주요 단계를 설명하겠습니다. 차원 ETL에 대한 게시물과 달리, 우리는 이 워크플로우에 대한 로직을 SQL과 Python의 조합을 사용하여 구현할 것입니다. 이는 각 단계를 가장 직관적으로 구현하는 언어에 따라 다릅니다.  또한, Databricks Platform의 강점 중 하나는 여러 언어를 지원하는 것입니다. 이를 전체 또는 없음의 선택으로 제시하는 대신, 우리는 데이터 엔지니어가 단일 구현 내에서 두 언어 사이에서 빠르게 전환할 수 있는 방법을 보여줄 것입니다.

단계 1-3: 델타 추출 단계

우리 워크플로우의 처음 두 단계는 운영 시스템에서 새로운 정보와 최근에 업데이트된 정보를 추출하는 데 초점을 맞춥니다. 첫 번째 단계에서는 LastModifiedDateTime  에 대한 최신 기록된 값을 간단히 조회합니다. 사실 테이블이 초기화 시점에 비어있다면, 우리는 소스 시스템에서 모든 관련 데이터를 캡처할 것이라고 믿는 시간으로 충분히 뒤로 돌아가는 기본값을 정의합니다:

이제 우리는 그 값을 사용하여 우리의 운영 시스템에서 필요한 데이터를 추출할 수 있습니다.  이 쿼리에는 상당히 많은 세부 정보가 포함되어 있지만, WHERE 절에 주목하세요. 여기서 우리는 이전 단계에서 관찰된 마지막 타임스탬프 값을 사용하여 새로운 또는 수정된 개별 항목(또는 새로운 또는 수정된 판매 주문과 관련된 항목)을 검색합니다:

이전과 마찬가지로, 추출된 데이터는 워크플로우의 후속 단계를 진행하기 전에 우리의 데이터 엔지니어만이 접근할 수 있는 스테이징 스키마의 테이블에 유지됩니다. 추가적인 데이터 클렌징을 수행해야 한다면, 지금이 그 때입니다.

단계 4: 늦게 도착하는 멤버 단계

데이터 웨어하우스 ETL 주기에서 일반적인 순서는 차원 ETL 워크플로우를 실행하고 그 직후에 사실 워크플로우를 실행하는 것입니다.  이런 방식으로 우리의 프로세스를 구성함으로써, 우리의 사실 레코드를 차원 데이터에 연결하는 데 필요한 모든 정보가 제자리에 있음을 더 잘 보장할 수 있습니다.  그러나 새로운 차원 지향 데이터가 도착하고 사실 관련 트랜잭션 레코드에 의해 선택되는 좁은 창이 있습니다.  이 창은 전체 ETL 사이클에서 실패가 발생하여 사실 데이터 추출이 지연되면 늘어납니다.  그리고, 물론, 출처 시스템에서 참조 실패가 발생하여 트랜잭션 레코드에 의심스러운 데이터가 나타날 수 있습니다.

이 문제로부터 우리 자신을 보호하기 위해, 우리는 스테이지된 팩트 데이터에서 발견되지만 현재(만료되지 않은) 차원 레코드 세트에는 없는 모든 비즈니스 키 값을 주어진 차원 테이블에 삽입할 것입니다.  이 방법은 사실 테이블이 참조할 수 있는 비즈니스 (자연) 키와 대체 키를 가진 레코드를 생성합니다.  이 레코드들은 대상 차원이 Type-2 SCD인 경우 늦게 도착한 것으로 표시되어 다음 ETL 주기에서 적절하게 업데이트할 수 있습니다.

시작하기 위해, 우리는 스테이징 데이터에서 주요 비즈니스 필드의 목록을 컴파일할 것입니다.  여기서, 우리는 이러한 필드를 동적으로 식별할 수 있게 해주는 엄격한 명명 규칙을 활용하고 있습니다:

참고: 다음 코드 예제에 대해 Python으로 전환하고 있습니다. Databricks는 동일한 워크플로우 내에서 여러 언어의 사용을 지원합니다. 이 예제에서, Python은 SQL 개념과 여전히 일치하면서 조금 더 유연성을 제공하므로, 이 접근 방식이 더 전통적인 SQL 개발자에게 접근 가능하게 합니다. 

날짜 키를 다른 비즈니스 키와 분리했음을 알 수 있습니다.  잠시 후에 그것들로 돌아가겠지만, 지금은 이 테이블의 날짜가 아닌 (다른) 키에 집중해봅시다.

각 비날짜 비즈니스 키에 대해, 우리는 필드와 테이블 명명 규칙을 사용하여 해당 키를 보유해야 하는 차원 테이블을 식별하고, 스테이징 테이블에서 차원 테이블에 없는 해당 컬럼의 값들을 식별하기 위해 왼쪽 반조인 (NOT IN() 비교와 유사하지만 필요한 경우 다중 컬럼 매칭을 지원)을 수행합니다.  우리가 일치하지 않는 값을 찾을 때, 우리는 단순히 그것을 IsLateArriving 필드에 적절한 설정으로 차원 테이블에 삽입합니다: 

이 로직은 우리의 사실 레코드가 유효한 항목에 연결되도록 하려면 우리의 날짜 차원 참조에 대해 잘 작동할 것입니다.  그러나, 많은 하류 BI 시스템은 날짜 차원이 가장 이른 값과 가장 늦은 값 사이에 연속적이고 끊어지지 않는 일련의 날짜를 포함하도록 하는 로직을 구현합니다.  만약 테이블의 값 범위 이전이나 이후의 날짜를 만나게 된다면, 누락된 멤버를 입력하는 것뿐만 아니라 끊김 없는 범위를 유지하기 위해 필요한 추가 값들을 생성해야 합니다.  그 이유로, 우리는 늦게 도착하는 날짜에 대해 약간 다른 로직이 필요합니다:

만약 당신이 Databricks나 Spark SQL과 많이 작업해 보지 않았다면, 이 마지막 단계의 핵심 쿼리는 아마도 생소할 것입니다. sequence() 함수는 지정된 시작과 중지를 기반으로 값의 시퀀스를 구축합니다. 결과는 우리가 explode() 함수를 사용하여 배열의 각 요소가 결과 세트의 행을 형성하도록 폭발시킬 수 있는 배열입니다. 그런 다음, 우리는 필요한 범위를 차원 테이블에 있는 것과 비교하여 어떤 요소가 삽입되어야 하는지 식별합니다. 그 삽입으로, 우리는 이 차원에서 사실 레코드가 참조할 수 있는 것을 보장하기 위해 스마트 키 로 대체 키 값을 구현하고 있습니다. 

단계 5 - 6: 데이터 게시 단계

이제 스테이징 테이블의 모든 비즈니스 키가 해당 차원의 레코드와 일치할 수 있다는 것을 확신할 수 있으므로, 사실 테이블로의 게시를 진행할 수 있습니다.

이 과정의 첫 번째 단계는 이러한 비즈니스 키에 대한 외래 키 값을 조회하는 것입니다.  이는 단일 게시 단계의 일부로 수행될 수 있지만, 쿼리에서 조인의 수가 많아 이 접근 방식을 유지하는 것이 종종 어렵습니다. 이러한 이유로, 우리는 덜 효율적이지만 이해하고 수정하기 쉬운 방법을 선택하여 외래 키 값을 한 번에 하나씩 찾아 스테이징 테이블에 추가할 수 있습니다:

다시 한번, 우리는 이 로직을 더 간단하게 구현하기 위해 명명 규칙을 활용하고 있습니다. 우리의 날짜 차원은 역할 연주 차원 이고 따라서 더 변동성 있는 명명 규칙을 따르기 때문에, 우리는 그 비즈니스 키들에 대해 약간 다른 로직을 구현합니다.

이 시점에서, 우리의 스테이징 테이블은 비즈니스 키와 대체 키 값, 그리고 우리의 측정치, 퇴화 차원 필드, 그리고 소스 시스템에서 추출된 LastModifiedDate 값을 함께 보유하고 있습니다. 게시를 더 쉽게 관리하기 위해, 우리는 사용 가능한 필드를 사실 테이블이 지원하는 필드와 일치시켜야 합니다. 그러기 위해, 우리는 비즈니스 키를 제거해야 합니다:

참고: source 데이터프레임은 이전 코드 블록에서 정의되었습니다.

필드가 정렬되면, 출판 단계는 간단합니다. 우리는 들어오는 레코드를 사실 테이블의 레코드와 퇴화 차원 필드를 기반으로 일치시키고, 이 필드는 사실 레코드에 대한 고유 식별자 역할을 하며, 필요에 따라 값을 업데이트하거나 삽입합니다:

다음 단계

이 블로그 시리즈가 Databricks 플랫폼에서 차원 모델을 구축하려는 사람들에게 유익했기를 바랍니다.  이 데이터 모델링 접근법과 그와 관련된 ETL 워크플로우에 익숙한 많은 사람들이 Databricks를 친숙하고 접근 가능하며, RDBMS 플랫폼에서 구현되었을 수 있는 것에 비해 최소한의 변경으로 오랫동안 확립된 패턴을 지원할 수 있을 것이라고 예상합니다. 변화가 생기는 곳에서, 예를 들어 Python과 SQL을 결합하여 워크플로우 로직을 구현할 수 있는 능력과 같은 경우, 우리는 데이터 엔지니어들이 이것이 시간이 지남에 따라 구현하고 지원하는 작업을 더 간단하게 만들어 줄 것이라고 기대합니다.

Databricks SQL에 대해 더 알아보려면 우리의 웹사이트 를 방문하거나 문서를 읽어보세요. 또한 Databricks SQL의 제품 투어를 확인해 볼 수 있습니다. 만약 당신이 기존의 창고를 뛰어난 사용자 경험과 더 낮은 총 비용을 가진 고성능, 서버리스 데이터 창고로 이전하려는 경우, Databricks SQL이 해결책입니다 — 무료로 시도해보세요.

 

(이 글은 AI의 도움을 받아 번역되었습니다. 원문이 궁금하시다면 여기를 클릭해 주세요)