Guarantees you can rely on

The model makes ten promises, and your queries can be built on them: identifiers never break, numbers are never silently rescaled or dropped, and every row says where it came from and under which licence. Most promises below come with a query that checks them on the public libraries. The ready-to-adapt recipes that rely on them are in the guides.

1. A published slug always resolves#

A slug is minted once and never recomputed. When factors are merged, split or re-keyed, each old slug stays in emission_factor_alias pointing at the factor that absorbed it, and the API answers it with a 301.

SELECT a.slug AS old_slug, a.reason, ef.slug AS current_slug
FROM open_ef.emission_factor_alias a
JOIN open_ef.emission_factor ef ON ef.id = a.emission_factor_id
JOIN open_ef.publisher p ON p.id = ef.publisher_id
WHERE p.code = 'ademe'
  AND a.slug LIKE 'ef-ademe-butane_maritime_included-europe-kwh-well_to_%'
ORDER BY a.slug

The boundary code was renamed from well_to_wheel to well_to_use, and a second factor was merged in. Both old slugs still lead to the same factor. On the public libraries, ADEME holds 1,970 key_fix and 1,370 merged aliases, DESNZ 454 and 1, ÖKOBAUDAT 6 merged.

2. A source_key never changes#

source_key identifies one value. It is hashed once, when the value is first stored, and never recomputed. If the recipe that builds keys ever changes, new values get a new prefix (efv2-) and every existing key stays valid. Today every served value on the public libraries carries efv1-:

SELECT f.library, split_part(f.source_key, '-', 1) AS key_prefix, count(*) AS factors,
  count(*) FILTER (WHERE f.value_co2e_native IS NULL) AS null_headline
FROM open_ef.factors_flat f
WHERE f.library IN ('ademe','agribalyse','exiobase','desnz','miterd','aib','epa','useeio','openceda','okobaudat')
  AND f.is_default_indicator AND f.is_latest_activity_year AND f.is_top_parent
GROUP BY 1, 2
ORDER BY 1, 2

Store it next to any number you report: Pin a factor value to a release shows how.

3. Stored numbers were never silently rescaled#

Only definitional unit conversions are applied, and each one is recorded in unit_conversion_factor on emission_factor_value. Dividing by it gives back the published number, and the untouched line is in source_row.raw. How factors are cleaned lists every conversion in use.

One transformation is deliberate and visible: factors_flat serves a spend factor once per accounting year, re-priced to that year's money by price_index_ratio. The stored value is untouched; only the served row moves.

SELECT f.applies_from_year, f.currency_year,
  round(f.price_index_ratio::numeric, 5) AS price_index_ratio,
  round(f.value_co2e_native::numeric, 5) AS value_co2e_native
FROM open_ef.factors_flat f
WHERE f.library = 'exiobase'
  AND f.slug = 'ef-exiobase-animal_products_nec-fr-eur-cradle_to_gate-e2b36c2f'
  AND f.is_default_indicator
  AND f.applies_from_year IN (2015, 2019, 2025)
ORDER BY f.applies_from_year

Watch out. unit_conversion_factor is not served on factors_flat. It lives on emission_factor_value, and dividing that row's value_co2e_native by it gives back the published number. A served row carries the publisher's figure only where price_index_ratio is 1, which is the row whose applies_from_year is the currency_year. Read emission_factor_value when you audit against the source. Match a spend factor to your spend year explains the re-pricing.

4. The headline total is always present#

value_co2e_native is filled on every served value; the null_headline column in the query above is 0 for every library. You never sum gas columns to get it. Where no publisher printed a total, open_ef summed the factor's parts once, at load, and says so with co2e_origin = 'summed_from_children'.

The components behind it follow one rule: NULL means not reported separately, 0.0 means reported as zero. Gases, biogenic carbon and land use shows both on one row.

value_co2e_normalized is filled too, but it is only comparable across libraries where gwp_normalization_basis is not unconverted. An unconverted row repeats the native number under its native GWP report. Drop those rows from a cross-library total and say how many you dropped, as Add up factors across libraries does. GWP gives the reasons a row stays unconverted.

5. Sibling values are alternatives, never summands#

One factor can carry several values for the same year: a dual report (location-based and market-based), a different grid mix, a different GWP characterization. is_default_indicator marks one of them. Selecting one is always safe; adding siblings is always wrong, and no query in the model needs it.

SELECT f.grid_mix, f.calculation_approach, f.is_default_indicator, f.value_co2e_native, f.reference_year
FROM open_ef.factors_flat f
WHERE f.library = 'aib'
  AND f.slug = 'ef-aib-grid_electricity-fr-kwh-generation-86d757e7'
  AND f.is_latest_activity_year
ORDER BY f.is_default_indicator DESC, f.grid_mix

Four answers to "the French grid in 2025", one per accounting question. Parents and their parts are a different relation, with their own rules: Parents, children, double counting.

6. Nothing was silently dropped#

Every line of a publisher's file is kept in source_row, imported or not, with an ingest_status and, for every line not imported, a skip_reason.

SELECT p.code AS library, r.version, sr.ingest_status, count(*) AS source_lines,
  count(*) FILTER (WHERE sr.ingest_status <> 'ingested' AND sr.skip_reason IS NULL) AS without_reason
FROM open_ef.source_row sr
JOIN open_ef.release r ON r.id = sr.release_id
JOIN open_ef.publisher p ON p.id = r.publisher_id
WHERE p.code = 'ademe'
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3

Most of ADEME's skipped lines are archived vintages (6,938), rows that belong to the AGRIBALYSE library (2,917) and source-data rows (1,878). See what was not imported, and why groups them for any library.

7. Provenance is explicit#

Every field open_ef could have filled on the publisher's behalf carries an *_origin marker: region_origin, system_boundary_origin, gwp_origin, co2e_origin, and origin on each text and classification row. Filter on source when you need what the publisher stated. Provenance markers has the values and a per-library count.

8. Vocabularies are closed and documented#

Every boundary, region and unit code comes from a documented list; boundary and region codes are enforced by foreign keys. unknown is a code you can filter on, never a stand-in for a guess.

SELECT f.library,
  count(DISTINCT f.system_boundary) AS boundary_codes,
  count(*) FILTER (WHERE f.system_boundary = 'unknown') AS unknown_boundary,
  count(*) FILTER (WHERE sb.code IS NULL) AS outside_vocabulary
FROM open_ef.factors_flat f
LEFT JOIN open_ef.system_boundary sb ON sb.code = f.system_boundary
WHERE f.library IN ('ademe', 'desnz', 'okobaudat')
  AND f.is_default_indicator AND f.is_latest_activity_year AND f.is_top_parent
GROUP BY 1
ORDER BY 1

The lists themselves are in the reference and System boundaries.

9. Schema evolution is additive#

Columns, codes and tables may be added. An existing column keeps both its name and its meaning: a genuine change of meaning becomes a new column, and so does a better name, with the old column deprecated rather than redefined and left standing unchanged beside the new one. A query that is correct today keeps returning what it says, and keeps resolving the names it was written with. "Deprecated" here means exactly that: still served, still meaning what it meant, with a successor named in the changelog entry that records it.

Note. Additive does not mean frozen values. A new release can change a number, and a new code can appear in a closed list. Pin the source_key when you need a number to stay put, and handle codes you have not seen. A source_key pins the published value and not everything a served row shows: see what a source_key does not pin for the two figures computed on top of it.

10. Licence and attribution ride with every row#

Every served row carries license_tier, license_code and attribution_text. "What may leave my system" is a WHERE clause, not a hard-coded list of publishers. open and copyleft may be passed on; copyleft also carries a ShareAlike duty onto what you publish from it.

SELECT f.library, f.license_tier, f.license_code, count(*) AS factors,
  count(*) FILTER (WHERE f.attribution_text IS NULL) AS without_attribution
FROM open_ef.factors_flat f
WHERE f.library IN ('ademe','agribalyse','exiobase','desnz','miterd','aib','epa','useeio','openceda','okobaudat')
  AND f.is_default_indicator AND f.is_latest_activity_year AND f.is_top_parent
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3

How fresh is this#

This is not an eleventh promise. The ten above say what the model does, not when the copy you are reading was last written, and those are different questions. factors_flat is a view, and the numbers behind it are precomputed and stored: that store is rebuilt after each load, after a re-seed of the price and exchange-rate series, and after an in-place correction to a value already loaded. Nothing rebuilds continuously, so any copy of the data, a query result you cached included, is a point in time. Every served row dates itself with two columns.

updated_at is the wall clock of the last write to that row and last_ingest_version is the load-run counter that wrote it. Both are open_ef's own bookkeeping and neither is a publisher date: reference_year is the year the number describes, release.released_on is the day the publisher shipped the edition.

SELECT f.library,
       r.version AS edition,
       r.released_on AS published_on,
       max(f.updated_at)::date AS last_written,
       max(f.last_ingest_version) AS ingest_stamp
FROM open_ef.factors_flat f
JOIN open_ef.release r ON r.id = f.release_id
WHERE f.library IN ('desnz', 'exiobase', 'openceda')
  AND f.is_default_indicator
  AND f.is_latest_activity_year
GROUP BY 1, 2, 3
ORDER BY 1

The first three columns hold still; the last two are the row's own freshness answer and they move, so the values above are what they read on this page's verified date. A stamp that stays put across a reload is correct rather than suspicious: a row is re-stamped when it is inserted, when its content changes and when its serving flags flip. The load ledger behind the stamps is open_ef.ingest_run, one row per run with its status, finished_at and the ingest_version it stamped.

Watch out. WHERE last_ingest_version > :last_seen is a cheap "what changed since" for a mirror, and it has two blind spots. A pass that recomputes value_co2e_normalized from the GWP tables writes the measure columns and nothing else, and a re-seed of the price and exchange-rate series moves every served spend number with no load at all. Neither moves the stamp or updated_at, so diff value_co2e_normalized and price_index_ratio beside it. What a source_key does not pin has both cases.

Which edition you are on, and when it landed#

released_on above is the publisher's own date for the edition. When open_ef finished loading that edition is a separate date, and a library can carry more than one edition at once. The library pages and GET /factors/api/libraries show both, as released_on and loaded_at, with a status beside each edition.

SELECT p.code AS library,
       r.version AS edition,
       max(i.finished_at)::date AS loaded_on,
       CASE WHEN max(i.finished_at) IS NULL THEN 'announced'
            WHEN r.is_latest THEN 'served'
            ELSE 'superseded' END AS status
FROM open_ef.release r
JOIN open_ef.publisher p ON p.id = r.publisher_id
LEFT JOIN open_ef.ingest_run i
  ON i.status = 'succeeded'
 AND (i.release_id = r.id
      OR (i.release_id IS NULL
          AND i.publisher_code = p.code
          AND i.release_version = r.version))
WHERE p.code IN ('desnz', 'exiobase', 'openceda')
GROUP BY p.code, r.version, r.released_on, r.is_latest
ORDER BY p.code

loaded_on is the finish time of the last successful load of that edition, so it can sit months after the publisher's date and it moves when an edition is reloaded. status is derived rather than stored, from whether the edition ever loaded and whether it is the latest of its line, and it is the same word the library page prints: served is the edition the surface reads today, superseded is one that was loaded and then replaced by a newer edition, and announced is one registered ahead of its load, with no loaded_on yet and no rows in factors_flat. Each of the three editions above is served; pin against the edition label rather than the status, which moves the day a new one lands.

When two surfaces disagree#

The search page, the factor pages, the exports, the read API and the SQL model all read one data set, but each answer is a point in time and they are not written in the same instant. Three of the five hand you a stamp you can compare. A row in the SQL model carries last_ingest_version. An anonymous read-API response carries the highest ingest stamp in factor search inside its ETag, written as "v<stamp>-<digest of the query>", so on a query you do not change, a changed ETag means the data moved under you. A downloaded edition workbook prints that same stamp beside its build date, so a workbook and a query result at one stamp were written from one data set.

The search page and the factor pages do not. The search page prints no such date at all; a factor page prints one twice, under Dataset in the citation it offers under Cite this factor, and as the last verified clause of the line under its title. Both come from the site build rather than from the row on the screen, so that date dates the page and not the data, and comparing it against an ingest stamp tells you nothing. Reconcile those two on the edition and then the number: the edition label a factor page shows is the one in Which edition you are on. Different stamps mean age rather than a defect. The same edition at the same stamp answering two different numbers is worth reporting.

If you read the data through a copy rather than through the served database, the copy is refreshed on its own cadence and the stamp you get back is the copy's. Take the as-of date from the query above and state it in anything you publish from it. Your copy is a copy is the other half: how to tell how far behind a copy sits, and the two checks that look like a freshness check and are not.

Whether a newer edition exists#

How recently a library was loaded is not how recently the publisher published. A periodic sweep polls each publisher and records what it saw in open_ef.library_update_check, one row per artifact it looked at. A library is often polled on several, and they can disagree, so read a sweep the way the library page does: take the newest sweep whole, then fold it worst first.

WITH severity (outcome, rank) AS (
  VALUES ('update_available', 1), ('inconclusive', 2), ('unreachable', 3), ('current', 4)
), latest AS (
  SELECT DISTINCT ON (publisher_code) publisher_code, run_id, checked_at
  FROM open_ef.library_update_check
  WHERE publisher_code = 'desnz'
  ORDER BY publisher_code, checked_at DESC, run_id
)
SELECT l.publisher_code AS library,
       l.checked_at::date AS last_checked,
       c.probe,
       c.outcome AS probe_says,
       first_value(c.outcome) OVER (PARTITION BY l.publisher_code ORDER BY s.rank)
         AS library_shows
FROM latest l
JOIN open_ef.library_update_check c
  ON c.publisher_code = l.publisher_code AND c.run_id = l.run_id
JOIN severity s ON s.outcome = c.outcome
ORDER BY c.probe

checked_at is when the sweep last looked, run_id names the sweep, and probe names the artifact polled. DESNZ is polled on three, and on this sweep they disagree: the collection's year list still shows the edition that is loaded, while the publication's attachment list and its own last-modified date have moved on. detail carries the one-line note behind a verdict. Swap the code in the WHERE, or drop that line, to read another library or the whole sweep.

library_shows is the fold, and it is the word the library page prints: the most demanding outcome of the sweep, because "up to date" while one artifact of three has moved is wrong in the direction that matters. current means the publisher still shows what is loaded. update_available means something newer is out that has not been loaded yet: a caveat to carry, not a reason to distrust the served number, which is still the edition it says it is. unreachable and inconclusive mean the check itself did not settle, and they outrank current for the same reason.

Watch out. Take the whole sweep, not its first row. A DISTINCT ON (publisher_code) that stops at one row returns whichever probe the query plan handed it first, because a sweep writes every probe of a library at the same checked_at. On DESNZ today that coin flip prints either current or update_available, and only one of them agrees with the library page.

Pinning is a different control from freshness. A pin fixes which number you get back and says nothing about whether a newer one exists; the sweep says whether a newer one exists and does not change what you are served. Pin a factor value to a release covers the first, including what a source_key does not pin.

Recipes built on these guarantees#

Next#

• Parents, children, double counting
• Choosing a value: the axes
• Identifiers and stability