How can I compare flat file and database table?

How can I compare flat file and database table?

4 Answers

1. Load the results from the database on a Map structure where the key is the Primary Key column and the value is the row itself.
2. Iterate the flat file collection, search the Map structure based on the primary key and add the differences to a list that will be your output.

How do you validate a flat file in ETL Testing?

1. Record Count Validation. Compare count of records of the flat file and database table.
2. Column Data Profile Validation.
3. Compare Entire Flat File and Target Data.
4. Automate Flat File Data Completeness Testing using ETL Validator.

How do you validate source and target data in ETL?

Integration testing of the ETL process and the related applications involves the following steps:

1. Setup test data in the source system.
2. Execute ETL process to load the test data into the target.
3. View or process the data in the target system.
4. Validate the data and application functionality that uses the data.

How do you check data if the source is flat file?

The Testing I have done is:

1. Structure validation of the file.
2. Check for duplicate records.
3. Select one row from the target file and for that record alone, run the source query and then compare the Source output and the target record in the flat file manually comparing each field one at a time.
4. Check for data truncation.

How do you validate complete data from the source and target table?

It involves validating full data set in the source and the target tables by using minus query.

1. You need to perform both source minus target and target minus source.
2. If the minus query returns a value, that should be considered as mismatching rows.

How do you manually test ETL?

Eight stages of the ETL testing process

1. Identify business requirements — Design the data model, define business flow, and assess reporting needs based on client expectations.
2. Validate data sources — Perform a data count check and verify that the table and column data type meets specifications of the data model.

How to check a flat file in ETL?

The flat file has millions of records and the source query is joining at least 10 tables. Structure validation of the file. Like name & number of fields, delimiter, naming convention, Header or Trailer records. Check for duplicate records.

How to compare ETL test results with target table?

Apply transformations on the data using SQL or a procedural language such as PLSQL to reflect the ETL transformation logic. Compare the results of the transformed test data with the data in the target table. The advantage with this approach is that the test can be rerun easily on a larger source data.

What kind of queries are used for ETL testing?

The data that needs to be tested is in heterogeneous data sources (eg. databases, flat files). Data is often transformed which might require complex SQL queries for comparing the data. ETL testing is very much dependent on the availability of test data with different test scenarios. If playback doesn’t begin shortly, try restarting your device.

How to test ETL with an empty database?

Start with an empty database, insert to source for your first test case, run the ETL and then compare the file to what you expected it to be. Once you feel good about the basic functionality on a small scale you can expand your testing to larger datasets.