Misusing tSQLt: test that table data is correct (historical unchanging fact data)

The following tSQLt test checks the historical unchanging data in a fact table to ensure that it has not been changed unexpectedly as part of the data warehouse update.

This test compares data between the staging database (DatabaseName_stg) & the live data warehouse (DatabaseName) which have the same or similar schema. For performance purposes, this is a relatively high-level test so the data is aggregated by a common dimension (e.g. Date).

CREATE PROCEDURE testFacts.[test that TableName data is correct]
AS
BEGIN

    IF OBJECT_ID('actual') IS NOT NULL DROP TABLE actual;
    IF OBJECT_ID('expected') IS NOT NULL DROP TABLE expected;

    -- Update DatabaseName, SchemaName & TableName to the appropriate names for the object that is to be tested

    -- Expected records from DatabaseName (aggregate: Date)
    SELECT d.Date AS StartDate
        ,COUNT(*) AS Count
        ,SUM(f.Value) AS Sum
    INTO expected
    FROM DatabaseName.SchemaName.TableName f
    INNER JOIN DatabaseName.SchemaName.dimDate d ON f.StartDateId = d.DateId
    GROUP BY d.Date;

    -- Actual records from DatabaseName_stg that existed before the last import
    -- i.e. where the VersionStartDate is earlier than or equal to the maximum VersionStartDate in DatabaseName
    SELECT d.Date AS StartDate
        ,COUNT(*) AS Count
        ,SUM(f.Value) AS Sum
    INTO actual
    FROM DatabaseName_stg.SchemaName.TableName f
    INNER JOIN DatabaseName_stg.SchemaName.dimDate d ON f.StartDateId = d.DateId
    WHERE f.VersionStartDate <= (
        SELECT MAX(g.VersionStartDate) AS MaxVersionStartDate
        FROM DatabaseName.SchemaName.TableName g
    )
    GROUP BY d.Date;

    EXEC tSQLt.AssertEqualsTable 
        @Expected = 'expected',
        @Actual = 'actual';

END

See Misusing tSQLt for more info.