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
quantidadeevalor. Tem as chavesproduto_id,loja_idedata_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.
| cliente_sk | cliente_id | nome | cidade | valido_de | valido_ate | atual |
|---|---|---|---|---|---|---|
| 1 | 10 | Joana | Recife | 2026-01-01 | 2026-08-31 | 0 |
| 2 | 10 | Joana | São Paulo | 2026-09-01 | 9999-12-31 | 1 |
| 3 | 11 | Marcos | Belém | 2026-01-01 | 9999-12-31 | 1 |
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
| Formato | Organização | Tem tipos e esquema? | Pontos fortes |
|---|---|---|---|
| CSV | Por linha, texto | Não: tudo é texto | Simples, abre em qualquer lugar, inclusive no Excel |
| JSON | Por registro, texto | Parcial | Dados aninhados, APIs, legível por humanos |
| Avro | Por linha, binário | Sim, com esquema junto | Escrever registro a registro, streaming, esquema que evolui |
| Parquet | Por coluna, binário | Sim | Aná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')
colunastem exatamente as chavesdata,cidadeevalor- 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.
Liste o conteúdo da pasta atual
lsanalitico bruto tratadoVeja as partições de 2026 da tabela de vendas tratada, em
tratado/vendas/ano=2026ls tratado/vendas/ano=2026mes=08 mes=09 mes=10Liste os arquivos da partição de outubro
ls tratado/vendas/ano=2026/mes=10parte-0000.parquet parte-0001.parquetCompare o tamanho do CSV bruto de outubro (
bruto/vendas_2026_10.csv) com a partição em Parquet, usandodu -shdu -sh bruto/vendas_2026_10.csv tratado/vendas/ano=2026/mes=10412M bruto/vendas_2026_10.csv 61M tratado/vendas/ano=2026/mes=10Some 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
| Data lake | Data warehouse | Lakehouse | |
|---|---|---|---|
| O que é | Arquivos em armazenamento barato (pastas, S3) | Banco analítico com tabelas e SQL | Arquivos do lake com recursos de warehouse |
| Formato | Qualquer um: CSV, JSON, Parquet, imagens | Interno, gerenciado pelo banco | Parquet com uma camada de tabela (Delta Lake, Iceberg) |
| Pontos fortes | Guarda tudo, inclusive o bruto, a baixo custo | Consultas rápidas, controle de acesso, transações | Um só lugar para bruto e analítico, com SQL e histórico |
| Risco | Virar um “pântano” sem organização | Custo maior e menos flexível com dados não tabulares | Mais 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.
- Escreva numa frase a granularidade da sua tabela fato de vendas
- Crie
fato_vendase pelo menos duas dimensões (produto e data, por exemplo) com dados sintéticos ou públicos - Responda duas perguntas de negócio com o molde fato mais JOINs mais GROUP BY
- Exporte o fato para CSV e para Parquet com
COPY fato_vendas TO 'vendas.parquet' (FORMAT parquet)e compare os tamanhos - Meça o tempo de uma soma lendo o CSV e o Parquet e anote o resultado no README