Best way to store physical stock data in a SQL db

Question

I work for a retail company and we are in the process of building a new data warehouse. One element we need to store is physical stock data as to how many units of any sku are available at a certain location at a certain time. the variables we want to look at are, therefore: skus, sites, day, hour and quantity.

The problem is that there are approximately: 100k skus and 200 stores. If I store data for every hour I would get 100k x 200 x 24 = 480,000,000 lines a day. I understand that not every sku has a quantity > 0 at every site. Let's assume sparsity is as high as 80% and when the quantity is 0 we don't store it so we remain with 96,000,000 rows a day. Moreover, even though we have websites and sales won't stop at night we may assume that night hours will have very little changes in quantities and that not every sku is sold every hour. Let's assume that on average a sku is sold only on 3 different hours a day. This brings us down to 12,000,000.

This is 2.5% of what we initially looked at, however, it is still a considerable amount. 12,000,000 rows every day is going to occupy a lot of space after a few months. A partial solution to the problem is what Kimball suggests in The Data Warehouse Toolkit which is to store data for each hour for the last month or so and then only daily for the last n months and then remove older records. Considering that we are already assuming that on average we sell a sku only on 3 different hours per day switching to daily data from hourly data will reduce only by two thirds. Let's assume I want to save stock data for the last six month. One month at hourly level and five months at daily level (let's also assume 30 days months to ease the math) this is going to be: (12,000,000 X 30) + (4,000,000 X 150) = 960,000,000 rows.

I really can't think of a better way to do this, had anyone had to work with physical stock data before? Any useful lesson you learned? Is there a more efficient way to store this data?

Thanks,

Answer 1

One thing you can do is create a separate table to store the quality and the changes for your stock so you can limit the width of the table. For example you could have a table with the following columns.

Store
SKU
Current Stock
Change In Stock
Change Source (Sales/Restock/Other Change Sources)

When this table is updated it will only include records that had a quantity change so that items that have the same stock level will not update. While you will still get a lot of records the disk usage should be minimized.

Another possibility for performance is to use a partition in the table design so you can more quickly get to the data you need.

Answer 2

Data Model

The data models I've seen has a table for

• Stock as of some date
• Sales/Returns/Arrivals (stock level changes/delta of stock)
• Historical Stock values every based on what is needed for Reports, etc.

If you need to delete chunks of your data based on date, you definitely want to PARTITION your data by date. This usually helps with the performance of the SELECT statements that limit the results based on DATE.

But, before you write a CREATE TABLE statement, you really need to get a list of Business Requirements for the Report/Queries end-users will run.

Physical Storage

You mentioned in one comment that you want to know how to store the data.

IMHO - You want to store the data in a table that exists on a RAID 10 disk subsystem. Preferably, you should be using a SAN.

The last time I checked, the minimum sized hard drive you could buy was 150GB in size. One or two hard drives should be suffice to store that much data. But, your really want to use RAID 10 for Performance and Reliability. Make sure you acquire enough hard drives to handle your required IOPS. You might have 3TB of extra space, but those Reports will be generated very quickly.

SSD vs HDD? You will have to determine that yourself based on your own requirements and your own benchmarks. "Cost" should be part of your decision.

Reducing Row Count

You should not care about attempting to reduce row count.

You should care about "Does my data model support the queries/reports/etc. that the end-users need?".

Answer 3

Since you seem to be primarily interested in stock levels varying over time for SKU/ store combinations I would suggest a time series database. There are many available. Some are propriety, some open source. Many are built on an existing storage engine, often with a SQL programming interface.

They are deployed widely in finance, for listing stock prices, and science for collecting measurements. Knowing these industries and the data volumes they can produce I'm confident this technology will be able to handle your use-case (providing you can supply the IOPS and RAM). KDB (one of the best-established) has a specific retail solution.

Answer 4

Only store a row when the value changes. If the stock stays the same one hour to the next, don't write a row. Write your queries with that in mind.