Pin a factor value to a release

Store the factor's slug, the value's source_key, the release version and the reference_year at the moment you use a number. Read it back later by source_key from open_ef.emission_factor_value, which works whichever release is current. Today each of the public libraries has one release, so the examples here have no older release to read back yet; other libraries in factor search already carry a superseded one. Pin the current release either way, so your numbers stay reproducible when the next one lands.

Which releases you can pin#

SELECT p.code AS library,
       r.version,
       r.released_on,
       r.is_latest,
       count(*) OVER (PARTITION BY p.code) AS releases
FROM open_ef.release r
JOIN open_ef.publisher p ON p.id = r.publisher_id
WHERE p.code IN ('ademe', 'agribalyse', 'exiobase', 'desnz', 'miterd', 'aib', 'epa', 'useeio', 'openceda', 'okobaudat')
ORDER BY p.code, r.released_on;

factors_flat serves only releases with is_latest. When a library publishes a new edition, the view moves to it, and the earlier values stay in open_ef.emission_factor_value.

Read a pinned value back#

The pin below is DESNZ's butane combustion factor as used from the 2026 release. The query reads the pinned value from the base table and puts the currently served value beside it.

WITH pin (slug, source_key) AS (
  VALUES ('ef-desnz-gaseous_fuels_butane-gb-l-combustion-d74d3fe6', 'efv1-f0d3b2528b05f5f6de25891ce4df3e30')
)
SELECT pin.slug,
       r.version AS pinned_release,
       r.is_latest AS release_is_latest,
       v.reference_year,
       v.value_co2e_native AS pinned_value,
       f.source_key AS current_source_key,
       f.value_co2e_native AS current_value,
       f.source_key IS NOT DISTINCT FROM pin.source_key AS unchanged
FROM pin
JOIN open_ef.emission_factor_value v ON v.source_key = pin.source_key
JOIN open_ef.release r ON r.id = v.release_id
LEFT JOIN open_ef.factors_flat f
  ON f.slug = pin.slug AND f.is_default_indicator AND f.is_latest_activity_year;

Today unchanged is true. After a new DESNZ release, current_source_key moves while the slug stays: that is the signal that the served number changed. The pinned row still answers with 1.74533.

What a source_key does not pin#

A source_key pins one published value in one edition, not the number itself: re-ingesting the same edition rewrites the row in place when the publisher's record changes, and a derived parent is re-folded when one of its children moves. Store the number beside the key and compare content_hash as well, which is what an edit in place changes. Two of the things factors_flat serves are computed on top of that number, and neither is inside the key.

The adjusted number on a spend factor. A monetary value is served multiplied by price_index_ratio, computed when the view is built from two vendored World Bank series, open_ef.price_index and open_ef.fx_annual. Each series carries a snapshot_version, and that snapshot is deliberately not part of the value's identity: when a series is re-seeded the same value_key can answer a slightly different adjusted number, with no new edition, no new source_key and no new slug. The ratio carried on the row is the disclosure, so store it with the number you used and the published value stays recoverable whatever the series does.

SELECT f.value_key,
       round(f.price_index_ratio::numeric, 4) AS price_index_ratio,
       pi.snapshot_version AS price_index_snapshot,
       fx.snapshot_version AS fx_snapshot
FROM open_ef.factors_flat f
JOIN open_ef.price_index pi
  ON pi.economy = f.deflation_economy
 AND pi.index_type = 'gdp_deflator_usd'
 AND pi.year = f.applies_from_year
JOIN open_ef.fx_annual fx
  ON fx.currency_code = f.unit_code
 AND fx.year = f.applies_from_year
WHERE f.library = 'exiobase'
  AND f.slug = 'ef-exiobase-hotel_and_restaurant-be-eur-cradle_to_gate-d6613af4'
  AND f.is_default_indicator
  AND f.applies_from_year = 2024

Neither snapshot is part of value_key, so the same key can answer a different adjusted number after a re-seed. Store price_index_ratio beside the number you used: dividing the served value by it gives back the publisher's own figure, which does not move. Where the index comes from shows how the ratio is built and how to recompute it.

A normalised value. value_co2e_normalized, and the normalised gas legs beside it, are recomputed from the GWP tables by a pass over values that are already loaded, not only at ingest. A change to those tables moves them with no re-ingest and no release, so no key moves either. value_co2e_native with gwp_method_native is the publisher's own report on the publisher's own GWP set, and does not.

So for a mirror, source_key is the right change detector for a published value and not a sufficient one for a served row. Carry price_index_ratio beside every spend row and value_co2e_normalized beside every row you compare across libraries, and diff those as well as the key. Neither price_index_ratio nor value_key can be asked for as an export column: an export names fields of the served row and refuses anything outside that set, so a reader who stores those two takes them from a query rather than from a file (Keeping an export checkable). When your copy was last written, and which edition it came from, are separate questions with separate answers: How fresh is this and Which edition you are on.

Watch out#

Watch out. Do not pin on slug alone. The slug names the factor and survives every release; source_key names the value, and the one served for a slug changes with a new release, a newly elected default or a corrected figure. Store both and compare the second.

Watch out. A spend factor is served once per activity year, so its pin is value_key (the source_key, an underscore and the year), read from factors_flat while its release is current. The base table holds only the published value: after the release changes, you get the number before the price-level adjustment, and you apply price_index_ratio from your own record. See Match a spend factor to your spend year.

Watch out. The base table is not filtered like the view. It has no licence columns, so take license_code and attribution_text from open_ef.release, and it keeps values marked problematic, so check data_quality_normalized before you use a number you did not pin yourself.

Why it works. source_key is unique in emission_factor_value and is computed from the publisher, the release and the value's own identity. The number itself is not part of the key, so a correction re-ingested into the same release rewrites the row under the key it already has: store the value you used beside the key, not only the key. The release is part of the key, which is why a new edition always shows up as a new source_key. What the view computes on top of that number is a separate matter: What a source_key does not pin.

Related#

• Identifiers and stability
• Trace a served value back to its source line
• Library versions and the changelog
• Table reference: emission_factor_value and release