Skip to content

View or edit on GitHub

This page is synchronized from trase/data/brazil/logistics/cnpj/receita_federal_do_brasil/cnpj_update_guide.md. Last modified on 2026-08-05 15:56 CEST by Harry Biddle. Please view or edit the original file there; changes should be reflected here after a midnight build (CET time), or manually triggering it with a GitHub action (link).

Updating the CNPJ dataset

End-to-end guide for adding a new version of the RFB CNPJ dataset (the dataset that ends up registered in Athena as s3_big_data.cnpj_<YYYY>_<MM>).

What the process looks like

There are four steps; each writes its output to a place that is fixed by convention, so the next step can find it. For downloading, its much faster (30mins vs 5h) if done from Brazil, or using a VPN connected to Brazil.

# Step What it does Output
1 Download the raw zip files ~36 zip files (~7.6 GB) s3://trase-storage/brazil/logistics/cnpj/receita_federal_do_brasil/originals/<YYYY-MM>/compressed/
2 Uncompress, fix encoding, upload Each zip unzipped to CSV, converted to UTF-8 encoding, uploaded …/originals/<YYYY-MM>/uncompressed/
3 Update the dbt pipeline Add the new version's original source files (bronze) + pre-processed (silver) + final (gold) models (copy-and-rename from the previous version) code in this repo
4 Run the pipeline dbt reads the bronze CSVs, builds silver tables, builds the gold table, and registers the gold table in Glue/Athena …/gold/cnpj_<YYYY>_<MM>/cnpj_<YYYY>_<MM>_gold.parquet and an Athena table s3_big_data.cnpj_<YYYY>_<MM>

The rest of this document walks through each step.

Step 1 — Download the raw files from the Brazilian Tax Agency (RFB)

As of 2026-06, RFB publishes the CNPJ open data on a Nextcloud public share (a data sharing system):

https://arquivos.receitafederal.gov.br/index.php/s/YggdBLfdninEJX9

If the above link doesn't work, try going through the national open data portal (https://dados.gov.br/dados/conjuntos-dados/cadastro-nacional-da-pessoa-juridica---cnpj) -> Recursos -> Inscrições no CNPJ -> Acessar o recurso.

Currently, the share root contains:

  • one monthly folder per release — 2023-05/, …, 2026-06/ — each with ~36 zip files (Empresas{0..9}.zip, Estabelecimentos{0..9}.zip, Socios{0..9}.zip, Simples.zip, and the reference tables Cnaes / Motivos / Municipios / Naturezas / Paises / Qualificacoes), totalling ~7.6 GB per month;
  • a single aggregate cnpj.tar.gz at the root (~59.5 GB, probably the full historical bundle).

Speed: download from Brazil

The data sharing service limits download speed by geography. Observed single-connection speeds:

Source Speed Monthly set (7.6 GB)
International IP ~0.43 MB/s ~5 h
Brazil IP ~3-6 MB/s ~40 min

→ Prefer a Brazilian connection. A VPN to Brazil works (for example, NordVPN). Once your laptop is on a Brazilian IP, the rest of this step runs in any terminal.

Download a monthly release (~7.6 GB, ~40 min)

The commands below run on your laptop. Run each block one at a time, check the output is what you expect, then move on to the next block.

1. Pick the month and set the paths

Replace MONTH with the year-month folder name as it appears in the share (for example 2026-06). Everything else uses $MONTH and stays the same.

TOKEN=YggdBLfdninEJX9                  # share token (between /s/ and ? in the URL)
MONTH=2026-06                          # <<-- adjust to the month you are downloading
BASE=https://arquivos.receitafederal.gov.br/public.php/webdav/$MONTH
DEST=s3://trase-storage/brazil/logistics/cnpj/receita_federal_do_brasil/originals/$MONTH/compressed

LOCAL=~/cnpj-download/$MONTH           # local folder the zips will land in
mkdir -p "$LOCAL" && cd "$LOCAL"

2. Ask the share for the list of zip files

This writes the file names to files.txt. Then cat it to confirm: you should see ~36 lines, all *.zip (Empresas0.zip, Estabelecimentos0.zip, etc.).

curl -s -u "$TOKEN:" -X PROPFIND -H 'Depth: 1' "$BASE/" \
  | grep -oE "/public.php/webdav/$MONTH/[^<]+\.zip" | sed 's#.*/##' | sort -u > files.txt

cat files.txt
wc -l files.txt

3. Download the zips, one at a time

If your connection drops, just re-run this block — already-finished files are skipped, half-finished ones resume from where they left off (that is what -C - and --retry do).

while read -r f; do
  echo ">>> $f"
  curl -C - --retry 10 -u "$TOKEN:" -O "$BASE/$f"
done < files.txt

When it finishes, check you really have all the zips:

ls -1 *.zip | wc -l            # should match the line count from step 2
du -sh .                       # should be ~7-8 GB

4. Upload to S3

aws s3 cp "$LOCAL" "$DEST/" --recursive --exclude '*' --include '*.zip'

# verify
aws s3 ls "$DEST/"

When all the .zip objects show up in S3, Step 1 is done. You can delete the local copies (rm -rf "$LOCAL").

Background — why the download isn't just a button click

Nextcloud renders the file list with JavaScript over WebDAV — there are no <a href> download links in the HTML to copy. The commands above talk to the WebDAV API directly, using the share token from the URL (YggdBLfdninEJX9) as the HTTP username with an empty password.

⚠️ Do not use the …/index.php/s/<token>/download?path=… URL — it routes through Nextcloud's on-the-fly zip packer and frequently hangs at 0 bytes.

Step 2 — Uncompress, normalise to UTF-8, upload

The zips on S3 are not directly readable by the pipeline: each one must be unzipped into a CSV, and the CSVs come in a mix of UTF-8 and latin-1 encodings, which has to be normalised to UTF-8 before they can be joined together.

Both are handled by the script rfb_uncompress_and_upload.sh in this folder. It works one file at a time, abort-on-failure, and deletes each local file as soon as it has been uploaded — so it can run on a small disk and you don't end up with half-finished uploads.

Recommended: run it on a SageMaker terminal. SageMaker is on the same AWS network as S3, so the upload is fast and free, and it doesn't tie up your laptop for the duration.

# inside a SageMaker terminal (or anywhere with the trase repo + aws cli):
cd trase/data/brazil/logistics/cnpj/receita_federal_do_brasil/
./rfb_uncompress_and_upload.sh 2026-02     # use the date you downloaded

The script writes the cleaned CSVs to:

s3://trase-storage/brazil/logistics/cnpj/receita_federal_do_brasil/originals/<YYYY-MM>/uncompressed/

That folder is what the dbt pipeline reads from in the next step.

Step 3 — Update the dbt pipeline for the new version

The pipeline already contains entries for past versions (2025_02_04, 2023_08_12, 2022_11_24, 2019_02). You just copy the most recent year's block and rename the date. There are three places to edit, all using the same <YYYY>_<MM> (e.g. 2026_02).

Tip: do a copy-and-rename on the previous version's block. The schemas are identical across years, so no logic needs to be changed.

3a. Bronze — raw CSV sources

File: trase/data_pipeline/models/brazil/logistics/cnpj/receita_federal_do_brasil/_sources_cnpj_receita_federal_do_brasil_cnpj.yml

Add one source entry per CSV (one each for cnaes, empresas, estabelecimentos, motivos, municipios, naturezas, paises) under sources: - name: source_brazil, following the existing pattern. Every path contains <YYYY-MM> — that's the date you used in Step 2.

3b. Silver — one cleaned table per CSV

Folder: trase/data_pipeline/models/brazil/logistics/cnpj/silver/

Copy the previous year's subfolder (e.g. cnpj_2025_02_04/) to cnpj_<YYYY>_<MM>/ (e.g. cnpj_2026_02/). Inside, rename each file and change the date in the dbt.source(...) call — everything else (the cleaning logic) is shared via trase/data/brazil/logistics/cnpj/cnpj_preprocessing.py and needs no editing.

3c. Gold — the final joined table

File: trase/data_pipeline/models/brazil/logistics/cnpj/gold/cnpj_<YYYY>_<MM>_gold.py

Copy cnpj_2025_02_04_gold.py to cnpj_<YYYY>_<MM>_gold.py and update the date in every dbt.ref(...) call. Then in the schema YAML _schema_cnpj_receita_federal_do_brasil.yml add the matching cnpj_<YYYY>_<MM>_gold model entry — again, copy from the previous year and update the date.

Make sure the new gold model entry carries the post-hook line:

post_hook: "{{ register_glue_table() }}"

This is what triggers automatic registration in Athena at the end of Step 4.

Sanity check

Before running anything, ask dbt to confirm the new YAML/Python parses cleanly:

cd trase/data_pipeline
./dbt parse --target production

No error means the new version is wired in.

Step 4 — Run the pipeline

Recommended: from a SageMaker terminal. Building the gold table involves several joins over the full CNPJ data and uses a lot of memory and temporary disk — SageMaker instances are sized for this, and the dbt wrapper auto-configures DuckDB's memory limit to 70% of the instance RAM.

Suggested instance sizing:

  • RAM: 128 GB
  • Storage: 100 GB or more (temporary files spill to disk during the joins)

Expected runtime: between 30 minutes and 1 hour on an instance of that size.

cd trase/data_pipeline

# build the new bronze + silver + gold for this version only
./dbt run --target production --select +cnpj_<YYYY>_<MM>_gold

# example for 2026_02:
./dbt run --target production --select +cnpj_2026_02_gold

What happens, in order:

  1. The "bronze" sources are loaded directly from the CSVs on S3 (no parquet is written — sources are read on the fly).
  2. Each "silver" model writes a cleaned parquet to s3://trase-storage/brazil/logistics/cnpj/silver/cnpj_<YYYY>_<MM>/.
  3. The gold model joins them and writes the final parquet to s3://trase-storage/brazil/logistics/cnpj/gold/cnpj_<YYYY>_<MM>/cnpj_<YYYY>_<MM>_gold.parquet.
  4. The post-hook on the gold model calls register_glue_table(), which inspects the new parquet and registers (or refreshes) the Athena table s3_big_data.cnpj_<YYYY>_<MM>. You'll see a line in the dbt output:
**** Registered Glue/Athena table 's3_big_data.cnpj_<YYYY>_<MM>' (N columns) -> s3://… ****

That's it — the new version is queryable from Metabase / Athena straight away (see How to explore the data in the dataset doc), and the new version's S3 path should be added to the Version history list in that same doc.