How to build a point-in-time investment fundamentals dataset

Store each filed fact with its fiscal period, publication timing, source filing, concept, unit, and revision. To answer an as-of query, choose from facts that were available at that cutoff. Keeping both the economic period and the publication date prevents later amendments from rewriting what users could have known earlier.

Choose the dataset grain and availability policy

For a first release, pick one metric, one issuer cohort, and a small set of accepted filing forms and units. Write down how you handle fiscal quarters, year-to-date values, amendments, and conflicting facts. Use SEC EDGAR submissions to identify filings and their acceptance metadata and Company Facts to retrieve extracted XBRL facts. Company Facts has a specific aggregation scope. A production dataset may need the original filing when a concept, entity context, or timestamp is absent. SEC EDGAR APIs

Use a documented first-public timestamp for public-information backtests, or first-ingested timestamp for replaying your pipeline. If only acceptance time is available, record it as a proxy and apply a conservative lag. SEC EDGAR publication timing

Build a reproducible release

What to automate

Capture source revisions

Schedule a fetch for a small list of approved issuers. Save the raw response and filing reference against issuer, accession, ingest time and the chosen availability timestamp. Record the basis for that timestamp in a separate field. Back off after source errors and alert Data Operations on a missing coverage window. Use the same accession and checksum to identify an exact replay.

Map facts to metrics

Define each metric with an approved concept, unit, fiscal period shape and scope. Store effective dates and examples in a versioned mapping. Ask AI for candidate matches using the published concept definitions, then have the data steward verify the unit and meaning. Quarantine unfamiliar concepts, foreign units and conflicting dimensions until the mapping is approved.

Resolve period and revisions

Keep the fiscal period and availability time as separate fields. For each requested cutoff, filter by availability first. Then choose among eligible facts for the same issuer, metric, unit and period using a reviewed form and revision priority. Preserve accession IDs and prior versions. If two equally ranked eligible facts conflict, hold the record for the data steward.

Review suspicious changes

Calculate magnitude changes on comparable periods and flag unit switches, conflicting candidate values and gaps in expected issuer coverage. Include the current and prior values, units, availability timestamps and original filing references in the queue. The data steward can clear a flag with cited evidence or add a documented mapping correction. A rerun applies the new mapping version without altering the prior release.

Publish the dataset

Package the rows with schema version, cutoff policy, eligible issuer coverage and row-level lineage. Validate the manifest and sample historical queries before promotion. Publish a new immutable version and record the pointer change. When QA finds an issue, retain the suspect package and roll back the pointer while the owner reviews affected rows.

Test for look-ahead leakage

In a synthetic fixture, a company originally files revenue of $100 million for the same economic period that it later restates to $92 million. If the original was available May 1 and the amendment June 10, a May 31 cutoff must return $100 million and a June 30 cutoff must return $92 million. Both should retain their original accession and time. Add a conflicting unit and a late filing to the test. Confirm the unit is quarantined, and the filing becomes eligible only after its availability timestamp. For backtests, measure lineage coverage and stale or missing fact rates alongside return metrics.

Build the bitemporal fundamentals table

Store each observation at issuer_id × statement_period × metric_id × source_revision. Preserve original report currency, scale, filing/effective date, public availability timestamp, ingestion timestamp and amendment lineage. Hold a separate identifier history with valid-from and valid-through dates. A current ticker lookup can map a historical filing to the wrong issuer.

1. Ingest the filed version and amendment as distinct rows. Use the selected information availability policy to define the earliest eligible date. Persist the source link, original unit and transformation version.
2. At each strategy date, filter by the availability cutoff before choosing the latest revision of the same period and metric. Decide whether simulated readers can use a filing the same session or only in the next tradable interval and apply that rule consistently. Avoid filtering only on reporting period.
3. Run completeness and currency checks per period and exclude stale rows under a named policy. Do not restate an older simulated observation with a value that became available later. Record null coverage and eligibility reason before imputing or aggregating.
4. Freeze the backtest manifest and independently query several issuers across amendments, identifier changes and late feeds. A revision seen after a run should affect only later cutoffs or a separately labeled correction.

WITH facts(issuer, metric, period, available_at, revision, amount) AS (
  VALUES ('E1', 'revenue', '2025-12-31', '2026-02-10', 1, 105),
         ('E1', 'revenue', '2025-12-31', '2026-03-05', 2, 98)
),
eligible AS (
  SELECT *, row_number() OVER (
    PARTITION BY issuer, metric, period
    ORDER BY available_at DESC, revision DESC
  ) AS priority
  FROM facts WHERE available_at <= '2026-02-15'
)
SELECT issuer, amount FROM eligible WHERE priority = 1;

The query returns E1 | 105. Changing the cutoff to March 6 returns 98. In production use timestamps with an explicit timezone, plus the market calendar and policy cutoff for the strategy.

Implement the work step by step

The investment data product lead decides whether customers can reproduce values as known at a past date and consume a stable version without look-ahead leakage. The workflow starts when an eligible filing appears. Ends with published facts and a release manifest or a held exception. Excludes rating or investment recommendations.

Ingest

• Inventory eligible filings. Poll with backoff and append accession, accepted timestamp and snapshot. Read SEC submissions, accepted issuer universe. Record New filings to content operations. Owner Data ops. Review unexpected form or issuer scope. Check Respect upstream availability and access limits.
• Preserve extracted and original evidence. Stage immutable raw fact payload, hash input, link accession to source filing. Read Company Facts, raw filing, filing metadata. Record Raw facts and filing pointers to parser. Owner Data ops. Resolve missing original context if relevant. Check Do not invent missing timestamp or entity dimension.

Normalize and resolve

• Map concepts and units. Validate known maps with required namespace, currency and period shape. AI can draft candidate mapping with cited definitions. Read Taxonomy, approved metric dictionary. Record Versioned mapping to data QA. Owner Content analyst. Approve newly introduced concept equivalence. Check Block ambiguous concepts and incompatible units.
• Normalize economic period. Apply accepted fiscal-period rules and preserve period form. Flag inferred facts. Read Filing dates, fiscal calendars, XBRL periods. Record Comparable facts with separate economic and available times. Owner Content analyst. Approve new fiscal calendar exceptions. Check Never mix quarterly and YTD amounts silently.
• Resolve restatement precedence. Evaluate eligibility at cutoff, select approved precedence, retain prior rows. Read Accession lineage, availability and correction rules. Record Versioned facts to release operations. Owner Data QA. Review inconsistent filing relations. Check Past cutoffs keep previous eligible facts.

Validate and publish

• Investigate a flagged unit or fact. Create issue keyed by fact and rule. Include source and previous release. AI can summarize cited raw comparison. Read Lineage, original filing, anomaly table. Record Decided release exception to steward. Owner Data QA. Approve correction or keep quarantined. Check Anomalies have explicit owner and no silent null replacement.
• Freeze and publish release. Check uniqueness and schema contract, verify as-of regression, switch pointer atomically. Read Curated facts, schema, consuming API. Record Immutable fact release and public pointer. Owner Release ops. Approve material schema or licensing change. Check Rollback restores prior release and lineage.

Parallel run. Use a known filing and correction cohort across two releases. Require an as-of leakage suite and a documented policy for conflicting forms, contexts and units.

Build it yourself with AI

Use a small CSV or SQLite database with synthetic facts for one issuer. Store concept rules and timestamp policy in concept_map.csv and playbook.md. Let AI help draft the code and review candidates. Give your build a deterministic eligibility rule so a historical query never depends on a model response.

1. Draft the source contract

   Design a table for issuer, accession, metric, unit, fiscal period, value, published_at, ingested_at, availability_basis and mapping version. Suggest a reviewed concept map from the supplied definitions. Show how to store a quarterly fact separately from a year-to-date fact and how to record conflicts.

2. Implement the cutoff and test it

   Write a Python and SQLite prototype that filters facts at the cutoff before ranking revisions. Given a same-period original value of 100 million available May 1 and an amended 92 million available June 10, assert May 31 returns 100 million and June 30 returns 92 million, each with its accession. Add a wrong-unit row and a conflicting same-rank row. Return them in an exception file.

3. Build a release report

   From these schema, coverage and test outputs, draft a release manifest and checklist. Include a count of held facts, which historical cutoffs passed, any availability proxies and source references for the reviewer.

Rebuild the same release from the stored inputs and compare its rows and manifest. Ask the data steward to inspect the underlying filings for a small sample of conflicts. Test switching back to the earlier release before exposing a versioned file or database view to consuming teams.

Need help automating this?

Explore the related financial services workflows for reconciling source records, retaining approved mappings and reviewing data exceptions. Explore Mage's financial services workflows.