Skip to content

DBT: Postgres Countries

File location: s3://trase-storage/postgres_views/postgres_countries.parquet

DBT model name: postgres_countries

Explore on Metabase: Full table; summary statistics

DBT details

Description

Queries the Postgres 'views.regions' table and brings the countries names, trase_id, the economic blocs they belong to (currently only using EU), and the last time it was refreshed within Postgres. It also joins this data with ISO codes from the repo 'lukes/ISO-3166-Countries-with-Regional-Codes' and FAO related codes from FAO Nomenclature of Countries and Territories for Statistical Use (NOCS) - https://www.fao.org/nocs/en

Details

Column Type Description
country_name VARCHAR Country name as stored in Postgres views.regions (level = 1), which tends to follow common short version of English usage but may have some variations.
country_trase_id VARCHAR Trase internal country identifier from views.regions, which is based on ISO Alpha-2, plus some additional codes for special regions and territories.
synonyms VARCHAR[] List or string of alternative names and spellings for the country from Postgres views.regions.
economic_bloc VARCHAR[] Economic bloc to which the country belongs (currently only EU), taken from Postgres views.economic_blocs for the given country_trase_id.
iso_alpha_3 VARCHAR ISO 3166-1 alpha-3 country or territory code (three-letter international standard, e.g. "AFG").
iso_numeric_code VARCHAR ISO 3166-1 numeric country or territory code (three-digit code, e.g. "004").
un_region VARCHAR UN Statistics Division (UNSD) macro-region name (e.g. "Asia"), as defined in the M49 standard. May be empty.
un_sub_region VARCHAR UNSD sub-region name (e.g. "Southern Asia"), as defined in the M49 standard. May be empty.
un_intermediate_region VARCHAR UNSD intermediate region name (e.g. "Caribbean"), as defined in the M49 standard. May be empty.
un_region_code VARCHAR UNSD numeric region code (M49) corresponding to the macro-region (e.g. "019"). May be empty.
un_sub_region_code VARCHAR UNSD numeric sub-region code (M49) corresponding to the sub-region (e.g. "034"). May be empty.
un_intermediate_region_code VARCHAR UNSD numeric intermediate region code (M49) corresponding to the intermediate region (e.g. "005"). May be empty.
fao_code BIGINT General FAO (FAOSTAT) geographic code.
fao_agrovoc BIGINT AGROVOC thesaurus concept code used by FAO’s Geopolitical Ontology to identify the country or territory.
fao_category VARCHAR Classification of the entity within FAO’s system, such as “FAO Member Nation”, “Territory”, “Associate Member”, or “Member Organization”.
fao_membership_date DATE Date on which the country became a member of FAO, where applicable. Not applicable for territories or non-member entities.
un_m49 VARCHAR UN Statistics Division (UNSD) M49 numerical code (“Standard Country or Area Codes for Statistical Use”) for the country or area.
undp VARCHAR UNDP (United Nations Development Programme) country/area code as used in UNDP datasets and classifications.
gaulcode BIGINT GAUL (Global Administrative Unit Layers) code representing the administrative unit used for spatial / geographic datasets.
pg_last_refresh_time TIMESTAMP WITH TIME ZONE Timestamp indicating the last time the underlying Postgres views.regions data for this country was refreshed.

Models / Seeds

  • source.trase_duckdb.source_world_metadata.country_iso_codes_source

  • model.trase_duckdb.fao_countries_and_territories_codes

    Macros

    • macro.trase_duckdb.attach_postgres

    • macro.trase_duckdb.detach_postgres

Sources

  • ['source_world_metadata', 'country_iso_codes_source']

No called script or script source not found.

-- postgres_countries
{{ 
    config(materialized='external') 
}}

WITH countries AS (
    SELECT
        name AS country_name,
        trase_id,
        synonyms,
        last_refresh_time,
    FROM 
        {{ this.identifier }}_postgres_db.views.regions
    WHERE regions.level = 1
),
economic_blocs AS (
    SELECT
        country_trase_id,
        economic_bloc
    FROM
        {{ this.identifier }}_postgres_db.views.economic_blocs
),
iso_codes AS (
    SELECT DISTINCT
        alpha_2,
        alpha_3,
        numeric_code,
        region,
        sub_region,
        intermediate_region,
        region_code,
        sub_region_code,
        intermediate_region_code
    FROM
        {{ source('source_world_metadata', 'country_iso_codes_source')}}
    WHERE alpha_2 IS NOT NULL
),
fao_codes AS (
    SELECT DISTINCT
        iso2,
        iso3,
        faostat AS fao_code,
        agrovoc AS fao_agrovoc,
        category AS fao_category,
        membership_date AS fao_membership_date,
        m49 AS un_m49,
        undp,
        gaulcode
    FROM
        {{ ref('fao_countries_and_territories_codes') }}
    WHERE iso3 IS NOT NULL
)

SELECT DISTINCT
    c.country_name,
    c.trase_id AS country_trase_id,
    c.synonyms,
    eb.economic_bloc,
    ic.alpha_3 AS iso_alpha_3,
    ic.numeric_code AS iso_numeric_code,
    ic.region AS un_region,
    ic.sub_region AS un_sub_region,
    ic.intermediate_region AS un_intermediate_region,
    ic.region_code AS un_region_code,
    ic.sub_region_code AS un_sub_region_code,
    ic.intermediate_region_code AS un_intermediate_region_code,
    fc.fao_code,
    fc.fao_agrovoc,
    fc.fao_category,
    fc.fao_membership_date,
    fc.un_m49,
    fc.undp,
    fc.gaulcode,
    c.last_refresh_time AS pg_last_refresh_time
FROM
    countries AS c
LEFT JOIN
    economic_blocs AS eb
ON
    c.trase_id = eb.country_trase_id
LEFT JOIN
    iso_codes AS ic
ON
    c.trase_id = ic.alpha_2
LEFT JOIN
    fao_codes AS fc
ON
    c.trase_id = fc.iso2