I currently capture well structured forecast data in a SQL Server. An example of this data is below:
What this tells me is that each day, I receive a forecast for the next three days. Note that in the real world, I may receive a forecast each hour (DateOfForecast) for the next 48 hours ('DateInQuestion') at 15 minute granularity.
How do I use this data? There are several use-cases, all with subtle but important differences. I'll go through them below.
Best (Latest) Data
If I want best data, this is the most recent forecast for each 'DateInQuestion'. So my best data for January would look like:
Note that in real world, the DateOfForecast may not be regular and may be more than once per day
At A Point in Time
This is where I want to see what the data looked like at a specific point in time. In example below, I'm showing what the data looked like on the 2nd of January.
I will skip the screenshot here - this would show the forecast created, for example, on the 2nd of January and would show all records where the 'DateOfForecast' is 2nd January.
In this case, I present 'what was the best data available 2 or more days before each 'DateInQuestion', giving:
That covers the majority of scenarios. Some volumes:
- If we think of the example forecast above as one data set (let's say it's temperature forecasts for London), I have several hundred thousand data sets. Each data set has a unique (artificial) key
- The description (meta) for each data set is stored in a separate system and need not be repeated in any replacement.
- The frequency of publication (number of forecasts per day) can be between 1 and approximately 48.
- I may have 10 years or more history for any given data set, none of which may be archived
- The horizon (how far out being forecasted for) can vary between one hour and one year.
- The granularity (length of period in DateInQuestion) can be anything from 1 minute to one month. It is, however, consistent within a dataset.
- In my current RDBMS database, I have around 2TB of data.
- Horizon of data retrieval by query, for any example above, can be from one period (DateInQuestion) to several years.
Problems with current system - Slowing down due to vertical scaling constraints - Several of the query use cases I have presented represent rather non-efficient query executions
So, leading on to my question!! I have being looking into NoSQL databases generally (i.e. not focussing on a single implementation). However, most case studies I have came across follow a more '[date,value]' structure and do not address the query use cases I have mentioned.
- Does this data lend itself well to NoSQL?
- Can you suggest an object design for NoSQL?
Please comment if any of the above is unclear and I will try to clarify.
Update 1 Any future database can be either 'on-prem', AWS or Azure. It must be usable from a variety of technologies such as .Net, Excel and Python. Obviously I can provide an API layer to address this if needed.