2

I currently capture well structured forecast data in a SQL Server. An example of this data is below:

Sample Data

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:

Best Data

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.

Point in Time

Specific Forecast

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.

Rolling Window

In this case, I present 'what was the best data available 2 or more days before each 'DateInQuestion', giving:

Rolling WIndow

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.

  1. Does this data lend itself well to NoSQL?
  2. 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.

Improve this question
3
  • I'm not a DB expert, but to me those queries sound like an RDBMS should be able to handle them just fine. I don't see what a NoSQL DB would give you here. Can you give more detail on what makes the queries so slow (if this question is still relevant at all)?
    – doubleYou
    Apr 28, 2019 at 9:16
  • 1
    I would not expect a NoSQL DB to be more appropriate than an RDBMS for this, quite the opposite. Proper indexing and denormalization are the standard tools to solve performance problems in such a system. Especially denormalization, since such kind of data consists usually of immutable records, which means denormalization won't lead to consistency issues.
    – Doc Brown
    Apr 28, 2019 at 9:24
  • 1
    NoSQL databases have their own woes. Namely transactions. They just do not exist in any convient form (they they do "exist"). These systems do offer atomic document updates/insert/remove presuming a single db server. However many of them will offer only eventual consistency in a multi-node system. Eventual consistency is ignorable when everything is reliable and consistent - I've yet to meet such a system in production though. Which means your program will be responsible for enforcing consistency accross all of its operations.
    – Kain0_0
    Apr 28, 2019 at 9:50

1 Answer 1

Reset to default
2

I think you have an X-Y problem here. Unless you've already tried very hard to optimize your SQL database (checking indices, looking at query execution plans, etc, etc) and found that doesn't fix the problem, that's that first thing you should be doing before saying "I need to move to NoSQL".

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.