Skip to content

DBT: Cnpj 2019 02 Gold

File location: s3://trase-storage/brazil/logistics/cnpj/gold/cnpj_2019_02/cnpj_2019_02_gold.parquet

DBT model name: cnpj_2019_02_gold

Explore on Metabase: Full table; summary statistics

Explore dependencies/lineage: link

Description

Final CNPJ table based on a download made by Javier and uploaded to s3 on 2 seperate files: one with the cnpj data and another with the secondary cnaes data

Brazilian National Registry of Legal Entities (CNPJ) from the Federal Tax Agency (RFB)

This is a dataset of CNPJs (Cadastro Nacional de Pessoas Jurídicas, or National Registry of Legal Entities) downloaded from the publicly-available dataset hosted at Brazil's RFB (Receita Federal do Brasil, or Federal Tax Agency). CNPJs are unique identifiers for legal entities in Brazil. This dataset is important for Trase's efforts to map and model supply chains; in particular for all of our SEI-PCS models in Brazil.

It is a large dataset: in the 2026-02 version the final, cleaned table is about 70 million rows and ~25 GB uncompressed. Because of that, the easiest way to explore it is through Athena (see How to explore the data below) rather than downloading the whole file.

To build the CNPJ table, we consolidate data from multiple files that the Federal Tax Agency makes available. The details are found in the link on how to update the data (futher below). In summary, the relevant files for building the table are the following:

File Description
cnpj-metadados.pdf Data dictionary
Cnaes Economic activity codes
Empresas* Companies
Estabelecimentos Establishments (sites of a company)
Motivos Reasons (for a registration status)
Municipios Municipalities
Naturezas Juridic natures
Paises Countries
Qualificaoes Qualifications
Simples Simplified-tax-regime membership
Socios* Partners
Regime Tributario Tax regime data: Immune and Exempt, Arbitrated Profit, Presumed Profit and Actual Profit

How often is the data updated by the Brazilian government?

The data is updated on a monthly basis. We aim to download the February version each year, so it already reflects all new registrations from the previous year.

Version history

  • 2026: s3://trase-storage/brazil/logistics/cnpj/gold/cnpj_2026_02/cnpj_2026_02_gold.parquet
  • 2025: s3://trase-storage/brazil/logistics/cnpj/gold/cnpj_2025_02_04/cnpj_2025_02_04_gold.parquet
  • 2023: s3://trase-storage/brazil/logistics/cnpj/gold/cnpj_2023_08_12/cnpj_2023_08_12_gold.parquet
  • 2022: s3://trase-storage/brazil/logistics/cnpj/gold/cnpj_2022_11_24/cnpj_2022_11_24_gold.parquet
  • 2019: s3://trase-storage/brazil/logistics/cnpj/gold/cnpj_2019_02/cnpj_2019_02_gold.parquet

How to update the dataset to a newer version

The full process — downloading the raw files from the RFB share, converting them to UTF-8 + uploading them to S3, adjusting the dbt pipeline, and running it to produce the new gold table — is documented in:

trase/data/brazil/logistics/cnpj/receita_federal_do_brasil/cnpj_update_guide.md

How to explore the data

Because the table is large, the easiest entry points are the ones that don't require downloading the whole file: Metabase or Athena (an AWS service that lets you run SQL queries directly against the files in S3).

1. Metabase (no code)

Pre-configured Athena connection, browse and query in the browser:

Metabase cnpj_2026_02 table

Or navigate manually: Brazil CNPJs (Athena) → s3_big_data → cnpj_2026_02.

2. Athena from Python

Use the Trase helper functions, which connects to Athena and can return the result as a Pandas DataFrame, Polars, or Polars LazyFrame (read_athena_pandas, read_athena_polars, read_athena_polars_lazy):

import pandas as pd
from trase.tools.aws.aws_helpers import read_athena_pandas

# Example 1: read a few records into a Pandas dataframe
df_sample = read_athena_pandas("SELECT * FROM cnpj_2026_02 LIMIT 5")

# Example 2: get a few records from TO state with soy farming as primary or secondary activity (CNAE: 115600)
soy_query = f"""
    SELECT * FROM cnpj_2026_02
    WHERE state = 'TO'
    AND (cnae = 115600 OR cnae_secondary LIKE '%115600%')
    LIMIT 5
"""
df_soy_sample = read_athena_pandas(soy_query)

# Example 3: get company details based on their CNPJ number
example_cnpjs = pd.DataFrame({
    "cnpj": ['11641575000100', '65004913000121', '12345678910']
})
cnpj_values = ", ".join(f"'{cnpj}'" for cnpj in example_cnpjs["cnpj"])
query = f"""
    SELECT cnpj, company_name, municipality
    FROM cnpj_2026_02
    WHERE cnpj IN ({cnpj_values})
"""
df_cnpj_details = read_athena_pandas(query)

3. Athena from R

Minimal example using the noctua package (Athena client in pure R; install once with install.packages("noctua")):

library(noctua)
library(DBI)

con <- dbConnect(
  noctua::athena(),
  s3_staging_dir = "s3://trase-temp/athena/",
  region_name    = "eu-west-1"
)

df <- dbGetQuery(con, "
  SELECT cnpj, company_name, municipality
  FROM s3_big_data.cnpj_2026_02
  WHERE cnpj IN ('11641575000100', '65004913000121')
")

print(df)

Authentication uses the AWS credentials already configured on your machine.

Acceptance criteria for sufficient level of quality of the dataset

We currently have no acceptance criteria defined for this dataset.

Details

Column Type Description
cnpj VARCHAR
razao_social VARCHAR
nome_fantasia VARCHAR
municipio VARCHAR
uf VARCHAR
tipo_de_registro INTEGER
indicador BOOLEAN
tipo_atualizacao VARCHAR
identificador_matriz_filial INTEGER
situacao_cadastral VARCHAR
data_situacao_cadastral DATE
motivo_situacao_cadastral VARCHAR
codigo_natureza_juridica INTEGER
data_inicio_atividade DATE
cnae_primary VARCHAR
cnae_secondary_array VARCHAR[]
descricao_tipo_logradouro VARCHAR
logradouro VARCHAR
numero VARCHAR
complemento VARCHAR
bairro VARCHAR
cep VARCHAR
ddd_telefone_1 VARCHAR
ddd_telefone_2 VARCHAR
ddd_fax VARCHAR
correio_eletronico VARCHAR
qualificacao_responsavel VARCHAR
capital_social_empresa DOUBLE
porte_empresa VARCHAR
opcao_pelo_simples INTEGER
opcao_pelo_mei VARCHAR
geocodmun VARCHAR
uf_code INTEGER
type VARCHAR

No data tests defined 🧐

Not referenced by any model or exposure.

Sources

No called script or script source not found.

{{ config(materialized='external') }}
SELECT
    cnpj.cnpj,
    cnpj.razao_social,
    cnpj.nome_fantasia,
    cnpj.municipio,
    cnpj.uf,
    cnpj.tipo_de_registro::INT AS tipo_de_registro,
    cnpj.indicador::BOOLEAN AS indicador,
    cnpj.tipo_atualizacao,
    cnpj.identificador_matriz_filial::INT AS identificador_matriz_filial,
    cnpj.situacao_cadastral,
    CAST(cnpj.data_situacao_cadastral AS DATE) AS data_situacao_cadastral,
    cnpj.motivo_situacao_cadastral,
    cnpj.codigo_natureza_juridica::INT AS codigo_natureza_juridica,
    CAST(cnpj.data_inicio_atividade AS DATE) AS data_inicio_atividade,
    cnpj.cnae_primary,
    cnae.cnae_secondary_array,
    cnpj.descricao_tipo_logradouro,
    cnpj.logradouro,
    cnpj.numero,
    cnpj.complemento,
    cnpj.bairro,
    cnpj.cep,
    cnpj.ddd_telefone_1,
    cnpj.ddd_telefone_2,
    cnpj.ddd_fax,
    cnpj.correio_eletronico,
    cnpj.qualificacao_responsavel,
    CAST(REPLACE(cnpj.capital_social_empresa, ',', '.') AS DOUBLE) AS capital_social_empresa,
    cnpj.porte_empresa,
    cnpj.opcao_pelo_simples::INT AS opcao_pelo_simples,
    cnpj.opcao_pelo_mei,
    cnpj.geocodmun,
    cnpj.uf_cod::INT AS uf_code,
    cnpj.type
FROM {{ source('source_brazil', 'cnpj_2019_02_bronze') }} AS cnpj
LEFT JOIN (
    SELECT
        cnpj,
        ARRAY_AGG(DISTINCT cnae_secondary) AS cnae_secondary_array
    FROM {{ source('source_brazil', 'cnpj_2019_02_cnae_secondary_bronze') }}
    GROUP BY cnpj
) AS cnae
ON cnpj.cnpj = cnae.cnpj
ORDER BY cnpj.cnpj ASC