Process

Data Retrieval

IATI Tables retrieves data from the Code for IATI Data Dump once a day. All data is replaced each time the process runs, so updates and removals are respected.

You can use the metadata table to find the cut-off time for the data which is included. The column iati_tables_updated_at shows the time at which the tables process finished. The column data_dump_updated_at shows the time of the Data Dump that was used by the latest run. IATI data published or edited after this time will not be included in IATI Tables until the next run.

Flattening

Top-level elements

There are two top-level tables in IATI Tables:

1. The activity table is generated from Activity files, and contains rows of iati-activities/iati-activity elements.

2. The organisation table is generated from Organisation files, and contains rows of iati-organisations/iati-organisation elements.

All other tables are children of these top-level tables. Tables prefixed with organisation_ contain child elements of the iati-organisations/iati-organisation element, and can be joined back to the organisation table using the column _link_organisation. The remaining tables contain child elements of the iati-activities/iati-activity element, and can be joined back to the activity table using the column _link_activity.

Note

IATI Tables doesn’t perform deduplication. If an activity appears multiple times in published data, it will appear multiple times in IATI tables.

Singular child elements

Child elements which can appear zero or one times become columns in the parent table.

For example, the iati-activities/iati-activity/iati-identifier element becomes the column iati-identifier in the activity table.

Repeatable child elements

Child elements which can appear more than once are unnested into a new table.

For example, the iati-activities/iati-activity/transaction element becomes the table transaction.

Narrative elements

Elements which contain a narrative element are flattened into a single string column in the parent table.

For example, given the following iati-activities/iati-activity/title element:

<title>
  <narrative>Activity title</narrative>
  <narrative xml:lang="fr">Titre de l'activité</narrative>
  <narrative xml:lang="es">Título de la actividad</narrative>
</title>

This element will be transformed into the string: Activity title, FR: Titre de l'activité, ES: Título de la actividad, and stored in the column title_narrative in the activity table.

Common columns

The following columns are present in all tables:

_link

The primary key for each table.

_link_activity or _link_organisation

The foreign key to the activity or organisation table respectively.

dataset

The name of the dataset this row came from. This can be used to find the dataset in the IATI registry, using the URL: https://www.iatiregistry.org/dataset/<DATASET_NAME>.

prefix

The registry publisher ID this row came from. This can be used to find the dataset in the IATI registry, using the URL: https://www.iatiregistry.org/publisher/<PREFIX>.

Codelists

Codelists are joined to the tables as part of the process.

For example, given the following iati-activities/iati-activity/activity-status element, whose attribute @code uses the ActivityStatus codelist:

<activity-status code="2" />

This element will be transformed into two columns in the activity table:

• The column activitystatus_code with the value 2.

• The column activitystatus_codename with the value Implementation.

Currency Conversion

IATI Tables converts the iati-activities/iati-activity/transaction/value element to USD.

This is calculated by pulling the Code for IATI IMF exchange rates dataset, and using the transaction’s @value-date attribute to determine the exchange rate to use.

The result is stored in the transaction (trans) table in a new column value_usd.

Transaction Splitting

IATI Tables transforms the transaction (trans) table to proportionally split the transaction values across multiple sectors or countries/regions.

It follows the same methodology set out in the CDFD documentation.