Etl bi

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

ETL (Extract, Transform, Load, Extração, Transformação e Carga) e BI (Business Intelligence, Inteligência de Negócios) formam a camada analítica de um sistema: movem dados operacionais para um ambiente otimizado para leitura e análise. O banco transacional OLTP (Online Transaction Processing, Processamento de Transações Online) é projetado para escrita rápida e consistência. O banco analítico OLAP (Online Analytical Processing, Processamento Analítico Online) é projetado para queries agregadas em grandes volumes históricos. Rodar análises pesadas diretamente no OLTP compromete a latência do sistema operacional.

A separação existe porque os dois têm requisitos opostos: otimizar para escrita prejudica leitura analítica, e vice-versa.

O pipeline move dados da fonte ao consumidor em camadas com responsabilidade clara.

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

A Staging layer é o ponto de recuperação do pipeline: se a transformação falhar, os dados brutos estão intactos e o pipeline reexecuta sem re-extrair da fonte.

Três estratégias com trade-offs de volume, latência e complexidade.

-- 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

Limitação do incremental: registros deletados no OLTP não aparecem na extração. CDC ou soft delete com DeletedAt resolvem esse ponto cego.

A diferença está em onde a transformação acontece.

ELT é o padrão moderno para warehouses como BigQuery, Snowflake e Redshift: armazenamento barato, SQL (Structured Query Language, Linguagem de Consulta Estruturada) nativo poderoso e transformações versionáveis com dbt. ETL continua a escolha certa quando os dados brutos não podem sair da rede interna ou quando a transformação exige lógica que SQL não expressa.

O modelo dimensional organiza dados para responder perguntas de negócio com queries diretas. A Fact table armazena métricas (o que aconteceu e quanto); as Dimension tables armazenam contexto (quem, onde, quando).

O grain define o que cada linha da fact table representa. Declarar antes de modelar:

Misturar grains na mesma fact table gera resultados incorretos em agregações.

Dimensões planas ao redor da fact table. Queries analíticas fazem poucos JOINs. Preferido para performance.

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)
);

Dimensões normalizadas: DimTeam referencia DimLeague, que referencia DimCountry. Reduz redundância, mas cada query adicional requer mais JOINs. Usar quando o custo de armazenamento for relevante ou quando as dimensões secundárias precisarem ser consultadas de forma independente.

Atributos de dimensão mudam ao longo do tempo. O tipo de SCD define como registrar esse histórico.

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)
);

Quando um jogador muda de time, o registro atual recebe ValidUntil = hoje e IsCurrent = 0. Uma nova linha é inserida com o novo time e IsCurrent = 1. Queries históricas filtram com ValidFrom <= @Date AND (ValidUntil > @Date OR ValidUntil IS NULL).

Ferramentas de BI (Power BI, Tableau, Metabase, Looker) conectam ao Data Warehouse ou aos Data Marts e geram SQL em tempo real ou sobre dados em cache.

Cada dashboard calcular o mesmo SUM de forma ligeiramente diferente gera divergência entre relatórios. A solução é definir a métrica uma vez e reusar em todos os contextos.

Ferramentas com camada semântica resolvem isso diretamente: Power BI com measures DAX, Looker com LookML, dbt com metrics. Sem uma dessas ferramentas, a alternativa é uma view ou tabela agregada no DW que serve de fonte única.

Queries recorrentes sobre volumes grandes se beneficiam de pré-agregação: calcular e persistir o resultado periodicamente.

-- 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)
);

Desenvolvido por @thiagocajadev · Fork baseado no repositório pmndrs/docs · Poimandres.