Sql-server – Storing daily yield curve data in SQL table

rdbmssql server

I have daily yield curve data for integer maturities that should be stored in a MSSQL server.

Currency.csv

Currently, the data is stored in CSV files per currency in the format:

date, 1y, 2y, 3y, ...
08/12/2020, 0.4, 0.5, 0.6, ...

I am wondering what the best choice for a table would be.

Same Format Table

The current data could be directly translated to a table with columns:

+------------+------+------+------+------+
|    date    |  1y  |  2y  |  3y  |  ... |
+------------+------+------+------+------+
| 08/12/2020 |  0.4 |  0.5 |  0.6 |  ... |
+------------+------+------+------+------+

Alternate Table Design

However, I could also store it like:

+------------+-----------+-----------+--------+
|    date    |  currency |  maturity |  value |
+------------+-----------+-----------+--------+
| 08/12/2020 |  "EUR"    |         1 |    0.4 |
| 08/12/2020 |  "EUR"    |         2 |    0.5 |
| 08/12/2020 |  "EUR"    |         3 |    0.6 |
+------------+-----------+-----------+--------+

Obviously, the second approach would take more space than the first, but I would see the advantage that I am not restricting the value of the maturity in case in the future it should be changed to non-integer values or to maturities that don't have a column yet.

Which of the two table designs would be better? Are there other designs that would fit better?

Best Answer

Your second choice (long) is preferable for the reasons you outline, provided you use the right keys and constraints.

Something like this is probably in the right direction:

CREATE TABLE Currency
(
  CurrencyCd    CHAR(3)      NOT NULL  /* This is the ISO currency code */
 ,CurrencyDesc  VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_Currency PRIMARY KEY (CurrencyCd)
 ,CONSTRAINT AK_Currency UNIQUE (CurrencyDesc)
 ,CONSTRAINT CK_CurrencyCd_Length CHECK (LEN(CurrencyCd) = 3)
)
GO

/* This is solely to prevent invalid values being added */
CREATE TABLE MaturityAge
(
  MaturityAgeYr  TINYINT  NOT NULL
 ,CONSTRAINT PK_Maturity PRIMARY KEY (MaturityAgeYr)
)
GO

CREATE TABLE CurrencyYield
(
  CurrencyCd     CHAR(3)       NOT NULL
 ,MaturityAgeYr  TINYINT       NOT NULL  /* Possibly consider using months if finer detail possible */
 ,AsOfDt         DATE          NOT NULL
 ,Yield          DECIMAL(9,1)  NOT NULL /* Can increase decimal precision if necessary */
 ,CONSTRAINT FK_CurrencyYield_Of_Currency FOREIGN KEY (CurrencyCd) REFERENCES Currency (CurrencyCd)
 ,CONSTRAINT FK_CurrencyYield_At_Maturity FOREIGN KEY (MaturityAgeYr) REFERENCES MaturityAge (MaturityAgeYr)
 ,CONSTRAINT PK_CurrencyYield PRIMARY KEY (CurrencyCd, AsOfDt, MaturityAgeYr)
)
GO

Here the choice of the composite clustered index form by the primary key (CurrencyCd, AsOfDt, MaturityAgeYr) ensures the data will be (more or less) stored sorted according to those columns. That means any queries of the type WHERE CurrencyCd = 'EUR' will only read the relevant pages.

This choice of clustered index + storing the data in a "long" format will make "widening" the data trivial - so if you need to view the data as you provide in the first example, it's a simple PIVOT.

Other considerations:

If the volume of data is very large, but most of the values do not change, you can explore only storing changed values. It takes a little more code to retrieve the data for a given date, but you can create date tables/views to simplify that. If this is the case, let me know in the comments and I'll expand my answer.
Over time the table will fragment (a large number of records will not be co-located according to the clustered index), so the clustered index will need to be rebuilt occasionally. There are a number of readily available tools/scripts to make this relatively simple.

Related Solutions

Sql-server – Best way of aggregating, storing and using data in SQL Server (triggers, scheduled jobs, SSAS?)

The aggregate table can probably stay in the same DB. You can however create it on a separate filegroup and disk.

Full update of a new aggregate table

One way of doing it using pure SQL and your sample:

WITH list ([Type], [FeatureId], [Measure], [Value], [Timestamp], [ID]) as (
    SELECT *
        , ROW_NUMBER() OVER(PARTITION BY [FeatureId] ORDER BY [Measure]) 
    FROM (
        SELECT 0, [FeatureId], [MeasureFrom], [Value], [Timestamp] FROM data
        UNION ALL
        SELECT 1, [FeatureId], [MeasureTo], [Value], [Timestamp] FROM data
    ) l([Type], [FeatureId], [Measure], [Value], [Timestamp])
)
SELECT l1.FeatureId, [MeasureFrom] = l1.Measure, [MeasureTo] = l2.Measure
    , [Value] = CASE WHEN l1.Type = 0 THEN l1.Value ELSE l2.Value END
    , [Timestamp] = CASE WHEN l1.Type = 0 THEN l1.Timestamp ELSE l2.Timestamp END
FROM list l1
INNER JOIN list l2 ON l1.FeatureId = l2.FeatureId AND l1.ID+1 = l2.ID
;

With this really small sample, I am not sure it covers all your needs. It may help to add a bigger sample with more data.

It does 4 table scans. Since you have 250k rows, it may not perform so well. It will probablibly better to run it in batch of X consecutive FeatureIds.

This could be done using a job and a SSIS package with either a single update or batch update. You would have to truncate the aggregate table first.

Trigger on each new row in main table

For new rows, using a trigger, this query could be used:

WITH new([FeatureId], [MeasureFrom], [MeasureTo], [Value], [Timestamp]) as (
--    SELECT 1, 1, 20, 2, '2015-01-01'
--  SELECT 1, 5, 15, 3, '2015-01-02'
    SELECT 1, 9, 10, 8, '2015-01-03'
), gap([FeatureId], mn, mx) as (
    SELECT n.[FeatureId], mn.mn, mx.mx
    FROM new n
    CROSS APPLY (SELECT mn = MAX([MeasureFrom]) FROM data3 WHERE [FeatureId] = n.[FeatureId] AND [MeasureFrom] < n.MeasureFrom) mn
    CROSS APPLY (SELECT mx = MIN([MeasureTo]) FROM data3 WHERE [FeatureId] = n.[FeatureId] AND [MeasureTo] > n.MeasureTo) mx
), list (x,[FeatureId], [Measure], [Value], [Timestamp], [ID]) as (
    SELECT *
        , ROW_NUMBER() OVER(PARTITION BY [FeatureId] ORDER BY [Measure]) 
    FROM (
        SELECT 0, d.[FeatureId], d.[MeasureFrom], d.[Value], d.[Timestamp] 
        FROM data3 d
        INNER JOIN gap g ON d.[FeatureId] = g.[FeatureId] AND (d.MeasureFrom >= g.mn AND d.MeasureFrom < g.mx)
        UNION ALL
        SELECT 1, d.[FeatureId], d.[MeasureTo], d.[Value], d.[Timestamp] 
        FROM data3 d
        INNER JOIN gap g ON d.[FeatureId] = g.[FeatureId] AND d.MeasureTo = g.mx
        UNION ALL
        SELECT 2, [FeatureId], [MeasureFrom], [Value], [Timestamp] FROM new
        UNION ALL
        SELECT 3, [FeatureId], [MeasureTo], [Value], [Timestamp] FROM new
    ) l(x, [FeatureId], [Measure], [Value], [Timestamp])
) 
MERGE data3 AS target
USING (
    SELECT l1.FeatureId, [MeasureFrom] = l1.Measure, [MeasureTo] = l2.Measure
        , [Value] = COALESCE(d.[Value], l1.[Value])
        , [Timestamp] = COALESCE(d.[Timestamp], l1.[Timestamp])
    FROM list l1
    INNER JOIN list l2 ON l1.FeatureId = l2.FeatureId AND l1.ID+1 = l2.ID
    LEFT JOIN data3 d ON d.MeasureFrom = l1.Measure OR d.MeasureTo = l2.Measure
) as source(FeatureId, [MeasureFrom], [MeasureTo], [Value], [Timestamp])
ON (target.FeatureId = source.FeatureId AND target.[MeasureFrom] = source.[MeasureFrom])
WHEN MATCHED THEN
    UPDATE SET target.[MeasureTo] = source.[MeasureTo]
WHEN NOT MATCHED BY target THEN
    INSERT (FeatureId, [MeasureFrom], [MeasureTo], [Value], [Timestamp])
    VALUES (source.FeatureId, source.[MeasureFrom], source.[MeasureTo], source.[Value], source.[Timestamp])
;

You must replace the new CTE by the values of the new inserted row in the trigger and use it to merge with the aggregate table.

Scheduled update of newly added rows

If you can find a way to get a list of all newly added rows to the main table since the last update of the aggregate table, you could schedule a job every x minutes or hours and only update what is necessary based on what has been recently added.

The trigger query will work as well with scheduled updates.

This can be run as a schedueled job and within a SSIS package.

Sql-server – MS SQL Table and ETL connundrum

The common sense approach would be to cleanse and sanitise the data on import. Creating a view to mask the problem is not fixing the underlying issue, it's just hiding it.

Also, having the views is unlikely to be a one stop fix, you'll likely need to write other code in other places to work around the issue, which will all need to be maintained.

Having code that works around a problem might be the quicker of the options, but in the long run it will create more work and problems than fixing the data as it comes in to the system.