Validate data migrated from other systems

This page explains how to use dbSum.yaml to validate data migrated from external systems by comparing aggregated checksums.

In scenarios where historical data from an existing system is migrated and transformed into a KX Sensors database, you can use dbSum.yaml as a tool for validation of the migration process. It is intended to help ensure data migration did not introduce any errors in the data, and that the migrated dataset is complete and accurate. dbSum.yaml aggregates aspects of the data based on configurable parameters and outputs this to a CSV file. This CSV file can then be compared to similarly summarized data aggregated from the original source tables to confirm a match.

Migrating historical data from an existing system involves two configuration parameter tables: dbSum.yaml and dbSumParams.yaml.

dbSumParams.yaml

In this configuration object, you define the initial settings for aggregations as well as the basic formatting and display of the validated tables from the script. It can be overridden as required.

dbSumParams:
  values:
    epoch:
      value: 2010.01.01D
      type: timestamp
      description: Base epoch for timestamp aggregation

    tsUnit:
      value: seconds
      type: symbol
      description: Timestamp precision for aggregation

    outputDelim:
      value: ","
      type: string
      description: Delimiter for aggregation output file

    floatPoint:
      value: 6
      type: short
      description: Floating point precision for float aggregation

    decimalDisp:
      value: 6
      type: short
      description: Decimal precision to display

    libraries:
      type: symbol
      description: Client-specific validation files to load

dbSum.yaml

In this configuration object, you specify the list of tables intended for data validation. Any table that is in the database can be entered into the dbSum.yaml. The parameter offers columns to define grouping, aggregation and temporal constraint clauses for the queries carried out on the tables.

dbSum:
  values:
    Account:
      groupCols: "COMPANY|D:orgID{ORG|Dr}"
      aggCols: "CREATED_TS;MODIFIED_TS:updateTS"
      deletedCol: "isDeleted"

    Reading:
      groupCols: "COMPANY|D:orgID{ORG|Dr};READING etc.,etc."
      aggCols: "READING:val{;fmt};CREATE_TS;update_TS"
      deletedCol: "intFlags{.bits.testbnz[:.flags.intRead.etc.,etc."

Column Name	Description	Example
tableName	The name of the table that you wish to validate. Note that same table can be listed in more than one row to allow for different validation cases on a single table. As well, the same validation can be carried out on multiple tables by inputting a list of tables in this column.	kxsReading
groupCols, aggCols	The column(s) that you wish to group and aggregate by.	readTS or updateTS
temporalConstraintCol	You can specify a window of time for the validation.	2018.10.01D00:00:00.000000000 2018.10.09D00:00:00.000000000 will examine data for the named table between the 1st of October to the end of the 8th of October 2018
deletedCol	The name of any columns that you wish to exclude from validation because they maintain data that is soft-deleted in the database. This can also be used to omit specific rows based on a value.	isDeleted
outputTableName	The name of the CSV file that will be generated for the table specified in tableName. If you leave outputTableName empty, a file called kxsReading.CSV will be generated based on the parameters defined in that row of the table. If you need to extract a different view of data from a table in question, you can add a new entry for the table and configured it to output a different CSV file. For example, if kxsReading data specific to reading flags was required, a new row would specify the parameters of interest and the outputTableName could be set to kxsReadingFlags.	kxsReading

The format for entering group or aggregation clauses are as follows:

SourceTableColumnName:TargetTableColumnName[pre aggregation function; post aggregation function]

In this case, Source Table would be the source system from which data is being migrated; Target Table would be the KX Sensors table.

When the q code file is initially loaded, a parser function resolves the configuration parameters table and creates a control table. From this, an aggregation function is called that runs a validation query for each table defined. The aggregation function uses the column type to determine which summation aggregation is required (timestamp, float, long). The order that the clauses are entered into each cell dictates the order that they are carried out in a query; therefore, standard q-sql rules apply. If the same aggregations are to be carried out on multiple tables, you can enter them into the same relative tableName cell.

From the parsed control table (dbSum.yaml) you can define pre-and post-functions (i.e., functions to be carried out on columns before or after summing). Also, the aggregation function checks to see whether the table is partitioned or not. If it is, a roll-up query is called, carrying across the summation aggregations across partitions.

When complete, the grouped and aggregated data is saved in a .CSV file using the name that you specify in the outputTableName column. You can set the location of the output file in the dbSumParams.yaml configuration object.

Next steps

• Recover your data — back up your database once a migration has been validated.
• HDB files — where migrated historical data is organized and stored once ingested.