Horizon Funding Data Warehouse

A dbt + DuckDB analytical warehouse over CORDIS Horizon Europe research-funding data. It transforms the EU's raw project and organisation CSV extracts into a tested, documented star schema with a set of analytical marts — funding by country, top recipient organisations, funding concentration (Pareto), and funding by project start year.

Everything runs locally: no warehouse account, no cloud credentials, no cost.

Key findings:

• Funding is heavily concentrated — the top 5 countries receive 54% of all funding (DE, FR, ES, IT, NL).
• Large national research centres receive most funding, participating in hundreds of projects (CENTRE NATIONAL DE LA RECHERCHE SCIENTIFIQUE from France is the leader, with 1,372 projects and more than €1 billion in EC funding).
• Projects attracting the most funds started in 2023 and 2024.
• The median of the number of partners in a project (2) sits well below the mean (6), the distribution is heavily right-skewed by lots of small 1–2 org projects (e.g. MSCA fellowships) alongside a few very large consortia.

---

Architecture

External CSVs → staging (stg_) → intermediate (int_) → marts (fct_ / dim_)

Layer	Prefix	Materialization	Purpose
Staging	stg_	view	Rename/cast raw columns; no business logic
Intermediate	int_	view	Joins, deduplication, derived fields
Marts	fct_, dim_	table	Star-schema outputs for analysis

Data flows one direction through progressively cleaner layers. Staging does nothing but rename and type raw columns, so every downstream model references clean names and correct types (never a raw CSV column). Intermediate views hold the joins and deduplication. Marts are materialized as tables — the star schema everyone queries.

Star schema

A central fact table surrounded by conformed dimensions, joined on integer-like keys:

• fct_funding — the fact table. One row per project × organisation × role funding relationship, carrying the ec_funding measure.
• dim_projects — one row per project (title, dates, status, budget flags).
• dim_organisations — one conformed row per organisation (name, country, type).

The star shape keeps the fact table narrow and fast while pushing descriptive attributes out to dimensions, so analytical questions become simple fact-to-dimension joins.

Built on top of the star are four analytical marts:

Mart	What it answers
fct_funding_by_country	Total EC funding per organisation's country
fct_top_recipient_organisations	Organisations ranked by total funding received, with project count
fct_funding_concentration_by_country	Each country's share + cumulative share of total funding (Pareto)
fct_funding_by_start_year	Total EC funding aggregated by project start year

DAG

---

Data Quality

The raw CORDIS extracts are somewhat messy. The transformations encode a number of deliberate data-quality decisions, each backed by a test:

• Associated-partner null rule. Associated partners and third parties participate in projects without a direct EC grant, so their ecContribution arrives empty. These are coerced to 0 in staging (coalesce(..., 0)) rather than left NULL, so ec_funding is always a real number and sums are never silently dropped.

• Reconciliation: 99.8%. Each project declares an EC grant ceiling (ecMaxContribution); independently, its organisations declare their individual contributions. For 99.78% of projects (22,478 of 22,528) these reconcile to within €20. The remaining 50 projects (0.22%) are flagged, not hidden — dim_projects carries budget_discrepancy_found and a budget_discrepancy amount for full transparency. A singular test (assert_budget_discrepancy_rate) fails the build if the discrepancy rate ever exceeds 1%.

• The EUROfusion mega-project. The single largest discrepancy (~€115M) is EUROfusion, the Euratom fusion-research consortium: its funding is channelled through a coordinating body rather than itemised against individual organisation rows, so the org-level sum legitimately falls short of the project ceiling. Kept in the data and flagged — an explainable outlier, not an error.

• Grain discovery: org × project × role. The natural assumption is one funding row per organisation × project. A unique test on that pair failed: ~234 pairs have an organisation holding two roles in the same project (e.g. participant and associated partner). The true grain is org × project × role, now enforced by the singular test assert_fct_funding_unique_org_project. The failing test is what surfaced the real grain.

• Rounding-drift analysis. Country- and year-level marts round each group's total to the whole euro. Summing those rounded groups back up drifts from the unrounded grand total by a few euros (+€7 across 152 countries, −€1 across 7 years) — expected per-group rounding noise on a €59.4B base, not a data bug. Aggregate before rounding when you need the exact grand total.

• project.csv preprocessing (auto-cleaned). The raw project.csv has a quoting edge case that DuckDB mis-parses (silently dropping most rows); a preprocessing step cleans it automatically on every build. See Automatic CSV preprocessing. Staging reads the cleaned copy.

---

Running the project

Setup

1. Install dbt with the DuckDB adapter:

   pip install dbt-duckdb

2. Get the raw data. The repo already ships with the 12 June 2026 CORDIS extract in data/raw/ (project.csv, organization.csv), so you can skip straight to building. To analyse a newer release instead, replace those two files — see Data Sources. (data/raw/project_clean.csv is generated automatically and is gitignored.)
3. Point dbt at the project-local profiles.yml (choose one):

   export DBT_PROFILES_DIR=$(pwd)     # or pass --profiles-dir . per command

Build & test

dbt build                     # run + test every model (preferred over dbt run)
dbt build --select staging    # build only one layer
dbt build --select marts
dbt test                      # run tests without rebuilding

cordis.duckdb is created in the project root on first run (gitignored). The raw project.csv is cleaned automatically at the start of the run (see below) — no manual preprocessing step.

Automatic CSV preprocessing

project.csv needs a Python cleaning pass before DuckDB can read it in full (a raw read silently drops all but ~23 rows). Python's csv module parses it correctly, so scripts/clean_project_csv.py rewrites a normalised copy at data/raw/project_clean.csv (fully quoted; short rows padded, over-long rows truncated to the 21-column schema). Staging reads the cleaned copy.

This step is automated so it can never go stale:

• scripts/clean_project_csv.py holds the cleaning logic and is runnable on its own (python scripts/clean_project_csv.py).

• dbt's own on-run-start hooks run SQL, so they can't call a Python script. Instead a small dbt-duckdb plugin (scripts/dbt_clean_plugin.py) does it: a plugin's initialize() runs when the DuckDB connection opens — before any model — which is the earliest hook available. It's registered in profiles.yml:

  module_paths: ["."]     # put the project root on sys.path so the plugin imports
  plugins:
    - module: scripts.dbt_clean_plugin

• The plugin regenerates data/raw/project_clean.csv only when project.csv is newer (an mtime check), so it's a cheap no-op on every run except right after you download a fresh extract. It also skips silently if project.csv isn't present yet.

Net effect: download a newer CORDIS extract into data/raw/, run dbt build, and the cleaned file is rebuilt and everything downstream refreshes — no extra commands.

Browse the docs

dbt generates a browsable catalog with model descriptions, column-level docs, tests, and an interactive DAG:

dbt docs generate
dbt docs serve                # opens the docs site in your browser

---

Tech Stack & Decisions

• dbt for the transformation layer — version-controlled SQL models, built-in testing, and auto-generated documentation and lineage.
• DuckDB (via dbt-duckdb) as the engine — an in-process analytical database that is local, free, and fast. No warehouse to provision, no credentials, no per-query cost; the whole warehouse is a single cordis.duckdb file.
• Native external sources over dbt-external-tables. Sources resolve straight to a DuckDB read_csv(...) via dbt-duckdb's meta.external_location, avoiding an extra package and a stage_external_sources step. Tradeoff: dbt source freshness is unavailable — acceptable for static extracts.
• Dependency-light testing. Where a check needed custom logic (grain uniqueness, the budget-discrepancy rate) singular tests are used — plain SQL files in tests/ — rather than pull in dbt_utils for one macro. The project ships with zero package dependencies.
• CSV cleaning wired into the build via a dbt-duckdb plugin rather than a manual step: see Automatic CSV preprocessing for the mechanism.

---

Data Sources

Raw data is from CORDIS, the EU's primary database for Horizon Europe R&D projects.

File	Contents
data/raw/project.csv	Project metadata: title, EC contribution, start/end dates, status
data/raw/organization.csv	Participating orgs: name, country, role, net EC contribution

Dataset version. This project was built against the 12 June 2026 CORDIS extract. To analyse a newer release, download the latest CSVs and drop project.csv and organization.csv into data/raw/ (replacing the existing files), then run dbt build — the automatic preprocessing hook re-cleans project.csv and everything downstream refreshes. Get the extracts from the EU Open Data Portal:

https://data.europa.eu/data/datasets/cordis-eu-research-projects-under-horizon-europe-2021-2027?locale=en