Metabase - Implement last report logic into Exclusion (Allocation) Agreement query

[justin-lepitzki]

Describe the task Implement the latest report logic that is used in the 'Fuel Supply Data' model in Metabase into the 'Exclusion (Allocation) Agreement' SQL query. That is, when more than one exclusion (allocation) report exists for an organization for a particular compliance period, only show the latest report. The latest report is the most recent report, either the original (if no supplemental reports exist) or the latest supplemental (if one or more supplemental reports exist). Note that we also want to exclude reports with the following states/statuses: draft, deleted, rejected.

This "latest report" logic was implemented in the 'Fuel Supply Data' model that exists in Metabase. We want to mirror this logic in the 'Exclusion (Allocation) Agreement' SQL query (and then turn it into a model).

Purpose The latest report logic is used to filter out duplicate / multiple reports for an organization within a specific compliance period. Without this logic, the query duplicates reported information by showing all of the reports from an organization for a specific compliance period: original report plus any supplemental reports. This leads to inaccurate data analyses because duplicate entries mean incorrect volumes and transactions.

Acceptance Criteria

• [ ] first
• [ ] second
• [ ] third

Additional context

• Metabase link: https://tfrs-metabase-prod.apps.silver.devops.gov.bc.ca/
• 'Exclusion (Allocation) Agreements' SQL query: https://tfrs-metabase-prod.apps.silver.devops.gov.bc.ca/question/274-exclusion-allocation-agreements
• 'Fuel Supply Data' model: https://tfrs-metabase-prod.apps.silver.devops.gov.bc.ca/model/192-fuel-supply-data

[justin-lepitzki]

Adding link to query for review: https://tfrs-metabase-prod.apps.silver.devops.gov.bc.ca/question/398-exclusion-allocation-agreements-latest-reports

[justin-lepitzki]

Original query for reference: https://tfrs-metabase-prod.apps.silver.devops.gov.bc.ca/question/274-exclusion-allocation-agreements

[Grulin]

Reviewed new query to see why supplemental reports were duplicating and if they were all being duplicated. Can confirm the following:

1) All exclusion reports that have one supplemental report are pulling a duplicate entry (of the final report submitted for both). 2) All exclusion reports that have multiple supplemental reports are pulling the final report the number of times a supplemental report was submitted for that organization/year. For example, if a company submitted one original and 2 supplemental reports, the query is showing 3 identical lines of the data from the final submitted report. 3) All exclusion reports that only have the original submission (no supplemental reports submitted) are pulling only one line of data.

I have a highlighted spreadsheet to illustrate.

[AlexZorkin]

I introduced logic to retrieve only the most recent report in the sequence by selecting the one with the highest traversal number.

[Grulin]

Skimmed new table to see if it matches exclusion reports listed in TFRS. I collapsed all these down to one report per year per company (as they report all these sold and purchased under one exclusion agreement id). Checked 2019 and noticed 4 reports not listed on the Metabase table, but they are listed in TFRS. I searched for a pattern of why these reports might have been excluded from the pull, but can't see anything obvious.

Have now identified all missing reports and cannot see any obvious pattern for why these reports were excluded. Will email @AlexZorkin a spreadsheet showing exclusion reports that are missing from the query.

[AlexZorkin]

I fixed a race condition between report types that was causing some exclusion reports to be excluded from the results. The query in metabase has been updated so please let me know if the results are showing all the expected reports now.

[Grulin]

@AlexZorkin I can confirm that this Metabase query has accurately pulled all exclusion reports (72 in TFRS, 72 in Metabase). I spot checked a few reports to see that accurate transactions were reported and it looked good. Thank you for fixing this!

My only other question is can we include the status of the reports in this? I know of at least one that was rescinded and many of them only just submitted, not accepted yet. @justin-lepitzki do you think we should include this?

[justin-lepitzki]

@Grulin that's a great idea, to show a similar "current status" column as in the Fuel Supply model. I think we should create a new card for that work. I could even take a look and see if I can figure out how to add it. We should create the card regardless, to track this work.