Schedule icon
Flow icon
Write icon
Get icon
If icon
Queries icon
Script icon
Process icon
Set icon
Switch icon
Log icon
SlackIncomingWebhook icon

Normalize Institution Affiliations to ROR IDs with a Parquet Cache and a Review Queue

Match messy affiliation strings to ROR IDs with the ROR v2 API, DuckDB and a Parquet cache, bucket them for review and re-query only new strings.

Categories
Data

Author lists, grant records and CRM accounts spell the same institution a dozen ways: Univ. of Oxford, 1 Dept of Chemistry, University of Oxford, UK, a PubMed string with an email tail, or two institutions in one cell. The Research Organization Registry (ROR) has an affiliation matcher that returns candidate organisations with a score and, when it is confident, one marked chosen. This flow wraps it for real lists: DuckDB cleans and deduplicates the strings, only strings the Parquet cache has not seen go to ROR, the decision is made after the lookup so thresholds can change without new calls, ambiguous strings land in a review queue with their top three candidates, and reviewer decisions come back as overrides. Only review items found in the current run are alerted.

How it works

  1. A monthly Schedule re-checks unresolved strings (ROR ships new organisations about once a month); a disabled Flow trigger can run it after the flow that exports your author or grant list, passing its affiliations_csv file output.
  2. inline_affiliations and inline_overrides (Storage Write) put the inline CSVs in internal storage; an uploaded affiliations_file takes precedence. previous_cache (KV Get) reads the cache URI and bootstrap (If) writes an empty cache on the first run.
  3. plan_lookups (DuckDB) removes footnote markers, email addresses and Electronic address: tails, collapses whitespace, splits cells on ; (ROR chooses at most one organisation per request) and builds a match key (lower case, accents removed, punctuation as spaces, non-Latin letters kept). Placeholders such as N/A or Self-employed matching skip_pattern are skipped. Override ROR ids are validated. The pending list holds keys and override ids not in the cache, plus unresolved keys older than recheck_after_days, capped by max_lookups.
  4. lookup_new (If) runs ror_lookup (Python, standard library) only when something is pending: one GET /v2/organizations?affiliation= per string and one GET /v2/organizations/{id} per new override id, paced, with retries on 429 and 5xx that honour Retry-After. If ROR keeps rate limiting, the rest stays pending instead of failing. It keeps the chosen pick (or the top candidate), display name, country, types, status, successor, runner-up score, ties at the top score and the top three candidates. merge_cache (DuckDB) writes the new Parquet cache; failed lookups are not cached. save_cache (KV Set) stores its URI.
  5. classify (DuckDB) buckets each part: override, then chosen on an active organisation, then a score-based accept (score at least auto_accept_score, alone at the top, ahead of the runner-up by min_margin) as MATCHED; a top score at or above review_score or a tie as NEEDS_REVIEW; otherwise NO_MATCH. ROR itself advises using chosen rather than the score, so the default auto_accept_score of 1.0 only accepts a lone perfect score; scores of 0.9 and above are often a different institution. It writes the enriched CSV and a review queue CSV.
  6. triage (Switch) routes on NEW_REVIEW_ITEMS (WARN log plus Slack), REVIEW_BACKLOG (info log) or the default (all resolved). warn_unknown_overrides names override ids that are malformed or unknown to ROR, and summary logs the counts.

What you get

  • An enriched CSV with every input column plus part number, cleaned query, bucket, reason, ROR id, canonical name, country, organisation types, status and successor, score, and whether the answer came from the cache.
  • A review queue CSV: one row per unresolved string with occurrences, reason (tied_top_score, unconfirmed_candidate, no_candidates, low_score) and the top three candidates, ready to copy into overrides_csv.
  • A Parquet cache shared by any list: re-runs and grown lists only query new strings, and changing thresholds re-buckets without API calls.
  • Alerts only for strings first seen this run, not the whole backlog every month.

Who it's for

  • Research offices and librarians reconciling publication or grant affiliations for reporting.
  • Data engineers enriching author, funder or CRM records with persistent organisation identifiers.
  • Scholarly publishing and repository teams adding ROR ids to metadata before deposit.

Why orchestrate this with Kestra

  • KV plus Parquet in internal storage make the cache stateful without a database.
  • DuckDB inputFiles and outputFiles keep cleaning, planning and bucketing as readable SQL.
  • If skips the API step when everything is cached; Switch separates new review work from backlog.
  • Schedule and Flow triggers run the same flow on a calendar or after an upstream export.

Prerequisites

  • Kestra 2.0 with the JDBC DuckDB, Python script and Slack plugins.
  • Python 3.9 or later on the worker; the script uses the standard library only. It runs with the Process task runner; switch to the Docker runner with a python:3.12-slim image if your workers run containers.
  • Outbound HTTPS to api.ror.org. No key is needed. ROR plans 2,000 requests per 5 minutes with a client id and 50 without; the flow paces at 1 request per second without one and 5 per second with one.

Secrets

  • SLACK_WEBHOOK_URL: incoming webhook for new review items (optional; without it notify_review fails with allowFailure and the run ends in WARNING).
  • ROR_CLIENT_ID: ROR API client id sent as the Client-Id header when use_ror_client_id is true (optional).

Quick start

  1. Import and Execute with the defaults: 14 rows, 15 parts after splitting, 11 unique strings, 2 placeholders skipped. The baseline sends 11 affiliation lookups and 1 override lookup and returns 9 MATCHED (8 chosen by ROR, 1 override), 3 NEEDS_REVIEW (a three-way tie at score 1.0, Harvard Medical School with Boston University as top candidate, Universität Heidelberg with SRH University as top candidate) and 1 NO_MATCH (Harvard).
  2. Execute again: 11 strings from the cache, 0 ROR calls, REVIEW_BACKLOG.
  3. Copy decisions from the review CSV into overrides_csv and add rows; only new strings and new override ids are queried.
  4. Change auto_accept_score or review_score to see the buckets move with no API calls.
  5. Add SLACK_WEBHOOK_URL, optionally ROR_CLIENT_ID, and leave the monthly schedule on.

How to extend

  • Enable the Flow trigger after an ORCID, Crossref or OpenAlex export and load the enriched CSV into your warehouse.
  • Keep overrides_csv in a namespace file so reviewers edit decisions without touching the flow.
  • ROR only returns active organisations from the matcher, but an override can point at an inactive one; use successor_ror_id to remap it to its successor.
  • Open a Jira or GitHub issue per new review item instead of, or in addition to, Slack.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.