ZrobDev/modelagem-de-dados

Modelagem de dados

Alvos: PostgreSQL, BigQuery, Snowflake. Transformação: dbt. Prototipagem local: DuckDB 2.0. Para mecânicas do dbt (configuração de modelo, YAML de testes, snapshots, camada semântica, migração v2) use os skills oficiais do dbt Labs puxados pelo skill de-vendors deste repositório. Este skill decide o que modelar, não como o dbt escreve.

Procedimento

1. Declare a pergunta de negócio e o consumidor (dashboard, recurso de ML, fechamento financeiro).
2. Escreva o grão como uma única frase antes de qualquer coluna: "Uma linha por item de pedido por dia." Se a frase precisar de "ou", são duas tabelas.
3. Escolha o tipo de fato (abaixo), então liste as dimensões que são verdadeiras naquele grão.
4. Nomeie a chave do grão (colunas naturais ou um hash delas). Ela recebe unique + not_null.
5. Prototipe no DuckDB com uma amostra, execute as verificações de fan-out, então porte.
6. Adicione os testes listados em "O que testar" antes de declarar como concluído.

Grão

• Grão primeiro, colunas depois. Toda medida e toda chave estrangeira devem ser de valor único no grão; uma coluna que não é pertence a outra tabela, porque ela multiplica linhas silenciosamente.
• Mantenha o menor grão que a fonte suporta. Agregados são deriváveis do detalhe; detalhe não é derivável de agregados. Construa rollups como marts separados.
• Nunca misture grãos em uma única tabela (totais do cabeçalho do pedido repetidos em linhas de item do pedido). Valores do cabeçalho somados sobre as linhas de item duplicam a contagem; modele fatos de cabeçalho e de item separadamente.

Fatos vs dimensões

• Fato: um evento ou medição; numérico, aditivo quando possível, chaveado por chaves de dimensão mais dimensões degeneradas (número do pedido).
• Dimensão: contexto descritivo usado para filtrar e agrupar. Larga e desnormalizada é aceitável.
• Classifique cada medida: aditiva (soma em todas as dims), semiaditiva (saldos: não através do tempo), não aditiva (razões: armazene numerador e denominador, divida após a agregação).
• Tipos de fato:
  • Transação: uma linha por evento. Escolha padrão.
  • Snapshot periódico: uma linha por entidade por período (saldo diário). Use quando o estado importa e os eventos são esparsos ou atrasados.
  • Snapshot acumulado: uma linha por instância de processo com uma coluna de data por marco, atualizada no local. Use para pipelines com um ciclo de vida conhecido (pedido feito, enviado, entregue). Espere atualizações; torne a carga idempotente.
  • Fato sem medidas: eventos ou cobertura sem medida (presença, elegibilidade). Conte as linhas.

Forma do esquema

• Star por padrão: um fato, dimensões planas. Snowflake (dimensões normalizadas) somente quando uma subdimensão é compartilhada e grande ou mantida por outra equipe; os joins extras custam legibilidade e compatibilidade com ferramentas de BI.
• Dimensões conformadas: uma dim_cliente, dim_data compartilhada por todo fato, mesmas chaves e significados de atributos. Construa-as uma vez; uma segunda tabela de cliente com regras diferentes é um defeito, porque relatórios entre fatos então discordam.
• Dimensões com papéis (data_pedido, data_envio) reutilizam uma dim_data através de colunas FK separadas.
• Dimensões lixo/mini para flags de baixa cardinalidade; não deixe 8 booleanos no fato.
• Membro desconhecido: mantenha uma linha (chave -1 ou hash de 'unknown') em cada dimensão para que fatos de chegada tardia mantenham suas linhas. Joins internos com dimensões descartam fatos silenciosamente; use left joins e um coalesce para a chave desconhecida.

Chaves

• Chave natural: o identificador de negócio do sistema fonte. Mantenha-a sempre como um atributo.
• Chave substituta: adicione uma quando as chaves naturais são compostas, podem mudar, vêm de várias fontes, ou a dimensão é SCD2 (uma entidade tem muitas versões). Em warehouses no estilo dbt, prefira um hash determinístico da chave natural (mais valid_from para SCD2) em vez de sequências, para que reexecuções e builds paralelos concordem.
• Não exponha chaves substitutas a usuários de negócio como identificadores; elas mudam na reconstrução.

Dimensões de mudança lenta (SCD)

• Tipo 1 (sobrescrever): quando o histórico não tem valor analítico ou a mudança é uma correção.
• Tipo 2 (nova linha por versão, valid_from / valid_to / is_current): quando os fatos devem ser reportados com o valor do atributo conforme a data do evento (nível do cliente na compra).
• Snapshots são suficientes quando a fonte não mantém histórico e você só precisa capturar mudanças daqui para frente; eles são o mecanismo de captura, a dimensão SCD2 ainda é um modelo sobre ele. Se a fonte já tem histórico ou um log de eventos, derive faixas de validade dele em vez de criar snapshots.
• Junte fatos a SCD2 na chave mais event_ts >= valid_from and event_ts < valid_to (intervalos semiabertos, para não haver sobreposição). Teste se não há intervalos sobrepostos ou com lacunas por chave natural.
• Tipo 3 ou tipo 2 por atributo: somente quando uma única coluna de "valor anterior" responde à pergunta; caso contrário, pule.

Fan-out e dupla contagem

Causa: juntar tabelas em grãos incompatíveis de modo que o lado "um" seja na verdade muitos. Sintomas: totais maiores que a fonte, count(*) cresce após um left join, médias se deslocam.

• Detecte: compare count(*) e count(distinct chave_grao) antes e depois de cada join; eles devem ser iguais após um join com uma dimensão. Teste a unicidade da chave de junção no lado da dimensão (select key from dim group by 1 having count(*) > 1).
• Reconcilie: a soma da medida no mart é igual à soma na fonte para o mesmo filtro.
• Corrija agregando o lado muitos ao grão do fato antes de juntar, ou juntando através de uma tabela ponte com pesos de alocação; nunca use distinct no resultado para esconder o problema.
• Muitos-para-muitos (cliente para conta, produto para categoria): tabela ponte com um fator de ponderação, ou escolha um primário e declare isso.
• SQL para essas verificações: veja referências/verificacoes-fan-out.md.

Camadas

• raw/staging: um modelo de staging por tabela fonte; renomeie, converta, deduplique, sem joins, sem lógica de negócio. Mantenha raw imutável.
• intermediate: joins, reformatação, lógica compartilhada reutilizada por 2+ marts. Não exposto ao BI.
• marts: fatos e dimensões, um processo de negócio por mart, documentado, testado, contratado. Apenas marts são públicos.
• Dependências fluem em uma direção; marts não leem raw, e staging nunca lê staging.
• Materialize staging como views, marts como tabelas (incremental quando grandes e a chave do grão é estável).

Nomenclatura

• stg_<fonte>__<entidade>, int_<entidade>_<verbo>, fct_<processo>, dim_<entidade>; snake_case; plural ou singular consistentemente (escolha um).
• Chaves <entidade>_id (natural) e <entidade>_key (substituta); timestamps <evento>_at em UTC; datas <evento>_date; booleanos is_ / has_; valores monetários carregam uma unidade ou moeda (valor_usd).

O que testar

• Chave do grão: unique + not_null, em todo modelo; para grãos compostos, teste a combinação.
• Toda FK em um fato: not_null (ou membro desconhecido) e um teste de relacionamento com a dimensão.
• Valores aceitos em colunas de status/tipo; um novo valor falha ruidosamente em vez de ser mal categorizado.
• Reconciliação de contagem de linhas e soma de cada mart com sua fonte, com uma tolerância declarada (zero para finanças).
• SCD2: sem intervalos sobrepostos, exatamente uma linha atual por chave natural.
• Frescor nas fontes. Quebre cada teste uma vez em dados de amostra para provar que pode falhar.

Revisão de esquema OLTP (PostgreSQL)

• Normalize para 3FN para tabelas transacionais; desnormalize apenas para um caminho de leitura medido. Toda tabela tem uma chave primária.
• Indexe colunas de chave estrangeira: o PostgreSQL não faz isso automaticamente, e FKs não indexadas fazem com que exclusões pai e joins varram a tabela filha.
• Use timestamptz, numeric para dinheiro, text em vez de limites varchar arbitrários; imponha regras com restrições NOT NULL, CHECK, UNIQUE e FK em vez de apenas código de aplicação.
• Prefira bigint identity ou chaves UUIDv7 em vez de UUIDv4 aleatório para tabelas grandes com muitas inserções (localidade de índice).
• Multi-inquilino: coloque tenant_id em toda tabela de propriedade do inquilino, torne-o a coluna principal de chaves compostas e índices, inclua-o em FKs (FK composto) para que referências entre inquilinos sejam impossíveis, e apoie com segurança no nível de linha. Esquema compartilhado com tenant_id por padrão; esquema ou banco de dados por inquilino somente para isolamento rígido ou necessidades de restauração por inquilino.
• Exclusões suaves: índices únicos parciais (where deleted_at is null) para que a unicidade ainda seja mantida.

Armadilhas e modos de falha observados: referências/armadilhas.md