See what was not imported, and why

Every line of a publisher's file is kept in open_ef.source_row, imported or not, with its ingest_status and a skip_reason. Group on the reason to see what a library left out: of DESNZ's 2026 file, 1,789 lines were imported and 2,504 mile-based lines were skipped as twins of their kilometre versions.

A factor can be missing for two different reasons, and each has its own query. The line was never imported, or the value was imported but withheld from factors_flat.

Lines skipped at import#

SELECT sr.ingest_status,
       split_part(sr.skip_reason, ':', 1) AS reason,
       count(*) AS source_lines,
       (array_agg(concat_ws(' / ', sr.raw ->> 'l3', sr.raw ->> 'l4', sr.raw ->> 'uom')
                  ORDER BY sr.row_index))[1] AS first_example
FROM open_ef.source_row sr
JOIN open_ef.release r ON r.id = sr.release_id AND r.is_latest
JOIN open_ef.publisher p ON p.id = r.publisher_id
WHERE p.code = 'desnz'
GROUP BY 1, 2
ORDER BY source_lines DESC;

skip_reason holds a code, often followed by a colon and a sentence; split_part keeps the code either way, and a reason with no colon is the whole of what was recorded. The fields inside raw are the publisher's own columns (l3, l4 and uom are DESNZ's), so adapt the example column for another library.

Each skip is a decision you can check. A factor per mile is the per-kilometre factor converted, so you convert your distance instead. A blank line is not a zero factor. Biogenic CO2 lines are reported outside the GHG Protocol scopes, so they are not scope emission factors.

Values withheld from factors_flat#

A value can be imported and still not be served, when it is marked problematic: a number that would mislead if used as is. It stays readable on open_ef.emission_factor_value, with the reason.

SELECT p.code AS library,
       v.data_quality_reason_normalized,
       count(*) AS withheld_values
FROM open_ef.emission_factor_value v
JOIN open_ef.release r ON r.id = v.release_id AND r.is_latest
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')
  AND v.data_quality_normalized = 'problematic'
GROUP BY 1, 2
ORDER BY 1, 3 DESC;

EPA's 189, for example, are totals with no CO2 leg on a fuel that emits CO2: serving them would under-count. EXIOBASE's implausible printed zeros are zeros the source matrix itself prints, an intensity computed as exactly zero rather than one that was measured; a blank cell never carries this tag. A genuine zero, such as wind power at the point of generation, carries structural_zero instead and is served.

Watch out#

Watch out. An absence is an answer, not a gap to fill. A skipped line or a withheld value has no row in factors_flat, and a query on the view returns nothing for it, never a zero. Do not substitute zero, and do not fall back to the raw number without reading why it was set aside.

Watch out. A third kind of absence has no reason row. A spend factor with no price index for your year is not served for that year: see Match a spend factor to your spend year. And a factor the publisher never printed is not in source_row at all.

Why it works. source_row is written before any interpretation, one row per line of the file, and every row carries a status: ingested, a skipped status with its reason, or failed. The unique key on release_id, artifact and row_index means no line is stored twice.

Related#

• How factors are cleaned
• Data quality and the glossary's data quality
• Table reference: source_row
• What DESNZ left out, in words: import notes and limitations