Test Generation Templates

Note

SQL samples are provided as-is. See disclaimer

The testing checks are generated from the design metadata using Handlebars templates, so a single template produces the test SQL for every applicable data object. Each template selects the first occurrence of each mapping ({{#if @first}}) because, for these checks, only the target data object is needed and it is the same across all mappings in the list.

Referential integrity

Confirms every Satellite row has a matching parent (Hub or Link) record, by counting rows for which no related key exists:

{{#each dataObjectMappings}}
{{#if @first}}

BEGIN
  DECLARE @TestResult VARCHAR(10) = 'Fail';
  DECLARE @TestOutput VARCHAR(MAX);
  DECLARE @Issues INT = 0;
  BEGIN TRY
    SELECT @Issues = COUNT(*)
    FROM [{{targetDataObject.name}}] sat
    WHERE NOT EXISTS
    (
    {{#each relationships}}
      SELECT 1 FROM [{{dataObject.name}}] hub
      WHERE 1=1
      {{#each businessKeyDefinitions}} AND sat.{{surrogateKey}} = hub.{{surrogateKey}}{{/each}}
    {{/each}}
    )
    SET @TestOutput = CONVERT(VARCHAR(10),@Issues)+' issue(s) were found.';
    IF @Issues = 0
      BEGIN
        SET @TestResult = 'Pass'
      END
  END TRY
  BEGIN CATCH
    SET @TestOutput = ERROR_MESSAGE();
    SET @TestResult = 'Fail'
  END CATCH

  SELECT @TestOutput AS [OUTPUT], @TestResult AS [RESULT];
END
{{/if}}
{{/each}}

Temporal consistency

Detects gaps and overlaps between consecutive time ranges for the same key, by comparing each record’s timestamp with the previous one:

{{#each dataObjectMappings}}
{{#if @first}}

BEGIN
  DECLARE @TestResult VARCHAR(10) = 'Fail';
  DECLARE @TestOutput VARCHAR(MAX);
  DECLARE @Issues INT = 0;
  BEGIN TRY
    WITH OrderedRanges AS
    (
      SELECT
        {{#each businessKeyDefinitions}}{{surrogateKey}},{{/each}}
        {{inscriptionTimeStampColumn}},
        {{inscriptionTimeStampBeforeColumn}},
        LAG({{inscriptionTimeStampBeforeColumn}}) OVER (
          PARTITION BY {{#each businessKeyDefinitions}}{{surrogateKey}}{{/each}}
          ORDER BY {{#each businessKeyDefinitions}}{{surrogateKey}}, {{/each}}
          {{inscriptionTimeStampColumn}})
          AS PREVIOUS_{{inscriptionTimeStampBeforeColumn}}
      FROM [{{targetDataObject.name}}] sat
    )
    ,Evaluation AS
    (
      SELECT
        {{#each businessKeyDefinitions}}{{surrogateKey}},{{/each}}
        {{inscriptionTimeStampColumn}},
        {{inscriptionTimeStampBeforeColumn}},
        PREVIOUS_{{inscriptionTimeStampBeforeColumn}},
        CASE
          WHEN PREVIOUS_{{inscriptionTimeStampBeforeColumn}} IS NOT NULL
            AND {{inscriptionTimeStampColumn}} >
                   PREVIOUS_{{inscriptionTimeStampBeforeColumn}} THEN 'Gap'
          WHEN PREVIOUS_{{inscriptionTimeStampBeforeColumn}} IS NOT NULL
            AND {{inscriptionTimeStampColumn}} <
                   PREVIOUS_{{inscriptionTimeStampBeforeColumn}} THEN 'Overlap'
          ELSE 'No Issue'
        END AS TEMPORAL_INCONSISTENCY_ISSUE
      FROM OrderedRanges
      WHERE
        (PREVIOUS_{{inscriptionTimeStampBeforeColumn}} IS NOT NULL
          AND {{inscriptionTimeStampColumn}} >
                 PREVIOUS_{{inscriptionTimeStampBeforeColumn}} )
        OR
        (PREVIOUS_{{inscriptionTimeStampBeforeColumn}} IS NOT NULL
          AND {{inscriptionTimeStampColumn}} <
                 PREVIOUS_{{inscriptionTimeStampBeforeColumn}} )
    )
    SELECT @Issues = COUNT(*)
    FROM Evaluation
    SET @TestOutput = CONVERT(VARCHAR(10),@Issues)+' issue(s) were found.';
    IF @Issues = 0
      BEGIN
        SET @TestResult = 'Pass'
      END
  END TRY
  BEGIN CATCH
    SET @TestOutput = ERROR_MESSAGE();
    SET @TestResult = 'Fail'
  END CATCH

  SELECT @TestOutput AS [OUTPUT], @TestResult AS [RESULT];
END
{{/if}}
{{/each}}