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

DBT details

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

Details

Column Type Description

Models / Seeds

  • source.trase_duckdb.source_brazil.cnpj_2019_02_bronze
  • source.trase_duckdb.source_brazil.cnpj_2019_02_cnae_secondary_bronze

Sources

  • ['source_brazil', 'cnpj_2019_02_bronze']
  • ['source_brazil', 'cnpj_2019_02_cnae_secondary_bronze']

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