SCD2 with a SHA-256 Fingerprint

Part 3 ended on a question it did not answer: when a dimension changes, how does the trigger decide whether the facts actually need rebuilding? A discount edited from 100.00 to 100.00 should cost nothing. A discount edited to 150.00 should rebuild everything downstream. KC Star answers this with a fingerprint — a SHA-256 hash of the sale's entire current dimension state — and this post is how that fingerprint is computed, stored, and (in V1) accidentally computed twice.

Hashing a sale's state

The real fingerprint function is CalculateVersionHash. It gathers every current dimension row for a sale, concatenates them in a stable order, and hashes the result with pgcrypto's digest:

CREATE OR REPLACE FUNCTION CalculateVersionHash(p_SalesReferenceId INT)
RETURNS BYTEA AS $$
DECLARE
    v_concatenated TEXT;
BEGIN
    SELECT STRING_AGG(DimTypeId || ':' || DimValue, '|' ORDER BY DimTypeId)
    INTO v_concatenated
    FROM SalesDim
    WHERE SalesReferenceId = p_SalesReferenceId AND IsCurrent = TRUE;

    RETURN digest(v_concatenated, 'sha256');
END;
$$ LANGUAGE plpgsql;

Two details make this work. The ORDER BY DimTypeId inside STRING_AGG guarantees the same set of dimension values always produces the same string, regardless of physical row order — without it, the hash would be non-deterministic and useless. And prefixing each value with DimTypeId ||':' means ProductPrice = 12 and ProductCount = 12 do not collide into the same contribution. The output is a 32-byte BYTEA, stored in the VersionHash column on both SalesDim and SalesFact.

The comparison itself is trivial: recompute the hash, compare it to the one cached in CurrentSalesVersion, and only rebuild facts if they differ.

CREATE TABLE CurrentSalesVersion (
    SalesReferenceId INT PRIMARY KEY,
    CurrentVersionHash BYTEA,
    UpdatedDate TIMESTAMP
);

That table is the whole change-detection cache — one row per sale, holding the fingerprint of its current state. When the SCD2 trigger runs, it recomputes, compares against this row, and short-circuits when nothing meaningful moved.

The two hashes problem

Here is the V1 wrinkle I promised in part 1. There are two different hashes in circulation, and they do not agree.

The insert trigger from part 3 computes a quick-and-dirty fingerprint at insert time:

v_hash := digest(CONCAT(NEW.ProductId, '|', NEW.CustomerId), 'sha256');

That hash sees only ProductId and CustomerId — two of the seven dimensions. Meanwhile CalculateVersionHash, used on every subsequent update, hashes all seven current dimensions in sorted, typed form. So the fingerprint a row is born with is not the fingerprint it will be judged against the first time it changes.

In V1 this is harmless — the first SCD2 update simply recomputes the real hash and overwrites the placeholder — but it is exactly the kind of quiet inconsistency that compounds. It means the born-hash cannot be trusted for change detection, only the recomputed one can, and nothing in the schema tells you which is which. I flagged it rather than fixed it, because the fix belongs with a broader rethink of what goes into the hash. By V4 the hash folds in the data-quality score; by V9 it folds in the employee and permission context. The hash grew into the version's identity function, and V1 is where I first noticed it wanted to.

The same timestamps do time travel

The beautiful thing about SCD2-everywhere is that history is not a separate mechanism — it is the absence of a filter. Every current dimension row expires at the far-future sentinel '9999-12-31 23:59:59'; every historical row has a real ExpiryDate. So “what did this sale look like on a given morning” is a query, not a reconstruction:

CREATE OR REPLACE FUNCTION GetSalesStateAtTime(
    p_SalesReferenceId INT,
    p_AsOfDate TIMESTAMP
)
RETURNS TABLE (...) AS $$
BEGIN
    RETURN QUERY
    SELECT ...
    FROM SalesDim d
    JOIN DimTypeLookup dt ON d.DimTypeId = dt.DimTypeId
    WHERE d.SalesReferenceId = p_SalesReferenceId
      AND d.EffectiveDate <= p_AsOfDate
      AND d.ExpiryDate    >  p_AsOfDate
    -- ...pivot, join facts on ValidFrom/ValidTo the same way...
    ;
END;
$$ LANGUAGE plpgsql;

The dimension predicate EffectiveDate <= AsOfDate AND ExpiryDate > AsOfDate selects exactly the rows that were current at that instant. Facts get the identical treatment through their ValidFrom/ValidTo window. Pivot the result and you have the sale as it stood, measures and all. Calling it looks like this:

SELECT * FROM GetSalesStateAtTime(4, '2023-05-04 10:00:00'::TIMESTAMP);

No snapshot tables. No event-log replay. The CurrentSalesState view from part 2 is just this function with the timestamp pinned to now — swap IsCurrent = TRUE for a point-in-time predicate and current-state reads become historical reads with no new machinery.

Why hash at all

You could ask why bother hashing when you could just diff the dimension rows directly. The answer is that the hash makes change detection cheap and uniform. Comparing two 32-byte values is one operation regardless of how many dimensions a sale has, and it gives you a single stable identity for a sale's entire state — which turns out to be exactly what you want to key a fact rebuild on, cache in CurrentSalesVersion, and later stamp onto every derived row so you can trace which fact came from which state.

The hash is also where the version boundaries become legible. Watching what each version decides to fold into the fingerprint — SaleType in V2, quality in V4, permissions in V9 — is a compact way to read what each version thought a sale's identity actually was. That is the story the rest of the series tells.

V1 runs on PostgreSQL. But the design is not PostgreSQL-specific, and proving that meant porting the whole thing to SQL Server — where digest becomes HASHBYTES and the triggers stop being row-level. That port, and what it taught me about both engines, closes the V1 series next.