# TileDB for sparse time series pivot tables? **URL:** \ **Category:** Uncategorized\ **Created:** [March 5, 2020, 8:56pm UTC](https://forum.tiledb.com/t/tiledb-for-sparse-time-series-pivot-tables/150 "2020-03-05T20:56:58Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Mtrl\_Scientist](https://yyz1.discourse-cdn.com/flex035/user_avatar/forum.tiledb.com/mtrl_scientist/32/134_2.png) [@Mtrl\_Scientist](https://forum.tiledb.com/u/Mtrl_Scientist)\ **Post date:** [March 5, 2020, 8:56pm UTC](https://forum.tiledb.com/t/tiledb-for-sparse-time-series-pivot-tables/150/1 "2020-03-05T20:56:58Z")
Is it possible to store financial time series pivot tables as tiles and append to them over time? ![photo_2020-03-05_21-45-22](https://canada1.discourse-cdn.com/flex035/uploads/tiledb/original/1X/9657e79a735c77e722939adfd7cc718c7a55f389.jpeg) Obviously, the index is going to change. But would it be possible to treat each tile as a separate object and then just place the next [x,10] matrix next to the old one? ---
**Author:** ![stavros](https://avatars.discourse-cdn.com/v4/letter/s/51bf81/32.png) [@stavros](https://forum.tiledb.com/u/stavros)\ **Post date:** [March 6, 2020, 3:45pm UTC](https://forum.tiledb.com/t/tiledb-for-sparse-time-series-pivot-tables/150/2 "2020-03-06T15:45:39Z")
Hello, Thanks for reaching out! Let’s first model the schema of your array for your use case, without thinking in terms of tiles. Tiling is used internally to tune your performance, and I will touch upon it later below. You need a **2D sparse array** , where the first dimension is time (if slicing on time is the most selective for your queries) and the second is price. Currently, TileDB supports only “homogeneous” dimensions, that is, the dimensions must have the same type (e.g., both `FLOAT32/64` or `UINT64`). The next TileDB version (to be release in April) relaxes this constraint and you will be able to define different types for your dimensions (e.g., `DATETIME_MS` for time and `FLOAT32/64` for price). The **dimension domains** in your case can be arbitrarily large, so that when you “append” data, essentially they can get written anywhere in the 2D domain without issues. Defining huge domains in TileDB does not affect performance, as TileDB does not pre-populate anything until you write to the array. You then need to define your **attributes** , which determine the values you store in the cells of your matrix above, so probably a `FLOAT32/64` attribute. “Appending” to the above array is not special. You just need to write triplets of cells in the form `(time, price, value)`, where `(time, price)` are the cell coordinates and `value` is the attribute value you are writing in the cell. Writing to sparse TileDB arrays is explained [here](https://docs.tiledb.com/developer/api-usage/writing-arrays/writing-sparse-cells). Now once you get the above working, you can tune performance by defining the **space tile extents** and **tile capacity**. We include a lot of performance tips [here](https://docs.tiledb.com/developer/performance-tips/choosing-tiling-and-cell-layout). Please let us know should you need any further information. Stavros ---
**Author:** ![Mtrl\_Scientist](https://yyz1.discourse-cdn.com/flex035/user_avatar/forum.tiledb.com/mtrl_scientist/32/134_2.png) [@Mtrl\_Scientist](https://forum.tiledb.com/u/Mtrl_Scientist)\ **Post date:** [March 6, 2020, 4:55pm UTC](https://forum.tiledb.com/t/tiledb-for-sparse-time-series-pivot-tables/150/3 "2020-03-06T16:55:29Z")
Thanks a lot for your extensive reply, Stavros! I appreciate the time you put in it! The lack of datetime support is no problem. I can just save it as int timestamp and convert it back when reading it out. However, I still don’t quite understand how to append data when the indices change, as shown in the figure below: ![Annotation 2020-03-06 174939](https://canada1.discourse-cdn.com/flex035/uploads/tiledb/original/1X/66e84fd533d190e5772b7baf8a640e385d6448c0.png) Does TileDB automatically re-index the data (backfill NaN values into cells at a price level that didn’t exist before) or do I need to read everything out and re-index it myself before saving it back to the DB? Kind regards, Frederic ---
**Author:** ![seth](https://avatars.discourse-cdn.com/v4/letter/s/f14d63/32.png) [@seth](https://forum.tiledb.com/u/seth)\ **Post date:** [March 6, 2020, 5:07pm UTC](https://forum.tiledb.com/t/tiledb-for-sparse-time-series-pivot-tables/150/4 "2020-03-06T17:07:55Z")
Frederic, In TileDB sparse arrays are truly sparse. The NaNs in your diagram won’t exist, only cells which contain values will exist. In this way the changing indices with each write do not matter. When you perform a sparse write you specify the specific coordinates that are being written, so TileDB only saves those cells. Empty cells in a sparse array are not materialized on disk and are not returned when you issue a query. In the [python sparse write example](https://docs.tiledb.com/developer/api-usage/writing-arrays/writing-sparse-cells) there is a diagram which highlights that sparse writes avoid materializing anything for the empty cells. The white cells below are “empty”/“do not exist” on disk so we avoid your NaN issue of the changing indices. ![writing_sparse_cells](https://canada1.discourse-cdn.com/flex035/uploads/tiledb/original/1X/39eefa247cd0ac5966b79a0c01c1202a0b53db1d.png) Seth ---
**Author:** ![Mtrl\_Scientist](https://yyz1.discourse-cdn.com/flex035/user_avatar/forum.tiledb.com/mtrl_scientist/32/134_2.png) [@Mtrl\_Scientist](https://forum.tiledb.com/u/Mtrl_Scientist)\ **Post date:** [March 6, 2020, 9:42pm UTC](https://forum.tiledb.com/t/tiledb-for-sparse-time-series-pivot-tables/150/5 "2020-03-06T21:42:18Z")
Oh, my mistake! I was confused about how sparse matrices work! ![Annotation 2020-03-06 223945](https://canada1.discourse-cdn.com/flex035/uploads/tiledb/original/1X/c9c8bea5634b1cc1d31244cadb827c53ea760da3.png) I got it working eventually, thanks for the help!