Skip to content

DBT: Main Economic Blocs

File location: s3://trase-storage/world/metadata/economic_blocs/main_economic_blocs.parquet

DBT model name: main_economic_blocs

Explore on Metabase: Full table; summary statistics

Explore dependencies/lineage: link

Description

Membership of countries in the main economic blocs and regional trade agreements Trase tracks: AfCFTA, ASEAN, EU, GCC, MERCOSUR, NAFTA and USMCA. One row per country per bloc, with the date the country joined and, where applicable, the date it left.

A country may appear more than once when one bloc succeeds another — the United States is NAFTA up to 2019 and USMCA from 2020 — but never twice for the same year. The rounded year ranges never overlap, so a lookup by country and year returns exactly one bloc, which is what keeps downstream joins and aggregations unambiguous.

Each bloc has a Trase ID (economic_bloc_trase_id, e.g. EU) and a name (economic_bloc_name, e.g. "European Union"). Names are in normal capitalisation, with accents (e.g. "Côte d'Ivoire"); only the ISO2 codes and the bloc Trase IDs are uppercase. Uppercased and with accents removed, a country name is the one postgres_countries holds for the same ISO2 code.

rounded_start_year and rounded_end_year turn the join and leave dates into whole years, so that year-level models can join on them directly.

Cutoff Rule
rounded_start_year August Joining Jan–Jul counts from that year; Aug–Dec counts from the next
rounded_end_year July Leaving Jan–Jun counts as ending the previous year; Jul–Dec counts as ending that year

rounded_end_year is NULL for current members, so year comparisons need COALESCE(rounded_end_year, 9999).

The two cutoffs are deliberately a month apart. When one bloc replaces another the outgoing end date is the day before the incoming start date, so its month is one lower; the offset makes the two ranges meet exactly, with no year belonging to both blocs and none belonging to neither. NAFTA ending 2020-06-30 and USMCA starting 2020-07-01 therefore give 2019 and 2020. This holds for a handover in any month.

The source of truth is the CSV seed trase/data_pipeline/seeds/world/metadata/economic_blocs/main_economic_blocs_curated.csv. Changes to the blocs, their members or their dates are made directly in that file, so they arrive as a reviewable diff with a history behind it.

To publish a change:

  1. Edit the CSV and commit it.
  2. Run, in a single invocation so the seed is still there when the model reads it:
trase/data_pipeline/dbt build --indirect-selection cautious \
    -s main_economic_blocs_curated main_economic_blocs

cautious keeps out the tests of downstream models that also read this dataset (for example the bloc membership checks on supply chains). It also leaves out the country-name check against postgres_countries, which can be run on its own: trase/data_pipeline/dbt test -s main_economic_blocs_country_names_match_postgres_countries.

The rounded year columns are computed by the model, so the seed only ever carries what a person curates.

Details

Column Type Description
economic_bloc_trase_id VARCHAR Trase ID of the economic bloc the country belongs to (e.g. EU).
economic_bloc_name VARCHAR Full name of the economic bloc, prefixed by its acronym where the acronym is not part of the name (e.g. "(GCC) Gulf Cooperation Council").
country_iso2 VARCHAR ISO 3166-1 alpha-2 code of the member country, matching country_trase_id in postgres_countries. A country appears once per bloc it has belonged to, and its year ranges do not overlap — see the no_overlapping_year_ranges test above.
country_name VARCHAR Country name in normal capitalisation, with accents (e.g. "Côte d'Ivoire"). Uppercased and with accents removed, it matches postgres_countries.country_name.
start_date TIMESTAMP Date the country joined the bloc (accession / entry into force).
end_date TIMESTAMP Date the country left the bloc. NULL for current members.
rounded_start_year INTEGER start_date as a whole year, cutoff August. See the model description for the rule and why the two cutoffs differ.
rounded_end_year INTEGER end_date as a whole year, cutoff July. NULL for current members, so year comparisons need COALESCE(rounded_end_year, 9999).
membership_observation VARCHAR Free-text note qualifying the membership (e.g. signed but not ratified, former member). NULL where there is nothing to qualify.

Review full report including sample errors records if they exist (link)

Test column Test name Failing rows Last test run Last status Query in Metabase
Whole table test main_economic_blocs_country_names_match_postgres_countries — 2026-10-19 06:21 pass 🔍 run query
Whole table test main_economic_blocs_one_bloc_per_country_per_year — 2026-10-19 06:21 pass 🔍 run query
Whole table test main_economic_blocs_unique_country_and_bloc — 2026-10-19 06:21 pass 🔍 run query
country_iso2 not_null_main_economic_blocs_country_iso2 — 2026-10-19 06:23 pass 🔍 run query
country_name not_null_main_economic_blocs_country_name — 2026-10-19 06:23 pass 🔍 run query
economic_bloc_name not_null_main_economic_blocs_economic_bloc_name — 2026-10-19 06:23 pass 🔍 run query
economic_bloc_trase_id accepted_values_main_economic_blocs_economic_bloc_trase_id__AFCFTA__ASEAN__EU__GCC__MERCOSUR__NAFTA__USMCA — 2026-10-19 06:13 pass 🔍 run query
economic_bloc_trase_id not_null_main_economic_blocs_economic_bloc_trase_id — 2026-10-19 06:23 pass 🔍 run query
rounded_start_year not_null_main_economic_blocs_rounded_start_year — 2026-10-19 06:23 pass 🔍 run query
start_date not_null_main_economic_blocs_start_date — 2026-10-19 06:23 pass 🔍 run query

Models

Models

  • main_economic_blocs_curated

No called script or script source not found.

"""Add the whole-year columns to the curated economic blocs CSV.

The rounding rule lives here rather than in whatever query produced the file, so it is
versioned with the repo. See README.md in this folder for the cutoffs and why the two
are a month apart.
"""

import polars as pl

# Joining in Aug-Dec counts from the following year: less than half the year as a
# member. Leaving in Jan-Jun counts as ending the previous year.
#
# The two cutoffs are deliberately a month apart. On a handover the outgoing end date
# is the day before the incoming start date, so its month is one lower; the offset
# makes the ranges meet exactly, with no year falling to both blocs and none to
# neither.
START_AFTER_MONTH = 7
END_BEFORE_MONTH = 7

# The order the published parquet carries: the rounded years sit next to the dates
# they are derived from.
COLUMNS = [
    "economic_bloc_trase_id",
    "economic_bloc_name",
    "country_iso2",
    "country_name",
    "start_date",
    "end_date",
    "rounded_start_year",
    "rounded_end_year",
    "membership_observation",
]


def add_rounded_years(curated: pl.DataFrame) -> pl.DataFrame:
    """Drop any blank rows in the CSV and add the two whole-year columns.

    A NULL end_date (a current member) rounds to NULL, which is what downstream year
    comparisons expect — they coalesce it to 9999.
    """
    return (
        curated.filter(pl.col("economic_bloc_trase_id").is_not_null())
        .with_columns(
            pl.when(pl.col("start_date").dt.month() > START_AFTER_MONTH)
            .then(pl.col("start_date").dt.year() + 1)
            .otherwise(pl.col("start_date").dt.year())
            .cast(pl.Int32)
            .alias("rounded_start_year"),
            pl.when(pl.col("end_date").dt.month() < END_BEFORE_MONTH)
            .then(pl.col("end_date").dt.year() - 1)
            .otherwise(pl.col("end_date").dt.year())
            .cast(pl.Int32)
            .alias("rounded_end_year"),
        )
        .select(COLUMNS)
    )


def model(dbt, session):
    dbt.config(materialized="external")

    # The CSV a person maintains, committed as a seed. It is the source of truth.
    curated = pl.from_pandas(dbt.ref("main_economic_blocs_curated").df())

    return add_rounded_years(curated)