Sample Data: OpenWeatherMap

OpenWeatherMap is a repository of recent historical and forecasted weather data in JSON format. Snowflake imports this weather data and makes it available to all Snowflake accounts free of charge so you can experiment with our unique, high-performance semi-structured columnar functionality using real-world data.


The sample weather data is provided for evaluation and testing purposes. The data is updated regularly in Snowflake, but is not maintained in real-time, which may result in occasional lapses in updates (i.e. we do not guarantee that the data is always current and/or gap-free).

As such, we do not recommend using the data in production systems.

In this Topic:


The data set includes the following tables, all stored in native JSON format and accumulated over time:

Table Name Description JSON Description
DAILY_14_TOTAL 12 days of daily weather forecasts for 20,000+ cities. Click here
DAILY_16_TOTAL 12 days of daily weather forecasts for 200,000+ cities (lower frequency of updates). Click here
HOURLY_14_TOTAL 4 days of hourly weather forecasts for 20,000+ cities. Click here
HOURLY_16_TOTAL 4 days of hourly weather forecasts for 200,000+ cities (lower frequency of updates). Click here
WEATHER_14_TOTAL Recent weather for 20,000 cities. Click here

Query Examples

The following query retrieves the recent high and low temperature readings for New York City, converted from celsius to fahrenheit temperatures, along with the latitude and longitude for the readings:

select (V:main.temp_max - 273.15) * 1.8000 + 32.00 as temp_max_far,
       (V:main.temp_min - 273.15) * 1.8000 + 32.00 as temp_min_far,
       cast(V:time as TIMESTAMP) time, lat,
       V:city.coord.lon lon,
where = 'New York'
and = 'US'
order by time desc
limit 10;

The following query compares weather forecasts to actual weather readings:

forecast as
(select ow.V:time         as prediction_dt,    as city, as country,
        cast(f.value:dt   as timestamp) as forecast_dt,
        f.value:temp.max  as forecast_max_k,
        f.value:temp.min  as forecast_min_k,
        f.value           as forecast
 from ow, lateral FLATTEN(input => V, path => 'data') f),

actual as
(select V:main.temp_max as temp_max_k,
        V:main.temp_min as temp_min_k,
        cast(V:time as timestamp)     as time_dt,     as city,  as country

select cast(forecast.prediction_dt as timestamp) prediction_dt,
from actual
left join forecast on = and
             = and
                      date_trunc(day, actual.time_dt) = date_trunc(day, forecast.forecast_dt)
where = 'New York'
and = 'US'
order by forecast_dt desc, prediction_dt desc;