SQL 中的時點基本面數據:版本化事實、撤回與重播測試
Point-in-Time Fundamentals in SQL: Versioned Facts, Withdrawals, and Replay Tests
文章提出一套以 SQL 管理時點基本面數據的方法,將穩定事實與其版本分開保存,並按決策截止時間選取最新可用版本,以免後續申報改動覆寫歷史查詢結果。文中以 PostgreSQL 風格 SQL 示範如何保留撤回狀態,並透過重播與策略輸出整合測試,檢查新增未來版本後歷史因子、排名及投資組合權重是否改變。
A historical rebalance should return the same financial inputs after you ingest a later filing. This sounds like an ordinary database requirement. It is easy to break with a single upsert.
This article was drafted with an AI writing tool. The inputs below are synthetic, and the SQL is a PostgreSQL-style reference implementation.
Suppose a company first reports a quarterly operating margin of 12%. A July correction changes it to 9%. If you key your table by company, metric, and quarter, then overwrite the row on conflict, every historical query now sees a database shaped by the July correction. Adding a 60-day reporting lag cannot recover the overwritten row.
1. Separate the fact from its versions
Give a financial fact a stable identity. In production that identity would typically include a company ID, normalized metric, period start and end, unit, and reporting scope. A fiscal quarter and a year-to-date period must survive as separate facts even when they share the same end date.
Store versions in a second table. Each version needs a source reference and the time it became usable by the strategy. In this example, source_revision has already been assigned by the normalization layer using the authoritative order of updates for that fact.
CREATE TABLE fact_version (
fact_id bigint NOT NULL,
source_revision integer NOT NULL,
source_id text NOT NULL,
public_at timestamptz NOT NULL,
usable_at timestamptz NOT NULL,
state text NOT NULL
CHECK (state IN ('reported', 'withdrawn')),
value numeric,
PRIMARY KEY (fact_id, source_revision),
CHECK (
(state = 'reported' AND value IS NOT NULL)
OR (state = 'withdrawn' AND value IS NULL)
),
CHECK (usable_at >= public_at)
);
Here, source_id identifies the provenance of the normalized version. In an EDGAR pipeline, keep the accession number and enough filing context to find the tagged fact again. The SEC's Submissions and Company Facts endpoints are useful starting points. SEC EDGAR API documentation
Give repeated deliveries of the same source version an idempotent ingestion path. An old source document can arrive late, especially during backfills. Its arrival timestamp determines when that record became usable on that path. It does not promote an old statement above a newer authoritative correction. When source precedence is uncertain, resolve it during normalization and expose that uncertainty in the data-quality record.
2. Filter on the cutoff before choosing the version
Use the same decision cutoff for every input in a rebalance. Resolve the latest eligible version per fact, then return its state. The example looks up one fact. A batch implementation can use the same ordering within a window partitioned by fact_id.
SELECT
fact_id,
source_revision,
source_id,
usable_at,
state,
CASE WHEN state = 'reported' THEN value END AS usable_value
FROM fact_version
WHERE fact_id = :fact_id
AND usable_at <= :decision_cutoff
ORDER BY source_revision DESC
LIMIT 1;
There are three distinct query results: no eligible row, a reported value, and a withdrawal. Keep that distinction explicit in the consuming code. It lets the model log whether a figure was unavailable, was present, or was known to have been withdrawn.
A common bug is adding state = 'reported' to the WHERE clause. If revision 3 withdraws revision 2, that filter throws away the withdrawal before ranking. Revision 2 silently comes back. The query should rank revision 3 first and return its withdrawn state with a null usable value.
3. Reproduce the failure with a small event history
Use one fact and four source versions. Revision 1 becomes usable on May 8 at 4:18 p.m. Eastern and reports 12%. Revision 2 becomes usable on July 30 at 8:05 a.m. and reports 9%. Revision 3 withdraws the figure on August 4 at 10:00 a.m. Revision 4 restores it with a new value of 9.4% on August 5 at 8:10 a.m.
| Decision cutoff | Expected version | Expected result |
|---|---|---|
| May 8, 4:17 p.m. | None | No eligible row |
| May 8, 4:18 p.m. | 1 | 12% |
| June 3, 9:25 a.m. | 1 | 12% |
| July 30, 8:04 a.m. | 1 | 12% |
| July 30, 8:05 a.m. | 2 | 9% |
| August 4, 10:00 a.m. | 3 | Withdrawn, value unavailable |
| August 5, 8:10 a.m. | 4 | 9.4% |
Then insert an out-of-order delivery case. Reprocess the May source after the July correction. Verify that the replay still chooses revision 2 for July 30 after 8:05 a.m. and revision 1 for June 3. For an existing source version, preserve its recorded availability and log the replayed receipt separately. Backfilling today must not manufacture a historical receipt timestamp.
Add a second fixture for facts used together. Give operating income and revenue different availability times. Compute the ratio only from components eligible at the same cutoff and retain both source references. Test the case in which a corrected numerator arrives while the denominator stays valid. Also test a change of unit or reporting scope, where your validation rules should prevent an incompatible pair.
4. Test the strategy output as well as the data query
A passing row-level test still leaves room for a leak in feature materialization. A daily batch might apply the correct cutoff, while an offline feature job pulls a cached latest-value snapshot. A model can then train on a version of the factor the online strategy never sees.
For a KAEL AI research pipeline, the integration test would pin a historical rebalance and compare its factor values, source versions, ranks, and portfolio weights before and after adding a future revision. Keep the decision cutoff, version IDs, and feature-calculation version with each stored output. Report version mismatches even when values happen to coincide. Two equal numbers can have different provenance, and that discrepancy matters when the next update arrives.
With those records, a changed historical selection becomes diagnosable. You can trace it to the ingestion timestamp, the version resolver, the feature job, or the portfolio construction step without treating the entire backtest as a black box.
來源:DEV Community · dev.to