Estrutura de Tabelas - Fenabrave Emplacamento

📋 Visão Geral

O projeto segue o padrão Medallion Architecture com três camadas de dados:

PostgreSQL (retroativo 2003-2026)    ┐
                                      ├─→ Bronze (dados brutos)
FTP Fenabrave (diário + mensal)      ┘
                                     │
                                     ↓
                                Silver (normalized)
                                     │
                                     ↓
                        Gold (star schema - Delta Lake)

🥉 Bronze Layer

Armazena dados brutos no S3 em dois locais.

Priorização de Fontes (deduplicação por numero_chassis):

ftp_diario — fonte oficial diária (prioridade máxima)
postgres — retroativo histórico (carga inicial 2003–2026)
ftp_mensal — correção/fechamento de mês (última prioridade)

1. PostgreSQL Bronze

Prefixo: fenabrave/bronze/postgres/dwemplacamento/

Fonte: tabelas PostgreSQL do schema dwemplacamento (dados históricos)

Tabelas extraídas:

Tabela	Tipo	Particionamento
`fato_emplacamento/`	Fato	`ano=YYYY`
`dim_empresa/`	Dimensão	Não
`dim_fabricante/`	Dimensão	Não
`dim_versao_associada/`	Dimensão	Não
`dim_versao/`	Dimensão	Não
`dim_segmento/`	Dimensão	Não
`dim_local/`	Dimensão	Não
`dim_combustivel/`	Dimensão	Não

Formato: Parquet (extraído com DAG postgres_emplacamento_retroativo)

Características:

Particionadas por ano (tabelas com coluna de data)
Idempotente: partições já presentes são puladas
Máximo 2 extrações paralelas para não sobrecarregar o banco

2. FTP Bronze

Prefixo: fenabrave/bronze/fenabrave/

Fonte: arquivos .txt do FTP Fenabrave (dados diários/mensais)

Estrutura de diretórios:

fenabrave/bronze/
└── ABRACAF/
    ├── Emplacamentos_Diario_Segmentos_NC_YYYY-MM-DD.txt        # uma cópia por dia
    ├── Emplacamentos_Diario_Segmentos_Mdl_NC_YYYY-MM-DD.txt    # uma cópia por dia
    ├── Emplacamentos_Mensal_Fabricante_NC_YYYY-MM.txt          # baixado apenas 1× por mês
    └── Emplacamentos_Mensal_Segmentos_Mdl_NC_YYYY-MM.txt       # baixado apenas 1× por mês

Características:

Formato: arquivos .txt (pipe-delimited, detecta encoding automaticamente)
Filtro: apenas arquivos com "diario" ou "mensal" no nome E contendo "nc"
Apenas ABRACAF é processado atualmente (filtro /ABRACAF/ no medallion)
Nomes de coluna normalizados para snake_case
Diário usa sufixo YYYY-MM-DD (uma cópia por dia, histórico completo)
Mensal usa sufixo YYYY-MM e é baixado apenas uma vez por mês — não há re-download diário, para reduzir consumo de banda/tempo no FTP. A cópia única do mês fica disponível para correção/fechamento no medallion.

🥈 Silver Layer

Prefixo: fenabrave/silver/ano=YYYY/chunk_NNNN.parquet

Dados transformados, limpos e normalizados. Consolidado a partir de Bronze.

Particionamento

Por ano: ano=YYYY (extraído de data_emplacamento)
Por chunk: máximo 2.000.000 linhas por arquivo (controle de memória)

Esquema Silver

Estrutura: Todas as colunas são String para garantir equivalência entre Postgres e FTP.

Coluna	Tipo	Origem	Descrição
`cnpj`	String	PG + FTP	CNPJ da concessionária
`is_cpf_empresa`	String	PG + FTP	True se CNPJ tem 11 dígitos (pessoa física)
`is_cpf_cliente`	String	PG + FTP	True se Tipo_Pessoa do FTP da ABRACAF for Física
`razao_social`	String	PG + FTP	Razão social ou "Pessoa Física"
`_origem`	String	Sistema	Fonte: `ftp_diario`, `ftp_mensal` ou `postgres`
`data_emplacamento`	Date	PG + FTP	Data do emplacamento
`numero_chassis`	String	PG	Número do chassis
`numero_placa`	String	PG	Placa do veículo
Fabricante
`fabricante_codigo`	Int	PG	Código do fabricante
`fabricante`	String	PG	Nome do fabricante
Modelo
`modelo_codigo`	Int	PG	Código da versão
`modelo`	String	PG	Descrição da versão
`grupomodeloveiculo_codigo`	Int	PG	Código do grupo modelo
`grupomodeloveiculo`	String	PG	Nome do grupo modelo
Segmentação
`segmento_codigo`	Int	PG	Código da categoria
`segmento`	String	PG	Nome da categoria
`subsegmento_codigo`	Int	PG	Código do segmento
`subsegmento`	String	PG	Nome do segmento
`combustivel_codigo`	Int	PG	Código do combustível
`combustivel`	String	PG	Tipo de combustível
Localização
`municipio_codigo`	Int	PG + FTP	Código IBGE do município
`municipio`	String	PG + FTP	Nome do município (campo único, sem pivot)
`estado`	String	PG + FTP	UF (campo único, sem pivot)
Região (pivotada por grupo_id)
`regiao_abracaf`	String	PG (pivotado)	Região segundo ABRACAF
`regiao_abcnissan`	String	PG (pivotado)	Região segundo ABCNISSAN
`regiao_abrare`	String	PG (pivotado)	Região segundo ABRARE
Veículo
`potencia`	String	PG	Potência do motor
`cilindradas`	String	PG + FTP	Cilindradas do motor (cm³)
`capacidade_carga`	String	PG	Capacidade de carga (kg)
`capacidade_passageiros`	String	PG	Número de passageiros
`cor_veiculo`	String	FTP	Cor do veículo
`codigo_nacionalidade`	String	PG	Código do país de origem
`ano_fabricacao`	Int	PG	Ano de fabricação
Restrições
`restricao_01`	String	PG	Restrição 01
`restricao_02`	String	PG	Restrição 02
`restricao_03`	String	PG	Restrição 03
`restricao_04`	String	PG	Restrição 04
Metadados
`_source_file`	String	Sistema	Path S3 do arquivo de origem

🥇 Gold Layer

Tipo: Delta Lake (S3: s3://bucket/fenabrave/gold/)

Implementa um star schema com dimensões desnormalizadas e fato.

Tabelas Gold

📊 dim_concessionaria

Dimensão de empresas concessionárias.

Coluna	Tipo	Descrição
`concessionaria_sk`	Int64	Surrogate Key (auto-incremento)
`cnpj`	String	Natural Key
`is_cpf_empresa`	Boolean	True se CNPJ tem 11 dígitos (pessoa física)
`razao_social`	String	Razão social (campo único, sem pivot)
`regiao_abracaf`	String	Região ABRACAF (pivotado por grupo_id)
`regiao_abcnissan`	String	Região ABCNISSAN (pivotado por grupo_id)
`regiao_abrare`	String	Região ABRARE (pivotado por grupo_id)

Estatísticas esperadas: ~5-10k registros

🏙️ dim_local

Dimensão de localidades (municípios).

Coluna	Tipo	Descrição
`local_sk`	Int64	Surrogate Key (auto-incremento)
`municipio_codigo`	Int	Natural Key (código IBGE)
`municipio`	String	Nome do município (campo único, sem pivot)
`estado`	String	UF (campo único, sem pivot)
`regiao_abracaf`	String	Região ABRACAF (pivotado por grupo_id)
`regiao_abcnissan`	String	Região ABCNISSAN (pivotado por grupo_id)
`regiao_abrare`	String	Região ABRARE (pivotado por grupo_id)

Estatísticas esperadas: ~5-6k registros

🚗 dim_veiculo

Dimensão de características de veículos.

Coluna	Tipo	Descrição
`veiculo_sk`	Int64	Surrogate Key (auto-incremento)
`veiculo_nk`	String	Natural Key (concat: `modelo_codigo\|fabricante_codigo\|combustivel_codigo\|subsegmento_codigo`)
`fabricante_codigo`	Int	Código do fabricante
`fabricante`	String	Nome do fabricante
`modelo_codigo`	Int	Código da versão/modelo
`modelo`	String	Descrição da versão
`grupomodeloveiculo_codigo`	Int	Código do grupo modelo
`grupomodeloveiculo`	String	Nome do grupo modelo
`segmento_codigo`	Int	Código da categoria
`segmento`	String	Nome da categoria
`subsegmento_codigo`	Int	Código do subsegmento
`subsegmento`	String	Nome do subsegmento
`combustivel_codigo`	Int	Código do combustível
`combustivel`	String	Tipo de combustível
`cor_veiculo`	String	Cor do veículo
`potencia`	String	Potência do motor
`cilindradas`	String	Cilindradas do motor (cm³)
`capacidade_carga`	String	Capacidade de carga (kg)
`capacidade_passageiros`	String	Número de passageiros

Estatísticas esperadas: ~100-200k registros

📅 dim_calendario

Dimensão de datas (cobertura 2000–2100, gerada pela DAG dim_calendario).

Coluna	Tipo	Descrição
`data`	Date	Natural Key
`ano`	Int32	Ano
`semestre`	Int32	1 ou 2
`trimestre`	Int32	1 a 4
`mes`	Int32	1 a 12
`nome_mes`	String	Janeiro, Fevereiro, ...
`semana_ano`	Int32	Semana ISO do ano
`dia_do_mes`	Int32	1 a 31
`dia_do_ano`	Int32	1 a 366
`dia_da_semana`	Int32	ISO: 1=Segunda ... 7=Domingo
`nome_dia_semana`	String	Segunda-feira, Terça-feira, ...
`fim_de_semana`	Boolean	True se sábado ou domingo
`feriado_nacional`	Boolean	True se feriado nacional
`nome_feriado`	String	Nome do feriado (null se não for)
`dia_util`	Boolean	True se não é fim de semana nem feriado
`primeiro_dia_do_mes`	Boolean	True se é dia 1
`ultimo_dia_do_mes`	Boolean	True se é último dia do mês

Feriados: nacionais brasileiros fixos + móveis (Carnaval, Páscoa, Sexta-feira da Paixão, Corpus Christi). Consciência Negra a partir de 2023.

Uso: join com fato_emplacamentos.data_emplacamento para enriquecer análises temporais. Também serve de referência para o ajuste de data_emplacamento no fato (que aplica nearest_business_day).

Estatísticas esperadas: ~36k registros (101 anos × 365.25)

📈 fato_emplacamentos

Tabela de fatos (detalhes de emplacamentos).

Coluna	Tipo	Descrição
`data_emplacamento`	Date	Data ajustada para o dia útil mais próximo dentro do mesmo mês (pula fim de semana e feriados)
`data_emplacamento_original`	Date	Data crua do source (sem ajuste de dia útil)
`ano_fabricacao`	Int	Ano de fabricação
`numero_chassis`	String	Número do chassis
`numero_placa`	String	Placa do veículo
`codigo_nacionalidade`	String	Código do país de origem
`restricao_01`	String	Restrição 01
`restricao_02`	String	Restrição 02
`restricao_03`	String	Restrição 03
`restricao_04`	String	Restrição 04
`concessionaria_sk`	Int64	FK → `dim_concessionaria`
`local_sk`	Int64	FK → `dim_local`
`veiculo_sk`	Int64	FK → `dim_veiculo`
`is_cpf_cliente`	Boolean	True se Tipo_Pessoa do ftp ABRACAF é física
`_source_file`	String	Path S3 da origem
`_ano_particao`	Int	Partição: ano para otimizar queries
`_origem`	String	Fonte: `ftp_diario`, `ftp_mensal` ou `postgres` (última coluna)

Particionamento: _ano_particao=YYYY

Estatísticas esperadas: ~25-50M registros

🔷 Supabase Layer

Tipo: Postgres (Supabase) — espelho relacional do Gold para consumo SQL/BI.

Schema: dw_emplacamento (não public).

Atualização: task sync_supabase no fim da DAG fenabrave_ftp_to_bronze (MWAA Serverless, 11h UTC). Carga inicial via scripts/supabase_initial_load.py standalone — não passa por Lambda.

Princípio

Espelho 1:1 das tabelas do Gold, no mesmo shape dimensional (mesmas colunas, mesmas SKs). Diferente do Gold (Delta Lake), aqui temos PKs/UNIQUE declaradas e particionamento nativo Postgres.

Gold Delta tipo	Postgres tipo
Int64	BIGINT
Int / Int32	INTEGER
String	TEXT
Date	DATE
Boolean	BOOLEAN

Tabelas

📊 dw_emplacamento.dim_concessionaria

PK: concessionaria_sk (BIGINT). UNIQUE: cnpj. Espelha o Gold.

🏙️ dw_emplacamento.dim_local

PK: local_sk (BIGINT). UNIQUE: municipio_codigo. Espelha o Gold.

🚗 dw_emplacamento.dim_veiculo

PK: veiculo_sk (BIGINT). UNIQUE: veiculo_nk. Espelha o Gold.

📅 dw_emplacamento.dim_calendario

PK: data (DATE). Espelha o Gold (cobertura 2000–2100).

📈 dw_emplacamento.fato_emplacamentos

Coluna	Tipo	Observação
`_ano_particao`	INTEGER NOT NULL	Coluna de particionamento
`numero_placa`	TEXT NOT NULL	PK composta com `_ano_particao`
`data_emplacamento`	DATE	Ajustada para dia útil
`data_emplacamento_original`	DATE	Sem ajuste
`ano_fabricacao`	INTEGER
`numero_chassis`	TEXT
`codigo_nacionalidade`	TEXT
`restricao_01..04`	TEXT
`concessionaria_sk`	BIGINT	FK lógica → dim_concessionaria
`local_sk`	BIGINT	FK lógica → dim_local
`veiculo_sk`	BIGINT	FK lógica → dim_veiculo
`is_cpf_cliente`	BOOLEAN
`_source_file`	TEXT
`_origem`	TEXT

Particionamento: PARTITION BY LIST (_ano_particao)

CREATE TABLE fato_emplacamentos_2025 PARTITION OF fato_emplacamentos FOR VALUES IN (2025);
CREATE TABLE fato_emplacamentos_2026 PARTITION OF fato_emplacamentos FOR VALUES IN (2026);

Queries em fato_emplacamentos com filtro WHERE _ano_particao = YYYY fazem partition pruning automático — só tocam a partição filha. Sem filtro, lê todas as partições (mesma performance de tabela única).

FKs NÃO são declaradas — só índices nas SKs. Razão: o pipeline TRUNCA as dims a cada sync e FK travaria o TRUNCATE.

Índices:

ix_fato_data_emplacamento em data_emplacamento
ix_fato_concessionaria_sk em concessionaria_sk
ix_fato_local_sk em local_sk
ix_fato_veiculo_sk em veiculo_sk

Como os dados chegam

Camada anterior	Para Supabase
Dims do Gold (4 tabelas Delta)	TRUNCATE + COPY (full refresh diário)
Fato do Gold (Delta particionado por `_ano_particao`)	UPSERT por `(_ano_particao, numero_placa)` em staging temp + `INSERT ... ON CONFLICT DO UPDATE`

Janela do UPSERT diário: últimos 7 dias do fato (cobre reposts da Fenabrave sem precisar de tracking de estado). Idempotente.

Particularidades vs Gold

Item	Gold (Delta)	Supabase
Schema	`s3://...../fenabrave/gold/`	`dw_emplacamento`
PK	Implícita (chave única do Delta)	Declarada nativa
Particionamento	Por `_ano_particao` (Delta)	`PARTITION BY LIST` (Postgres)
RLS	n/a	Desabilitado (necessário pro COPY)
Cobertura temporal	2003–2026 (todo histórico)	Apenas 2025–2026
Linha sem `numero_placa`	Mantida	Descartada (PK NOT NULL)

Estatísticas esperadas

Tabela	Linhas	Tamanho
`dim_concessionaria`	~27 k	~5 MB
`dim_local`	~5,5 k	~1 MB
`dim_veiculo`	~17 k	~5 MB
`dim_calendario`	~37 k	~5 MB
`fato_emplacamentos_2025`	~2,5 M	~675 MB
`fato_emplacamentos_2026`	até ~2,5 M (conforme ano avança)	até ~675 MB
Total (cheio)	~5 M	~1,4 GB

Free tier do Supabase (500 MB) não cabe — produção usa plano Pro (8 GB).

Para uso diário ver

Ver infra/supabase/README.md no repositório — como conectar (pooler, roles, search_path), queries de exemplo, operações de manutenção (adicionar ano novo, role read-only, validar consistência) e gotchas.

🔄 Fluxo de Dados

┌─────────────────────────────────────────────────────────────────┐
│ ORIGEM: Postgres + FTP                                          │
└──────────────────────┬──────────────────────────────────────────┘
                       │
         ┌─────────────┴─────────────┐
         │                           │
    DAG: postgres_           DAG: fenabrave_
    emplacamento_retroativo  ftp_to_bronze
         │                           │
         └─────────────┬─────────────┘
                       ↓
    ┌──────────────────────────────────────┐
    │ Bronze: dados brutos                 │
    │ - postgres/dwemplacamento/*          │
    │ - fenabrave/bronze/ABRACAF/*         │
    └──────────────────────┬───────────────┘
                           │
                DAG: fenabrave_medallion
                           │
         ┌─────────────────┴─────────────────┐
         │ Task: bronze_to_silver            │
         └─────────────────┬─────────────────┘
                           ↓
    ┌──────────────────────────────────────┐
    │ Silver: dados normalizados            │
    │ - fenabrave/silver/ano=YYYY/chunk_*  │
    └──────────────────────┬───────────────┘
                           │
         ┌─────────────────┴─────────────────┐
         │ Task: silver_to_gold              │
         └─────────────────┬─────────────────┘
                           ↓
    ┌──────────────────────────────────────┐
    │ Gold: star schema (Delta Lake)        │
    │ - dim_concessionaria                  │
    │ - dim_local                           │
    │ - dim_veiculo                         │
    │ - dim_calendario (DAG própria)        │
    │ - fato_emplacamentos                  │
    └──────────────┬───────────────────────┘
                   │
        ┌──────────┴──────────┐
        ↓                     ↓
 Task: sync_dynamo     Task: sync_supabase
        │                     │
        ↓                     ↓
 ┌─────────────┐    ┌──────────────────────────────┐
 │ DynamoDB    │    │ Supabase (dw_emplacamento)   │
 │ (lookup     │    │ - dim_*  (full refresh)       │
 │  placa)     │    │ - fato_emplacamentos          │
 └─────────────┘    │   (UPSERT últimos 7 dias)     │
                    └──────────────────────────────┘

⚙️ Configurações

Variáveis de Ambiente

PostgreSQL:

PG_EMPLACAMENTO_HOST=<host>
PG_EMPLACAMENTO_PORT=5432
PG_EMPLACAMENTO_DB=emplacamento
PG_EMPLACAMENTO_SCHEMA=dwemplacamento
PG_EMPLACAMENTO_USER=<user>
PG_EMPLACAMENTO_PASS=<password>

FTP Fenabrave:

FTP_FENABRAVE_HOST=ftp.fenabrave.org.br (padrão)
FTP_FENABRAVE_USER=<user>
FTP_FENABRAVE_PASS=<password>
FTP_FENABRAVE_ROOT=/ABRACAF (padrão, processado atualmente)

S3/MinIO:

S3_CONN_ID=minio (dev) ou aws_default (prod)
BUCKET=fenabrave (padrão)

Filtros

medallion.py - FILTER_YEARS:

FILTER_YEARS: set[int] = {2024, 2025, 2026}  # Deixe vazio para todos

emplacamento_retroativo.py - FILTER_YEARS:

FILTER_YEARS: set[int] = {2026}  # Deixe vazio para todos

📊 Estatísticas

Camada	Entidade	Linhas	Particionamento	Tamanho Esperado
Gold	dim_concessionaria	~10k	Não	~10 MB
Gold	dim_local	~6k	Não	~5 MB
Gold	dim_veiculo	~150k	Não	~50 MB
Gold	dim_calendario	~36k	Não	~2 MB
Gold	fato_emplacamentos	~50M	Por ano	~500 MB-1 GB
Silver	todos os anos	~50M	`ano=YYYY` + chunks	~200 MB-500 MB

🔍 Queries de Exemplo

Contar emplacamentos por concessionária (2026)

SELECT 
  c.razao_social,
  COUNT(*) as total_emplacamentos,
  COUNT(DISTINCT f.numero_placa) as veiculos_unicos
FROM fato_emplacamentos f
JOIN dim_concessionaria c ON f.concessionaria_sk = c.concessionaria_sk
WHERE f._ano_particao = 2026
GROUP BY c.razao_social
ORDER BY total_emplacamentos DESC;

Emplacamentos por estado (último mês)

SELECT 
  l.estado,
  COUNT(*) as total
FROM fato_emplacamentos f
JOIN dim_local l ON f.local_sk = l.local_sk
WHERE f._ano_particao = 2026
  AND YEAR(f.data_emplacamento) = 2026
  AND MONTH(f.data_emplacamento) = 12
GROUP BY l.estado;

Veículos mais emplacados

SELECT 
  v.fabricante,
  v.modelo,
  COUNT(*) as total
FROM fato_emplacamentos f
JOIN dim_veiculo v ON f.veiculo_sk = v.veiculo_sk
WHERE f._ano_particao = 2026
GROUP BY v.fabricante, v.modelo
ORDER BY total DESC
LIMIT 10;

📝 Notas

Idempotência: Bronze e Silver podem ser reprocessados sem duplicação
Upsert em Gold: Dimensões usam merge Delta (natural key), fato usa overwrite no 1º chunk e append nos demais
Schema Consistency: Silver tracking schema para evitar dtype mismatch ao concatenar frames
Memory Management: Leitura streaming e chunks para processar 50M+ linhas sem sobrecarregar RAM
Particionamento: Silver e Fato particionados por ano para otimizar queries

📋 Visão Geral​

🥉 Bronze Layer​

1. PostgreSQL Bronze​

2. FTP Bronze​

🥈 Silver Layer​

Particionamento​

Esquema Silver​

🥇 Gold Layer​

Tabelas Gold​

📊 dim_concessionaria​

🏙️ dim_local​

🚗 dim_veiculo​

📅 dim_calendario​

📈 fato_emplacamentos​

🔷 Supabase Layer​

Princípio​

Tabelas​

📊 dw_emplacamento.dim_concessionaria​

🏙️ dw_emplacamento.dim_local​

🚗 dw_emplacamento.dim_veiculo​

📅 dw_emplacamento.dim_calendario​

📈 dw_emplacamento.fato_emplacamentos​

Como os dados chegam​

Particularidades vs Gold​

Estatísticas esperadas​

Para uso diário ver​

🔄 Fluxo de Dados​

⚙️ Configurações​

Variáveis de Ambiente​

Filtros​

📊 Estatísticas​

🔍 Queries de Exemplo​

Contar emplacamentos por concessionária (2026)​

Emplacamentos por estado (último mês)​

Veículos mais emplacados​

📝 Notas​