BroadOakData.UK

Reconciliation between source and target tables

—

by

broadoakdata
in

MySQL INFORMATION_SCHEMA database offers useful statistics for basic data reconciliation.

SELECT 
tbl.TABLE_NAME,
cols.ORDINAL_POSITION,
tbl.TABLE_ROWS,
cols.COLUMN_NAME,
ROUND(((tbl.DATA_LENGTH + tbl.INDEX_LENGTH) / 1024 / 1024), 2) AS SizeMB,
tbl.CREATE_TIME,
tbl.UPDATE_TIME,
cols.COLUMN_TYPE
FROM INFORMATION_SCHEMA.TABLES tbl
INNER JOIN INFORMATION_SCHEMA.COLUMNS cols ON tbl.TABLE_NAME = cols.TABLE_NAME and tbl.TABLE_SCHEMA=cols.TABLE_SCHEMA
WHERE tbl.TABLE_SCHEMA = 'database_schema_name'
ORDER BY cols.TABLE_NAME asc,cols.ordinal_position asc

PDI transformation to check data between source and target tables