Etl bi

Escopo: transversal. Aplica-se a qualquer linguagem ou stack do projeto.

Rodar um relatório pesado no banco que atende os usuários deixa o sistema lento para todo mundo. A camada analítica existe para evitar isso: ela copia os dados operacionais para um ambiente montado para leitura, e a análise passa a rodar longe do caminho crítico.

ETL (Extract, Transform, Load · Extração, Transformação e Carga) é o processo que move esses dados. BI (Business Intelligence · Inteligência de Negócios) é o que o time de negócio faz com eles depois: dashboards, relatórios e perguntas soltas. O banco transacional OLTP (Online Transaction Processing · Processamento de Transações Online) é montado para escrever rápido e manter a consistência. O banco analítico OLAP (Online Analytical Processing · Processamento Analítico Online) é montado para varrer milhões de linhas e devolver um total.

Os dois bancos existem separados porque as otimizações se anulam. O schema normalizado que evita redundância na escrita obriga a query analítica a montar dezenas de JOINs. A redundância que deixa a leitura rápida encarece cada escrita. Cada lado escolhe um extremo.

O pipeline leva o dado da fonte até o dashboard passando por camadas, e cada uma tem uma responsabilidade só.

Fonte → Staging → Transform → Data Warehouse → Data Marts → BI

A camada de Staging é o ponto de recuperação do pipeline. Quando a transformação quebra, os dados brutos continuam lá, e a reexecução parte deles sem precisar bater na fonte de novo. Isso importa quando a fonte é uma API com teto de chamadas, ou um banco de produção que já está no limite.

Três estratégias, com custos diferentes de volume, atraso e complexidade.

O watermark é o carimbo do ponto até onde a última execução chegou. A próxima execução pede só o que mudou depois dele, e o volume extraído deixa de crescer junto com a tabela.

-- extrair registros modificados após o último watermark
SELECT
  Players.Id,
  Players.Name,
  Players.Position,
  Players.TeamId,
  Players.UpdatedAt
FROM
  Players
WHERE
  Players.UpdatedAt > @LastWatermark
ORDER BY
  Players.UpdatedAt;

-- ao final do job: persistir o maior UpdatedAt processado como novo watermark

O incremental tem um ponto cego: a linha apagada no OLTP some da tabela e nunca aparece na extração, então ela continua viva no warehouse para sempre. Duas saídas resolvem. O CDC lê o log de transações e enxerga o DELETE. O soft delete marca a linha com DeletedAt em vez de removê-la, e aí o UpdatedAt muda e a extração incremental a captura.

A diferença entre ETL e ELT está no momento em que o dado é transformado. No ETL, uma ferramenta externa limpa e calcula antes de gravar no warehouse. No ELT, o dado bruto entra primeiro, e a transformação roda dentro do próprio warehouse com SQL.

O ELT virou o padrão nos warehouses modernos como BigQuery, Snowflake e Redshift, por três motivos: o armazenamento ficou barato, o SQL (Structured Query Language · Linguagem de Consulta Estruturada) desses bancos dá conta da transformação, e a lógica escrita em SQL entra no controle de versão com dbt. O ETL segue sendo a escolha certa em dois casos: quando o dado bruto não pode sair da rede interna, e quando a transformação exige uma lógica que SQL expressa mal.

O modelo dimensional organiza as tabelas para que uma pergunta de negócio vire uma query curta. A Fact table guarda o que aconteceu e quanto (o gol, o valor da venda). As Dimension tables guardam o contexto ao redor: quem, onde e quando.

Declare o grain antes de criar a tabela, em uma frase:

Misturar dois grains na mesma fact table corrompe as agregações. Se metade das linhas é um gol e a outra metade é um resumo diário, o SUM conta os mesmos gols duas vezes, e ninguém percebe olhando o número.

As dimensões ficam ao lado da fact table, cada uma a um JOIN de distância. A query analítica toca poucas tabelas, o que a deixa rápida. É o formato preferido.

DimDate + DimPlayer + DimTeam + DimStadium → FactGoal

-- fact table: uma linha por gol marcado
CREATE TABLE FactGoal
(
  GoalKey    INT      NOT NULL IDENTITY(1, 1),
  DateKey    INT      NOT NULL,
  PlayerKey  INT      NOT NULL,
  TeamKey    INT      NOT NULL,
  StadiumKey INT      NOT NULL,
  Minute     SMALLINT NOT NULL,
  IsOwnGoal  BIT      NOT NULL DEFAULT 0,
  IsPenalty  BIT      NOT NULL DEFAULT 0,

  CONSTRAINT PK_FactGoal        PRIMARY KEY (GoalKey),
  CONSTRAINT FK_FactGoal_Date   FOREIGN KEY (DateKey)    REFERENCES DimDate (DateKey),
  CONSTRAINT FK_FactGoal_Player FOREIGN KEY (PlayerKey)  REFERENCES DimPlayer (PlayerKey),
  CONSTRAINT FK_FactGoal_Team   FOREIGN KEY (TeamKey)    REFERENCES DimTeam (TeamKey)
);

-- dimension table: uma linha por jogador (ou por versão com SCD Tipo 2)
CREATE TABLE DimPlayer
(
  PlayerKey   INT              NOT NULL IDENTITY(1, 1), -- surrogate key
  PlayerId    UNIQUEIDENTIFIER NOT NULL,               -- natural key do OLTP
  Name        NVARCHAR(200)    NOT NULL,
  Position    NVARCHAR(50)     NOT NULL,
  Nationality NVARCHAR(100)    NOT NULL,

  CONSTRAINT PK_DimPlayer PRIMARY KEY (PlayerKey)
);

A surrogate key (PlayerKey) é um número sequencial que só existe no warehouse. A natural key (PlayerId) é o identificador que veio do OLTP. As duas convivem porque o mesmo jogador pode ter várias linhas no warehouse, uma por versão histórica, e cada versão precisa de uma chave própria.

As dimensões se dividem em outras dimensões: DimTeam aponta para DimLeague, que aponta para DimCountry. A redundância cai, e cada query passa a precisar de mais JOINs. Vale a pena quando o armazenamento pesa no custo, ou quando as dimensões secundárias são consultadas por conta própria.

O jogador troca de time, o produto muda de preço, o cliente muda de cidade. O tipo de SCD define o que acontece com o histórico nesse momento.

A escolha errada apaga informação que ninguém consegue recuperar depois. Se o time do jogador é Tipo 1, a transferência sobrescreve o passado, e os gols que ele fez pelo clube anterior passam a aparecer no clube novo.

CREATE TABLE DimPlayer
(
  PlayerKey  INT              NOT NULL IDENTITY(1, 1), -- surrogate key
  PlayerId   UNIQUEIDENTIFIER NOT NULL,               -- natural key do OLTP
  Name       NVARCHAR(200)    NOT NULL,
  Position   NVARCHAR(50)     NOT NULL,
  TeamName   NVARCHAR(200)    NOT NULL,               -- denormalizado: evita JOIN em queries históricas
  ValidFrom  DATE             NOT NULL,
  ValidUntil DATE             NULL,                   -- NULL = registro atual
  IsCurrent  BIT              NOT NULL DEFAULT 1,

  CONSTRAINT PK_DimPlayer PRIMARY KEY (PlayerKey)
);

Na transferência, a linha vigente recebe ValidUntil = hoje e IsCurrent = 0, e uma linha nova entra com o time novo e IsCurrent = 1. A query histórica escolhe a versão certa filtrando por ValidFrom <= @Date AND (ValidUntil > @Date OR ValidUntil IS NULL).

As ferramentas de BI (Power BI, Tableau, Metabase, Looker) se conectam ao Data Warehouse ou aos Data Marts. Elas geram SQL na hora em que o usuário clica, ou leem de um cache que já foi preenchido antes.

Quando cada dashboard calcula o mesmo SUM com um filtro ligeiramente diferente, os relatórios chegam a números diferentes, e a reunião vira uma discussão sobre qual planilha está certa. Defina a métrica em um lugar só e faça todos os dashboards lerem dali.

As ferramentas com camada semântica já oferecem esse lugar: measures em DAX no Power BI, LookML no Looker, metrics no dbt. Sem nenhuma delas, uma view ou uma tabela agregada no warehouse cumpre o papel de fonte única.

Uma query recorrente sobre volume grande não precisa varrer tudo de novo a cada abertura do dashboard. Um job noturno calcula o total do dia e grava numa tabela pequena, e o dashboard passa a ler dessa tabela.

-- tabela de agregado diário: populada uma vez por dia via job agendado
CREATE TABLE AggGoalsByPlayerByDay
(
  DateKey        INT NOT NULL,
  PlayerKey      INT NOT NULL,
  TotalGoals     INT NOT NULL DEFAULT 0,
  TotalPenalties INT NOT NULL DEFAULT 0,

  CONSTRAINT PK_AggGoals PRIMARY KEY (DateKey, PlayerKey)
);

O custo é a atualidade: o número reflete o último processamento, e não o instante da consulta. Combine com a frequência que o negócio aceita.

DoDocs v3.7.0 · Desenvolvido por @thiagocajadev · Baseado no trabalho de pmndrs/docs · Poimandres.