Built this with my son many years ago as a weekend project—he was learning SQL, we both love data, so we created our own personal weather data collector.

The PowerShell script pulls current hourly observations from National Weather Service stations (via their public API), parses the JSON, and inserts/stores the data in a SQL Server database.

GitHub repo contains:

  1. Full database creation scripts (tables, indexes, constraints)
  2. Initial data population scripts (sample stations, metadata)
  3. Main PowerShell collector script (with error handling, logging, scheduling-friendly)
  4. Power BI .pbix dashboard for visualization (current conditions, trends, maps, alerts)

Everything is open-source and ready to clone/run.

GitHub: https://github.com/thesqlgeek/WeatherData