À medida que as organizações consolidam as cargas de trabalho de análise em banco de dados, elas geralmente precisam adaptar as técnicas tradicionais de data warehouse. Esta série explora como implementar a modelagem dimensional – especificamente, esquemas de estrela – em banco de dados. O primeiro blog focou no design de esquema. Este blog atrai pipelines ETL para tabelas de dimensões, incluindo os padrões de Dimensões Tipo 1 e Tipo-2 em mudança lenta (SCD). O último blog mostrará como criar tabelas ETL para fatos.

Dimensões em mudança lentamente (SCD)

No Último blogdefinimos nosso esquema de estrelas, incluindo uma tabela de fatos e suas dimensões relacionadas. Destacamos uma tabela de uma dimensão em particular, Dimcustomer, como mostrado aqui (com alguns atributos removidos para economizar espaço):

Os últimos três campos nesta tabela, ou seja, StartDateAssim, EndDate e Islatearrivingrepresente metadados que nos auxiliam nos registros de versões. Como renda de um determinado cliente, estado civil, propriedade da casa, número de crianças em casa ou outras características mudam, queremos criar novos registros para esse cliente, para que fatos como nossas transações de vendas on -line em FactInternetSales estão associados à representação certa desse cliente. O Chave natural (também conhecida como negócios)Assim, CustomeralternateKeyserá o mesmo nesses registros, mas os metadados serão diferentes, permitindo -nos saber o período para o qual a versão do cliente foi válida, assim como o chave substitutaAssim, CustomerKeypermitindo que nossos fatos se vinculem à versão certa.

OBSERVAÇÃO: Como a chave substituta é comumente usada para vincular fatos e dimensões, as tabelas de dimensão geralmente são agrupadas com base nessa chave. Diferentemente dos bancos de dados relacionais tradicionais que utilizam índices de árvore B em registros classificados, o Databricks implementa um método exclusivo de agrupamento conhecido como agrupamento líquido. Embora as especificidades do cluster de líquido estejam fora do escopo deste blog, usamos consistentemente o cluster por cláusula na chave substituta de nossas tabelas de dimensão durante sua definição para alavancar esse recurso de maneira eficaz.

Esse padrão de dimensão de versão registra como atributos, a mudança é conhecida como o Dimensão do tipo 2 de mudança lentamente (ou simplesmente padrão SCD do tipo 2). O padrão SCD tipo 2 é preferido para gravar dados de dimensão na metodologia dimensional clássica. No entanto, existem outras maneiras de lidar com mudanças nos registros de dimensão.

Uma das maneiras mais comuns de lidar com a mudança dos valores da dimensão é atualizar os registros existentes. Apenas uma versão do registro é criada, para que a chave de negócios continue sendo o identificador exclusivo para o registro. Por várias razões, não as menos as quais são o desempenho e a consistência, ainda implementamos uma chave substituta e vinculamos nossos registros de fatos a essas dimensões nessas chaves. Ainda assim, o StartDate e EndDate Campos de metadados que descrevem os intervalos de tempo sobre os quais um determinado registro de dimensão é considerado ativo não é necessário. Isso é conhecido como o Tipo 1 SCD padrão. A dimensão da promoção em nosso esquema Star fornece um bom exemplo de uma implementação da tabela de dimensão Tipo 1:

Mas e quanto ao Islatearriving Campo de metadados visto na dimensão do cliente Tipo 2, mas ausente na dimensão da promoção do tipo 1? Este campo é usado para sinalizar registros como chegada tardia. UM Registro de chegada tardia é aquele para o qual a chave de negócios aparece durante um ciclo ETL de fato, mas não há registro para essa chave localizada durante o processamento da dimensão anterior. No caso do SCDS tipo 2, esse campo é usado para denotar que, quando os dados para um registro de chegada tardia são observados pela primeira vez em um ciclo ETL da dimensão, o registro deve ser atualizado no local (assim como em um padrão SCD do tipo 1) e depois a versão a partir desse ponto. No caso do SCDS do Tipo 1, esse campo não é necessário porque o registro será atualizado no local, independentemente.

OBSERVAÇÃO: O grupo Kimball reconhece padrões adicionais de SCD, a maioria das quais são variações e combinações dos padrões do tipo 1 e do tipo 2. Como os SCDs tipo 1 e tipo 2 são os mais frequentemente implementados desses padrões e as técnicas usadas com as outras estão intimamente relacionadas ao que está empregado com elas, estamos limitando este blog a apenas esses tipos de duas dimensões. Para obter mais informações sobre os oito tipos de SCDs reconhecidos pelo grupo Kimball, consulte o Técnicas de dimensão lentamente em mudança seção de este documento.

Implementando o padrão SCD tipo 1

Com os dados sendo atualizados no local, o padrão de fluxo de trabalho SCD do tipo 1 é o mais direto dos padrões bidimensionais de ETL. Para apoiar esses tipos de dimensões, simplesmente:

  1. Extraia os dados necessários do (s) nosso (s) sistema operacional (s)
  2. Execute quaisquer operações de limpeza de dados necessárias
  3. Compare nossos registros recebidos com os que já estão na tabela de dimensões
  4. Atualize quaisquer registros existentes em que os atributos recebidos diferem do que já está registrado
  5. Insira todos os registros recebidos que não possuem um registro correspondente na tabela de dimensões

Para ilustrar uma implementação do Type 1 SCD, definiremos o ETL para a população em andamento do Dimpromotion mesa.

Etapa 1: Extrair dados de um sistema operacional

Nosso primeiro passo é extrair os dados do nosso sistema operacional. Como nosso data warehouse é padronizado após o banco de dados AdventureWorksdw Sample fornecido pela Microsoft, estamos usando o associado intimamente associado Banco de dados de amostra de AdventureWorks (OLTP) como nossa fonte. Este banco de dados foi implantado em uma instância do banco de dados SQL do Azure e tornado acessível em nosso ambiente de banco de dados por meio de um Consulta federada. A extração é então facilitada com uma consulta simples (com alguns campos redigidos para economizar espaço), com os resultados da consulta persistiu em uma tabela em nosso encenação Esquema (que é acessível apenas aos engenheiros de dados em nosso ambiente por meio de configurações de permissão não mostradas aqui). Esta é apenas uma das muitas maneiras pelas quais podemos acessar os dados do sistema de origem nesse ambiente:

Etapa 2: Compare os registros de entrada com os da tabela

Supondo que não tenhamos etapas adicionais de limpeza de dados a serem executadas (que poderíamos implementar com um ATUALIZAR ou outro Crie a tabela como declaração), podemos então abordar nossas operações de atualização/inserção de dados de dimensão em uma única etapa usando um Declaração de mesclagemCombinando nossos dados e dados de dimensão encenados sobre a chave de negócios:

Uma coisa importante a ser observada sobre a declaração, como foi escrita aqui, é que atualizamos quaisquer registros existentes quando uma correspondência é encontrada entre os dados da tabela de dimensões encenadas e publicadas. Poderíamos adicionar critérios adicionais à cláusula quando correspondentes para limitar as atualizações às instâncias em que um registro no estadiamento tem informações diferentes do que é encontrado na tabela de dimensões, mas, dado o número relativamente pequeno de registros nesta tabela em particular, optamos por empregar a lógica relativamente mais magra mostrada aqui. (Usaremos a lógica adicional quando correspondente com Dimcustomer, que contém muito mais dados.)

O padrão SCD tipo 2

O padrão SCD tipo 2 é um pouco mais complexo. Para apoiar esses tipos de dimensões, devemos:

  1. Extraia os dados necessários do (s) nosso (s) sistema operacional (s)
  2. Execute quaisquer operações de limpeza de dados necessárias
  3. Atualize qualquer registro de membro tardio na tabela de destino
  4. Expire todos os registros existentes na tabela de destino para os quais novas versões são encontradas na estadiamento
  5. Insira qualquer nova (ou nova versões) de registros na tabela de destino

Etapa 1: Extrair e limpar dados de um sistema de origem

Como no padrão SCD tipo 1, nossos primeiros passos são extrair e limpar dados do sistema de origem. Usando a mesma abordagem acima, emitimos uma consulta federada e persistimos os dados extraídos para uma tabela em nossa encenação esquema:

Etapa 2: Compare com uma tabela de dimensão

Com esses dados desembarcados, agora podemos compará -los com a nossa tabela de dimensões para fazer as modificações de dados necessárias. A primeira delas é atualizar em vigor que todos os registros sinalizados como tardios chegassem a partir de processos ETL da tabela de fatos anteriores. Observe que essas atualizações são limitadas a esses registros sinalizados como chegada tardia e o Islatearriving O FLAG está sendo redefinido com a atualização para que esses registros se comportem como SCDs do tipo 2 normais que avançam:

Etapa 3: Expire Records Versioned

O próximo conjunto de modificações de dados é expirar todos os registros que precisam ser versionados. É importante que o EndDate Valor que definimos para essas correspondências o StartDate Das novas versões de registro que implementaremos na próxima etapa. Por esse motivo, definiremos um registro de data e hora variável Para ser usado entre essas duas etapas:

OBSERVAÇÃO: Dependendo dos dados disponíveis, você pode optar por empregar um EndDate Valor originário do sistema de origem, momento em que você não declararia necessariamente uma variável como mostrado aqui.

Observe os critérios adicionais usados ​​na cláusula quando correspondida. Como estamos executando apenas uma operação com essa instrução, seria possível mover essa lógica para a cláusula ON, mas a mantemos separada da lógica de correspondência principal, onde estamos correspondendo à versão atual do registro de dimensão para maior clareza e manutenção.

Como parte dessa lógica, estamos usando pesado do igual_null () função. Esta função retorna verdadeira quando o primeiro e o segundo valores são iguais ou nulos; Caso contrário, ele retorna falsa. Isso fornece uma maneira eficiente de procurar alterações na base de coluna por coluna. Para mais detalhes sobre como os Databricks suportam a semântica nula, consulte este documento.

Nesta fase, quaisquer versões anteriores dos registros na tabela de dimensões que expiraram foram datadas de encerramento.

Etapa 4: Insira novos registros

Agora podemos inserir novos registros, tanto novos quanto recém -versão:

Como antes, isso poderia ter sido implementado usando um Inserir declaraçãomas o resultado é o mesmo. Com esta declaração, identificamos todos os registros na tabela de estadiamento que não possuem um registro correspondente não expirado nas tabelas de dimensão. Esses registros são simplesmente inseridos com um StartDate valor consistente com quaisquer registros expirados que possam existir nesta tabela.

Próximas etapas: Implementando a tabela de fatos ETL

Com as dimensões implementadas e preenchidas com dados, agora podemos nos concentrar nas tabelas de fato. No próximo blog, demonstraremos como o ETL para essas tabelas pode ser implementado.

Para saber mais sobre o Databricks SQL, visite nosso site ou leia a documentação. Você também pode conferir o Tour do produto para Databricks SQL. Suponha que você queira migrar seu armazém existente para um armazém de dados sem servidor e de alto desempenho, com uma ótima experiência do usuário e menor custo total. Nesse caso, o Databricks SQL é a solução – Experimente de graça.