ZEVA - upload older files from ICBC

[katerinkus]

Problem Description ICBC sent C&E team of updated monthly datasets from October 2021 to June 2023. These datasets contain updates to the ZEV flag column. Original datasets did not mark some of the ZEV car records in the ZEV flag column. As a result, the original datasets underestimated ZEV counts. The updated datasets correct this issue.

Solution Needs C&E team needs to upload the data but it is unclear how do so without affecting database structure. Do we need to add functionality to upload datasets by date? E.g. Select a date prior to upload sot that the original October 2023 dataset gets replaced with the updated October 2023 dataset. Or is there an easier way such as adding the data directly into the database (manually) instead of the ICBC parsing tool.

Timebox

• 1/2 day

Outcome Details describing the outcome of the research

• Was it successful? What direction should the work go?
• Was it unsuccessful? Discuss next steps with team

[rogerlcleung]

Based on the problem description, the new dataset should be uploaded with the normal procedure.

Before uploading into the ZEVA app, the dataset should be filtered such that it only consists of valid ZEV entries. Only 4 columns are needed in the "processed" CSV file: MODEL_YEAR, MAKE, MODEL, and VIN. If an entry has a VIN already exists in the system, it will either be ignored (if it is just a duplicate of the existing one) or updated (if there is any change in the entry). New VINs will be added to the database. Existing data in the database with no matching VINs will be untouched.

Therefore, if the ZEV flag column in the new dataset marked some ZEV records not being marked in the original dataset, those new ZEV records can be uploaded with the normal procedure. However, if the ZEV flag column in the new dataset unmarked some ZEV records already uploaded (which does not seem to be the case in the problem description), then those incorrect records would not be removed from the database.