Scope

This test set is designed to test the loading of Satellites.

Although Satellites depend on correct loading of related Hubs and Links, that is treated as a different test set.

For purposes of this test, All related Hubs and Links need to be correctly pre populated.

Only 1 instance of each Key must be present in the Source Table.

Definitions

The Driving Keys of the Satellite are used to detect Inserts, Updates and Deletes.

An Insert Occurs when a new key is detected, or when the most recent instance of the key is in a Deleted State.

An Update Occurs when a Current instance of a Key is detected and where the payload differs from that of the current instance.

A Delete Occurs when, during a Full load, A key is found with no matching key in the Source Table.

Allowable Data Types:

  1. bigint
  2. binary
  3. bit
  4. char
  5. date
  6. datetime
  7. datetimeoffset
  8. datetime2
  9. decimal
  10. float
  11. int
  12. money
  13. nchar
  14. numeric
  15. nvarchar
  16. real
  17. smalldatetime
  18. smallint
  19. smallmoney
  20. time
  21. tinyint
  22. varbinary
  23. varchar

Functions

The following functions are tested:

  1. Inserts – when a new Key is detected, it is created with:
    1. A Start Date/Time which reflects the current Server Clock Time;
    2. An End DateTime as specified the the Defaults Table;
    3. Includes an exact representation of the source, for all columns, which have been specified in the Configuration, excluding those columns marked as Discarded or Deleted;
    4. The row is marked as Current
    5. If one of the Payload Columns has been marked as a Source Date, then the Source date for the row should reflect that value. If not, the Server Clock Time is used.
    6. The test should include examples of all allowable data types;
    7. The test should include some collations which are different from the default on the SQL Instance;
  2. Updates – when an existing key is found,
    1. The current instance of the key is End Dated, using the current Server Clock Time;
    2. The Current Row indicator is unset;
    3. A new Row is created with a Start Date/Time exactly the same as the End Date/Time of the prior row;
    4. The End Date of the new Row is the one specified in the Defaults Table
    5. The row is marked as Current;
    6. If one of the Payload Columns has been marked as a Source Date, then the Source date for the row should reflect that value. If not, the Server Clock Time is used.
    7. The test should include examples of all allowable data types;
    8. The test should include some collations which are different from the default on the SQL Instance;
  3. Deletes – When a key is not present in a Full Source load:
    1. The current instance of the key is End Dated, using the current Server Clock Time;
    2. The Current Row indicator is unset;
    3. A new Row is created with a Start Date/Time exactly the same as the End Date/Time of the prior row;
    4. The End Date of the new Row is the one specified in the Defaults Table
    5. The row is marked as being a ‘Tombstone’;
    6. If one of the Payload Columns has been marked as a Source Date, then the Source date for the row should reflect that value. If not, the Server Clock Time is used.

Test Scheme

In order to achieve a thorough test of the “Load Satellite” feature-

Arrange:

  1. Create 3 Source Tables ST1, ST2 and ST3;
  2. ST1 and ST2 should each have a column for each allowable Data Type, together with a Primary Key;
  3. ST1 Config should load all available Columns;
  4. ST1 should be set to be a “Full” Load;
  5. ST2 should have 2 Discard Columns and 1 Deleted Column;
  6. ST2 should be designed to make use of one of the Payload Columns as a “Load Date/Time”;
  7. ST2 should be set to be a “Delta” load;
  8. ST3 should consist of a pair of columns, which represent a key link between ST1 and ST2;
  9. ST3 should be set to be a “Delta” load;
  10. Create the necessary Configuration to be able to load ST1 and ST2 as Hub / Satellite combinations;
  11. Create the necessary Configuration Data to be able to load ST3 as a Link with 2 Satellites, 1 for each column;
  12. Populate the “High Date” default.

Act:

  1. Create Key1 in ST1 with all columns populated;
  2. Create Key2 in ST1 where all columns except for the key columns are Null;
  3. Create Key1 in ST2 where all columns are populated. Ensure that the column being used as “Load Date/Time” has a valid date/time value;
  4. Create Key2 in ST2 with all columns, except for keys, set to Null Values;
  5. Create a Key Combination in ST3, with a cartesian product of ST1 and ST2 keys;
  6. Run1: load all 3 Tables;
  7. Update Key1 in ST1 to make all Columns NULL;
  8. Update Key2 in ST1 to provide a value for each Column;
  9. Change the value of each column of Key1 in ST2;
  10. Run2: load all 3 tables;
  11. Delete Key1 in all 3 tables;
  12. Run3: load all 3 tables;
  13. Replace Key1 in all 3 tables, with new values in the Payload;
  14. Run4: load all 3 tables.

Assert:

  1. ST1 Key1:
    1. Row1 should be fully populated.
      1. The Load Date/Time should be the Server Clock Time as at Run1;
      2. The Row Start Date/Time Server Clock Time as at Run1;
      3. The Row End Date/Time should be the Server Clock Time as at Run 2;
      4. The Current Row indicator should be False;
      5. The Payload should match the Run1 Source.
    2. Row2 should be populated with Nulls.
      1. The Load Date/Time should be the Server Clock Time, as should the Row Start Date/Time as at Run2;
      2. The Row Start Date Should be exactly the same as the Row End Date/Time of Row1;
      3. The End Date should be the Server Clock Time as at Run3;
      4. The Current Row indicator should be false;
      5. The Payload should match Run2 source.
    3. Row3 Should be a Tombstone Row.
      1. The Load Date/Time should be the Server Clock Time as at Run3
      2. The Start Date/Times should be Server Clock Time as at Run3
      3. The End Date/Time should be Server Clock Time as at Run3
      4. The Current Row indicator should be False
      5. The Payload should be all Nulls.
    4. Row4 should be fully populated.
      1. The Load Date should be the Server Clock Time as at Run4;
      2. The Row Start Date/Time should be the Server Clock Time as at Run4;
      3. The Row End Date/Time should be the High Date as specified in the Defaults Table;
      4. The Current Row Indicator should be True;
      5. The Payload should Match Run4.
  2. ST2 Key1
    1.  Row1 should be fully populated.
      1. The Load DateTime should be the value of the designated Load Date Column as at Run1;
      2. The Row Start Date/Time Server Clock Time as at Run1;
      3. The Row End Date/Time should be the Server Clock Time as at Run 2;
      4. The Current Row indicator should be False;
      5. The Payload should be fully populated and match Run1, excluding those columns excluded as Discarded or Deleted.
    2. Row2 should be populated with Nulls.
      1. The Load Date should be the value of the designated Load Date Column as at Run2;
      2. The Row Start Date/Time should be an exact match with the Row End Date/Time of Row1;
      3. The Row End Date/Time should be the Server Clock Time as at Run4;
      4. The Current Row indicator should be False;
      5. The Payload should be fully populated and match Run2, excluding those columns excluded as Discarded or Deleted.
    3. Row3 should be fully populated.
      1. The Load Date should be the value of the designated Load Date Column as at Run4;
      2. The Row Start Date/Time should be an exact match with the Row End Date/Time of Row2;
      3. The Row End Date/Time should be the High Date as specified in the Defaults Table;
      4. The Current Row Indicator should be True;
      5. The Payload should Match Run4.
  3. ST3 Key1
    1. Row 1 Should be fully Populated across 2 Satellites.
      1. The Load Date/Time should be the Server Clock Time as at Run1;
      2. The Row Start Date/Time Server Clock Time as at Run1;
      3. The Row End Date/Time should be the High Date as specified in the Defaults Table;
      4. The Current Row indicator should be True;
      5. The Payload should match the Run1 Source.

 

Description of the Raw Vault Satellite Load Feature