Modelagem analítica e formatos

Tabelas fato e dimensão, esquema estrela, granularidade, histórico com SCD tipo 2, formatos por linha e por coluna, particionamento e onde guardar os dados.

Cerca de 16 min · Revisado em

Ao final, você vai conseguir

  • Separar medidas e contexto em tabelas fato e dimensão de um esquema estrela
  • Consultar um esquema estrela e definir a granularidade de uma tabela fato
  • Explicar quando usar SCD tipo 1 ou tipo 2 e consultar uma dimensão com histórico
  • Comparar CSV, JSON, Avro e Parquet e escolher uma coluna de particionamento
  • Diferenciar data lake, data warehouse e lakehouse

Imagine uma rede de supermercados com milhões de vendas por dia. A diretoria pergunta quanto a categoria Padaria vendeu no Nordeste nos feriados, e quer a resposta em segundos, não em horas. Isso depende de duas decisões que a engenharia de dados toma bem antes da pergunta chegar: como organizar as tabelas e em que formato guardar os arquivos. Nesta aula você aprende as duas.

Fatos e dimensões: o que aconteceu e o contexto

Tabela fato
Guarda os eventos medidos: vendas, entregas, consultas. Tem muitas linhas, colunas numéricas que se somam (as medidas) e chaves que apontam para as dimensões.
Tabela dimensão
Guarda o contexto descritivo: produto, loja, cliente, data. Tem poucas linhas e muitas colunas de texto usadas para filtrar e agrupar.
Medida
Um número do evento que faz sentido somar ou tirar média: quantidade, valor, desconto.

Na aula de tabelas e relações, você viu como separar dados em várias tabelas para evitar repetição: é assim que um banco OLTP, o do aplicativo, é organizado. Para análise, a prioridade muda. Você aceita repetir um pouco de texto nas dimensões em troca de consultas simples, com poucos JOINs.

Você está modelando as vendas de uma padaria. Cada informação vai para o fato ou para uma dimensão?

Medida no fato · Atributo de uma dimensão

  • Quantidade vendida
  • Valor pago
  • Desconto aplicado
  • Nome do produto
  • Categoria do produto
  • Cidade da loja
  • Dia da semana
  • Forma de pagamento

O esquema estrela na prática

Quando você desenha a tabela fato no centro e as dimensões em volta, cada uma ligada ao fato por uma chave, o desenho parece uma estrela. Daí o nome esquema estrela (star schema), o modelo mais usado em data warehouses.

O esquema estrela das vendas usado nos exercícios. Explore cada tabela.

              dim_produto
                   |
dim_data ---- fato_vendas ---- dim_loja
  • dim_produto: Uma linha por produto: nome e categoria. Para saber vendas por categoria, você agrupa por uma coluna daqui.
  • dim_data: Parece estranho ter uma tabela só de datas, mas ela guarda o que o calendário não diz sozinho: mês, dia da semana, se era feriado ou Black Friday.
  • fato_vendas: O centro. Cada linha é um produto vendido em uma loja em um dia, com quantidade e valor. Tem as chaves produto_id, loja_id e data_id.
  • dim_loja: Uma linha por loja, com cidade e região. Se uma loja nova abre, entra uma linha aqui e nada muda no fato.

Faturamento por categoria e mês

Junte o fato com dim_produto e dim_data e devolva categoria, mes e faturamento (soma de valor), uma linha por categoria e mês.

SELECT produto_id, SUM(valor) AS faturamento
FROM fato_vendas
GROUP BY produto_id;
  • Uma linha por categoria e mês (6 linhas)
  • Padaria faturou 58,50 em outubro
  • Laticínios faturou 72 em outubro

Feriados por região

Compare o faturamento em feriados e dias normais por região: devolva regiao, feriado (o 0 ou 1 de dim_data) e faturamento.

SELECT l.regiao, SUM(f.valor) AS faturamento
FROM fato_vendas f
JOIN dim_loja l ON l.loja_id = f.loja_id
GROUP BY l.regiao;
  • Há uma linha por região e tipo de dia (3 combinações com vendas)
  • O Nordeste faturou 123,50 em feriados

Granularidade: decida o que é uma linha

Na aula de SQL analítico você viu que somar coisas de granularidades diferentes gera dupla contagem. Na modelagem, a granularidade é a primeira decisão: antes de escolher colunas, você escreve numa frase o que uma linha do fato representa. No exercício anterior: “uma linha por produto vendido, por loja, por dia”.

A rede quer descobrir quais produtos são comprados juntos, como café e pão. Qual granularidade da tabela fato permite essa análise?

  • Uma linha por item de cada cupom, com o número do cupom
  • Uma linha por cupom, com o valor total da compra
  • Uma linha por produto, por loja, por dia
  • Uma linha por loja, por mês
Se a granularidade mais fina ocupa mais espaço, por que ela costuma ser a escolha recomendada?

Porque dá para somar do fino para o grosso a qualquer momento, mas nunca o contrário. De itens de cupom você chega a vendas por mês; de vendas por mês, nunca volta aos itens. Guardar armazenamento é barato; perder detalhe é irreversível.

Quando a dimensão muda: histórico com SCD tipo 2

Dimensões que mudam devagar ganharam um nome: SCD (slowly changing dimension, dimensão que muda lentamente). No tipo 1, você sobrescreve o valor antigo e perde o histórico. No tipo 2, você guarda uma nova linha para cada versão, com o período em que ela valeu.

A dimensão de clientes em SCD tipo 2
cliente_skcliente_idnomecidadevalido_devalido_ateatual
110JoanaRecife2026-01-012026-08-310
210JoanaSão Paulo2026-09-019999-12-311
311MarcosBelém2026-01-019999-12-311

O cliente_id é a chave do sistema de origem e se repete entre versões. O cliente_sk é uma chave substituta (surrogate key): um número criado no warehouse para identificar cada versão. A data 9999-12-31 é uma convenção para “ainda vale”.

Vendas pela cidade da época

A consulta usa a cidade atual de cada cliente e joga todas as compras da Joana em São Paulo. Corrija o JOIN para usar a versão válida na data de cada pedido (f.data BETWEEN d.valido_de AND d.valido_ate), devolvendo cidade e faturamento.

SELECT d.cidade, SUM(f.valor) AS faturamento
FROM fato_pedidos f
JOIN dim_cliente d ON d.cliente_id = f.cliente_id AND d.atual = 1
GROUP BY d.cidade;
  • Recife recebe as compras de julho e agosto (150)
  • São Paulo fica só com a compra de setembro (80)
  • O total continua 270 (nenhum pedido duplicou nem sumiu)

Uma cliente percebeu que o nome dela estava cadastrado como “Mraia” e pediu a correção para “Maria”. Como tratar na dimensão?

  • Sobrescrever o nome (tipo 1), porque é a correção de um erro, não uma mudança real
  • Criar uma nova versão (tipo 2), para nunca perder nada
  • Apagar a cliente e criar outra com o nome certo

Formatos de arquivo: por linha ou por coluna

Os formatos que você vai encontrar
FormatoOrganizaçãoTem tipos e esquema?Pontos fortes
CSVPor linha, textoNão: tudo é textoSimples, abre em qualquer lugar, inclusive no Excel
JSONPor registro, textoParcialDados aninhados, APIs, legível por humanos
AvroPor linha, binárioSim, com esquema juntoEscrever registro a registro, streaming, esquema que evolui
ParquetPor coluna, binárioSimAnálise: lê só as colunas pedidas e comprime muito bem

O Parquet ganha em análise por dois motivos. Primeiro, uma consulta que usa 3 de 50 colunas lê só essas 3. Segundo, valores parecidos ficam juntos (uma coluna inteira de cidades, por exemplo), e dados repetitivos comprimem muito melhor. É comum um CSV de 400 MB virar um Parquet de 60 MB.

De linhas para colunas

Transforme a lista vendas (uma linha por venda) no dicionário colunas, com uma lista por coluna (data, cidade, valor), na mesma ordem. Depois calcule total somando só a lista de valores. Veja no fim a diferença de tamanho depois de comprimir.

import json
import zlib

cidades = ['Recife', 'Salvador', 'Belém']
vendas = [
    {'data': '2026-10-%02d' % (i % 28 + 1), 'cidade': cidades[i % 3], 'valor': float(i % 50)}
    for i in range(1000)
]
print('Primeira linha:', vendas[0])

colunas = {}  # ex.: {'data': [...], 'cidade': [...], 'valor': [...]}
total = 0     # some apenas colunas['valor']

print('Total:', total)
por_linha = len(zlib.compress(json.dumps(vendas).encode()))
por_coluna = len(zlib.compress(json.dumps(colunas).encode()))
print('Por linha, comprimido:', por_linha, 'bytes')
print('Por coluna, comprimido:', por_coluna, 'bytes')
  • colunas tem exatamente as chaves data, cidade e valor
  • As cidades estão na mesma ordem das vendas
  • total é a soma da coluna de valores
  • O formato por coluna ficou menor

Qual formato combina melhor com cada situação?

CSV · JSON · Parquet · Avro

  • Mandar uma planilha para o financeiro abrir no Excel
  • Exportação simples de um sistema antigo, sem tipos
  • Guardar a resposta de uma API com endereço dentro do cliente
  • Um arquivo de configuração com listas, lido por pessoas
  • Tabela de 2 bilhões de vendas consultada por poucas colunas
  • Somar o valor das vendas do ano lendo só a coluna de valor
  • Eventos gravados um a um num fluxo contínuo
  • Mensagens cujo esquema ganha campos novos com o tempo

Particionar e escolher onde guardar

Particionar é dividir uma tabela grande em pastas por uma coluna, quase sempre a data: ano=2026/mes=10/. Quando a consulta pede só outubro, o motor de consulta nem abre as outras pastas. É como o arquivo morto de um escritório, com uma caixa por mês.

Passeio por um data lake local

Explore as camadas do lake, encontre a partição de outubro e compare o tamanho do CSV bruto com o Parquet tratado.

  1. Liste o conteúdo da pasta atual

    ls
    analitico  bruto  tratado
  2. Veja as partições de 2026 da tabela de vendas tratada, em tratado/vendas/ano=2026

    ls tratado/vendas/ano=2026
    mes=08  mes=09  mes=10
  3. Liste os arquivos da partição de outubro

    ls tratado/vendas/ano=2026/mes=10
    parte-0000.parquet  parte-0001.parquet
  4. Compare o tamanho do CSV bruto de outubro (bruto/vendas_2026_10.csv) com a partição em Parquet, usando du -sh

    du -sh bruto/vendas_2026_10.csv tratado/vendas/ano=2026/mes=10
    412M	bruto/vendas_2026_10.csv
     61M	tratado/vendas/ano=2026/mes=10
  5. Some o valor das vendas de outubro com o DuckDB lendo só essa partição

    duckdb -c "SELECT SUM(valor) FROM 'tratado/vendas/ano=2026/mes=10/*.parquet'"
    ┌──────────────┐
    │ sum(valor)   │
    │    double    │
    ├──────────────┤
    │ 18734512.75  │
    └──────────────┘

Quase todas as consultas na tabela de vendas filtram por período. Qual coluna faz mais sentido para particionar?

  • A data da venda, por ano e mês
  • O id do cliente
  • O valor da venda
Onde os dados analíticos moram
Data lakeData warehouseLakehouse
O que éArquivos em armazenamento barato (pastas, S3)Banco analítico com tabelas e SQLArquivos do lake com recursos de warehouse
FormatoQualquer um: CSV, JSON, Parquet, imagensInterno, gerenciado pelo bancoParquet com uma camada de tabela (Delta Lake, Iceberg)
Pontos fortesGuarda tudo, inclusive o bruto, a baixo custoConsultas rápidas, controle de acesso, transaçõesUm só lugar para bruto e analítico, com SQL e histórico
RiscoVirar um “pântano” sem organizaçãoCusto maior e menos flexível com dados não tabularesMais peças para configurar e entender

Fechamento

  • A tabela fato guarda eventos com medidas que se somam; as dimensões guardam o contexto usado para filtrar e agrupar.
  • No esquema estrela, o fato fica no centro e cada dimensão se liga a ele por uma chave, deixando as consultas simples e previsíveis.
  • A granularidade do fato é a primeira decisão de modelagem, e a mais fina permite sempre resumir depois.
  • SCD tipo 2 guarda uma linha por versão de um atributo que muda, com período de validade, para que relatórios antigos não mudem sozinhos.
  • Parquet guarda por coluna e comprime bem, ideal para análise; particionar por data faz a consulta ler só as pastas necessárias.

Modele vendas e compare CSV com Parquet

Use o DuckDB no seu computador (ele roda sem servidor e lê CSV e Parquet direto) para montar um pequeno esquema estrela e medir a diferença entre os formatos.

  1. Escreva numa frase a granularidade da sua tabela fato de vendas
  2. Crie fato_vendas e pelo menos duas dimensões (produto e data, por exemplo) com dados sintéticos ou públicos
  3. Responda duas perguntas de negócio com o molde fato mais JOINs mais GROUP BY
  4. Exporte o fato para CSV e para Parquet com COPY fato_vendas TO 'vendas.parquet' (FORMAT parquet) e compare os tamanhos
  5. Meça o tempo de uma soma lendo o CSV e o Parquet e anote o resultado no README

Referências

Cursos relacionados