📋 Regras de Negócio — Views de Faturamento

Data Lake Grupo LOS • Documentação Técnica para Validação

🔄 Última atualização: 25/08/2026 — Regras revisadas com Arão: exclusão de status L (Cancelado) em ambas as visões; pendentes tratados por espécie (NFe sempre entra, CTE pendente só entra no operacional se minuta série M); dimensões passaram a ter recarga recorrente.

1. Fluxo de Dados (Carga Diária)

Os dados são carregados diariamente do PostgreSQL (sistema TransÁgil) para o Data Lake via jobs automatizados.

Tabelas carregadas diariamente (00h BRT)

Tabela no Data Lake Origem Modo Filtro
raw_sales public.sales Full overwrite emissao_em >= 2026-01-01
raw_appropriations public.appropriations Full overwrite Todas de 2026+
raw_ctrcs public.ctrcs Full overwrite Todas de 2026+
raw_ctrc_recibo_cte public.ctrc_recibo_cte Full overwrite Todas de 2026+
⚠️ Importante: Essas 4 tabelas são recarregadas integralmente toda noite (00h BRT). A partição anterior é deletada e uma nova é criada com os dados atuais. Não há acúmulo de dados — sempre reflete o estado atual do banco.

Tabelas de dimensão (recarga full recorrente — job datalake-carga-full-dimensoes)

🔄 Atualizado 25/08/2026: as dimensões deixaram de ser carga única (07/07/2026) e passaram a ser recarregadas integralmente pelo job datalake-carga-full-dimensoes. Assim, clientes/motoristas/veículos novos passam a aparecer nas views. A cada execução a foto anterior é substituída (mantém só a partição de ingestão mais recente); as views deduplicam por id pegando sempre a foto mais nova.
Tabela Registros Observação
raw_cliente ~294k Deduplicada nas views (ROW_NUMBER por idcliente)
raw_filial ~38 Deduplicada nas views (ROW_NUMBER por idfilial)
raw_motorista ~88k Deduplicada nas views (ROW_NUMBER por idmotorista)
raw_veiculo ~146k Deduplicada nas views (ROW_NUMBER por idveiculo)
raw_planocusto ~284 Partição única (sem duplicação)
raw_cidade ~6.1k Partição única (sem duplicação)

2. Filtros Comuns a TODAS as Views de Faturamento

Filtros de exclusão (aplicados na tabela raw_sales)

Filtro de centros de custo (via EXISTS em raw_appropriations + raw_planocusto)

O CTe só entra se tiver pelo menos 1 appropriation com plano de custo válido (não excluído).

Centros de custo EXCLUÍDOS:

Prioridade de Status (raw_ctrc_recibo_cte)

Quando um CTe tem mais de um status, a prioridade é:

PrioridadeStatusDescrição
1 (maior)CCONFIRMADO
2FFS-DA
3NNEGADO
4EENVIADO
5LCANCELADO
6 (menor)NULLPENDENTE (sem registro)
Se um CTe tem status C (Confirmado) e N (Negado), o Confirmado ganha. Em caso de empate de prioridade, pega o registro com maior id (mais recente).

3. Diferenças: Operacional vs Fiscal

Regra OPERACIONAL FISCAL
Série M (Minutas) Inclui minutas como docs individuais Exclui serie = 'M'
CTes-pai com minutas Exclui (qtd_minutas > 0) Não filtra
Diferenciação NFe × CTe Campo especie (case-insensitive: UPPER(especie)) Campo especie (case-insensitive: UPPER(especie))
NFe (espécie NF*) Sempre entra (toda NFe fica pendente) Sempre entra (toda NFe fica pendente)
CTe PENDENTE (status NULL) Exclui, EXCETO minuta (série M) Exclui todo CTe pendente
Status N (Negado) Exclui Exclui
Status E (Enviado/Substituído) Exclui Exclui
Status L (Cancelado) Exclui Exclui (cancelamento não é faturamento)
Resumo (revisado 25/08/2026): a diferença essencial entre as duas visões são as minutas. O OPERACIONAL conta com minutas (série M entra como documento individual, mas exclui CTes-pai com qtd_minutas_vinculadas > 0 para não duplicar). O FISCAL é mais simples: exclui série M e não olha minutas vinculadas. Nos status, ambos excluem cancelados (L), negados (N) e enviados/substituídos (E). A única particularidade é o PENDENTE: como toda minuta fica pendente, o operacional não descarta minutas pendentes; já o fiscal (que não usa minutas) descarta todo CTe pendente. Em ambos, NFe sempre entra (toda NFe nasce pendente).

4. Views Disponíveis

vw_faturamento_cte_operacional

View detalhada — 1 linha por CTe com todos os campos (motorista, veículo, rota, impostos, etc.)

  • Cancelados (L), anulados, substituídos
  • CTes-pai com minutas vinculadas (COALESCE(minutas, 0) = 0)
  • Status N, E, L (Negado, Enviado, Cancelado)
  • CTe pendente, exceto minutas série M
  • Centros de custo da lista acima
  • Inclui minutas como documentos individuais (série M entra)
  • NFe sempre entra (espécie NF*, mesmo pendente)
  • SELECT DISTINCT (evita duplicação)

vw_faturamento_cte_fiscal

View detalhada — 1 linha por CTe (mesmos campos que operacional)

  • Cancelados (L), anulados, substituídos
  • Série M (minutas não entram)
  • Status N, E, L (Negado, Enviado, Cancelado)
  • CTe pendente (status NULL) — não entra no fiscal
  • Centros de custo da lista acima
  • NFe sempre entra (espécie NF*, mesmo pendente)
  • SELECT DISTINCT (evita duplicação)

vw_faturamento_cliente_operacional

View agregada — SUM(total_receita) agrupado por data + raiz CNPJ + razão social

Mesmas regras da vw_faturamento_cte_operacional, mas sem detalhes de CTe individual.

vw_faturamento_cliente_fiscal

View agregada — SUM(total_receita) agrupado por data + raiz CNPJ + razão social

Mesmas regras da vw_faturamento_cte_fiscal, mas sem detalhes de CTe individual.

vw_faturamento_cliente_rota_operacional

View agregada — SUM(total_receita) agrupado por data + raiz CNPJ + rota (cidade origem/destino)

vw_faturamento_cliente_rota_fiscal

View agregada — SUM(total_receita) agrupado por data + raiz CNPJ + rota (cidade origem/destino)

5. Valores Atuais (validação 06/08/2026)

Julho/2026

View Valor (R$ milhões) Linhas
vw_faturamento_cte_operacional R$ 168,25M 283.726
vw_faturamento_cliente_operacional R$ 168,24M —
vw_faturamento_cte_fiscal R$ 179,39M 284.554
vw_faturamento_cliente_fiscal R$ 179,39M —

Junho/2026

View Valor (R$ milhões)
vw_faturamento_cte_operacional R$ 158,91M
vw_faturamento_cte_fiscal R$ 167,85M
🔄 Revalidação 25/08/2026 (views agregadas por cliente, após novas regras):
View (Junho/2026)ValorLinhas
vw_faturamento_cliente_operacionalR$ 167,37M2.151
vw_faturamento_cliente_fiscalR$ 167,83M2.111
O valor fiscal caiu em relação ao ~R$ 179M anterior porque a nova regra exclui CTe pendente do fiscal (antes entravam). As views por cliente e por rota batem no mesmo total. Alvo fiscal a reconfirmar com Arão.

Breakdown por status (Operacional — Julho/2026)

StatusQtd CTesValor
CONFIRMADO279.271R$ 163,23M
PENDENTE (minutas série M)4.455R$ 5,01M

6. Lógica SQL Simplificada

View Operacional (cliente)

-- 1. Deduplica raw_cliente (pode ter múltiplas partições) WITH clientes_dedup AS ( ROW_NUMBER() OVER (PARTITION BY idcliente ORDER BY year DESC, month DESC, day DESC) -- Pega apenas a partição mais recente de cada cliente ) -- 2. Agrupa clientes por raiz CNPJ (8 primeiros dígitos) , cliente_base AS ( SUBSTR(cnpj, 1, 8) → 1 razão social por raiz CNPJ ) -- 3. Conta minutas vinculadas a cada CTe , minutas AS ( COUNT por sale_distribuicao_id em raw_ctrcs ) -- 4. Filtra vendas válidas , vendas_filtradas AS ( FROM raw_sales WHERE emissao >= 2024 AND NOT cancelado AND NOT anulado AND NOT substituído (tipo <> 't') AND sem minutas vinculadas AND EXISTS (appropriation com plano válido) ) -- 5. Pega status com prioridade (C > F > N > E > L) , cte_status AS ( ROW_NUMBER() por ctrc_id com prioridade de status ) -- 6. Resultado SELECT data, cnpj_raiz, razao_social, SUM(receita) WHERE status NOT IN ('N', 'E') -- exclui negados/enviados AND (serie = 'M' OR status IS NOT NULL) -- exclui pendentes (exceto minutas) GROUP BY data, cnpj_raiz, razao_social

Diferença na Fiscal

-- Na fiscal, muda: -- 1. NÃO filtra minutas (CTes-pai entram normalmente) -- 2. Exclui serie = 'M' (minutas não entram como doc) -- 3. Inclui pendentes (NÃO tem o filtro "sl.serie = 'M' OR st.status IS NOT NULL") -- 4. WHERE: status IS NULL OR status NOT IN ('N', 'E') -- → pendentes (NULL) entram!

7. Problemas Corrigidos (06/08/2026)

🐛 Bug: Views agregadas multiplicando linhas (400M ao invés de 177M)

Causa raiz (2 problemas combinados):

  • raw_cliente com 6 partições: A carga full original (07/07) + cargas incrementais criaram 6 partições. O JOIN sem deduplicação multiplicava cada CTe por 6.
  • INNER JOIN raw_appropriations: Um CTe pode ter múltiplas appropriations com planos válidos. O JOIN direto gerava N linhas por CTe (ao invés de usar EXISTS que retorna sim/não).

Correção aplicada:

  • CTE clientes_dedup com ROW_NUMBER() → 1 registro por cliente
  • Trocado INNER JOIN por EXISTS → não multiplica, apenas verifica existência

8. Pontos para Validação com Arão

#PerguntaContexto
1 O valor de referência "177M" é da visão fiscal ou operacional? Operacional = R$ 168M, Fiscal = R$ 179M. A diferença são os pendentes + minutas.
2 Pendentes devem entrar na operacional? Atualmente excluímos pendentes (exceto minutas série M). Se incluir todos os pendentes, sobe para ~R$ 180M.
3 Status L (Cancelado) deve ser excluído da operacional? Atualmente não excluímos L na operacional (só N e E). Na fiscal também não.
4 A lista de centros de custo excluídos está completa? 15 centros excluídos atualmente. Algum novo a adicionar ou remover?