Modelagem dimensional e formatos

Fatos, dimensões, grão e histórico, e por que Parquet é tão mais rápido que CSV para análise.

8 min de leitura · Intermediário · Revisado em

Dois bancos, dois objetivos

O banco da aplicação e o banco de análise
Transacional (OLTP)Analítico (OLAP)
Pergunta típicaQual é o pedido 42?Quanto vendemos por região em cada mês?
AcessoPoucas linhas, muitas vezes por segundoMilhões de linhas, poucas colunas
ModeloNormalizado, sem repetiçãoDimensional, com alguma repetição
ExemplosPostgreSQL, MySQLBigQuery, Snowflake, DuckDB, Redshift

Rodar relatórios pesados no banco da aplicação deixa o sistema lento para todo mundo. Por isso os dados são copiados para um armazém (data warehouse) e remodelados para leitura.

O modelo estrela

Fato
Um evento que aconteceu e pode ser medido: uma venda, um clique, um pagamento. Tem números (valor, quantidade) e chaves para as dimensões.
Dimensão
O contexto do fato: quem, o quê, onde, quando. Cliente, produto, loja, data. Tem textos usados para filtrar e agrupar.
Grão
O que uma linha da tabela fato representa. Ex.: "um item de um pedido". Defina antes de qualquer coluna.

Fato no centro, dimensões em volta

-- grão: um item vendido em um pedido
CREATE TABLE fato_vendas (
  data_id      INT REFERENCES dim_data,
  produto_id   INT REFERENCES dim_produto,
  cliente_id   INT REFERENCES dim_cliente,
  pedido_id    TEXT,
  quantidade   INT,
  valor_total  NUMERIC(12, 2)
);

CREATE TABLE dim_produto (
  produto_id INT PRIMARY KEY,
  nome       TEXT,
  categoria  TEXT,
  marca      TEXT
);

A pergunta de negócio vira uma consulta simples

SELECT d.ano_mes, p.categoria, SUM(f.valor_total) AS receita
FROM fato_vendas f
JOIN dim_data d    ON d.data_id = f.data_id
JOIN dim_produto p ON p.produto_id = f.produto_id
GROUP BY d.ano_mes, p.categoria
ORDER BY d.ano_mes;

Quando a dimensão muda

Uma cliente se muda de São Paulo para Recife. As vendas antigas devem contar para qual cidade? A resposta define o tipo de dimensão de mudança lenta (SCD).

As duas estratégias mais usadas
TipoComo funcionaResultado
SCD 1Sobrescreve o valorTodas as vendas passam a contar para Recife
SCD 2Fecha a linha antiga e cria uma nova, com valido_de e valido_ateVendas antigas ficam em São Paulo, novas em Recife

Formatos de arquivo

Onde cada formato brilha
FormatoOrganizaçãoBom para
CSVLinhas de textoTrocar dados com pessoas e planilhas. Sem tipos.
JSONDocumentos de textoAPIs e dados aninhados. Grande e lento para analisar.
ParquetColunas, comprimido, com tiposAnálise: lê só as colunas da consulta.
AvroLinhas binárias, com esquemaEventos e streaming, com evolução de esquema.
  • O grão de cada tabela fato está escrito em uma frase.
  • Fatos guardam números e chaves; textos ficam nas dimensões.
  • Existe uma dimensão de data com ano, mês, dia da semana e feriados.
  • Decidi, para cada dimensão, se o histórico importa (SCD 1 ou 2).
  • Dados de análise estão em formato colunar, como Parquet.

Referências

Trilhas relacionadas