[CIIP 2023] Measured Emission Factor table does not parse records beyond 2017 [7]

[dleard]

Probability: 5 - It will never parse data beyond 2017 Effect: 2

There was a change to the SWRS XML schema after 2017 that is causing our Measured Emission Factor table to data after 2017. We need to update our xpath logic to include this data.

It was noted in the metabase channel that the priority on this issue is "before the next reporting cycle".

Old schema: used a MeasuredEmissionFactor tag, our xpath currently looks for this tag in order to parse the data.

New Schema: MeasuredEmissionFactor has been replaced with EmissionFactor and a new EmissionFactorDefaultOrMeasured has been added under EmissionFactor.

~There a couple possible fixes here::~

~1) Keep the Measured Emission Factor table as is and adapt the transform materialized view to parse the data from newer reports (including the new tag so that results can be filtered & coalescing that value to 'measured' so that older reports under the old tag have a value). The benefit here being that any metabase questions currently using that table will be unaffected. Downside is that the table name isn't totally accurate since we're including emission factors that aren't necessarily 'measured'~

~2) Deprecate the Measured Emission Factor table in favor of an Emission Factor table. The solution here is the same as above as far as the code written, except we'd create a new table. This would make the table name align better with what it contains, but break any metabase questions currently using the Measured Emission Factor table.~

~To decide on a fix, it would be good to look at what effect dropping the Measured Emission Factor table would have on metabase. (drop the table in test and run the broken questions script).~

Update No questions in metabase rely on the Measured Emission Factor table. We can drop this table in favour of a new Emission Factor table.

Implementation: We will keep the Measured Emission Factor materialized view to parse the old data, but remove the Measured Emission Factor table.

• [x] Add an emission_factor materialized view. It should be similar to the measured_emission_factor materialized view, but updated with new xpaths to retrieve the data from the new tags
• [x] Do not include unit_name or sub_unit_name in the new materialized view
• [x] Remove unit_name and sub_unit_name from the measured_emission_factormaterialized view
• [x] Add an emission_factor table. It should have the same data as measured_emission_factor with a few differences:
  • no unit_name or sub_unit_name columns
  • has an EmissionFactorType column -> this column is derived from the metadata on the EmissionFactors xml tag
  • has a DefaultOrMeasured column -> this column is derived from the data in the EmissionFactorDefaultOrMeasured tag
• [x] Add a load_emission_factor() function that inserts data from the old measured_emission_factor materialized view and then data from the newly created emission_factor materialized view
• [x] update the load() function to replace measured_emission_factor in the mv_array with emission_factor
• [x] remove the load_measured_emission_factor function
• [x] remove the measured_emission_factor table
• [ ] add tests that ensure the proper data, both pre and post 2017 is being parsed correctly

[Dianadec]

Added high priority label based on Dylan's feedback that this should be done before next ciip cycle.

[pbastia]

@BCerki @dleard is there an easy way of testing that in -dev, without access to the prod data?

[dleard]

you can't really test this any better in dev than what is written in the pgtap test (demo fixture).

[pbastia]

Thank you! I'll mark it as approved then :) Thanks for the great work @BCerki and @dleard