Skip to content

DBT: Cnpj 2023 08 12 Gold

File location: s3://trase-storage/brazil/logistics/cnpj/gold/cnpj_2023_08_12/cnpj_2023_08_12_gold.parquet

DBT model name: cnpj_2023_08_12_gold

Explore on Metabase: Full table; summary statistics

DBT details

Description

Final CNPJ table based on data from 'Receita Federal do Brasil'. Versions of different years should be located at brazil/logistics/cnpj/gold/cnpj_YYYY_MM_DD/cnpj_YYYY_MM_DD_gold.parquet.

Details

Column Type Description

Models / Seeds

  • model.trase_duckdb.cnpj_site_2023_08_12_silver
  • model.trase_duckdb.cnpj_company_2023_08_12_silver
  • model.trase_duckdb.cnpj_nature_2023_08_12_silver
  • model.trase_duckdb.cnpj_reason_2023_08_12_silver
  • model.trase_duckdb.cnpj_country_2023_08_12_silver
  • model.trase_duckdb.cnpj_cnae_2023_08_12_silver
  • model.trase_duckdb.cnpj_city_2023_08_12_silver
  • model.trase_duckdb.cnpj_city_ibge_2023_08_12_silver

No called script or script source not found.

-- cnpj_2023_08_12_gold
-- Code adapted from `trase/data/brazil/logistics/cnpj/receita_federal_do_brasil/receita_federal.py`
{{ config(materialized='external') }}

SELECT
    site.cnpj,
    company.company_name,
    company.juridic_nature,
    company.nature_name,
    company.company_social_capital,
    company.company_size,
    company.company_size_name,
    company.national_responsible_entity,
    site.id_head_branch,
    site.head_branch_name,
    site.trade_name,
    site.id_registration_status,
    site.registration_status_name,
    site.registration_status_date,
    site.id_registration_status_reason,
    reason.reason_name,
    site.foreign_city_name,
    site.id_country,
    country.country_name,
    site.start_activity_date,
    site.cnae,
    cnae.cnae_name,
    site.cnae_secondary,
    site.cnae_secondary_array,
    site.address_type,
    site.address_street,
    site.address_number,
    site.address_complement,
    site.address_neighbourhood,
    site.postal_code,
    site.id_city,
    city.city_name,
    city_ibge.municipality::TEXT AS municipality,
    city_ibge.id_state,
    city_ibge.state,
    site.phone_1_area_code,
    site.phone_1,
    site.phone_2_area_code,
    site.phone_2,
    site.fax_area_code,
    site.fax, email,
    site.special_status,
    site.special_status_date
FROM {{ ref('cnpj_site_2023_08_12_silver') }} AS site
LEFT JOIN (
    SELECT
        a.cnpj_basic,
        a.company_name,
        a.juridic_nature,
        b.nature_name,
        a.company_social_capital,
        a.company_size,
        a.company_size_name,
        a.national_responsible_entity
    FROM {{ ref('cnpj_company_2023_08_12_silver') }} AS a
    JOIN {{ ref('cnpj_nature_2023_08_12_silver') }} AS b
        ON a.juridic_nature = b.id_nature
) AS company
    ON site.cnpj_basic = company.cnpj_basic
LEFT JOIN {{ ref('cnpj_reason_2023_08_12_silver') }} AS reason
    ON site.id_registration_status_reason = reason.id_reason
LEFT JOIN {{ ref('cnpj_country_2023_08_12_silver') }} AS country
    ON site.id_country = country.id_country
LEFT JOIN {{ ref('cnpj_cnae_2023_08_12_silver') }} AS cnae
    ON site.cnae = cnae.id_cnae
LEFT JOIN {{ ref('cnpj_city_2023_08_12_silver') }} AS city
    ON site.id_city = city.id_city
LEFT JOIN {{ ref('cnpj_city_ibge_2023_08_12_silver') }} AS city_ibge
    ON city.city_name = city_ibge.city_name
    AND site.state = city_ibge.state
ORDER BY site.cnpj ASC