Best Practices for Financial Data Storage

The following partitioning strategies are recommended for various types of market data:

Factor mining is an essential component of quantitative trading. With the growing scale of quantitative trading and AI model training, practitioners must handle large volumes of factor data. Efficient storage of such data is crucial.

There are two main formats for storing factor data - narrow and wide:

• The narrow format table has four columns, holding timestamps, security IDs, factor names and factor values, as shown in the image 2-1.

  

• The wide format has a separate column for each factor, as shown in the image 2-2.

  

We recommend using a narrow format table to store factor data. Compared to the wide format, the narrow format offers the following advantages:

• More efficient deletion of expired or deprecated factors

  In a narrow format table, deleting a specific factor involves deleting certain rows concentrated in one or several partitions, which makes the operation highly efficient.

• More efficient addition of new factors

  In a wide format table, adding a factor involves adding a column and updating it, which is less efficient. In contrast, in a narrow format table, adding a factor only requires inserting rows.

• More efficient updates of specific factors by name

  A wide format table requires updating an entire column, whereas a narrow format table updates only relevent rows.

• Optimized multi-factor query performance

  Using the PIVOT BY statement, narrow data can be transformed into the panel format, where each row represents a unique combination of a timestamp and a security ID, and each factor is presented in a separate column. Parallel optimization of thePIVOT BY statement ensures efficient multi-factor queries with the narrow format.

The following partitioning strategies are recommended for storing factor data with 5,000 symbols at various frequencies, using the TSDB storage engine:

create database "dfs://options"
partitioned by VALUE(2020.01.01..2021.01.01), HASH([SYMBOL, 20])
engine='TSDB'

create table "dfs://options"."options"(
    TradingDay DATE[comment="trade date", compress="delta"]
    ExchangeID SYMBOL
    LastPrice DOUBLE
    PreSettlementPrice DOUBLE
    PreClosePrice DOUBLE
    PreOpenInterest DOUBLE
    OpenPrice DOUBLE
    HighestPrice DOUBLE
    LowestPrice DOUBLE
    Volume INT
    Turnover DOUBLE
    OpenInterest DOUBLE
    ClosePrice DOUBLE
    SettlementPrice DOUBLE
    UpperLimitPrice DOUBLE
    LowerLimitPrice DOUBLE
    PreDelta DOUBLE
    CurrDelta DOUBLE
    UpdateTime SECOND
    UpdateMillisec INT
    BidPrice DOUBLE[]
    BidVolume INT[]
    AskPrice DOUBLE[]
    AskVolume INT[]
    AveragePrice DOUBLE
    ActionDay DATE
    InstrumentID SYMBOL
    ExchangeInstID STRING
    BandingUpperPrice DOUBLE
    BandingLowerPrice DOUBLE
    tradeTime TIME
    receivedTime NANOTIMESTAMP
    perPenetrationTime LONG
)
partitioned by TradingDay, InstrumentID,
sortColumns=[`InstrumentID,`ReceivedTime],
keepDuplicates=ALL

The script creates the database and table for storing option snapshot data, using the TSDB storage engine and composite partitioned by day and the hash value of InstrumentID. The data is sorted by InstrumentIDand receivedTime columns.

create database "dfs://dayFactorDB" 
partitioned by RANGE(date(datetimeAdd(1980.01M,0..80*12,'M'))), VALUE(`f1`f2), 
engine='TSDB'

create table "dfs://dayFactorDB"."dayFactorTB"(
    tradetime DATE[comment="time column", compress="delta"], 
    securityid SYMBOL, 
    value DOUBLE, 
    factorname SYMBOL
)
partitioned by tradetime, factorname,
sortColumns=[`securityid, `tradetime], 
keepDuplicates=ALL, 
sortKeyMappingFunction=[hashBucket{, 500}]

The script creates the database and table for storing factors calculated at every day, using the TSDB storage engine and composite partitioned by day and factor name. The data is sorted by securityid and tradetime columns.

The TSDB engine supports specifyingsortColumns used to sort and index the data within each partition. If multiple columns are specified, the last column must be a time column. The preceding columns are used as the sort keys. This allows the engine to quickly locate records within a partition. Except for the last element of sortColumns, the preceding elements—collectively referred to as the SortKey—can be specified as the columns that are frequently used as filtering conditions in queries. As a rule of thumb, the number of SortKey entries should not exceed 1,000 per partition for optimal performance. The sortKeyMappingFunction parameter can be used to reduce the dimensionality of SortKey entries when needed. Based on our tests, we found the optimal configuration for daily factor storage is to sort partitions by the securityid and tradetime columns with sortKeyMappingFunction set to 500.

create database "dfs://halfhourFactorDB"
partitioned by RANGE(date(datetimeAdd(1980.01M,0..80*12,'M'))), VALUE(`f1`f2),
engine='TSDB'

create table "dfs://halfhourFactorDB"."halfhourFactorTB"(
    tradetime TIMESTAMP[comment="time column", compress="delta"],
    securityid SYMBOL,
    value DOUBLE,
    factorname SYMBOL,
)
partitioned by tradetime, factorname,
sortColumns=[`securityid, `tradetime],
keepDuplicates=ALL,
sortKeyMappingFunction=[hashBucket{, 500}]

The script creates the database and table for storing factors calculated on a 30-minute basis, using the TSDB storage engine and composite partitioned by year and factor name. The data is sorted by securityid and tradetime columns.

create database "dfs://tenMinutesFactorDB" 
partitioned by VALUE(2023.01M..2023.06M), 
VALUE(`f1`f2), 
engine='TSDB'

create table "dfs://tenMinutesFactorDB"."tenMinutesFactorTB"(
    tradetime TIMESTAMP[comment="time column", compress="delta"], 
    securityid SYMBOL, 
    value DOUBLE, 
    factorname SYMBOL
)
partitioned by tradetime, factorname,
sortColumns=[`securityid, `tradetime], 
keepDuplicates=ALL, 
sortKeyMappingFunction=[hashBucket{, 500}]

The script creates the database and table for storing factors calculated on a 10-minute basis, using the TSDB storage engine and composite partitioned by month and factor name. The data is sorted by the securityid and tradetime columns.

create database "dfs://minuteFactorDB" 
partitioned by VALUE(2012.01.01..2021.12.31), VALUE(`f1`f2), 
engine='TSDB'

create table "dfs://minuteFactorDB"."minuteFactorTB"(
    tradetime TIMESTAMP[comment="time column", compress="delta"], 
    securityid SYMBOL, 
    value DOUBLE, 
    factorname SYMBOL
)
partitioned by tradetime, factorname,
sortColumns=[`securityid, `tradetime], 
keepDuplicates=ALL, 
sortKeyMappingFunction=[hashBucket{, 500}]

The script creates the database and table for storing factors calculated on a 1-minute basis, using the TSDB storage engine and composite partitioned by day and factor name. The data is sorted by the securityid and tradetime columns.

create database "dfs://secondFactorDB" 
partitioned by VALUE(datehour(2022.01.01T00:00:00)..datehour(2022.01.31T00:00:00)), VALUE(`f1`f2), 
engine='TSDB'

create table "dfs://secondFactorDB"."secondFactorTB"(
    tradetime TIMESTAMP[comment="time column", compress="delta"], 
    securityid SYMBOL, 
    value DOUBLE, 
    factorname SYMBOL
)
partitioned by tradetime, factorname,
sortColumns=[`securityid, `tradetime], 
keepDuplicates=ALL, 
sortKeyMappingFunction=[hashBucket{, 500}]

The script creates the database and table for storing factors calculated on a 1-second basis, using the TSDB storage engine and composite partitioned by hour and factor name. The data is sorted by the securityid and tradetime columns.

create database "dfs://level2FactorDB" partitioned by VALUE(2022.01.01..2022.12.31), VALUE(["f1", "f2"]), 
engine='TSDB'

create table "dfs://level2FactorDB"."level2FactorTB"(
    tradetime TIMESTAMP[comment="time column", compress="delta"], 
    securityid SYMBOL, 
    value DOUBLE, 
    factorname SYMBOL
)
partitioned by tradetime, factorname,
sortColumns=[`securityid, `tradetime], 
keepDuplicates=ALL, 
sortKeyMappingFunction=[hashBucket{, 500}]

The script creates the database and table for storing factors calculated based on the level 2 snapshot data, using the TSDB storage engine and composite partitioned by day and factor name. The data is sorted by the securityid and tradetime columns.

create database "dfs://tickFactorDB" partitioned by VALUE(2022.01.01..2022.12.31), VALUE(["f1", "f2"]), HASH([SYMBOL,10])
engine='TSDB'

create table "dfs://tickFactorDB"."tickFactorTB"(
    tradetime TIMESTAMP[comment="time column", compress="delta"], 
    securityid SYMBOL, 
    value DOUBLE, 
    factorname SYMBOL
)
partitioned by tradetime, factorname,securityid,
sortColumns=[`securityid, `tradetime], 
keepDuplicates=ALL

The script creates the database and table for storing factors calculated based on the tick-by-tick data, using the TSDB storage engine and composite partitioned by day, factor name, and the hash value of securityid. Since each partition contains approximately 500 stocks, there is no need to specify the sortKeyMappingFunction parameter. The data is sorted by the securityid and tradetime columns.

create database "dfs://futuresFactorDB" 
partitioned by VALUE(2022.01.01..2023.01.01), VALUE(["f1", "f2"]), 
engine='TSDB' 

create table "dfs://futuresFactorDB"."futuresFactorTB"(
    tradetime TIMESTAMP[comment="time column", compress="delta"], 
    securityid SYMBOL, 
    value DOUBLE, 
    factorname SYMBOL
)
partitioned by tradetime, factorname,
sortColumns=[`securityid, `tradetime], 
keepDuplicates=ALL, 
sortKeyMappingFunction=[hashBucket{, 500}]

The script creates the database and table for storing factors calculated at every 500 milliseconds, using the TSDB storage engine and composite partitioned by day and factor name. The data is sorted by the securityid and tradetime columns.