Project overview
I built this project to keep the latest weather readings from 11 stations in one place. When the program finds a new file, it checks for repeated stations and loads the readings into a separate table before updating the main one. I wanted to see which problems the program would flag and whether the last good set of readings would stay available when a file had an issue.
How the pipeline works
-
Weather CSVNew station readings
-
Staging tableLoad and check new readings
-
Final tableReplace the previous readings
Three intentional failure tests
These deliberate tests check what appears in the load log and which readings reach the final table.
Omit a station row
Remove one row for a station that was present in the last good load.
Expected resultThe load log identifies the missing station by comparing the new batch with the previous readings.
Duplicate a station row
Add a second copy of a row with the same station ID.
Expected resultBash flags the repeated station ID in the load log and stops before any rows are imported.
Put text in a numeric field
Replace a temperature with
X.Expected resultThe load log shows a warning. MySQL can turn the invalid temperature into zero and finish the import, so this test also checks the final readings. MySQL reference
What if a station is missing?
Suppose the final table holds 11 readings. The new file has 10, and MySQL finishes the import successfully. The load log names the missing station.
Textbook
Checks the log and sees the missing station.
Keeps the previous 11 readings.
Project script
Checks whether MySQL finished successfully.
Can replace the previous 11 readings with the 10 new ones.
What the results show
The duplicate check flagged a repeated station in an archived CSV. A manual recovery with a clean file confirmed that staging and the final table held the same 11 readings.
The missing-station and invalid-temperature examples show expected behavior.
Sources
- Rick Silva, MySQL Crash Course, Chapter 16, “Building a Weather Database,” pp. 261–276. See exercise 16-1 on p. 275.