Weather Source on the Snowflake Data Marketplace
Predicting the future starts with understanding the past and this is no different when working with weather data. Just think how much weather affects agriculture, construction, transportation, and even consumer behavior. Let’s focus on Weather Source, one of the premium weather data providers in the Data Marketplace.
You can use Snowflake to find correlations between your past data and historical weather data, and also to find a source of weather predictions into the future. I already shared how to access NOAA weather data for free in Snowflake — and today I’m going to focus on Weather Source, one of the premium weather data providers in the Data Marketplace.
With a premium global weather data provider like Weather Source, you can find richer and more localized data and with a deep historical database going back 20 years and into the future with a 42 day forecast of future predictions. Weather Source has all of the data you need to get started creating actionable business intelligence. They even have historical forecast data in a format that is perfect for back-testing.
To get you started, Weather Source publishes a free dataset covering 1,000 zip codes around the US, and 6 world major cities. This sample dataset includes past, present, forecast and climatology weather data in daily format.
For example, these are the average temperatures for each zip code — with some of the hottest in Arizona, Texas, and Florida:
Or we can look at the places with the most wind, and you can see there’s a lot of wind in the middle of the country — like in Oklahoma, Nebraska, and Kansas. Dorothy would know:
Another interesting finding is the correlation between solar radiation and cloud cover. On the bottom right, you can see the places with the most solar radiation and the least cloudy days, like some in California and Nevada.
And on the top left you can see the city that is covered most frequently with clouds, and gets the least amount of sunlight. I wonder why they called it “Grayland”:
All of this was the average numbers for 2 years of daily observations on the free dataset, but if you go premium you can go deeper, and get hour by hour observations.
As Weather Source says, they offer a rich, statistically consistent dataset with a deep historical database that is homogenized with the forecast and climatology data makes it easy for Snowflake users to perform regression analyses — to quantify the impact of weather using a historical time-series and then create a predictive models using the forecast data.
This will allow you to predict and make better decisions for your business, once you figure out the correlations between weather and the metrics you care about.
Naming each zip code
The sample dataset has 1,000 zip codes — but it doesn’t have a name for each. To solve this you can load GeoNames into Snowflake:
Then it’s easy to join this table with Weather Source’s data. For example, this query finds the averages plotted earlier and adds a name to each zip code:
select max(place||', '||admin_name)
, avg(avg_temperature_air_2m_f) temp
, avg(avg_radiation_solar_total_wpm2) solar_radiation
, avg(avg_cloud_cover_tot_pct) cloud_cover
, avg(avg_wind_speed_10m_mph) wind
, min(date_valid_std) since
, max(date_valid_std) until
where country = 'US'
group by 1
Predictions for each zip code
This query includes a bit to write “today + X”, so results show that these are predictions:
select postal_code zip
, 'today + ' || (date_valid_std - current_date()) date
, avg_temperature_air_2m_f temp, avg_pressure_2m_mb press
, avg_wind_speed_10m_mph wind, avg_cloud_cover_tot_pct clouds
, avg_radiation_solar_total_wpm2 solar
where postal_code = '94164'
order by date_valid_std;
Check out Weather Source in the Snowflake Data Marketplace — the free dataset is ready for you to query, and is updated daily.
- Try this out with a Snowflake free trial account — you only need an email address to get started.
I’m Felipe Hoffa, Data Cloud Advocate for Snowflake. Thanks for joining me on this adventure. You can follow me on Twitter and LinkedIn. Check reddit.com/r/snowflake for the most interesting Snowflake news.