How to Compare Source and Target Database Schemas?
In this use case, we will explore how to verify table structures between two different database schemas using iceDQ. This type of testing is crucial for scenarios like migration projects or comparing table structures between staging and data warehouse environments. By following these steps, you can ensure the consistency and integrity of your table structures.
Steps
Below steps have been followed in the video.
Create Rule
To begin, create a reconciliation rule in iceDQ. Give the rule an appropriate name. In this example, we will compare table structures between the "Human Resources" and "Staff" schemas.
Set up Connections
Set up the source connection to the "AdventureWorks" database with the "Human Resources" schema. Utilize the information schema tables to fetch relevant details such as table names, column names, data types, maximum lengths, and constraints.
Similarly, configure the target dataset using the same "AdventureWorks" connection, but this time with the "Staff" schema. Mirror the query structure of the source to retrieve corresponding information from the target schema.
Add Join Condition
Add DiffJoin conditions to establish connections and map the columns based on their respective names. Use the "Add All as Expression" option to include all necessary checks. Focus on table names and column names for comparison.
Publish & Run
Navigate to the checks tab to review the added checks. Remove the checks for table name and column name, as they are part of the joining condition. Publish the rule and execute it.
Review Result
After execution, review the results. In this case, a warning indicates discrepancies in the diffjoin condition for 51 records. By clicking on the instance ID, we can observe missing tables in the target schema and differences in table and column names. These differences highlight areas that require attention and potential structural adjustments.
Video: How to Compare Source and Target Database Schemas?
Conclusion
By utilizing iceDQ's reconciliation rule, we successfully verified the table structures between the "Human Resources" and "Staff" schemas. By identifying discrepancies in table and column names and missing tables, we can ensure consistency and integrity in our database structures. Regular verification of table structures is vital for maintaining data integrity and facilitating seamless data migrations or comparisons between different environments.