Silver Brazil Comtrade Coffee 2020 Monthly

s3://trase-storage/brazil/coffee/trade/2020/silver/silver_brazil_comtrade_coffee_2020_monthly.parquet

Dbt path: trase_production.main_brazil_coffee.silver_brazil_comtrade_coffee_2020_monthly

Explore on Metabase: Full table; summary statistics

Containing yaml file link: trase/data_pipeline/models/brazil/coffee/trade/_schema_brazil_coffee.yml

Model file link: trase/data_pipeline/models/brazil/coffee/trade/silver_brazil_comtrade_coffee_2020_monthly.sql

Dbt test runs & lineage: Test results · Lineage

Full dbt_docs page: Open in dbt docs (includes lineage graph -at the bottom right-, tests, and downstream dependencies)

Tags: silver, brazil, coffee, trade, comtrade, 2020, diet-trase-coffee

silver_brazil_comtrade_coffee_2020_monthly

Description

Filters the coffee Comtrade data for Brazil records and relevant values (Totals, and filters out 'World' Partner), and adds a FOB_PER_KG_AVERAGE column

Details

ColumnsDepends OnModel code

Column	Type	Description
`YEAR`	`BIGINT`
`MONTH`	`BIGINT`
`HS_CODE`	`VARCHAR`
`HS_CODE_DESCRIPTION`	`VARCHAR`
`COUNTRY_SOURCE_ISO`	`VARCHAR`
`COUNTRY_SOURCE`	`VARCHAR`
`COUNTRY_DESTINATION_ISO`	`VARCHAR`
`COUNTRY_DESTINATION`	`VARCHAR`
`UNIT`	`VARCHAR`
`NET_WEIGHT`	`DOUBLE`
`IS_NET_WEIGHT_ESTIMATED`	`BOOLEAN`
`FOB_VALUE_IN_USD`	`DOUBLE`
`FOB_PER_KG_AVERAGE`	`DOUBLE`

Models / Seeds

source.trase_duckdb.source_world_trade.original_comtrade_coffee_2020_monthly

Sources

['source_world_trade', 'original_comtrade_coffee_2020_monthly']

No called script or script source not found.

-- Filters the coffee Comtrade data for relevant values, and adds a FOB_PER_KG_AVERAGE column
{{ 
    config(
        materialized='external',
        meta={
            "external_location": "s3://trase-storage/brazil/coffee/trade/2020/silver/silver_brazil_comtrade_coffee_2020_monthly.parquet",
            "model_maturity": "in dev",
            "year": 2020,
        },
        persist_docs={"relation": true, "columns": true}        
    ) 
}}
WITH source_data AS (
    SELECT
        RefYear AS YEAR,
        RefMonth AS MONTH,
        CmdCode AS HS_CODE,
        CmdDesc AS HS_CODE_DESCRIPTION,
        UPPER(ReporterISO) AS COUNTRY_SOURCE_ISO,
        ReporterDesc AS COUNTRY_SOURCE,
        PartnerISO AS COUNTRY_DESTINATION_ISO,
        PartnerDesc AS COUNTRY_DESTINATION,
        QtyUnitAbbr AS UNIT,
        NetWgt AS NET_WEIGHT,
        IsNetWgtEstimated AS IS_NET_WEIGHT_ESTIMATED,
        Fobvalue AS FOB_VALUE_IN_USD
    FROM 
        {{ source('source_world_trade', 'original_comtrade_coffee_2020_monthly') }}
    WHERE ReporterISO = 'BRA'
    AND PartnerISO != 'W00' -- World
    AND CustomsDesc = 'TOTAL CPC' -- Total Customs Procedure Code
    AND MotDesc = 'TOTAL MOT' -- Total Modes of Transport
)

SELECT
    *,
    FOB_VALUE_IN_USD / NET_WEIGHT AS FOB_PER_KG_AVERAGE
FROM source_data