Postgres Countries

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

Dbt path: trase_production.main_postgres_tables.postgres_countries

Explore on Metabase: Full table; summary statistics

Containing yaml file link: trase/data_pipeline/models/postgres_tables/_schema_postgres_tables.yml

Model file link: trase/data_pipeline/models/postgres_tables/postgres_countries.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: postgres, regions, countries

---

postgres_countries

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.

---

Details

ColumnsDepends OnModel code

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

  Macros

  • macro.trase_duckdb.attach_postgres

  • macro.trase_duckdb.detach_postgres

Sources

• ['source_world_metadata', 'country_iso_codes']

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
        alpha_2,
        alpha_3,
        numeric_code::VARCHAR AS numeric_code,
        fao_code::INT as fao_code
    FROM
        {{ source('source_world_metadata', 'country_iso_codes')}}

)

SELECT 
    c.country_name,
    c.trase_id AS country_trase_id,
    ic.alpha_3 AS iso_alpha_3,
    ic.numeric_code AS iso_numeric_code,
    ic.fao_code AS fao_code,
    c.synonyms,
    eb.economic_bloc,
    c.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