Case de pipeline com dbt(parte 1): Transformação de dados

Source: https://github.com/jmilhomem/dbt_purchases_project

Como falamos anteriormente, o dbt se trata de um framework, o qual utiliza de SQL como base de sintaxe, para processamento/transformação de dados analíticos. Ou seja, tem como foco a etapa de Transformação (T) do ETL (Extraction, Transformation and Load).

E no post anterior, falamos sobre features fundamentais do DBT, com objetivo de dar uma visibilidade das funcionalidades necessárias para utilização do framework. Caso não tenha visto, clique aqui para ler mais sobre o assunto.

Legal! Já entendemos o que é o DBT, algumas features básicas e fundamentais do framework, agora bora falar de um case com começo, meio e fim no DBT para fixar esse entendimento?! Com ceeerteza!
Como citado anteriormente, o objetivo deste artigo é abordarmos um case para aplicação de transformação e tratamento dos dados, o qual utiliza desse projeto como referência para todo o conteúdo descrito nesse artigo.

BORA FALAR DO NOSSO CASE!

Precisamos apoiar uma empresa de bens de consumo novíssima a efetuar análises e responder algumas das perguntas abaixo:

• Qual o faturamento mensal da empresa?
• Quais as top 5 categorias de produtos mais compradas?
• Quais os produtos mais vendidos e que por isso, devem ser analisados em maior profundidade para manutenção de estoque?
• Quais produtos tiveram maior aplicação de ofertas e/ou descontos e que com isso, tiveram saídas? E quais não tiveram?
• Quais lojas que tem a maior volumetria de vendas?

E por aí vai! Como percebeu, em resumo, através da construção de uma estrutura analítica, nosso objetivo é apoiar a empresa a fazer suas análises descritivas, ou seja, possibilitar que faça suas explorações de dados!

Legal! E qual será nossa base de dados utilizada?

Como descrito abaixo em detalhes, a base de dados disponível é composta por 2 arquivos em excel, os quais devem ser ingeridos e importados no banco de dados para que seja possível o processo de tratamento dos dados e posterior fonte para análises, como iremos abordar posteriormente.

• Os arquivos utilizados são originados do Kaggle e podem ser baixados através desse link.
• Como o Kaggle em geral contém arquivos para modelos de dados de Data Science, em geral, não possui de domínio da maioria dos dados, possuindo dessa forma, apenas os seus ids, o que é exatamente o caso dos nossos arquivos.

Descritivo de arquivos e respectivos atributos de dados utilizados:

1. transactions: contém o histórico de transações para todos os clientes por um período de pelo menos 1 ano antes do incentivo oferecido

• Id — id único que representa um cliente
• chain — número inteiro que representa uma rede de lojas
• dept — agrupamento agregado da categoria (por exemplo, água)
• category - a categoria do produto (por exemplo, água com gás)
• company — id da empresa que vende o item
• brand — id da marca à qual o item pertence
• date — a data da compra
• productize — valor da compra do produto (por exemplo, 16 dólares para produto água)
• productmeasure —unidades da compra do produto
• purchasequantity — número de unidades compradas
• purchaseamount — valor em dólares da compra

Observação: Valores negativos em productquantity e/ou em purchaseamount indicam devolução de produto.

2. offers : contém informações sobre as ofertas:

• offer — id que representa uma determinada oferta
• category — categoria do produto (por exemplo, água com gás)
• quantity —número de unidades que se deve comprar para obter o desconto
• company — id da empresa que vende o item
• offervalue — valor em dólares da oferta
• brand — id da marca à qual o item pertence

Estruturando o modelo para considerar o processo de transformação dos dados!

IMPORTAÇÃO DOS DADOS:

O primeiro passo para a transformação dos dados é efetuar a disponibilização dos arquivos em /data:

Os arquivos precisam ser disponibilizados nessa pasta e importados através do comando descrito posteriormente.
Ao executar o comando, o DBT irá efetuar a criação de tabelas com mesma nomenclatura dos arquivos. Sendo assim, na nomenclatura não considere nada case-sensitive (ou seja, não considere espaços, ou qualquer caracter especial, assim como, não considere initcap).
A mesma regra deve ser considerada para os atributos dos arquivos.

Importante: Ao executar o comando dbt, as tabelas serão criadas e os dados serão importados. Com isso, se o mesmo comando for executado novamente, será apresentado erro, devido as tabelas já existirem.

TRANSFORMAÇÃO DOS DADOS:

Como já abordado, como este projeto está considerando um conjunto de dados limitado (sem os domínios de cada id), para modelagem de dados analítica foi considerada a “técnica de flat table”, a qual é geralmente utilizada pelos times de Data Science para criação de modelos, tendo como colunas, as features (ou também chamadas variáveis).
Se tivessemos dados de domínios, as estratégias de modelagem seriam consideravelmente expandidas, devido diferentes técnicas de modelagem dos dados, o que teríamos como consequência, maior riqueza no processo de análise.

Dito isso, vamos iniciar discussão sobre a estrutura para processo de transformação!
Ao final, deverá ser criada a seguinte DAG (Directed Acyclic Graph):

Source: https://github.com/jmilhomem/dbt_purchases_project

Com propósito de organização e tratamento dos dados, o processo ETL está dividido em 3 etapas (e pastas):

• raw: contém o arquivo source.yml, que possui as tabelas de origem.
• staging: contém os modelos de staging, modelos intermediários, que têm as transformações aplicadas.
• processed: contém os modelos finais, os quais criam as tabelas analíticas e fontes de dados para análise, onde são aplicadas as transformações finais.

RAW:

purchases.yml: Esse se trata do arquivo que contém a source de dados raw de todo o pipeline.

• sources. name: Possui o nome do schema do repositório que contém os dados raw da base, no caso warehouse.
• tables.name: Possui os nomes das tabelas criadas, as quais contém os dados raw. No caso, transactions e offers.

STAGING:

stg_offers.sql: Base criada para conter todas as ofertas, mantendo apenas as ofertas (por marca, categoria e fornecedor) com data mais recente.

stg_transactions.sql: Base criada para listar todas as transações realizadas, marcando informações de compras realizadas e devoluções efetuadas.

stg_transactions_offers.sql: Base criada para listar todas as transações realizadas, identificando transações + produtos que tiveram ofertas e/ou descontos, adicionalmente às informações tratadas anteriormente.

schema.yml: Se trata do arquivo, o qual contem validações de dados a serem feitas em tabelas de staging, que nesse caso, aplica validação de garantia de dados não nulos na tabela stg_transactions, já que a coluna id se trata da primary key da tabela e espera-se que não tenha registros nulos.

PROCESSED:

analytics_offers.sql: Base criada para listar volumetria de ofertas por categoria, fornecedor e marca.

analytics_transactions.sql: Base criada para análise de dados raw de transações e ofertas com tratamentos aplicados.

analytics_transactions_yearmonth.sql: Base criada para visualização de resultados mensais a nível granular de transações, identificando categoria de produtos, fornecedores e volumetria de produtos vendidos, assim, como, faturamentos.

schema.yml: Se trata do arquivo, o qual contem validações de dados a serem feitas para tabelas analíticas, que nesse caso, aplica validação em duas tabelas principais:
1. Analytics_offers: Checa se a combinação de categoria, fornecedor e marca é única, já que se trata de chave para a tabela final criada. Alem de validar se os registros nunca estão nulos, como esperado.
2. analytics_transactions: Checa se a combinação de id de transações, categoria, fornecedor e marca resulta registros nulos, como não esperado.

Como executar o processo full de carga?

1.No terminal, dentro do seu projeto, execute o dbt seed para importar os arquivos disponíveis em /data.

dbt seed

2. No terminal, dentro do seu projeto, execute o dbt run para executar todo o pipeline etl.

dbt run

3. No terminal, dentro do seu projeto, execute o teste dbt para executar os testes relativos aos modelos criados.

dbt test

Gostaria de executar o projeto inteiro sem ter de reproduzir os códigos criados, é possível?

Opa! Com certeza!
Caso não queira reproduzir o código inteiro, basta baixar/clonar do repositório do git (https://github.com/jmilhomem/dbt_purchases_project) e executá-lo! Para isso, basta considerar os seguintes pré-requisitos:

• Utilização de Linux/Ubuntu
• Ter o dbt instalado
• Possuir uma instância de Postgres criada localmente ou em cloud (esse projeto utiliza de AWS RDS)
• Ter o profiles.yml preenchido com configuração de repositório analítico criado e disponível em ~/.dbt
• Ter instalado o python 3.6
• Ter instalado o make

O processo para execução do projeto está disponível e descrito em detalhes no readme. Basta segui-lo, e divirta-se!

Super bacana, né?! Espero que tenha aprendido e se divertido no processo!

A próxima etapa se trata da integração com Python, a qual tem como objetivo fazer a orquestração e acompanhamento do log de carga.

Fique atentx e nos vemos em breve! 😉

Até lá! ❤