06 - Point-in-Time Integrity Logic

24 min read

Point-in-Time Data Integrity


flowchart TD
    A["Need an as-of answer"] --> B{"Is the business result<br/>published as a dated snapshot?"}
    B --> Y1([YES])
    B --> N1([NO])
    Y1 --> C["Filter the snapshot fact table<br/>by the exact publication date"]
    N1 --> D{"Do rows carry<br/>business validity dates?"}
    D --> Y2([YES])
    D --> N2([NO])
    Y2 --> E["Use half-open intervals and enforce<br/>one open row per business key"]
    N2 --> F["Add an effective-dated model<br/>before trusting PIT answers"]
    E --> G{"Must you reproduce what the system<br/>knew before later corrections arrived?"}
    G --> Y3([YES])
    G --> N3([NO])
    Y3 --> H["Use transaction-time history<br/>such as system-versioned temporal tables"]
    N3 --> I["Valid-time history is enough,<br/>but still gate outputs with reconciliations"]
    H --> J["Validate weights, constituent counts,<br/>and temporal joins before publication"]
    I --> J

    classDef yesNode fill:#1f3b2d,stroke:#73d13d,stroke-width:2px,color:#c0caf5,font-weight:bold;
    classDef noNode fill:#4a1f24,stroke:#db4b4b,stroke-width:2px,color:#c0caf5,font-weight:bold;
    class Y1,Y2,Y3 yesNode;
    class N1,N2,N3 noNode;

Effective-Dated Constituent Lists

The first PIT question is always: what kind of time surface are you querying? In the live stoxx database, the answer is mixed.

PIT surfaceLive tableWhat it answers wellMain rule
Daily snapshot factgold.scores_daily”What was published for this index on this date?”Filter by the exact snapshot date.
Effective-dated reference rowssilver.index_dimSlowly changing descriptive attributesKeep one active row per business key and use half-open intervals once history exists.
Transaction-time historydbo.demo_pit_temporal in the example below”What did we know before a later correction?”Separate valid time from system-recorded time.

Inspect whether the live reference table is truly historical

SELECT
    COUNT(*) AS total_rows,
    COUNT(DISTINCT CONCAT(_index, '|', symbol)) AS distinct_index_symbol_pairs,
    SUM(CASE WHEN valid_to IS NULL THEN 1 ELSE 0 END) AS open_ended_rows,
    SUM(CASE WHEN is_current = 1 THEN 1 ELSE 0 END) AS current_rows
FROM silver.index_dim;
total_rowsdistinct_index_symbol_pairsopen_ended_rowscurrent_rows
169169169169

silver.index_dim is structurally ready for valid-time modeling, but the live data is still current-state only: every business key appears once, every row is open-ended, and every row is marked current. That means this table is safe for current reference attributes, but not yet a full historical constituent source.

ColumnValueWatchMeaningImplication
total_rows = distinct_index_symbol_pairsOne row per (_index, symbol) pair✅No duplicated business keys in the current surface.Good baseline for introducing historical versions later.
open_ended_rows = total_rowsAll rows are still openDependsNo row has been closed with a valid_to value.Treat the table as current-state reference data, not as a complete PIT history.
current_rows = total_rowsEvery row is currentDependsThe model has no superseded rows yet.A later historization step must close old rows before PIT reconstruction becomes possible.

Inspect the current valid-time shape

SELECT TOP (15)
    _index,
    symbol,
    valid_from,
    valid_to,
    is_current
FROM silver.index_dim
ORDER BY _index, symbol;
_indexsymbolvalid_fromvalid_tois_current
euro_stoxx_50ABI.BR2026-03-04 22:11:36.2143639NULL1
euro_stoxx_50AD.AS2026-03-04 22:11:36.2552380NULL1
euro_stoxx_50ADS.DE2026-03-04 22:11:36.2552380NULL1
euro_stoxx_50ADYEN.AS2026-03-04 22:11:36.2552380NULL1
euro_stoxx_50AI.PA2026-03-04 22:11:36.2225440NULL1
euro_stoxx_50AIR.PA2026-03-04 22:11:36.2061894NULL1
euro_stoxx_50ALV.DE2026-03-04 22:11:36.2061894NULL1
euro_stoxx_50ARGX.BR2026-03-04 22:11:36.2511587NULL1
euro_stoxx_50ASML.AS2026-03-04 22:11:36.1898627NULL1
euro_stoxx_50BAS.DE2026-03-04 22:11:36.2470557NULL1
euro_stoxx_50BAYN.DE2026-03-04 22:11:36.2511587NULL1
euro_stoxx_50BBVA.MC2026-03-04 22:11:36.2143639NULL1
euro_stoxx_50BMW.DE2026-03-04 22:11:36.2429718NULL1
euro_stoxx_50BN.PA2026-03-04 22:11:36.2470557NULL1
euro_stoxx_50BNP.PA2026-03-04 22:11:36.2184535NULL1

The live rows confirm the aggregate picture: valid_from is present, but the table currently has no closed historical rows. The note’s production rule is therefore simple: use silver.index_dim as current reference data today, and only treat it as a true PIT source once old versions are explicitly closed and retained.

ColumnValueWatchMeaningImplication
valid_fromPopulated✅The model records when the row became active.Good foundation for future valid-time history.
valid_toNULLDependsThe row has no recorded business expiry yet.Safe for current-state reads, insufficient for closed-interval PIT reconstruction.
is_current1✅ in this current-state snapshotRow is the active version.Once history exists, only one active row per business key should remain.

Pull a published PIT snapshot directly from the daily score table

SELECT TOP (10)
    score_date,
    symbol,
    CAST(index_weight AS decimal(18,10)) AS index_weight,
    composite_score,
    composite_rank
FROM gold.scores_daily
WHERE _index = 'euro_stoxx_50'
  AND score_date = '2026-04-08'
ORDER BY index_weight DESC, symbol;
score_datesymbolindex_weightcomposite_scorecomposite_rank
2026-04-08ASML.AS0.08864526000.01559812028353261628
2026-04-08MC.PA0.0474672585-0.2090708165763542641
2026-04-08OR.PA0.0383950841-0.6337535449276218149
2026-04-08RMS.PA0.0354363420-0.556658887406122948
2026-04-08SAP.DE0.03476614720.1436639280313133619
2026-04-08TTE.PA0.03470270350.495393385405863362
2026-04-08SIE.DE0.03284347010.02298118780901000126
2026-04-08ITX.MC0.0320484842-0.1082070342839071037
2026-04-08DTE.DE0.03056404710.380500665029655575
2026-04-08SAN.MC0.02884700670.325750213740841079

This is a clean PIT query because the date grain is explicit in the fact table itself. No interval logic is needed: the business answer for 2026-04-08 is exactly the rowset stamped 2026-04-08.

ColumnValueWatchMeaningImplication
score_dateExact publication date✅Snapshot date for the constituent row.PIT retrieval is a simple date equality filter.
index_weightFractional weightDependsPublished constituent weight for the snapshot.Should be validated in aggregate before publication.
composite_rankLower is stronger in this modelDependsRelative ranking inside the snapshot.Use only within the same score_date and _index population.

Track one constituent across published snapshots

SELECT
    score_date,
    symbol,
    index_weight,
    composite_score,
    composite_rank
FROM gold.scores_daily
WHERE _index = 'euro_stoxx_50'
  AND symbol = 'ASML.AS'
ORDER BY score_date DESC;
score_datesymbolindex_weightcomposite_scorecomposite_rank
2026-04-08ASML.AS0.088645260014847290.01559812028353261628
2026-03-12ASML.AS0.092032401215200900.1761043235028200019
2026-03-07ASML.AS0.088957997704891170.2051431107976499417
2026-03-04ASML.AS0.091156872325552350.2982340119335741811

This is what a trustworthy PIT history looks like on a snapshot fact table: the business key stays constant, the date grain is explicit, and the historically published values remain queryable without reconstructing intervals.

Bi-Temporal Model

Snapshot facts answer “what was published on date X?” Valid-time rows answer “what was true for the business date?” Bi-temporal modeling answers the harder audit question: “what did the system know at publication time, before later corrections arrived?”

Snapshots lose correction history

If a later correction can change a previously published weight, score, or classification, a plain snapshot fact is not enough to reproduce the original decision path.

[!success] Separate valid time from system time

Store transaction-time history separately from business-validity dates. In SQL Server, system-versioned temporal tables are the cleanest built-in way to retain the earlier row version automatically.

Disposable system-versioned demo

Example

The following demo uses a disposable table in dbo so the note can show real FOR SYSTEM_TIME output without mutating production tables. It demonstrates a correction to one published weight for ASML.AS.

Demo DDL still mutates the database

These commands create and update demo objects in stoxx. They are safe for a lab or documentation workflow, but they are still DDL and DML. Do not run them blindly in shared environments without agreeing on naming, retention, and cleanup.

[!success] Keep demos isolated

Use a dedicated demo table when teaching temporal behavior. Keep production temporal tables focused on real audited entities, not documentation experiments.

[!info]-

This cleanup batch removes any previous copy of the demo table.

  • Temporal tables cannot be dropped while SYSTEM_VERSIONING = ON.
  • The script first turns system versioning off if the table exists.
  • It then drops the history table and current table in the correct order.

Remove any previous copy of the disposable temporal demo.

IF OBJECT_ID('dbo.demo_pit_temporal', 'U') IS NOT NULL
BEGIN
    ALTER TABLE dbo.demo_pit_temporal SET (SYSTEM_VERSIONING = OFF);
    DROP TABLE IF EXISTS dbo.demo_pit_temporal_history;
    DROP TABLE dbo.demo_pit_temporal;
END;
CREATE TABLE dbo.demo_pit_temporal
(
    row_id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    _index varchar(50) NOT NULL,
    symbol varchar(20) NOT NULL,
    weight_pct decimal(18,10) NOT NULL,
    valid_from date NOT NULL,
    valid_to date NOT NULL,
    sys_start datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
    sys_end datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (sys_start, sys_end)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.demo_pit_temporal_history));
INSERT INTO dbo.demo_pit_temporal (_index, symbol, weight_pct, valid_from, valid_to)
VALUES ('euro_stoxx_50', 'ASML.AS', 0.0911568723, '2026-03-04', '9999-12-31');
 
WAITFOR DELAY '00:00:01';
 
UPDATE dbo.demo_pit_temporal
SET weight_pct = 0.0886452600
WHERE _index = 'euro_stoxx_50'
  AND symbol = 'ASML.AS';

Verify that SQL Server registered the table as temporal

SELECT
    t.name AS table_name,
    t.temporal_type_desc,
    OBJECT_NAME(t.history_table_id) AS history_table_name
FROM sys.tables AS t
WHERE t.name IN ('demo_pit_temporal', 'demo_pit_temporal_history')
ORDER BY t.name;
table_nametemporal_type_deschistory_table_name
demo_pit_temporalSYSTEM_VERSIONED_TEMPORAL_TABLEdemo_pit_temporal_history
demo_pit_temporal_historyHISTORY_TABLENULL

SQL Server registered the current table and its history table correctly. At this point, updates against the current table automatically preserve the previous row version in the history table.

ColumnValueWatchMeaningImplication
temporal_type_descSYSTEM_VERSIONED_TEMPORAL_TABLE✅ on the current tableSQL Server is maintaining transaction-time history automatically.FOR SYSTEM_TIME queries are valid.
temporal_type_descHISTORY_TABLE✅ on the history tableTable stores prior row versions.Do not treat it as the application-facing current surface.
history_table_nameNon-NULL on current table✅Current table is linked to a history table.Version retention is configured.

Read both row versions with FOR SYSTEM_TIME ALL

SELECT
    _index,
    symbol,
    CAST(weight_pct AS decimal(18,10)) AS weight_pct,
    valid_from,
    valid_to,
    sys_start,
    sys_end
FROM dbo.demo_pit_temporal
FOR SYSTEM_TIME ALL
ORDER BY sys_start;
_indexsymbolweight_pctvalid_fromvalid_tosys_startsys_end
euro_stoxx_50ASML.AS0.09115687232026-03-049999-12-312026-04-08 14:34:29.87566652026-04-08 14:34:30.8821615
euro_stoxx_50ASML.AS0.08864526002026-03-049999-12-312026-04-08 14:34:30.88216159999-12-31 23:59:59.9999999

The valid-time meaning of the row did not change: it still applies from 2026-03-04 forward. What changed is transaction time. SQL Server preserved both versions, so the correction is auditable rather than destructive.

ColumnValueWatchMeaningImplication
valid_from / valid_toSame across both versionsDependsBusiness-valid interval stayed constant.This was a correction to the recorded content, not a change in business-valid range.
sys_startLater timestamp on the corrected row✅Marks when the corrected version became current in the database.Distinguishes original publication-time knowledge from later knowledge.
sys_endOpen-ended sentinel on current row✅Current version is still active.Closed historical rows should have a real end timestamp.

Ask what the system knew before the correction

DECLARE @as_of_before_update datetime2(7);
 
SELECT @as_of_before_update = DATEADD(NANOSECOND, -100, MAX(sys_end))
FROM dbo.demo_pit_temporal_history
WHERE symbol = 'ASML.AS';
 
SELECT
    @as_of_before_update AS as_of_utc,
    t._index,
    t.symbol,
    CAST(t.weight_pct AS decimal(18,10)) AS weight_pct,
    t.valid_from,
    t.valid_to
FROM dbo.demo_pit_temporal
FOR SYSTEM_TIME AS OF @as_of_before_update AS t
WHERE t.symbol = 'ASML.AS';
as_of_utc_indexsymbolweight_pctvalid_fromvalid_to
2026-04-08 14:34:30.8821614euro_stoxx_50ASML.AS0.09115687232026-03-049999-12-31

This is the audit answer snapshot facts alone cannot provide. The business-valid date is still 2026-03-04, but the transaction-time question “what did we know before the correction?” returns the original weight 0.0911568723.

Weight Normalization

Weight totals are not advisory in index pipelines. They are a publication gate.

The live gold.scores_daily table stores index_weight as FLOAT, which is common in exploratory or scoring-oriented surfaces, but not ideal for final auditable weight control. For validation, cast to DECIMAL, compute the deviation explicitly, and gate the output with a tolerance that is strict enough for the business rule.

Float equality is not publication proof

Never compare SUM(index_weight) = 1.0 directly on FLOAT data and call the result “exact”. Binary floating-point is not a publication-grade proof of weight closure.

[!success] Validate weights in fixed-point

Cast to DECIMAL, compute the deviation from 1.000000000000, and make the pass or fail decision explicit in the output that the pipeline reviews.

Validate weight closure across every loaded snapshot

SELECT
    score_date,
    _index,
    COUNT(*) AS constituent_count,
    CAST(SUM(CAST(index_weight AS decimal(20,12))) AS decimal(20,12)) AS weight_sum,
    CAST(
        ABS(
            SUM(CAST(index_weight AS decimal(20,12)))
            - CAST(1.000000000000 AS decimal(20,12))
        ) AS decimal(20,12)
    ) AS deviation,
    CASE
        WHEN ABS(
            SUM(CAST(index_weight AS decimal(20,12)))
            - CAST(1.000000000000 AS decimal(20,12))
        ) <= 0.000000001000
        THEN 'PASS'
        ELSE 'FAIL'
    END AS weight_check
FROM gold.scores_daily
WHERE index_weight IS NOT NULL
GROUP BY score_date, _index
ORDER BY score_date DESC, _index;
score_date_indexconstituent_countweight_sumdeviationweight_check
2026-04-08euro_stoxx_50500.9999999999990.000000000001PASS
2026-04-08oil_20190.9999999999960.000000000004PASS
2026-04-08stoxx_asia_50500.9999999999980.000000000002PASS
2026-04-08stoxx_usa_50500.9999999999970.000000000003PASS
2026-03-12euro_stoxx_50500.9999999999990.000000000001PASS
2026-03-12oil_20191.0000000000010.000000000001PASS
2026-03-12stoxx_asia_50500.9999999999950.000000000005PASS
2026-03-12stoxx_usa_50501.0000000000020.000000000002PASS
2026-03-07euro_stoxx_50501.0000000000010.000000000001PASS
2026-03-07stoxx_asia_50501.0000000000010.000000000001PASS
2026-03-07stoxx_usa_50501.0000000000010.000000000001PASS
2026-03-04euro_stoxx_50490.9999999999990.000000000001PASS
2026-03-04stoxx_asia_50501.0000000000030.000000000003PASS
2026-03-04stoxx_usa_50480.9121532432920.087846756708FAIL

Most loaded snapshots close within the chosen tolerance after decimal casting. One rowset does not: stoxx_usa_50 on 2026-03-04 is materially incomplete, with a summed weight of only 0.912153243292. That is not a rounding issue. It is a publication-blocking integrity failure.

ColumnValueWatchMeaningImplication
weight_sumWithin a tiny tolerance of 1.000000000000✅Aggregate weight closes properly after fixed-point validation.Snapshot can move to the next integrity checks.
weight_sumMaterially below or above 1.0❌Missing rows, duplicated rows, or broken normalization logic.Halt publication and investigate the load or scoring step.
deviationTiny residual such as 0.000000000001✅Residual from float storage converted to decimal validation.Usually acceptable if the business tolerance explicitly allows it.
deviationLarge residual such as 0.087846756708❌Real business defect, not precision noise.Indicates missing or malformed constituent weights.
weight_checkPASS✅Snapshot satisfies the configured tolerance.Keep auditing other invariants.
weight_checkFAIL❌Snapshot violates the publication gate.Stop downstream publication or index-level calculation.

Performance Tuning for Large-Scale Joins

PIT joins are expensive when the query shape does not respect the data grain. In stoxx, one recurring pattern is joining daily scores to the latest quarterly record known on or before the daily date.

Align quarterly rows to a daily snapshot with OUTER APPLY

SELECT TOP (10)
    d.symbol,
    d.current_price,
    qa.as_of_date AS matched_quarter_end,
    qa.overall_risk
FROM gold.scores_daily AS d
OUTER APPLY (
    SELECT TOP (1)
        q.as_of_date,
        q.overall_risk
    FROM silver.signals_quarterly AS q
    WHERE q._index = d._index
      AND q.symbol = d.symbol
      AND q.as_of_date <= d.score_date
    ORDER BY q.as_of_date DESC
) AS qa
WHERE d._index = 'euro_stoxx_50'
  AND d.score_date = '2026-04-08'
ORDER BY d.symbol;
symbolcurrent_pricematched_quarter_endoverall_risk
ABI.BR61.6200000000000002025-12-317
AD.AS41.6900000000000002025-12-281
ADS.DE130.849999999999992025-12-317
ADYEN.AS844.200000000000052025-12-312
AI.PA181.500000000000002025-12-312
AIR.PA162.620000000000002025-12-311
ALV.DE367.199999999999992025-12-317
ARGX.BR648.600000000000022025-12-314
ASML.AS1113.80000000000002025-12-311
BAS.DE51.9300000000000002025-12-312

This is the correct temporal join shape for a daily-to-quarterly PIT alignment. The quarter used for each symbol is explicit in the output, so the join is auditable as well as performant.

ColumnValueWatchMeaningImplication
matched_quarter_endRecent quarter on or before the daily date✅The as-of join found the correct prior quarterly row.Daily score can inherit quarterly attributes without look-ahead bias.
matched_quarter_endNULL❌No quarterly row exists on or before the daily date.The daily surface is missing required historical context.
overall_riskDomain score from the matched quarterDependsQuarter-level risk classification in effect for the join.Use only after confirming the quarter selection logic is correct.

Check alignment coverage across all loaded euro_stoxx_50 daily snapshots

WITH aligned AS (
    SELECT
        d.score_date,
        d.symbol,
        qa.as_of_date
    FROM gold.scores_daily AS d
    OUTER APPLY (
        SELECT TOP (1)
            q.as_of_date
        FROM silver.signals_quarterly AS q
        WHERE q._index = d._index
          AND q.symbol = d.symbol
          AND q.as_of_date <= d.score_date
        ORDER BY q.as_of_date DESC
    ) AS qa
    WHERE d._index = 'euro_stoxx_50'
)
SELECT
    score_date,
    COUNT(*) AS daily_rows,
    SUM(CASE WHEN as_of_date IS NULL THEN 1 ELSE 0 END) AS missing_quarterly_match_rows,
    MIN(as_of_date) AS oldest_quarter_used,
    MAX(as_of_date) AS newest_quarter_used
FROM aligned
GROUP BY score_date
ORDER BY score_date DESC;
score_datedaily_rowsmissing_quarterly_match_rowsoldest_quarter_usednewest_quarter_used
2026-04-085002025-09-302026-01-31
2026-03-125002025-09-302026-01-31
2026-03-075002025-09-302026-01-31
2026-03-044902025-09-302026-01-31

All currently loaded euro_stoxx_50 daily rows have a valid quarterly predecessor. The quarter range also shows that different symbols can legitimately resolve to different quarter ends on the same daily snapshot, which is normal when reporting calendars differ by company.

ColumnValueWatchMeaningImplication
missing_quarterly_match_rows = 0No gaps✅Every daily row found a qualifying quarterly row.The as-of join is complete for the loaded snapshots.
missing_quarterly_match_rows > 0Gap exists❌Some daily rows have no valid quarterly predecessor.Join output is incomplete and potentially biased.
oldest_quarter_used / newest_quarter_usedReasonable spreadDependsDifferent issuers can map to different reported quarter ends.Normal if the domain supports staggered reporting calendars.

Inspect the actual index surface on the PIT join tables

SET QUOTED_IDENTIFIER ON;
 
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    i.type_desc,
    i.is_primary_key,
    i.is_unique,
    STUFF((
        SELECT ', ' + c2.name
        FROM sys.index_columns AS ic2
        JOIN sys.columns AS c2
          ON ic2.object_id = c2.object_id
         AND ic2.column_id = c2.column_id
        WHERE ic2.object_id = i.object_id
          AND ic2.index_id = i.index_id
          AND ic2.is_included_column = 0
        ORDER BY ic2.key_ordinal
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'), 1, 2, '') AS key_columns,
    NULLIF(STUFF((
        SELECT ', ' + c3.name
        FROM sys.index_columns AS ic3
        JOIN sys.columns AS c3
          ON ic3.object_id = c3.object_id
         AND ic3.column_id = c3.column_id
        WHERE ic3.object_id = i.object_id
          AND ic3.index_id = i.index_id
          AND ic3.is_included_column = 1
        ORDER BY c3.column_id
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'), 1, 2, ''), '') AS include_columns
FROM sys.tables AS t
JOIN sys.schemas AS s
  ON t.schema_id = s.schema_id
JOIN sys.indexes AS i
  ON t.object_id = i.object_id
WHERE s.name IN ('silver', 'gold')
  AND t.name IN ('index_dim', 'signals_quarterly', 'scores_daily')
  AND i.index_id > 0
ORDER BY s.name, t.name, i.index_id;
schema_nametable_nameindex_nametype_descis_primary_keyis_uniquekey_columnsinclude_columns
goldscores_dailyPK__scores_d__3213E83F41C788A9CLUSTERED11idNULL
goldscores_dailyUX_gold_scores_dailyNONCLUSTERED01_index, symbol, score_dateNULL
silverindex_dimPK__index_di__3213E83F590AA69ECLUSTERED11idNULL
silverindex_dimUX_silver_index_dim_currentNONCLUSTERED01_index, symbolNULL
silversignals_quarterlyPK__signals___3213E83FF7A5FF47CLUSTERED11idNULL
silversignals_quarterlyIX_silver_signals_quarterly_symbol_dateNONCLUSTERED01_index, symbol, as_of_dateNULL

The live index surface is good for the daily-to-quarterly as-of join: gold.scores_daily has an exact uniqueness key on (_index, symbol, score_date), and silver.signals_quarterly has the ordered key needed for the TOP (1) ... as_of_date <= score_date pattern. The weak spot is silver.index_dim: its uniqueness index is current-state only, so if the table becomes a true historical PIT source later, it will need a dedicated valid-time access path.

ColumnValueWatchMeaningImplication
type_descCLUSTEREDDependsBase rowstore structure for the table.Often supports the PK, but not always the main PIT predicate.
type_descNONCLUSTEREDDependsSecondary access path.Usually where PIT-specific seek patterns should live.
is_primary_key = 1Primary key indexDependsEnforces table identity.Helpful, but may not match PIT query shape.
is_unique = 1Unique key✅ when aligned to business grainPrevents duplicate keys for the indexed shape.Critical for reliable snapshot and temporal joins.
key_columns = _index, symbol, as_of_dateOrdered temporal key✅Supports the quarterly as-of lookup pattern directly.Good production index for OUTER APPLY TOP (1).
key_columns = _index, symbol onlyCurrent-state keyDependsNo date component in the access path.Insufficient once the table must answer historical interval predicates.

Reconciliation Queries

Reconciliation queries are not optional reporting extras. They are the checks that stop a mathematically plausible but historically wrong publication from moving downstream.

Compare shared snapshot dates across gold surfaces

WITH snapshot_counts AS (
    SELECT
        score_date,
        _index,
        COUNT(*) AS snapshot_constituents
    FROM gold.scores_daily
    GROUP BY score_date, _index
)
SELECT
    p.perf_date,
    p._index,
    p.stocks_count,
    s.snapshot_constituents,
    p.stocks_count - s.snapshot_constituents AS count_diff
FROM gold.index_performance AS p
JOIN snapshot_counts AS s
  ON p.perf_date = s.score_date
 AND p._index = s._index
WHERE p._index = 'euro_stoxx_50'
ORDER BY p.perf_date DESC;
perf_date_indexstocks_countsnapshot_constituentscount_diff
2026-03-12euro_stoxx_5050500
2026-03-04euro_stoxx_5049490

On the dates shared by both gold-layer surfaces, constituent counts reconcile exactly. That does not prove every downstream metric is correct, but it does prove that the aggregate index surface and the constituent snapshot surface agree on the breadth of the index for those dates.

ColumnValueWatchMeaningImplication
count_diff = 0Exact match✅Aggregate and constituent surfaces agree on constituent count.Good cross-table integrity signal.
count_diff <> 0Mismatch❌Gold surfaces disagree about the same business day.Investigate missing rows, stale downstream loads, or divergent filtering logic.

Production rules

RuleWhy it matters
Prefer exact-dated snapshot facts when the business publishes daily constituents or scores.Equality on an explicit date is safer and simpler than reconstructing intervals.
Do not call a table “historical” just because it has valid_from and valid_to columns.The live silver.index_dim data proves that structural columns alone do not create real history.
Keep valid time and transaction time separate.Backfills and corrections must not erase what the system knew at publication time.
Cast float weights to decimal before validation.The current live weight surface uses FLOAT, so the audit gate must normalize the arithmetic surface first.
Gate publication on explicit reconciliation outputs.A pass/fail query is operational; a vague expectation is not.

SQL Server Point-in-Time Data Integrity References

Graph View

Backlinks