audit_helper e dbt: elevando o nível da auditoria de dados

As ferramentas audit_helper e dbt são indispensáveis para aprimorar a auditoria de dados, garantindo precisão nos resultados em um modelo refatorado.

Na rotina de quem é analytics engineer, a auditoria de dados desempenha um papel crucial, especialmente ao lidar com a refatoração de tabelas criadas por meio de SQL Server Stored Procedures ou Alteryx Workflows.

É nesse contexto que entra em cena o pacote audit_helper, para auxiliar esse processo de auditoria de dados.

Vamos mostrar neste artigo como o audit_helper, como o próprio nome sugere, pode ajudar para que um modelo refatorado forneça resultados praticamente idênticos ao original.

Acompanhe essa jornada rumo à segurança e precisão dos seus projetos de análise de dados.

Boa leitura!

Por que refatorar modelos de transformação de dados?

As ferramentas audit_helper e dbt são indispensáveis para aprimorar a auditoria de dados, garantindo precisão nos resultados em um modelo refatorado.

É comum que profissionais da área de dados tenham que refatorar (ou reproduzir) modelos de transformação de dados criados em SQL Server® Stored Procedures, Alteryx Workflows® ou outras ferramentas de modelagem em modelos dbt, ou mesmo refatorar um modelo dbt para atualizar suas fontes de dados.

E isso pode ser feito com a ajuda de uma valiosa ferramenta: audit_helper.

Muitas vezes, algumas regras de negócios são aplicadas em diferentes modelos (e ferramentas), e quem é da área de engenharia de dados precisa garantir que os resultados sejam compatíveis.

No entanto, garantir que os valores na tabela original e na tabela refatorada correspondam costumava ser uma tarefa difícil, envolvia muita escrita de código e alguns testes genéricos. Essa abordagem não só consome bastante tempo, como também pode gerar suposições erradas, por exemplo, de que os valores correspondem, tendo como base medidas agregativas, como contagens ou somas.

Para fornecer uma abordagem de auditoria de dados mais avançada e precisa, o dbt Labs criou o audit_helper, um pacote para dbt cujo objetivo principal é auditar dados comparando duas tabelas (a tabela original versus um modelo refatorado).

Esse pacote usa uma estrutura de consulta simples e intuitiva em SQL, que permite comparar tabelas rapidamente com base nos valores das colunas, quantidade de linhas e até tipos de dados das colunas.

Veja a Figura 1 que a seguir ilustra o fluxo de trabalho e onde o audit_helper está posicionado no processo de refatoração:

Sabendo onde o audit_helper está posicionado no processo de refatoração, é importante destacar os benefícios de utilizá-lo e, principalmente, de auditar modelos refatorados.

Vamos aos benefícios do audit_helper:

• garantia de qualidade: o audit_helper garante que um modelo refatorado está chegando ao mesmo resultado que o modelo original;

• código fácil e intuitivo: como o audit_helper é baseado em macros do dbt, ele foi projetado para ser uma ferramenta intuitiva, executada por consultas (queries) simples de SQL;

• output claro: o audit_helper fornece um output claro, mostrando quanto sua tabela refatorada corresponde à original (legada);

• visibilidade para o projeto: a ferramenta audit_helper dá visibilidade a um processo de refatoração, mostrando como o código está entregando os mesmos resultados em comparações de linha e coluna;

• flexibilidade para comparar linhas e colunas: rapidamente e de modo simples, é possível comparar os resultados em linhas ou colunas através de modelos pré-prontos que exigem apenas que quem é analytics engineer ajuste os nomes de suas colunas e os do modelo original.

No tópico seguinte, você vai encontrar algumas dicas para o uso do audit_helper, incluindo instruções sobre:

1- como instalar o pacote audit_helper em seu projeto dbt;

2- como executar a comparação de linhas (com a macro compare_queries); e

3- como executar a comparação de colunas (com compare_column_values).

Como instalar o pacote audit_helper

Seguindo a documentação do dbt, o pacote audit_helper deve ser executado em versões do dbt superiores ou iguais a 1.2.0, e inferiores a 2.0.0.

Os dois passos a seguir explicam como você deve instalar e colocar o audit_helper em funcionamento. Veja!

Passo 1

Adicione um arquivo packages.yml ao seu projeto dbt, caso ainda não o tenha criado.

Dentro do arquivo packages.yml, especifique o ou os pacotes que deseja adicionar, usando uma das sintaxes suportadas.

Para adicionar o audit_helper, copie e cole o código a seguir, e observe que isso deve estar no mesmo nível do arquivo dbt_project.yml:

packages:
- package: dbt-labs/audit_helper
  version: 0.7.0

Passo 2

Execute o comando dbt deps na linha de comando para instalar o ou os pacotes, que serão  instalados no diretório dbt_packages.

Por padrão, esse diretório dbt_packages é ignorado pelo git, para evitar a duplicação do código-fonte do pacote.

Para mais informações sobre como usar pacotes em seu projeto dbt, você pode verificar a documentação do dbt.

E agora que o audit_helper está instalado, entenda suas duas principais macros:

• compare_queries - para auditar linhas; e
• compare_column_values ​​- para auditar valores em colunas.

Pacote audit_helper: auditoria de linhas (compare_queries)

A documentação do pacote audit_helper aponta que essa macro é útil para:

• filtrar registros de uma das relações;
• renomear ou reformular colunas para serem auditadas; e
• comparar apenas algumas colunas.

Como funciona a auditoria de linhas

Quando você executa o modelo de auditoria dbt, ele compara todas as colunas, linha por linha.

Para que o match da auditoria contabilize, cada linha da tabela legada deve corresponder exatamente a uma linha da tabela refatorada. É o que mostramos no exemplo a seguir com a Figura 2:

Veja que o modelo comparou linha por linha e, nesse caso, como todas as linhas em ambos os modelos são equivalentes, o resultado deve ser 100%.

A Figura 3 representa uma linha em que duas das três colunas são iguais e apenas a última coluna da linha 1 possui valores divergentes.

E apesar de a maior parte da linha 1 ser idêntica, essa linha não será contabilizada para o resultado final nesse caso.

Nesse exemplo, apenas as linhas 2 e 3 são válidas, resultando em uma correspondência de 66,6% no total de linhas analisadas.

Já falamos que, para que a correspondência seja válida, todos os valores das colunas de uma linha de um modelo devem ser iguais aos do outro modelo.

É por isso que, às vezes, precisamos excluir colunas da comparação, como colunas de data, que podem ter uma diferença de fuso horário do modelo original para o refatorado, por exemplo.

Passo a passo do código para a macro compare_queries

Passo 1

Crie um novo modelo .sql na pasta de sua escolha.

Passo 2

Copie e cole o seguinte exemplo no modelo criado:

# in dbt Develop #}

{% set old_fct_orders_query %}
  select
    id as order_id,
    amount,
    customer_id
  from old_etl_schema.fct_orders
{% endset %}
{

{% set new_fct_orders_query %}
  select
    order_id,
    amount,
    customer_id
  from {{ ref('fct_orders') }}
{% endset %}

{{ audit_helper.compare_queries(
    a_query=old_fct_orders_query,
    b_query=new_fct_orders_query,
    primary_key="order_id"
) }} 

Entenda a seguir os argumentos usados ​​na macro compare_queries:

• primary_key (opcional)

O modelo aceita uma chave primária como comparação, mas se os modelos comparados não tiverem uma chave, você pode criar uma ou simplesmente remover essa linha de código.

Como esse tipo de comparação avalia todas as colunas em uma linha, ele não vai mostrar diferença alguma se uma chave primária não for especificada.

• summarize (opcional)

Este argumento permite que você alterne entre uma exibição resumida ou detalhada dos dados comparados, e ainda aceita valores verdadeiros ou falsos (seu padrão é definido como verdadeiro).

Passo 3

Substitua as fontes do exemplo conforme a Figura 4:

Veja que a cláusula from aceita o caminho do modelo usando a macro ref e o caminho completo do data warehouse (o que vai ser útil quando você for refatorar um SQL Server Stored Procedure ou Alteryx Workflow que já estiver sendo materializado no data warehouse).

Passo 4

Substitua o nome das colunas conforme a Figura 5:

Substitua as colunas de exemplo pelas colunas de seus modelos, exatamente como estão escritas em cada modelo.

Você pode renomear as colunas para torná-las visualmente mais atraentes, mas, na prática, o modelo vai considerar apenas o número de colunas e a ordem em que elas aparecem.

Por exemplo, se uma coluna em um modelo for rotulada como id e a coluna correspondente em outro modelo for rotulada como order_id, não vai ser necessário renomeá-la. Mas verifique se elas estão na mesma ordem dentro da cláusula select.

Quando houver muitas colunas no conjunto de dados, comece com algumas (cinco colunas, por exemplo) e execute o modelo de audit.

E quando você obtiver uma boa correspondência, adicione mais colunas e execute novamente.

Você também pode comentar as colunas que não deseja comparar usando # antes delas.

Ao comparar várias colunas de uma só vez e obter um resultado ruim, pode ser difícil saber em qual coluna os valores das linhas diferem. Porém, lembre-se de que basta que os valores sejam diferentes em uma das colunas para invalidar a linha como um todo.

Por isso, recomendamos fortemente que você comece com algumas colunas e vá gradualmente adicionando, à medida que uma boa correspondência é obtida.

Quando uma nova coluna causar uma queda na correspondência, examine a coluna em ambos os modelos.

Passo 5

Execute o modelo de auditoria como você executaria qualquer outro modelo de dbt usando este comando:

dbt run -select <nome do modelo a ser auditado>

Passo 6

Confira o resultado do match entre os modelos. Para fazer isso, você pode copiar e colar o código abaixo na IDE do data warehouse ou usar softwares, como o DBeaver conectado ao data warehouse.

select * from <nome do modelo a ser auditado>

A saída será semelhante à mostrada na Figura 6:

Veja que a saída é apresentada em formato de tabela, com cada coluna explicada a seguir:

-IN_A: dados presentes no modelo A

-IN_B: dados presentes no modelo B

-COUNT: contagem do número de linhas

-PERCENT_OF_TOTAL: porcentagem do total para todas as linhas

Veja que, na primeira linha, vemos TRUE na coluna IN_A e TRUE na coluna IN_B, o que significa que, em ambos os modelos, existem 1.966.215 linhas com todas as colunas correspondentes aos valores, representando 97,65% do total.

E na segunda linha, vemos que 20.543 linhas do modelo A não estão presentes no modelo B.

Já a terceira linha afirma conclusivamente que 26.720 linhas do modelo B não estão presentes no modelo A.

Passo extra!

Para você saber a diferença entre os modelos (no exemplo acima, 2,35%), crie uma query que una as duas fontes. Você pode fazer isso no DBeaver ou no seu IDE de data warehouse usando a chave primária.

Coloque as mesmas colunas lado a lado, e use também a cláusula where para ajudar a descobrir onde uma coluna é diferente do modelo A quando comparado ao modelo B.

Essa inspeção pode ser uma primeira etapa para determinar a possível causa do erro.

Recomendamos analisar uma coluna por vez.

Como funciona a auditoria de colunas (compare_column_values)

Até aqui, falamos sobre a macro compare_queries para:

• auditar linhas;
• fornecer a quem é engenheira ou engenheiro de analytics uma ampla visão da taxa de compatibilidade geral;
• selecionar, com alguma flexibilidade, grupos de colunas específicos; e
• aplicar regras de negócios diretamente sobre a query de auditoria final.
  

Mas, apesar de ser uma ferramenta poderosa, ela não resolve todos os problemas de auditoria de dados entre modelos legados e refatorados.

Você até pode confiar nessa visão geral para validar o modelo refatorado final em relação ao seu equivalente legado, mas atenção, pois essa mesma visão pode ser menos útil no meio do processo de reconstrução de um fluxo de dados.

É que, nesse momento, você vai precisar rastrear quais são exatamente as colunas que estão causando problemas de incompatibilidade e o que há de errado com elas.

E uma maneira útil de verificar quais colunas específicas estão diminuindo a porcentagem de correspondência entre as tabelas é usar a macro compare_column_values, ​​que permite auditar os valores das colunas.

Essa macro requer que uma coluna de chave primária (primary key) seja definida para que possa ser usada como uma âncora para comparar as linhas entre a coluna refatorada do modelo de dbt e a coluna da tabela legada.

Veja como funciona essa macro compare_column_values ​​na Figura 7:

A saída da macro resume o status de compatibilidade da coluna, dividindo-a nestas diferentes categorias:

• perfect match (correspondência perfeita);
• both are null (ambos são nulos);
• values do not match (valores não coincidem);
• value is null in A only (valor é nulo apenas em A);
• value is null in B only (valor é nulo apenas em B);
• missing from A (ausente em A); e
• missing from B (ausente em B).  
  

Esse nível de detalhamento torna mais simples para quem é analytics engineer descobrir o que está causando problemas de incompatibilidade entre os modelos.

Ao refatorar um modelo, é comum que algumas chaves usadas para unir modelos sejam inconsistentes, trazendo como resultado valores nulos indesejados no modelo final, e isso faria com que a query de auditoria de linha falhasse, sem fornecer mais detalhes.

Com a macro compare_column_values, o output do relatório aborda especificamente esse problema, apontando para a pessoa analista de dados quais são as inconsistências que podem estar causando baixa compatibilidade.

Passo a passo do código para a macro compare_column_values

Passo 1

Crie um novo modelo .sql na pasta de sua escolha.

Passo 2

Copie e cole o seguinte exemplo no modelo criado:

{# in dbt Develop #}

{% set old_etl_relation_query %}
    select * from public.dim_product
    where is_latest
{% endset %}

{% set new_etl_relation_query %}
    select * from {{ ref('dim_product') }}
{% endset %}

{% set audit_query = audit_helper.compare_column_values(
    a_query=old_etl_relation_query,
    b_query=new_etl_relation_query,
    primary_key="product_id",
    column_to_compare="status"
) %}

{% set audit_results = run_query(audit_query) %}

{% if execute %}
{% do audit_results.print_table() %}
{% endif %}

Os argumentos utilizados por essa macro são praticamente os mesmos utilizados pela macro compare_queries, porém com a adição do argumento column_to_compare, que é responsável, como o nome sugere, por declarar qual coluna especificamente será testada.

Embora seja semelhante à macro compare_queries, a macro compare_columns_value dá grande importância ao argumento primary_key.

E essa chave primária vai ser fundamental para gerar corretamente as métricas atribuídas à coluna comparada, atuando como âncora para comparar cada linha gerada a partir do modelo refatorado com sua contraparte legada.

Além disso, você pode ver que há uma opção habilitada para que a tabela seja mostrada como padrão (print_table).

Então, o resultado da consulta é impresso no terminal quando a macro é executada.

E isso pode ser útil para você verificar rapidamente o status de compatibilidade de uma coluna sem sair do editor de código enquanto refatora as tabelas através do dbt.

Passo 3

Substitua os nomes das colunas e referências de origem do exemplo pelas respectivas informações dos modelos comparados.

Passo 4 (opcional)

Desabilite o comando print_table() para que o modelo seja materializado em seu data warehouse.

Para desabilitar a impressão da tabela no terminal e habilitar a materialização do modelo em seu data warehouse de destino, substitua toda essa seção de código, que declara o modelo SQL como variável e torna-o imprimível (padrão de execução da macro).

-- Replace the commented code below:
-- {% set audit_results = run_query(audit_query) %}

-- {% if execute %}
-- {% do audit_results.print_table() %}
-- {% endif %}

-- With the following piece of code:
{{ run_query(audit_query) }}

Passo 5

Para obter os resultados, você pode simplesmente executar o modelo como faria com um modelo regular de dbt usando o seguinte comando:

dbt run --select < nome do seu modelo de auditoria >

Mas, diferentemente da macro compare_queries, se você manteve habilitada a função de impressão da tabela, precisa esperar que uma tabela seja impressa na linha de comando ao rodar o modelo, caso contrário ela será materializada no data warehouse, como mostra a Figura 8:

A macro compare_column_values ​​separa os resultados da auditoria de coluna em sete rótulos diferentes:

1. perfect match (combinação perfeita): contagem de linhas (e porcentagem relativa) em que os valores das colunas, comparados entre ambas as tabelas, são iguais e não nulos;
   
2. both are null (ambos são nulos): contagem de linhas (e porcentagem relativa) em que os valores das colunas, comparados entre ambas as tabelas, são nulos;
   
3. missing from A (ausente em A): contagem de linhas (e porcentagem relativa) com valores de coluna que existem na tabela B, mas não na tabela A;
   
4. missing from B (ausente em B): contagem de linhas (e porcentagem relativa) com valores de coluna que existem na tabela A, mas não na tabela B;
   
5. value is null in A only (valor é nulo apenas em A): contagem de linhas (e porcentagem relativa) com valores de coluna que não são nulos na tabela B, mas são nulos na tabela A;
   
6. value is null in B only (valor é nulo apenas em B): contagem de linhas (e porcentagem relativa) com valores de coluna que não são nulos na tabela A, mas são nulos na tabela B;
   
7. values do not match (valores não coincidem): contagem de linhas (e porcentagem relativa) em que os valores de coluna, comparados entre ambas as tabelas, são diferentes e não nulos.
   
   

Com esse relatório detalhado, é muito mais fácil para quem é profissional de engenharia de analytics descobrir o que pode estar errado com o fluxo de trabalho de refatoração de dados, para que o problema seja investigado e resolvido diretamente.

Além disso, com algum código e orquestração extras, os relatórios de coluna gerados podem ser agregados e colocados em um ambiente de produção, levando a observabilidade da validação de dados a um nível ainda mais alto.

audit_helper e dbt: elevando o nível da auditoria de dados - Referências

Estudar é preciso sempre! Então, aproveite e consulte também as principais referências que usamos para escrever este artigo sobre o audit_helper:

• Repositório GitHub do pacote audit_helper
• Post do dbt Labs sobre auditoria de dados: How to not lose your mind when auditing data - Parte 1
• Post do dbt Labs sobre auditoria de dados: How to not lose your mind when auditing data - Parte  2
• Post do dbt Labs: Como migrar de SQL Server Stored Procedures para modelos dbt
  

Assine aqui nossa news e siga a Indicium para ficar por dentro das novidades do mundo de data science e analytics.

E leia nosso blog. Temos muito material rico e gratuito com informações sempre acessíveis que certamente serão úteis para você.

Sucesso!